联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-01110 ORA-17070 OSD-04006 故障恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友找到我说应用访问数据库和导出数据都报ORA-01110 ORA-17070 OSD-04006之类错误,数据库可以正常open,但是业务访问关键数据和导出报错
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一般由于归档日志丢失或者非归档库可能在数据库启动的时候出现类似如下错误
SQL> startup ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2282960 bytes Variable Size 587205168 bytes Database Buffers 1543503872 bytes Redo Buffers 4894720 bytes Database mounted. ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'F:\ORADATA\XIFENFEI\SYSTEM01.DBF'
主要是由于数据文件不一致需要比较老的日志,但是日志不存在从而导致该问题
现在可以通过Oracle Recovery Tools工具一键解决
SQL> recover database; Media recovery complete. SQL> alter database open; Database altered.
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
12.2备库报错
2018-06-13T19:29:00.302767+08:00 Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc: ORA-01110: data file 1: '/u01/app/oracle/oradata/xifenfei/system01.dbf' 2018-06-13T19:29:00.829861+08:00 Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc: ORA-01110: data file 2: '/u01/app/oracle/oradata/xifenfei/rich101.dbf' 2018-06-13T19:29:00.930632+08:00 Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc: ORA-01110: data file 3: '/u01/app/oracle/oradata/xifenfei/sysaux01.dbf' 2018-06-13T19:29:01.010230+08:00 Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc: ORA-01110: data file 4: '/u01/app/oracle/oradata/xifenfei/undotbs01.dbf' 2018-06-13T11:29:01.055975+00:00 Archived Log entry 5072 added for T-1.S-5020 ID 0x6a8e9d72 LAD:1 RFS[18]: Selected log 10 for T-1.S-5024 dbid 1787743346 branch 957530932 2018-06-13T19:29:01.091059+08:00 Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc: ORA-01110: data file 5: '/u01/app/oracle/oradata/xifenfei/richman01.dbf' 2018-06-13T19:29:01.172613+08:00 Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc: ORA-01110: data file 7: '/u01/app/oracle/oradata/xifenfei/users01.dbf' 2018-06-13T19:29:01.251906+08:00 Errors in file /u01/app/oracle/diag/rdbms/xifenfeidg/xifenfei/trace/xifenfei_m000_2457.trc: ORA-01110: data file 8: '/u01/app/oracle/oradata/xifenfei/r_index01.dbf'
trace文件
*** 2018-06-13T19:29:00.282836+08:00
*** SESSION ID:(2281.15120) 2018-06-13T19:29:00.282868+08:00
*** CLIENT ID:() 2018-06-13T19:29:00.282873+08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2018-06-13T19:29:00.282878+08:00
*** MODULE NAME:(MMON_SLAVE) 2018-06-13T19:29:00.282883+08:00
*** ACTION NAME:(DDE async action) 2018-06-13T19:29:00.282888+08:00
*** CLIENT DRIVER:() 2018-06-13T19:29:00.282892+08:00
========= Dump for error ORA 312 (no incident) ========
----- DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
dbkh_reactive_run_check: BEGIN
dbkh_reactive_run_check:; incident_id=0
dbkh_run_check_internal: BEGIN; check_namep=DB Structure Integrity Check, run_namep=<null>
dbkh_run_check_internal: BEGIN; timeout=0
dbkh_run_check_internal: AFTER RUN CREATE; run_id=1841
*** 2018-06-13T19:29:00.302510+08:00
DDE previous invocation failed before phase II
DDE was called in a 'No Invocation Mode'
----- Start Diag Diagnostic Dump -----
Diagnostic dump is performed due to an error in the diagfw code during error handling.
Dump error and call stack for the diagnostic dump:
*** 2018-06-13T19:29:00.302576+08:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
ORA-01110: data file 1: '/u01/app/oracle/oradata/xifenfei/system01.dbf'
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+119 call kgdsdst() 7FFF1A0D6C68 000000002
7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
000000000 000000082 ?
dbkedDefDump()+1200 call ksedst() 000000000 000000002 ?
7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
000000000 ? 000000082 ?
ksedmp()+259 call dbkedDefDump() 000000001 000000000
7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
000000000 ? 000000082 ?
dbgexExecuteIntDiag call ksedmp() 000000001 000000000 ?
Dmp()+1457 7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
000000000 ? 000000082 ?
dbgeBeginInvoke()+3 call dbgexExecuteIntDiag 7F5A00000003 7F5A99B856C0
59 Dmp() 7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
000000000 ? 000000082 ?
dbgePostErrorKGE()+ call dbgeBeginInvoke() 7F5A99B856C0 7FFF1A0D7D20
1676 7FFF1A0B86D0 ? 7FFF1A0B87E8 ?
000000000 ? 000000082 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 7F5A99BC59A0 7F5A99AA0048
90 000000456 7FFF1A0B87E8 ?
000000000 ? 000000082 ?
kgeade()+432 call dbkePostKGE_kgsf() 7F5A99BC59A0 7F5A99AA0048
000000456 7FFF1A0B87E8 ?
000000000 ? 000000082 ?
kgerelv()+144 call kgeade() 7F5A99BC59A0 ? 7F5A99BC5BE8 ?
7F5A99AA0048 ? 000000456 ?
000000000 000000000
kgerev()+36 call kgerelv() 7F5A99BC59A0 ? 7F5A99AA0048 ?
7F5A99AA0048 ? 000000456 ?
012E79CF4 ? 000000002 ?
kserec2()+185 call kgerev() 7F5A99BC59A0 ? 7F5A99AA0048 ?
7F5A99AA0048 ? 000000456 ?
7FFF1A0D8000 000000002 ?
kcf_record_fn()+634 call kserec2() 7F5A99BC59A0 ? 000000000
000000001 000000001 00000002C
141E0C518
kcvvra_dfh()+5278 call kcf_record_fn() 000000001 151622BB8 000000000
7FFF1A0DA5D8 00000002C ?
141E0C518 ?
kcidr_file_header_c call kcvvra_dfh() 7FFF1A0DA460 ? 7FFF1A0D9FE8 ?
heck_common()+4669 000000000 ? 7FFF1A0D9398
7F5A94379000 ? 000000001 ?
kcidr_file_header_a call kcidr_file_header_c 7F5A99A9F7A0 7F5A94379000
ll_check_common()+2 heck_common() 000000001 000000000
259 7F5A94379000 ? 000000000
kcidr_cross_check() call kcidr_file_header_a 7F5A99A9F7A0 7FFF1A0DABE4
+566 ll_check_common() 000000001 ? 000000000 ?
7F5A94379000 ? 000000000 ?
dbkird_cross_check( call kcidr_cross_check() 7F5A99A9F7A0 7FFF1A0DABE4 ?
)+557 7F5A99BC5BE8 000000000 ?
7F5A94379000 ? 000000000 ?
dbkh_run_check_inte call dbkird_cross_check( 7F5A99A9F7A0 7FFF1A0DABE4 ?
rnal()+2228 ) 7F5A99BC5BE8 ? 000000000 ?
7F5A94379000 ? 000000000 ?
dbkh_reactive_run_c call dbkh_run_check_inte 7FFF1A0DB970 000000000
heck()+3011 rnal() 000000002 000000000 000000000
000000000
dbgdaAsyncReceive() call dbkh_reactive_run_c 7F5A99B856C0 7FFF1A0DBC90
+279 heck() 000000002 ? 000000000 ?
000000000 ? 000000000 ?
dbgea_exec_()+1739 call dbgdaAsyncReceive() 7F5A99B856C0 0020C0029
7FFF1A0E7CA0 7FFF1A0E7D20
000000002 000000000 ?
dbgea_exec()+621 call dbgea_exec_() 7F5A99B856C0 7F5A94984D18
0000000E8 000000000
000000002 ? 000000000 ?
dbkea_exec()+1718 call dbgea_exec() 7F5A99B856C0 7F5A94984D18
0000000E8 000000000
000000002 ? 000000000 ?
dbkea_slave_exec()+ call dbkea_exec() 7F5A99B856C0 ? 7F5A94984D18 ?
518 0000000E8 ? 000000000 ?
000000002 ? 000000000 ?
kebm_slave_cb()+64 call dbkea_slave_exec() 1453D7248 7F5A94984D18 ?
0000000E8 ? 000000000 ?
000000002 ? 000000000 ?
kebm_slave_main()+7 call kebm_slave_cb() 1453D7248 ? 7F5A94984D18 ?
72 0000000E8 ? 000000000 ?
000000002 ? 000000000 ?
ksvrdp_int()+2010 call kebm_slave_main() 1453D7248 ? 1453D7248
0000000E8 ? 000000000 ?
000000002 ? 000000000 ?
opirip()+602 call ksvrdp_int() 000000000 ? 000000000 ?
0000000E8 ? 000000000 ?
000000002 ? 000000000 ?
opidrv()+602 call opirip() 000000032 000000004
7FFF1A0EAD98 000000000 ?
000000002 ? 000000000 ?
sou2o()+145 call opidrv() 000000032 000000004
7FFF1A0EAD98 000000000 ?
000000002 ? 000000000 ?
opimai_real()+202 call sou2o() 7FFF1A0EAD70 000000032
000000004 7FFF1A0EAD98
000000002 ? 000000000 ?
ssthrdmain()+417 call opimai_real() 000000000 7FFF1A0EB080
000000004 ? 7FFF1A0EAD98 ?
000000002 ? 000000000 ?
main()+262 call ssthrdmain() 000000000 000000003
7FFF1A0EB080 000000001
000000000 000000000 ?
__libc_start_main() call main() 000000000 7FFF1A0EB2B8
+245 7FFF1A0EB080 ? 000000001 ?
000000000 ? 000000000 ?
_start()+41 call __libc_start_main() 000D05240 000000001
7FFF1A0EB2B8 7F5A95015C05 ?
000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
BUG:24844841 – PHSB:CDB M000 REPORTED ORA-1110 ON ADG WHEN A DATAFILE IS ADDED ON PRIMARY
@ The M000 messages is a false alarm as well. It is a false alarm by DRA check
@ that doesn’t consider standby media recovery properly. Adding a file happens
@ to trigger the timing for the false alarm.
@ One way to fix this is to skip file header check if standby recovery is
@ running inside kcidr_file_header_all_check_common.
M000进程检查数据库文件头信息,由于bug原因报ORA-01110错误.
处理建议
1.打上补丁24844841
2.19.1版本修复该问题
3.重启备库,启动mgr
4.暂时忽略该问题(目前没有发现影响数据库同步)
参考:ORA-01110 For All Files In Standby Database (Doc ID 2322290.1)
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库版本
数据库启动报错
Completed: ALTER DATABASE MOUNT Thu Aug 17 12:34:52 2017 alter database open Thu Aug 17 12:34:52 2017 Ping without log force is disabled . Thu Aug 17 12:34:52 2017 Errors in file D:\APP\ORACLE\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_ora_5960.trc: ORA-01113: 文件 5 需要介质恢复 ORA-01110: 数据文件 5: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF' ORA-1113 signalled during: alter database open
客户尝试恢复
SQL> startup mount; ORACLE 例程已经启动。 Total System Global Area 1.0301E+10 bytes Fixed Size 3842760 bytes Variable Size 1778388280 bytes Database Buffers 8489271296 bytes Redo Buffers 29708288 bytes 数据库装载完毕。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01113: 文件 5 需要介质恢复 ORA-01110: 数据文件 5: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF' SQL> alter database datafile 5 offline drop; 数据库已更改。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01113: 文件 6 需要介质恢复 ORA-01110: 数据文件 6: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_USERS_DN9MQH75_.DBF' SQL> recover datafile 6; ORA-00283: 恢复会话因错误而取消 ORA-00322: 日志 3 (用于线程 1) 不是最新副本 ORA-00312: 联机日志 3 线程 1: 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG' ORA-00322: 日志 3 (用于线程 1) 不是最新副本 ORA-00312: 联机日志 3 线程 1: 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG'
使用Oracle Database Recovery Check检测结果
尝试恢复
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG'
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG'
SQL> recover database until cancel
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.ARC
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LOG
ORA-00310: archived log contains sequence 716; sequence 717 required
ORA-00334: archived log:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LOG'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'
SQL> recover database until cancel
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.AR
C
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG
ORA-00310: archived log contains sequence 715; sequence 717 required
ORA-00334: archived log:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'
SQL> recover database until cancel
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.ARC
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 5 online;
数据库已更改。
SQL> recover database until cancel;
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.AR
C
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'
ORA-01112: 未启动介质恢复
SQL> recover datafile 5;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
SQL> SELECT status,
2 checkpoint_change#,
3 checkpoint_time,FUZZY,
4 count(*) ROW_NUM
5 FROM v$datafile_header
6 GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy
7 ORDER BY status, checkpoint_change#, checkpoint_time;
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FUZ ROW_NUM
------- ------------------ ------------------- --- ----------------
ONLINE 9850826 2017-08-17 08:15:45 NO 1
ONLINE 9857411 2017-08-17 08:15:45 NO 7
ONLINE 9857411 2017-08-17 09:00:48 NO 2
SQL> set numw 16
SQL> SELECT status,
2 checkpoint_change#,
3 checkpoint_time,last_change#,
4 count(*) ROW_NUM
5 FROM v$datafile
6 GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
7 ORDER BY status, checkpoint_change#, checkpoint_time;
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME LAST_CHANGE# ROW_NUM
------- ------------------ ------------------- ---------------- ----------------
ONLINE 9857411 2017-08-17 09:00:48 9850826 1
ONLINE 9857411 2017-08-17 09:00:48 9857411 8
SYSTEM 9857411 2017-08-17 09:00:48 9857411 1
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'
[/shell]
这里比较明显，由于controlfile的scn 大于db的scn，从而出现了ORA-01152的错误,重试重建控制文件
<br>
<strong>重建控制文件</strong>
SQL> alter database backup controlfile to trace as 'd:\app\ctl.txt';
数据库已更改。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 10301210624 bytes
Fixed Size 3842760 bytes
Variable Size 1778388280 bytes
Database Buffers 8489271296 bytes
Redo Buffers 29708288 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG',
10 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LO
G'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
13 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV68H_.LOG',
14 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LO
G'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 3 (
17 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG',
18 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LO
G'
19 ) SIZE 50M BLOCKSIZE 512
20 DATAFILE
21 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF',
22 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSAUX_DN9MK6B3_.DBF',
23 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF',
24 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_USERS_DN9MQH75_.DBF',
25 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE06',
26 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE05',
27 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE04',
28 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE03',
29 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE02',
30 'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE01'
31 CHARACTER SET ZHS16GBK
32 ;
控制文件已创建。
尝试恢复
SQL> recover database; 完成介质恢复。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [4194], [41], [36], [], [], [], [], [], [], [], [], [] 进程 ID: 2864 会话 ID: 62 序列号: 54236
本来到这一步，错误比较明显，undo异常，这类直接对undo进行处理即可，可是运气不太好
异常crash之后redo损坏
SQL> startup pfile='d:/app/pfile.txt'
ORACLE 例程已经启动。
Total System Global Area 1.0301E+10 bytes
Fixed Size 3842760 bytes
Variable Size 1778388280 bytes
Database Buffers 8489271296 bytes
Redo Buffers 29708288 bytes
数据库装载完毕。
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 60 更改 23924938639111 时间 08/17/2017 21:36:16
ORA-00312: 联机日志 1 线程 1:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'
ORA-00312: 联机日志 1 线程 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 60 change 23924938639111 time 08/17/201721:36:16
ORA-00312: online log 1 thread 1:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'
ORA-00312: online log 1 thread 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG'
SQL> recover database until cancel;
ORA-00279: 更改 10050832 (在 08/17/2017 21:36:13 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_718_%U_.ARC
ORA-00280: 更改 10050832 (用于线程 1) 在序列 #718 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 60 change 23924938639111 time 08/17/2017 21:36:16
ORA-00334: archived log:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'
到这一步，只能通过屏蔽oracle 事务前滚，强制拉库恢复。
SQL> startup pfile='d:/app/pfile.txt' mount;
ORACLE 例程已经启动。
Total System Global Area 1.0301E+10 bytes
Fixed Size 3842760 bytes
Variable Size 1778388280 bytes
Database Buffers 8489271296 bytes
Redo Buffers 29708288 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 10050832 (在 08/17/2017 21:36:13 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O
1_MF_1_718_%U_.ARC
ORA-00280: 更改 10050832 (用于线程 1) 在序列 #718 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9M
N5OT_.DBF'
ORA-01112: 未启动介质恢复
SQL> alter database open resetlogs;
数据库已更改。
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户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_DATA\YCCY\REDO02.LOG Recovery of Online Redo Log: Thread 1 Group 3 Seq 18687 Reading mem 0 Mem# 0: E:\ORACLE_DATA\YCCY\REDO03.LOG Recovery of Online Redo Log: Thread 1 Group 1 Seq 18688 Reading mem 0 Mem# 0: E:\ORACLE_DATA\YCCY\REDO01.LOG Mon Mar 28 10:20:38 2016 Hex dump of (file 45, block 7431) in trace file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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_DATA\YCCY\DT_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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_DATA\YCCY\DT_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0k_3900.trc (incident=131189): ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131189\yccy_pr0k_3900_i131189.trc ERROR: Unable to normalize symbol name for the following short stack (at offset 199): Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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_DATA\YCCY\DT_SYS_DAT.ORA' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131245\yccy_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0d_2112.trc (incident=131133): ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], [] Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131133\yccy_pr0d_2112_i131133.trc Mon Mar 28 10:20:39 2016 Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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_DATA\YCCY\DT_SYS_DAT.ORA' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131141\yccy_pr0e_3260_i131141.trc Mon Mar 28 10:20:39 2016 Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr04_3980.trc (incident=131021): ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], [], [], [], [], [] Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131021\yccy_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_DATA\YCCY\DT_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0f_816.trc (incident=131149): ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131149\yccy_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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_DATA\YCCY\DT_SYS_DAT.ORA' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131173\yccy_pr0i_2132_i131173.trc Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131190\yccy_pr0k_3900_i131190.trc Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr01_2220.trc (incident=131037): ORA-00600: internal error code, arguments: [kcbrapply_14], [], [], [], [], [], [], [], [], [], [], [] Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131037\yccy_pr01_2220_i131037.trc Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x12EC62C, kcbzdh()+942] Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131150\yccy_pr0f_816_i131150.trc Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131038\yccy_pr01_2220_i131038.trc Mon Mar 28 10:20:39 2016 Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_pr0h_4036.trc (incident=131165): ORA-00600: internal error code, arguments: [kcbr_validate_read_1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131165\yccy_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131166\yccy_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131134\yccy_pr0d_2112_i131134.trc Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131022\yccy_pr04_3980_i131022.trc Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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_DATA\YCCY\DT_SYS_DAT.ORA' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131142\yccy_pr0e_3260_i131142.trc Mon Mar 28 10:20:41 2016 Trace dumping is performing id=[cdmp_20160328102041] Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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_DATA\YCCY\DT_SYS_DAT.ORA' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131174\yccy_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131077\yccy_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:\oracle\diag\rdbms\yccy\yccy\trace\yccy_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:\oracle\diag\rdbms\yccy\yccy\incident\incdir_130853\yccy_pmon_3856_i130853.trc Trace dumping is performing id=[cdmp_20160328102042] Errors in file d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_131077\yccy_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_DATA\YCCY\REDO01.LOG Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0, 0000000074CAE3F0] Errors in file d:\oracle\diag\rdbms\yccy\yccy\trace\yccy_ora_3508.trc (incident=143022): ORA-07445: 出现异常错误: 核心转储 [PC:0x74CAE3F0] [ACCESS_VIOLATION] [ADDR:0x2E7FFFFFE] [PC:0x74CAE3F0] [UNABLE_TO_READ] [] Incident details in: d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_143022\yccy_ora_3508_i143022.trc Errors in file d:\oracle\diag\rdbms\yccy\yccy\incident\incdir_143022\yccy_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_DATA\YCCY\DT_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_DATA\YCCY\DT_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_DATA\YCCY\DT_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_DATA\YCCY\DT_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:\ORACLE\FLASH_RECOVERY_AREA\YCCY\ARCHIVELOG\2016_03_28\O1_MF_1_18689_%U_.ARC
ORA-00280: 更改 1226478477 (用于线程 1) 在序列 #18689 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\ORACLE_DATA\YCCY\REDO02.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 2 online;
数据库已更改。
SQL> alter database open resetlogs;
数据库已更改。
数据库基本上属于正常打开,处理掉3020部分的坏块基本ok
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：12C sysaux 异常恢复—ORA-01190错误恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友请求支援，他们数据库由于file 3 大量坏块，然后直接使用rman 备份还原了file 3，但是在recover过程中发现归档丢失，而且整个库在丢失归档的scn之后，还做过resetlogs操作，导致现在整个库无法正常启动，报ORA-01190错误，希望帮忙把file 3 给online起来，整个库正常open【当然在丢失sysaux的情况下，数据库可以open起来，但是这种情况下，迁移数据比较麻烦】
SQL> startup; ORACLE 例程已经启动。 Total System Global Area 3.1868E+10 bytes Fixed Size 3601144 bytes Variable Size 2.8655E+10 bytes Database Buffers 3154116608 bytes Redo Buffers 54804480 bytes 数据库装载完毕。 ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前 ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
Oracle Database Recovery Check Result结果显示[脚本]
尝试不完全恢复并使用隐含参数打开库
Fri Oct 02 19:10:12 2015 ALTER DATABASE RECOVER database until cancel Fri Oct 02 19:10:12 2015 Media Recovery Start Started logmerger process Fri Oct 02 19:10:12 2015 Media Recovery failed with error 16433 Fri Oct 02 19:10:14 2015 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database until cancel ... Fri Oct 02 19:10:37 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5176.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:10:37 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5176.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. ALTER DATABASE RECOVER database until cancel Fri Oct 02 19:11:18 2015 Media Recovery Start Started logmerger process Fri Oct 02 19:11:18 2015 Media Recovery failed with error 16433 Fri Oct 02 19:11:19 2015 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database until cancel ... alter database open resetlogs ORA-1139 signalled during: alter database open resetlogs... alter database open Fri Oct 02 19:11:49 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_4252.trc: ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前 ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF' ORA-1190 signalled during: alter database open... Fri Oct 02 19:15:38 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5292.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:15:38 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5292.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:20:39 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_2276.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:20:39 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_2276.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:25:40 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_4804.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:25:40 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_4804.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:30:41 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_876.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:30:41 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_876.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:32:40 2015 Shutting down instance (abort)
数据库遭遇ORA-16433,此类方法无法打开数据库，根据经验值出现此类问题，可能需要重建控制文件，但是由于其中file 3的resetlogs scn不正确，无法包含该文件重建控制文件
Fri Oct 02 20:10:55 2015
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Fri Oct 02 20:10:55 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_5004.trc:
ORA-01189: ????????????? RESETLOGS
ORA-01110: ???? 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2921
LOGFILE
GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG' size 50M,
GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG' size 50M
DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF'
CHARACTER SET AL32UTF8
...
除掉file 3 继续重建控制文件
Fri Oct 02 20:33:11 2015
Successful mount of redo thread 1, with mount id 1419796614
Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2921
LOGFILE
GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG' size 50M,
GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG' size 50M
DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
--'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF'
CHARACTER SET AL32UTF8
继续恢复数据库
ALTER DATABASE OPEN
Fri Oct 02 20:34:57 2015
…………
Archived Log entry 3 added for thread 1 sequence 8 ID 0x54a083a3 dest 1:
Fri Oct 02 20:35:16 2015
Tablespace 'SYSAUX' #1 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #3 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00003' in the controlfile.
Corrected file 15 plugged in read-only status in control file
Corrected file 16 plugged in read-only status in control file
Corrected file 17 plugged in read-only status in control file
Corrected file 18 plugged in read-only status in control file
Corrected file 19 plugged in read-only status in control file
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Fri Oct 02 20:35:19 2015
SMON: enabling tx recovery
Fri Oct 02 20:35:19 2015
*********************************************************************
WARNING: The following temporary tablespaces in container(CDB$ROOT)
contain no files.
Starting background process SMCO
Fri Oct 02 20:35:19 2015
SMCO started with pid=45, OS id=1500
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
Fri Oct 02 20:35:21 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003'
Fri Oct 02 20:35:21 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003'
Error 376 happened during db open, shutting down database
USER (ospid: 2220): terminating the instance due to error 376
Fri Oct 02 20:35:26 2015
Instance terminated by USER, pid = 2220
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (2220) as a result of ORA-1092
此时由于file 3 未包含在控制文件中，但是存在数据字典中，因此在数据库open的时候出现了默认文件名MISSING0003，尝试重命名改文件指定为存在的file 3,并且尝试恢复
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 3.1868E+10 bytes
Fixed Size 3601144 bytes
Variable Size 2.8655E+10 bytes
Database Buffers 3154116608 bytes
Redo Buffers 54804480 bytes
数据库装载完毕。
SQL> alter database datafile 3 offline;
数据库已更改。
SQL> alter database rename file 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\
MISSING00003' to 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF';
数据库已更改。
SQL> recover database until cancel;
ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ORAADM\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_10_02\O1_MF_1_9_%U_.ARC
ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG
ORA-00310: archived log contains sequence 7; sequence 9 required
ORA-00334: archived log: 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ORAADM\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_10_02\O1_MF_1_9_%U_.ARC
ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 3 online;
数据库已更改。
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01122: 数据库文件 3 验证失败
ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
ORA-01202: 此文件的原型错误 - 创建时间错误
这里比较明显ORA-01202，由于创建控制文件之时没有file 3信息，因此导致控制文件中关于file 3的信息和该文件头的创建时间不一致（此处之时显示了时间不一致，如果通过bbed修改时间，后续可能还有很多东西不一致，因此通过bbed 一个个修改一个个尝试，理论可行，但实际可操作性不好）,因此尝试直接使用bbed修改file 3文件头（由于是win环境，操作稍微麻烦点），把resetlogs信息修改和其他的一样
BBED> m /x 3c6b2b35
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 112 to 143 Dba:0x00c00002
------------------------------------------------------------------------
3c6b2b35 386b2200 00000000 00000000 00000000 00000000 00004000 bb460000
<32 bytes per line>
BBED> set offset 116
OFFSET 116
BBED> m /x 3137ca24
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 116 to 147 Dba:0x00c00002
------------------------------------------------------------------------
3137ca24 00000000 00000000 00000000 00000000 00004000 bb460000 7dc12b35
<32 bytes per line>
BBED> m /x b9f8
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 484 to 515 Dba:0x00c00002
------------------------------------------------------------------------
b9f8a424 00000000 e65e2435 01000000 d3410000 b89b0000 10000900 02000000
<32 bytes per line>
BBED> set offset +2
OFFSET 486
BBED> m /x cc24
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 486 to 517 Dba:0x00c00002
------------------------------------------------------------------------
cc240000 0000e65e 24350100 0000d341 0000b89b 00001000 09000200 00000000
<32 bytes per line>
BBED> m /x 87df offset 492
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 492 to 523 Dba:0x00c00002
------------------------------------------------------------------------
87df2435 01000000 d3410000 b89b0000 10000900 02000000 00000000 00000000
<32 bytes per line>
BBED>
BBED> m /x 2b35 offset +2
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 494 to 525 Dba:0x00c00002
------------------------------------------------------------------------
2b350100 0000d341 0000b89b 00001000 09000200 00000000 00000000 00000000
<32 bytes per line>
BBED> d offset 140
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 140 to 171 Dba:0x00c00002
------------------------------------------------------------------------
bb460000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 4248
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 140 to 171 Dba:0x00c00002
------------------------------------------------------------------------
42480000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> d offset 148
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 148 to 179 Dba:0x00c00002
------------------------------------------------------------------------
ba460000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 4148
File: SYSAUX01.dbf (3)
Block: 2 Offsets: 148 to 179 Dba:0x00c00002
------------------------------------------------------------------------
41480000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 3, Block 2:
current = 0xd0c8, required = 0xd0c8
BBED> verify
DBVERIFY - Verification starting
FILE = SYSAUX01.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
修改完file 3的文件头之后，再次重建控制文件,此次包含file 3
Fri Oct 02 21:19:58 2015
Successful mount of redo thread 1, with mount id 1419797885
Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2921
LOGFILE
GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG' size 50M,
GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG' size 50M
DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF'
CHARACTER SET AL32UTF8
继续恢复数据库,数据库正常open，而且file 3 已经正常online，数据库可以直接导出来，至此恢复大体完成
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：system01.dbf文件被offline,导致数据库报ORA-01245 ORA-01110故障恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友找到我,说数据库做recover报ORA-01245和ORA-01110错误,无法继续恢复,请求支持
SQL> recover database using backup controlfile until cancel; ………… 第 1 行出现错误: ORA-01245: RESETLOGS 完成时脱机文件 1 将丢失 ORA-01110: 数据文件 1: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\SYSTEM01.DBF'
通过Oracle Database Recovery Check检查数据库情况,发现datafile 1处于offline状态
Wed Aug 26 23:11:00 2015 alter database datafile 1 offline drop Completed: alter database datafile 1 offline drop
从这里基本上可以知道为什么出现ORA-01245错误了,由于system表空间中文件被offline导致.
redo信息
Mon Aug 24 22:38:35 2015 alter database clear unarchived logfile group 2 Clearing online log 2 of thread 1 sequence number 5705 Completed: alter database clear unarchived logfile group 2 Wed Aug 26 23:13:23 2015 alter database clear logfile group 3 Clearing online log 3 of thread 1 sequence number 5706 Completed: alter database clear logfile group 3
除当前redo之外,其他redo被clear
尝试恢复
SQL> alter database datafile 1 online;
数据库已更改。
SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG
ORA-00310: 归档日志包含序列 5706; 要求序列 5705
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG'
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG
ORA-00339: 归档日志未包含任何重做
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG'
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG
ORA-00310: 归档日志包含序列 5707; 要求序列 5705
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG'
数据库做恢复需要seq 5705的redo,但是redo已经被clear,导致现在数据库常规手段无法恢复,只用使用隐含参数屏蔽数据库前滚(一致性检查)
再次尝试打开数据库
ORACLE 例程已经启动。
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 486540712 bytes
Database Buffers 285212672 bytes
Redo Buffers 5259264 bytes
数据库装载完毕。
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
介质恢复已取消。
SQL> alter database open resetlogs;
数据库已更改。
在数据库恢复中,请不要对system表空间数据文件进行offline操作,如果对此类文件进行offline操作,讲在数据库恢复过程中出现ORA-01245和ORA-01110错误,而且文件还会出现SYSOFF状态
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：aix平台 ORA-01115 ORA-01110 ORA-27067 故障恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接到朋友恢复请求，aix 5.3,Oracle 10.2.0.1平台，数据库启动报ORA-01115 ORA-01110 ORA-27067错误，数据库无法正常打开，通过分析，是由于10201在aix上面的bug导致，通过技巧规避，完美解决给问题，数据0丢失
数据库报错alert日志
Mon Aug 10 13:25:22 2015 ALTER DATABASE MOUNT Mon Aug 10 13:25:29 2015 Setting recovery target incarnation to 1 Mon Aug 10 13:25:29 2015 Successful mount of redo thread 1, with mount id 432339141 Mon Aug 10 13:25:29 2015 Database mounted in Exclusive Mode Completed: ALTER DATABASE MOUNT Mon Aug 10 13:25:36 2015 alter database open Mon Aug 10 13:25:36 2015 Beginning crash recovery of 1 threads parallel recovery started with 15 processes Mon Aug 10 13:25:37 2015 Started redo scan Mon Aug 10 13:25:52 2015 Completed redo scan 7889582 redo blocks read, 75305 data blocks need recovery Mon Aug 10 13:25:53 2015 Errors in file /dc/admin/datacent/bdump/datacent_p002_144124.trc: ORA-01115: IO error reading block from file 2 (block # 40704) ORA-01110: data file 2: '/dc/oradata/datacent/undotbs01.dbf' ORA-27067: size of I/O buffer is invalid Additional information: 2 Additional information: 1572864 Mon Aug 10 13:25:53 2015 Aborting crash recovery due to slave death, attempting serial crash recovery Mon Aug 10 13:25:53 2015 Beginning crash recovery of 1 threads Mon Aug 10 13:25:53 2015 Started redo scan Mon Aug 10 13:26:09 2015 Completed redo scan 7889582 redo blocks read, 75305 data blocks need recovery Mon Aug 10 13:26:12 2015 Aborting crash recovery due to error 1115 Mon Aug 10 13:26:12 2015 Errors in file /dc/admin/datacent/udump/datacent_ora_123384.trc: ORA-01115: IO error reading block from file 2 (block # 39077) ORA-01110: data file 2: '/dc/oradata/datacent/undotbs01.dbf' ORA-27067: size of I/O buffer is invalid Additional information: 2 Additional information: 1310720 ORA-1115 signalled during: alter database open...
这里报的前面两个错误ORA-01115 ORA-01110我们都非常熟悉，类似数据库启动遇到坏块或者io错误之时可能就会报如此错误。但是ORA-27067确实不多见，从mos上看，很多是由于rman备份之时的bug可能导致该错误。
dbv检测undo坏块文件
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Aug 10 23:18:15 2015 Copyright (c) 1982, 2003, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /dc/oradata/datacent/undotbs01.dbf DBVERIFY - Verification complete Total Pages Examined : 329600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 327504 Total Pages Processed (Seg) : 17 Total Pages Failing (Seg) : 0 Total Pages Empty : 2096 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 1887888 (0.1887888)
这里可以看到，undo文件本身并没有逻辑和物理的坏块,证明因为数据库异常的原因，可能是由于ORA-27067: size of I/O buffer is invalid导致。根据官方文档ORA-01115 ORA-27067 DURING PARALLEL INSTANCE RECOVERY AFTER INSTANCE CRASH中的解释，我们基本上可以确定很可能是由于10.2.0.1在aix平台的jfs2系统中，由于大量事务操作，突然abort掉数据库（也可能断电），从而数据库在启动的时候进行实例恢复，而由于内部的bug，导致实例恢复无法成功。通过我们处理后的，数据库完美启动，数据0丢失
数据库启动日志
Mon Aug 10 16:34:14 2015 alter database open Mon Aug 10 16:34:14 2015 Beginning crash recovery of 1 threads parallel recovery started with 15 processes Mon Aug 10 16:34:14 2015 Started redo scan Mon Aug 10 16:34:27 2015 Completed redo scan 7889582 redo blocks read, 0 data blocks need recovery Mon Aug 10 16:34:27 2015 Started redo application at Thread 1: logseq 664704, block 1286922 Mon Aug 10 16:34:27 2015 Recovery of Online Redo Log: Thread 1 Group 4 Seq 664704 Reading mem 0 Mem# 0 errs 0: /dev/rredo04 Mon Aug 10 16:34:32 2015 Recovery of Online Redo Log: Thread 1 Group 5 Seq 664705 Reading mem 0 Mem# 0 errs 0: /dev/rredo05 Mon Aug 10 16:34:38 2015 Recovery of Online Redo Log: Thread 1 Group 6 Seq 664706 Reading mem 0 Mem# 0 errs 0: /dev/rredo06 Mon Aug 10 16:34:40 2015 Completed redo application Mon Aug 10 16:34:40 2015 Completed crash recovery at Thread 1: logseq 664706, block 1017805, scn 8554793334 0 data blocks read, 0 data blocks written, 7889582 redo blocks read Mon Aug 10 16:34:40 2015 Thread 1 advanced to log sequence 664707 Thread 1 opened at log sequence 664707 Current log# 1 seq# 664707 mem# 0: /dev/rredo01 Successful open of redo thread 1 Mon Aug 10 16:34:40 2015 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Aug 10 16:34:40 2015 SMON: enabling cache recovery Mon Aug 10 16:34:40 2015 Successfully onlined Undo Tablespace 1. Mon Aug 10 16:34:40 2015 SMON: enabling tx recovery Mon Aug 10 16:34:41 2015 Database Characterset is ZHS32GB18030 replication_dependency_tracking turned off (no async multimaster replication found) WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected. Mon Aug 10 16:34:41 2015 SMON: Parallel transaction recovery tried Mon Aug 10 16:34:42 2015 db_recovery_file_dest_size of 2048 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. Mon Aug 10 16:34:42 2015 Completed: alter database open
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：分享一次ORA-01113 ORA-01110故障处理过程
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库报ORA-01113 ORA-01110无法正常open
SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
alert日志
Wed Jul 16 17:17:56 2014 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 1380870980 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Wed Jul 16 17:19:01 2014 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc (incident=367575): ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\metadata\HM_FINDING.ams], [1245], [], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_367575\orcl_m000_3540_i367575.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc (incident=367576): ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [1245], [0], [80], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_367576\orcl_m000_3540_i367576.trc Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc: ORA-51106: check failed to complete due to an error. See error below ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [1245], [0], [80], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
这里出现ORA-00700[dbgrmblcp_corrupt_page]与ORA-00600[dbgrmblgp_get_page_1]是Bug 10321285 : ORA-600 [DBGRMBLCP_CORRUPT_PAGE]
尝试recover database 遭遇ORA-00283 ORA-16433
SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode.
alert日志信息
Wed Jul 16 17:36:33 2014 ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Wed Jul 16 17:36:33 2014 Media Recovery failed with error 16433 Slave exiting with ORA-283 exception Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr00_3868.trc: ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode. Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database ... Wed Jul 16 17:36:45 2014 alter database open Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3236.trc: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open...
重建控制文件后恢复,遭遇ORA-600 2662错误
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 10255212544 bytes
Fixed Size 2264088 bytes
Variable Size 4529849320 bytes
Database Buffers 5704253440 bytes
Redo Buffers 18845696 bytes
SQL> @d:\ctl.sql
Control file created.
SQL> recover database using backup controlfile;
ORA-00279: change 13953689551797 generated at 07/16/2014 17:53:41 needed for
thread 1
ORA-00289: suggestion :
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_07_16\O1_MF_1_1_%U_
.ARC
ORA-00280: change 13953689551797 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
d:\app\administrator\oradata\orcl\redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [3248], [3635774399],
[3249], [3580470066], [4194545], [], [], [], [], [], []
Process ID: 3932
Session ID: 200 Serial number: 1
因为该数据库是11.2.0.3未打上scn patch,直接推进scn(可以使用event,隐含参数,oradebug,bbed等),数据库就正常open