联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
使用Oracle Recovery Tools 工具实现一键解决此类问题,参考:一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools
数据库版本
数据库启动报错
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>
1
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;
数据库已更改。
加我QQ
