标签归档：ORA-01110

一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools

发表于 2020 年 9 月 25 日 由 惜分飞

一般由于归档日志丢失或者非归档库可能在数据库启动的时候出现类似如下错误

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'

主要是由于数据文件不一致需要比较老的日志,但是日志不存在从而导致该问题
1

以前是通过一系列的方法强制open库,以前类似文章:
12c ORA-01113 ORA-01110 恢复
分享一次ORA-01113 ORA-01110故障处理过程

现在可以通过Oracle Recovery Tools工具一键解决
2
3

尝试open数据库

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.
发表在 小工具 | 标签为 , , , | 评论关闭

12.2 standby 报ORA-01110

发表于 2018 年 6 月 13 日 由 惜分飞

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)

发表在 Data Guard, ORACLE 12C | 标签为 | 评论关闭

12c ORA-01113 ORA-01110 恢复

发表于 2017 年 8 月 20 日 由 惜分飞

数据库版本
VERSION

数据库启动报错

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检测结果
check_ctl
check_resulte

尝试恢复

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;

数据库已更改。
发表在 Oracle备份恢复 | 标签为 , | 评论关闭