联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一、数据库启动异常
网友因异常断电导致数据库不能正常启动，处理过程记录
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.member from 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 Mining and 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 blocks read 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 compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for 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]，但是数据库还是正常打开，具体原因待研究