一 介绍
1.1 介绍
Oracle Redo 损坏分三种情况:unused 状态日志损坏 inactive 状态日志损坏 active 状态日志损坏 current 状态日志损坏
二 恢复
与inactive 状态日志损坏
如果这个日志是inactive, 手动执行clearing 操作:SQL> alter database clear logfile group 2;alter database clear logfile group 2* 第 1 行出现错误:ORA-00350: 日志 2 ( 实例 orcl 的日志, 线程 1) 需要归档ORA-00312: 联机日志 2 线程 1:F:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG 执行如下操作:SQL> alter database clear unarchived logfile group 2; 数据库已更改。
2.2 active 状态日志损坏
存在归档直接使用归档恢复即可.SYS@orcl11g>recover database until cancel; -- 指定恢复的时间点( 如果不知道，就是untill cancel)ORA-00279: change 1763218 generated at 06/24/2021 12:02:00 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/arch/1_74_816622368.dbfORA-00280: change 1763218 for thread 1 is in sequence #74Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/arch/1_74_816622368.dbfORA-00279: change 1769094 generated at 06/24/2021 13:34:43 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/arch/1_75_816622368.dbfORA-00280: change 1769094 for thread 1 is in sequence #75ORA-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/redo01.log -- 指定current 日志Log applied.Media recovery complete.
2.3 Current 状态日志损坏
常规情况: 设置隐藏参数: 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 1ORA-00289: suggestion : /u01/app/oracle/arch/1_2_818948248.dbfORA-00280: change 1789650 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/arch/1_2_818948248.dbfORA-00279: change 1789904 generated at 06/24/2021 13:41:02 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/arch/1_3_818948248.dbfORA-00280: change 1789904 for thread 1 is in sequence #3ORA-00278: log file '/u01/app/oracle/arch/1_2_818948248.dbf' no longer neededfor this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/u01/app/oracle/oradata/orcl11g/system01.dbf' SYS@orcl11g> alter database open resetlogs;Database altered.
如若出现与 SCN 相关 ORA-00600 错误使用以下推进 SCN 方式进行处理
推进scn 修复
1. 查看当前数据库的Current SCNSYS@orcl> select current_scn||'' from v$database;CURRENT_SCN||''-------------------------------------------------------------------------------- 4563483988 可以看到当前SCN 是4563 483988 ，我现在想推进SCN ，在10w 级别，也就是4563483988 标红数字修改为指定值。
2. 重新启动数据库到mount 阶段SYS@orcl> shutdown abortORACLE instance shut down.SYS@orcl> startup mountORACLE instance started.Total System Global Area 1235959808 bytesFixed Size 2252784 bytesVariable Size 788529168 bytesDatabase Buffers 436207616 bytesRedo Buffers 8970240 bytesDatabase mounted.
3. 使用oradebug poke 推进SCN 我这里直接把十万位的"4" 改为"9" 了，相当于推进了50w 左右： 说明：实验发现oradebug poke 推进的SCN 值，既可以指定十六进制的0x11008DE74 ，也可以直接指定十进制的4563983988 。SYS@orcl> oradebug setmypidStatement 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 4563983988BEFORE: [06001AE70, 06001AE78) = 00000000 00000000AFTER: [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 可以看到已经成功将SCN 推进到4563983988 ，SCN 不断增长，所以这里查到的值略大一些。
4. 举例ORA-600[2662] 错误下poke 计算方式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.
计算方式:ORA-00600: internal error code, arguments: [2662], [2], [1424107441], [2], [1424142235], [8388617], [], [] select 2*power(2,32)+1424142235 from dual; 10014076827ORA-00600: internal error code, arguments: [2662], [2], [1424142249], [2], [1424142302], [8388649], [], [] select 2*power(2,32)+1424143000 from dual; 10014077592 总结公式：c * power(2,32) + d {+ 可适当加一点，但不要太大！}c 代表：Arg [c] dependent SCN WRAPd 代表：Arg [d] dependent SCN BASE
2.4 12c event 21307096 推进scn 修复
计算方式
Lowest_scn+event level * 1000000 查看当前数据库SCN:SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN)---------------------------------------- 12796139551520 2. 添加event 以及参数alter system set "_allow_resetlogs_corruption"=true scope=spfile;alter system set event='21307096 trace name context forever,level 3' scope=spfile; 3. 启动数据库SQL> shutdown immediate;Database dismounted.ORACLE instance shut down. SQL> startup mount;ORACLE instance started. Total System Global Area 1660944384 bytesFixed Size 8793448 bytesVariable Size 889193112 bytesDatabase Buffers 754974720 bytesRedo Buffers 7983104 bytesDatabase mounted. SQL> recover database using backup controlfile until cancel;ORA-00279: change 12796139551734 generated at 04/20/2022 11:13:44 needed forthread 1ORA-00289: suggestion :/app/oracle/product/12.2.0/db_1/dbs/arch1_1_1102504135.dbfORA-00280: change 12796139551734 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelMedia 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 成功推进300w
推进scn 修复
Session 1: 查询当前scn: SQL> select current_scn from v$database; CURRENT_SCN----------- 2910718245 查询当前SCN 转成16 进制后的值：SQL> select to_char(2910718245,'xxxxxxxxxxxx') from dual;TO_CHAR(29107------------- ad7e0925 查询预修改的 SCN 转换成 16 进制后的值，本次将最高位增加一位数SQL> select to_char(3910718245,'xxxxxxxxxxxx') from dual; TO_CHAR(39107------------- e918d325 SQL> oradebug setmypidStatement processed. SQL> oradebug dumpvar sga kcsgscn_kscn8 kcsgscn_ [060017E98, 060017EA0) = AD7E093B 00000000 需要注意的是，060017E98 是SCN BASE 值，AD7E093B 是当前的SCN 值，可以理解为060017E98 是一个代号x ，当前的x 等于AD7E093B ，待会儿我们修改SCN 值的时候，就会需要指定060017E98 这个值等于多少。 Session 2:[oracle@redhat19c11 ~]$ ps -ef|grep LOCAL=YESoracle 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=YESoracle 20109 20105 0 Feb15 ? 00:00:13 oracletestdb19c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 本次测试库是orcl ，因此选9824[oracle@redhat19c11 ~]$ gdb $ORACLE_HOME/bin/oracle 9824GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-114.el7Copyright (C) 2013 Free Software Foundation, Inc.License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>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---> 将SCN BASE 修改为刚才查出来的值(gdb) quitA debugging session is active. Inferior 1 [process 9824] will be detached.Quit anyway? (y or n) yDetaching from program: /oracle/app/product/19.3.0/db_1/bin/oracle, process 9824 返回session1 查询, 修改成功:SQL> select current_scn from v$database;CURRENT_SCN----------- 3910718287 重启数据库, 也可正常打开数据库SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup;ORACLE instance started. Total System Global Area 2466250400 bytesFixed Size 9137824 bytesVariable Size 603979776 bytesDatabase Buffers 1845493760 bytesRedo Buffers 7639040 bytesDatabase mounted.Database opened.SQL> select current_scn from v$database; CURRENT_SCN----------- 3910719415
