异常断电导致current redo损坏处理

一、数据库启动异常
网友因异常断电导致数据库不能正常启动，处理过程记录
1、sqlplus中提示

C:\Documents and Settings\Administrator>sqlplus"/as sysdba"   SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 12:57:02 2011   Copyright (c) 1982, 2007, Oracle.  All rights reserved.   已连接到空闲例程。   SQL> startup ORACLE 例程已经启动。   Total System Global Area 1581916160 bytes Fixed Size                  1336060 bytes Variable Size             964693252 bytes Database Buffers          603979776 bytes Redo Buffers               11907072 bytes 数据库装载完毕。 ORA-00368: 重做日志块中的校验和错误 ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11 ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'

2、alert日志文件

ALTER DATABASE OPEN Beginning crash recovery of 1 threads  parallel recovery started with 7 processes Started redo scan Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc  (incident=214262): ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11 ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG' Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214262\hzyl_ora_2960_i214262.trc Aborting crash recovery due to error 368 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc: ORA-00368: 重做日志块中的校验和错误 ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11 ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG' ORA-368 signalled during: ALTER DATABASE OPEN... Wed Dec 07 12:57:17 2011 Trace dumping is performing id=[cdmp_20111207125717] Wed Dec 07 12:57:17 2011 Sweep Incident[214262]: completed Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_m000_2400.trc  (incident=214326): ORA-00353: log corruption near block 12014 change 9743799889 time 12/05/2011 09:21:11 ORA-00312: online log 3 thread 1: 'R:\ORADATA\HZYL\REDO03.LOG' Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214326\hzyl_m000_2400_i214326.trc Trace dumping is performing id=[cdmp_20111207125718]

二、查询是损坏日志组

SQL> col member for a35 SQL> select a.group#,a.status,b.memberfrom v$log a,v$logfile b    2 where a.group#=b.group#;       GROUP# STATUS           MEMBER ---------- ---------------- -----------------------------------          3 CURRENT          R:\ORADATA\HZYL\REDO03.LOG          2 INACTIVE         R:\ORADATA\HZYL\REDO02.LOG          1 INACTIVE         R:\ORADATA\HZYL\REDO01.LOG

确定是因为断电导致当前redo 损坏，需要使用隐含参数+不完全恢复处理(经验)

三、不完全恢复

SQL> recover database until cancel; ORA-00279: 更改 9743796718 (在 12/05/2011 09:07:40 生成) 对于线程 1 是必需的 ORA-00289: 建议: S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC ORA-00280: 更改 9743796718 (用于线程 1) 在序列 #15114 中     指定日志: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: 无法打开归档日志 'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。     ORA-00308: 无法打开归档日志 'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC' ORA-27041: 无法打开文件 OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。     ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误 ORA-01194: 文件 1 需要更多的恢复来保持一致性 ORA-01110: 数据文件 1: 'R:\ORADATA\HZYL\SYSTEM01.DBF'

四、启用隐含参数，尝试打开数据库

SQL> create pfile='d:/1.txt' from spfile;   文件已创建。   #############添加隐含参数################## *._allow_resetlogs_corruption=TRUE *._allow_error_simulation=TRUE ##########################################   SQL> shutdown immediate ORA-01109: 数据库未打开     已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup pfile ='d:\1.txt'; ORACLE 例程已经启动。   Total System Global Area 1581916160 bytes Fixed Size                  1336060 bytes Variable Size             964693252 bytes Database Buffers          603979776 bytes Redo Buffers               11907072 bytes 数据库装载完毕。 ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项     SQL> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-00603: ORACLE 服务器会话因致命错误而终止 进程 ID: 3388 会话 ID: 335 序列号: 5

五、查看alert日志

Wed Dec 07 13:02:49 2011 SMON: enabling cache recovery Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216664): ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216664\hzyl_ora_3388_i216664.trc Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc: ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Error 600 happened during db open, shutting down database USER (ospid: 3388): terminating the instance due to error 600 Wed Dec 07 13:02:50 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmnl_3884.trc  (incident=216640): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216640\hzyl_mmnl_3884_i216640.trc Wed Dec 07 13:02:50 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_diag_4020.trc  (incident=216552): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216552\hzyl_diag_4020_i216552.trc Wed Dec 07 13:02:50 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dia0_2896.trc  (incident=216584): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216584\hzyl_dia0_2896_i216584.trc Wed Dec 07 13:02:51 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_pmon_2964.trc  (incident=216536): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216536\hzyl_pmon_2964_i216536.trc Wed Dec 07 13:02:51 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmon_3764.trc  (incident=216632): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216632\hzyl_mmon_3764_i216632.trc Wed Dec 07 13:02:51 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbrm_2060.trc  (incident=216560): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Wed Dec 07 13:02:51 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_psp0_1476.trc  (incident=216568): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Wed Dec 07 13:02:52 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mman_3040.trc  (incident=216576): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Wed Dec 07 13:02:52 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbw0_2116.trc  (incident=216592): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_lgwr_2624.trc  (incident=216600): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_smon_592.trc  (incident=216616): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ckpt_3052.trc  (incident=216608): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] Instance terminated by USER, pid = 3388 ORA-1092 signalled during: alter database open resetlogs... Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216665): ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], [] ORA-01092: ORACLE 实例终止。强制断开连接 ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216666): ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], [] ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], [] ORA-01092: ORACLE 实例终止。强制断开连接 ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc: ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], [] ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], [] ORA-01092: ORACLE 实例终止。强制断开连接 ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Wed Dec 07 13:02:53 2011 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=218920): ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []

这里看到熟悉的ora-600 [2662]，处理方法增加scn，参考ORA-00600 [2662]

六、增加SCN，打开数据库

SQL> exit 从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Miningand Real Application Testing options 断 开   C:\Documents and Settings\Administrator>sqlplus"/as sysdba"   SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 13:04:51 2011   Copyright (c) 1982, 2007, Oracle.  All rights reserved.   已连接到空闲例程。   SQL> startup pfile ='d:\1.txt' mount; ORACLE 例程已经启动。   Total System Global Area 1581916160 bytes Fixed Size                  1336060 bytes Variable Size             964693252 bytes Database Buffers          603979776 bytes Redo Buffers               11907072 bytes 数据库装载完毕。 SQL> alter session set events '10015 trace name adjust_scn level 1';   会话已更改。     SQL> alter database open;   数据库已更改。   SQL> select status from v$instance;   STATUS ------------ OPEN

七、启动过程中，alert日志现ORA-00600[2256]

Completed: ALTER DATABASE   MOUNT Wed Dec 07 13:06:07 2011 alter database open Beginning crash recovery of 1 threads  parallel recovery started with 7 processes Started redo scan Completed redo scan  1 redo blocks read, 0 data blocks need recovery Started redo application at  Thread 1: logseq 1, block 2, scn 9743796722 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0   Mem# 0: R:\ORADATA\HZYL\REDO01.LOG Completed redo application Completed crash recovery at  Thread 1: logseq 1, block 3, scn 9743816724  0 data blocks read, 0 data blocks written, 1 redo blocksread Wed Dec 07 13:06:08 2011 Thread 1 advanced to log sequence 2 Thread 1 opened at log sequence 2   Current log# 2 seq# 2 mem# 0: R:\ORADATA\HZYL\REDO02.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Dec 07 13:06:08 2011 SMON: enabling cache recovery Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3096.trc  (incident=219072): ORA-00600: 内部错误代码, 参数: [2256], [0], [1073741824], [2], [1153882138], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_219072\hzyl_ora_3096_i219072.trc Dictionary check beginning Dictionary check complete Verifying file header compatibilityfor 11g tablespace encryption.. Verifying 11g file header compatibilityfor tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK Wed Dec 07 13:06:09 2011 Trace dumping is performing id=[cdmp_20111207130609] Opening with internal Resource Manager plan Wed Dec 07 13:06:10 2011 Sweep Incident[219072]: completed Starting background process FBDA Starting background process SMCO Wed Dec 07 13:06:10 2011 FBDA started with pid=26, OS id=2436 Wed Dec 07 13:06:10 2011 SMCO started with pid=27, OS id=2876 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Wed Dec 07 13:06:11 2011 QMNC started with pid=28, OS id=2812 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Wed Dec 07 13:06:15 2011 db_recovery_file_dest_size of 10000 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Wed Dec 07 13:06:25 2011 Completed: alter database open

打开过程中，出现ORA-00600[2256]，但是数据库还是正常打开，具体原因待研究

更多Oracle学习：http://www.wyzc.com/ocp/?tg=3006123630