【Oracle】Current online Redo 和 Undo 损坏的处理方法

由于机房断电,导致数据库异常down 机，重启的时候报错： Errors in file /opt/oracle/admin/aliuid/bdump/aliuid_arc1_19960.trc: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 26402 change 0 time 02/14/2012 01:32:17 ORA-00312: online log 1 thread 1: /opt/oracle/oradata/aliuid/redo01.log Mon Feb 20 15:57:00 2012 Errors in file /opt/oracle/admin/aliuid/udump/aliuid_ora_20140.trc: ORA-00600: internal error code, arguments: [2662], [0], [94450252], [0], [94509989], [8388625], [], [] ORA-00354 ,ORA-00353,ORA-00312 报错是由于在线日志块损坏导致！ 针对日志坏块，我使用了 _disable_logging 隐含参数，然后清理了在线日志！ @ alter system set "_disable_logging"=false scope=both; System altered. @ alter database clear unarchived logfile /opt/oracle/oradata/aliuid/redo01.log; Database altered. @ select * from v$log;     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------          1          1          0  104857600          1 YES UNUSED                94410240 2012-02-13 21:35:38          2          1        381  104857600          1 NO  INACTIVE              94430242 2012-02-20 15:35:43          3          1        382  104857600          1 NO  CURRENT               94450247 2012-02-20 15:56:57 @ exit oracle@c9e016r3ectk1xl67j8p:aliuid /home/oracle sqlplus "/as sysdba" @ startup mount; ORACLE instance started. Total System Global Area 3693056168 bytes Fixed Size                   745640 bytes Variable Size             469762048 bytes Database Buffers         3221225472 bytes Redo Buffers                1323008 bytes Database mounted. @ col name for a60 @ select * from v$log;     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------          1          1        383  104857600          1 NO  CURRENT               94470251 2012-02-20 20:12:56          2          1        381  104857600          1 YES INACTIVE              94430242 2012-02-20 15:35:43          3          1        382  104857600          1 YES INACTIVE              94450247 2012-02-20 15:56:57 @ alter database clear unarchived logfile /opt/oracle/oradata/aliuid/redo01.log; alter database clear unarchived logfile /opt/oracle/oradata/aliuid/redo01.log ERROR at line 1: ORA-01624: log 1 needed for crash recovery of thread 1 ORA-00312: online log 1 thread 1: /opt/oracle/oradata/aliuid/redo01.log @ alter database clear unarchived logfile /opt/oracle/oradata/aliuid/redo02.log; Database altered. @ col name for a60 @ select * from v$log;     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------          1          1        383  104857600          1 NO  CURRENT               94470251 2012-02-20 20:12:56          2          1          0  104857600          1 YES UNUSED                94430242 2012-02-20 15:35:43          3          1        382  104857600          1 YES INACTIVE              94450247 2012-02-20 15:56:57 @ alter database clear unarchived logfile /opt/oracle/oradata/aliuid/redo03.log; Database altered. @ alter database open; alter database open ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error oracle@c9e016r3ectk1xl67j8p:aliuid /home/oracle sqlplus "/as sysdba" @ startup mount; ORACLE instance started. Total System Global Area 3693056168 bytes Fixed Size                   745640 bytes Variable Size             469762048 bytes Database Buffers         3221225472 bytes Redo Buffers                1323008 bytes Database mounted. @ col name for a60 @ select * from v$log;     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------          1          1        383  104857600          1 YES INACTIVE              94470251 2012-02-20 20:12:56          2          1        384  104857600          1 NO  CURRENT               94490254 2012-02-20 20:15:36          3          1          0  104857600          1 YES UNUSED                94450247 2012-02-20 15:56:57 ORA-600 [2662] "Block SCN is ahead of Current SCN"，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数，分别代表不同的含义， ORA-600 [2662] [a] [b] [c] [d] [e] 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. 当前的报错是当前的SCN=94450252 小于dependent SCN 94509989！ 文档上介绍 "1.数据库crash后设置了_DISABLE_LOGGING隐含参数 2.硬件错误引起数据库没法写控制文件和重做日志文件 3.错误的部分恢复数据库 4.恢复了控制文件但是没有使用recover database using backup controlfile进行恢复 5.使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库 6.在并行服务器环境中DLM存在问题 " 参考了eygle 的文章 使用ADJUST_SCN事件来调整当前的SCN，使其大于dependent SCN!(然后保证数据库可以全库的导出，然后重建数据库导入数据) @ alter session set events IMMEDIATE trace name ADJUST_SCN level 1;  ERROR: ORA-00600: internal error code, arguments: [kcsadjn1], [], [], [], [], [], [], [] 这一步执行错误，本应该在mount状态执行此命令的！eygle的文章： 增进SCN有两种常用方法: 1.通过immediate trace name方式(在数据库Open状态下) alter session set events IMMEDIATE trace name ADJUST_SCN level x; 2.通过10015事件(在数据库无法打开，mount状态下) alter session set events 10015 trace name adjust_scn level x; 注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整。 本例由于数据库无法打开，只能使用的二种方法。 之后多次shutdown ，startup 之后 scn 的报错消失，出现关于undo的ora-600 [4193] 报错，次错误是和undo表空间相关的报错 “ While backing out an undo record (i.e. at the time of rollback) we found a  transaction id mis-match indicating either a corruption in the rollback   segment or corruption in an object which the rollback segment is trying to  apply undo records on.This would indicate corrupted rollback segment.” 其解决办法是(针对非open状态的数据库) 1 在pfile里修改  *.undo_management=MANUAL  *.rollback_segments=SYSTEM  2 重新启动数据库  3 drop tablespace undotbs1 including contents and datafiles;  4 create undo tablespace undotbs2 datafile /opt/oracle/oradata/aliuid/undotbs2.dbf size 500m;  5 shutdown immediate;  6 重新修改数据库参数文件为新的undo表空间！ *.undo_management=AUTO *.undo_retention=900 *.undo_tablespace=UNDOTBS2 7 创建新的spfile文件并再次重新启动数据库 最终数据库问题解决！ how to deal with ora600_4137 error How.to.Resolve.Ora-600.2662.error. ORA-600 [4193]错误解决方法 current online redo和undo 损坏解决方法