1 Introduction
1.1 Introduction
Oracle Redo corruption is divided into four situations: unused status log is corrupted inactive status log is corrupted active status log is corrupted current status log is corrupted. The processing methods for log corruption in different states are different, and will be introduced one by one below.
Two Recovery
2.1 Unused and inactive status logs are corrupted
If this log is inactive, manually perform the clearing operation:
SQL> alter database clear logfile group 2; alter database clear logfile group 2 * The 1 An error occurred in the line: ORA-00350: log 2 (Example orcl 的log, Thread 1) Need to archive ORA-00312: 联机log 2 Thread 1: F:ORACLEPRODUCT10.2.
Perform the following operations:
SQL> alter database clear unarchived logfile group 2;
The database has been changed.
2.2 Active status log is corrupted
If there is an archive, you can just use the archive to recover it directly..
SYS@orcl11g>recover database until cancel; --Specify the time point for recovery(If you don't know，that isuntill cancel)
ORA-00279: change 1763218 generated at 06/24/2021 12:02:00 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_74_816622368.dbf
ORA-00280: change 1763218 for thread 1 is in sequence #74
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/1_74_816622368.dbf
ORA-00279: change 1769094 generated at 06/24/2021 13:34:43 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_75_816622368.dbf
ORA-00280: change 1769094 for thread 1 is in sequence #75
ORA-00278: log file '/u01/app/oracle/arch/1_74_816622368.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl11g/ --Specifycurrentlog
Log applied.
Media recovery complete.
2.3 Current status log is corrupted
Regular situation:
Set hidden parameters:
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SYS@orcl11g> recover database until cancel;
ORA-00279: change 1789650 generated at 06/24/2021 13:40:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_2_818948248.dbf
ORA-00280: change 1789650 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/1_2_818948248.dbf
ORA-00279: change 1789904 generated at 06/24/2021 13:41:02 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_3_818948248.dbf
ORA-00280: change 1789904 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/1_2_818948248.dbf' no longer needed
for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl11g/'
SYS@orcl11g> alter database open resetlogs;
Database altered.
If SCN-related error occurs, ORA-00600 is used to handle it using the following SCN method.
2.3.1 Poke promotes scn repair
1. View the Current SCN of the current database
SYS@orcl> select current_scn||'' from v$database; CURRENT_SCN||'' -------------------------------------------------------------------------------- 4563483988
You can see that the current SCN is 4563483988. I want to promote SCN now, at the 10w level, that is, the red number of 4563483988 is modified to the specified value.
2. Restart the database to the mount stage
SYS@orcl> shutdown abort ORACLE instance shut down. SYS@orcl> startup mount ORACLE instance started. Total System Global Area 1235959808 bytes Fixed Size 2252784 bytes Variable Size 788529168 bytes Database Buffers 436207616 bytes Redo Buffers 8970240 bytes Database mounted.
3. Use oradebug poke to promote SCN
I changed the "4" of 100,000 digits to "9", which is equivalent to pushing about 50w: Note: The experiment found that the SCN value of oradebug poke can be used to specify the hexadecimal 0x11008DE74, or the decimal 4563983988 directly.
SYS@orcl> oradebug setmypid
Statement processed.
SYS@orcl> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SYS@orcl> select to_char(checkpoint_change#, 'XXXXXXXXXXXXXXXX') from v$database;
TO_CHAR(CHECKPOINT_CHANGE#,'XXXXXX
----------------------------------
110013C41
SYS@orcl> oradebug poke 0x06001AE70 8 4563983988
BEFORE: [06001AE70, 06001AE78) = 00000000 00000000
AFTER: [06001AE70, 06001AE78) = 1008DE74 00000001
SYS@orcl> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 1008DE74 00000001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SYS@orcl> alter database open;
Database altered.
SYS@orcl> select current_scn||'' from v$database;
CURRENT_SCN||''
--------------------------------------------------------------------------------
4563984271
It can be seen that SCN has been successfully promoted to 4563983988, and SCN has been growing continuously, so the value found here is slightly larger.
4. Give an example ORA-600[2662] error poke calculation method
A data block SCN is ahead of the current SCN. The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN stored in a UGA variable. If the SCN is less than the dependent SCN then we signal the ORA-600 [2662] internal error. ARGUMENTS: Arg [a] Current SCN WRAP Arg [b] Current SCN BASE Arg [c] dependent SCN WRAP Arg [d] dependent SCN BASE Arg [e] Where present this is the DBA where the dependent SCN came from.
Calculation method:
ORA-00600: internal error code, arguments: [2662], [2], [1424107441], [2], [1424142235], [8388617], [], [] select 2*power(2,32)+1424142235 from dual; 10014076827 ORA-00600: internal error code, arguments: [2662], [2], [1424142249], [2], [1424142302], [8388649], [], [] select 2*power(2,32)+1424143000 from dual; 10014077592
Summary formula: c * power(2,32) + d {+ You can add a little appropriately, but not too big! }
c stands for: Arg [c] dependent SCN WRAP
d stands for: Arg [d] dependent SCN BASE
2.3.2 12c event 21307096 Promoting Scn repair
1. Calculation method
Lowest_scn+event level * 1000000
View the current database SCN:
SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 12796139551520
2. Add event and parameters
alter system set "_allow_resetlogs_corruption"=true scope=spfile; alter system set event='21307096 trace name context forever,level 3' scope=spfile;
3. Start the database
SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1660944384 bytes
Fixed Size 8793448 bytes
Variable Size 889193112 bytes
Database Buffers 754974720 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12796139551734 generated at 04/20/2022 11:13:44 needed for
thread 1
ORA-00289: suggestion :
/app/oracle/product/12.2.0/db_1/dbs/arch1_1_1102504135.dbf
ORA-00280: change 12796139551734 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
12796142552279
SCN successfully promoted 300w
2.3.3 gdb promotes scn repair
Session 1:
Query the currentscn:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2910718245
Query the currentSCNConvert to16The value after the cardinal：
SQL> select to_char(2910718245,'xxxxxxxxxxxx') from dual;
TO_CHAR(29107
-------------
ad7e0925
Query pre-modifiedSCNConvert to16The value after the cardinal，This time, the highest digit will be added to the number of digits
SQL> select to_char(3910718245,'xxxxxxxxxxxx') from dual;
TO_CHAR(39107
-------------
e918d325
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kscn8 kcsgscn_ [060017E98, 060017EA0) = AD7E093B 00000000
It should be noted that 060017E98 is the SCN BASE value, AD7E093B is the current SCN value, which can be understood as 060017E98 is a code name x, and the current x is equal to AD7E093B. When we modify the SCN value later, we need to specify how much the value 060017E98 is equal.
Session 2:
oracle 9824 9730 0 Feb22 ? 00:00:01 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 18621 8636 0 01:18 pts/1 00:00:00 grep --color=auto LOCAL=YES
oracle 20109 20105 0 Feb15 ? 00:00:13 oracletestdb19c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
This test library isorcl，Therefore choose9824
[oracle@redhat19c11 ~]$ gdb $ORACLE_HOME/bin/oracle 9824
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-114.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later </licenses/>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
-------------------------------------------
-------------------------------------------
(gdb) set *((int *) 0x060017E98) = 0xe918d32--->WillSCN BASEModify to the value found just now
(gdb) quit
A debugging session is active.
Inferior 1 [process 9824] will be detached.
Quit anyway? (y or n) y
Detaching from program: /oracle/app/product/19.3.0/db_1/bin/oracle, process 9824
Return session1 query, and the modification is successful:
SQL> select current_scn from v$database; CURRENT_SCN ----------- 3910718287
Restart the database and open the database normally
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2466250400 bytes Fixed Size 9137824 bytes Variable Size 603979776 bytes Database Buffers 1845493760 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. SQL> select current_scn from v$database; CURRENT_SCN ----------- 3910719415
Summarize
The recovery method for Oracle Redo log corruption depends on the status of the log. Logs in Unused and Inactive states can usually be cleared directly; logs in Active states need to be restored in combination with archived logs; logs in Current states are most severely damaged and may require complete recovery based on the latest backup. Rationally configure log management policies, back up the database regularly, and properly handle archived logs, which can effectively reduce the risk of data loss caused by log corruption.
The above is the detailed guide for Oracle Redo log corruption rescue. For more information about Oracle Redo log corruption, please follow my other related articles!