oracle数据库强制关机导致数据库无法正常启动恢复例子
有客户qq找到我,说有朋友推荐,让我帮他们恢复数据库.由于强制关机后,数据库无法正常启动.
数据库recover database失败
Mon Mar 28 10:20:33 2016
ALTER DATABASE RECOVER database
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 32 slaves
Mon Mar 28 10:20:36 2016
Recovery of Online Redo Log: Thread 1 Group 2 Seq 18686 Reading mem 0
Mem# 0: E:ORACLE_DATAYCCYREDO02.LOG
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18687 Reading mem 0
Mem# 0: E:ORACLE_DATAYCCYREDO03.LOG
Recovery of Online Redo Log: Thread 1 Group 1 Seq 18688 Reading mem 0
Mem# 0: E:ORACLE_DATAYCCYREDO01.LOG
Mon Mar 28 10:20:38 2016
Hex dump of (file 45, block 7431) in trace file d:oraclediagrdbmsyccyyccytraceyccy_pr0q_2968.trc
Corrupt block relative dba: 0x0b401d07 (file 45, block 7431)
Mon Mar 28 10:20:38 2016
Hex dump of (file 45, block 7836) in trace file d:oraclediagrdbmsyccyyccytraceyccy_pr01_2220.trc
Bad header found during media recovery
Corrupt block relative dba: 0x0b401e9c (file 45, block 7836)
Data in bad block:
Bad header found during media recovery
type: 0 format: 0 rdba: 0x1d070000
last change scn: 0x4917.f8dc0b40 seq: 0x0 flg: 0x00
spare1: 0x6 spare2: 0xa2 spare3: 0xc7f7
consistency value in tail: 0x06010000
check value in block header: 0x601
block checksum disabled
Reading datafile 'E:ORACLE_DATAYCCYDT_SYS_IDX12.DBF' for corruption at rdba: 0x0b401d07 (file 45, block 7431)
Reread (file 45, block 7431) found valid data
Repaired corruption at (file 45, block 7431)
Hex dump of (file 45, block 7556) in trace file d:oraclediagrdbmsyccyyccytraceyccy_pr0q_2968.trc
Corrupt block relative dba: 0x0b401d84 (file 45, block 7556)
Bad header found during media recovery
Data in bad block:
type: 106 format: 3 rdba: 0x1d840000
last change scn: 0x461d.391a0b40 seq: 0x0 flg: 0x00
spare1: 0x6 spare2: 0xa2 spare3: 0x2499
consistency value in tail: 0x06013999
check value in block header: 0x401
block checksum disabled
Reading datafile 'E:ORACLE_DATAYCCYDT_SYS_IDX12.DBF' for corruption at rdba: 0x0b401d84 (file 45, block 7556)
Reread (file 45, block 7556) found valid data
Repaired corruption at (file 45, block 7556)
Mon Mar 28 10:20:38 2016
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1334748, kcbzfw()+3094]
Mon Mar 28 10:20:39 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0k_3900.trc (incident=131189):
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131189yccy_pr0k_3900_i131189.trc
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0r_3060.trc (incident=131245):
ORA-07445: exception encountered: core dump [kcbzfw()+3094] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1334748] [UNABLE_TO_READ] []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 169345, file offset is 1387274240 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131245yccy_pr0r_3060_i131245.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Mon Mar 28 10:20:39 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0d_2112.trc (incident=131133):
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131133yccy_pr0d_2112_i131133.trc
Mon Mar 28 10:20:39 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0e_3260.trc (incident=131141):
ORA-00600: internal error code, arguments: [3020], [5], [163457], [21134977], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 163457, file offset is 1339039744 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131141yccy_pr0e_3260_i131141.trc
Mon Mar 28 10:20:39 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr04_3980.trc (incident=131021):
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131021yccy_pr04_3980_i131021.trc
Data in bad block:
type: 0 format: 0 rdba: 0x1e9c0000
last change scn: 0x4915.f8320b40 seq: 0x0 flg: 0x00
spare1: 0x6 spare2: 0xa2 spare3: 0x8029
consistency value in tail: 0x0602e40c
check value in block header: 0x602
block checksum disabled
Reading datafile 'E:ORACLE_DATAYCCYDT_SYS_IDX12.DBF' for corruption at rdba: 0x0b401e9c (file 45, block 7836)
Reread (file 45, block 7836) found valid data
Repaired corruption at (file 45, block 7836)
Mon Mar 28 10:20:39 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0f_816.trc (incident=131149):
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131149yccy_pr0f_816_i131149.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Mon Mar 28 10:20:39 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0i_2132.trc (incident=131173):
ORA-00600: internal error code, arguments: [3020], [5], [154240], [21125760], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 154240, file offset is 1263534080 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131173yccy_pr0i_2132_i131173.trc
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0k_3900.trc (incident=131190):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131190yccy_pr0k_3900_i131190.trc
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr01_2220.trc (incident=131037):
ORA-00600: internal error code, arguments: [kcbrapply_14], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131037yccy_pr01_2220_i131037.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0f_816.trc (incident=131150):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131150yccy_pr0f_816_i131150.trc
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr01_2220.trc (incident=131038):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbrapply_14], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131038yccy_pr01_2220_i131038.trc
Mon Mar 28 10:20:39 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0h_4036.trc (incident=131165):
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131165yccy_pr0h_4036_i131165.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942]
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B, kcbzpnd()+299]
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1351BB9, kcbs_dump_adv_state()+1529]
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B, kcbzpnd()+299]
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0h_4036.trc (incident=131166):
ORA-07445: exception encountered: core dump [kcbzdh()+942] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131166yccy_pr0h_4036_i131166.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B, kcbzpnd()+299]
Mon Mar 28 10:20:40 2016
Checker run found 60 new persistent data failures
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0d_2112.trc (incident=131134):
ORA-07445: exception encountered: core dump [kcbzpnd()+299] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131134yccy_pr0d_2112_i131134.trc
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr04_3980.trc (incident=131022):
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1529] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1351BB9] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131022yccy_pr04_3980_i131022.trc
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0e_3260.trc (incident=131142):
ORA-07445: exception encountered: core dump [kcbzpnd()+299] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [3020], [5], [163457], [21134977], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 163457, file offset is 1339039744 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131142yccy_pr0e_3260_i131142.trc
Mon Mar 28 10:20:41 2016
Trace dumping is performing id=[cdmp_20160328102041]
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr0i_2132.trc (incident=131174):
ORA-07445: exception encountered: core dump [kcbzpnd()+299] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC13B] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [3020], [5], [154240], [21125760], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 154240, file offset is 1263534080 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: data file 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131174yccy_pr0i_2132_i131174.trc
Mon Mar 28 10:20:41 2016
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0, 0000000074CAE3F0]
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pr06_2684.trc (incident=131077):
ORA-07445: exception encountered: core dump [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_131077yccy_pr06_2684_i131077.trc
Mon Mar 28 10:20:42 2016
Exception [type: ACCESS_VIOLATION, UNABLE_TO_WRITE] [ADDR:0x0] [PC:0x4D20D2, kslgetl()+54]
Mon Mar 28 10:20:42 2016
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_pmon_3856.trc (incident=130853):
ORA-07445: exception encountered: core dump [kslgetl()+54] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x4D20D2] [UNABLE_TO_WRITE] []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_130853yccy_pmon_3856_i130853.trc
Trace dumping is performing id=[cdmp_20160328102042]
Errors in file d:oraclediagrdbmsyccyyccyincidentincdir_131077yccy_pr06_2684_i131077.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []
Process debug not enabled via parameter _debug_enable
Trace dumping is performing id=[cdmp_20160328102043]
Mon Mar 28 10:21:01 2016
RECO (ospid: 3524): terminating the instance due to error 472
Instance terminated by RECO, pid = 3524
通过观察这段日志,基本上可以发现主要是FILE 45,虽然提示坏块但是最终验证确定为正常块（类似：Reread (file 45, block 7836) found valid data),这里主要是file 5，报了大量的ORA-600[3020].
对数据文件逐个进行recover操作
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 1.7103E+10 bytes
Fixed Size 2192864 bytes
Variable Size 9059699232 bytes
Database Buffers 8019509248 bytes
Redo Buffers 21762048 bytes
数据库装载完毕。
SQL> recover datafile 1;
完成介质恢复。
SQL> recover datafile 2;
ORA-03113: 通信通道的文件结尾
进程 ID: 1652
会话 ID: 551 序列号: 55
SQL> recover datafile 3;
完成介质恢复。
SQL> recover datafile 4;
完成介质恢复。
SQL> recover datafile 5;
ORA-03113: 通信通道的文件结尾
进程 ID: 4900
会话 ID: 551 序列号: 56131
SQL> recover datafile 6;
完成介质恢复。
…………
SQL> recover datafile 63;
完成介质恢复。
SQL> recover datafile 64;
完成介质恢复。
除掉datafile 2，5之外，其他文件全部recover成功.
对于file 2 尝试处理
无法通过recover成功,只能暂时放弃,后续考虑先offline open库,然后把这个文件强制online
SQL> recover datafile 2 ;
ORA-03113: 通信通道的文件结尾
进程 ID: 5020
会话 ID: 551 序列号: 3
Mon Mar 28 10:47:12 2016
ALTER DATABASE RECOVER datafile 2
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 18688 Reading mem 0
Mem# 0: E:ORACLE_DATAYCCYREDO01.LOG
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0, 0000000074CAE3F0]
Errors in file d:oraclediagrdbmsyccyyccytraceyccy_ora_3508.trc (incident=143022):
ORA-07445: 出现异常错误: 核心转储 [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []
Incident details in: d:oraclediagrdbmsyccyyccyincidentincdir_143022yccy_ora_3508_i143022.trc
Errors in file d:oraclediagrdbmsyccyyccyincidentincdir_143022yccy_ora_3508_i143022.trc:
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] []
对于file 5处理
SQL> recover datafile 5;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [163457], [21134977], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 163457, file
offset is 1339039744 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
SQL> recover datafile 5 allow 1 corruption;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [162433], [21133953], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 162433, file
offset is 1330651136 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
SQL> recover datafile 5 allow 1 corruption;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [166272], [21137792], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 166272, file
offset is 1362100224 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
SQL> recover datafile 5 allow 1 corruption;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [5], [169346], [21140866], [], [], [],
[], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 169346, file
offset is 1387282432 bytes)
ORA-10564: tablespace DT_SYS_DAT
ORA-01110: 数据文件 5: 'E:ORACLE_DATAYCCYDT_SYS_DAT.ORA'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
SQL> recover datafile 5 allow 1 corruption;
完成介质恢复。
open数据库并online datafile 2
SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。
Total System Global Area 1.7103E+10 bytes
Fixed Size 2192864 bytes
Variable Size 9059699232 bytes
Database Buffers 8019509248 bytes
Redo Buffers 21762048 bytes
数据库装载完毕。
SQL> alter database datafile 2 offline;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> shutdown immediate;
ORA-03113: 通信通道的文件结尾
SQL> conn / as sysdba
已连接到空闲例程。
SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。
Total System Global Area 1.7103E+10 bytes
Fixed Size 2192864 bytes
Variable Size 9059699232 bytes
Database Buffers 8019509248 bytes
Redo Buffers 21762048 bytes
数据库装载完毕。
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
3 INACTIVE
2 CURRENT
SQL> recover database until cancel;
ORA-00279: 更改 1226478477 (在 03/28/2016 20:23:37 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:ORACLEFLASH_RECOVERY_AREAYCCYARCHIVELOG2016_03_28O1_MF_1_18689_%U_.ARC
ORA-00280: 更改 1226478477 (用于线程 1) 在序列 #18689 中
指定日志: {
E:ORACLE_DATAYCCYREDO02.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 2 online;
数据库已更改。
SQL> alter database open resetlogs;
数据库已更改。
数据库基本上属于正常打开,处理掉3020部分的坏块基本ok
原文来自:http://www.xifenfei.com/2016/03/强制关机导致数据库无法正常启动恢复.html
