ORA-01189的完整解决过程(File is from a different RESETLOGS than previous files)

exeron21 · 发表于 2017-9-13 18:41

200 多个数据文件。怎么找出来他们具体编号？

一个WRONG FILE NUMBER,两个UNKOWN ERROR，其他全是FILE MISSING

exeron21 · 发表于 2017-9-14 11:43

现在几乎所有数据文件都ONLINE了, alter database open resetlogs 报错：

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/u01/app/oracle/oradata/jxkh/F_GL_QT01.dbf'
ORA-01210: data file header is media corrupt

这个文件又不能offline drop

SQL> alter database datafile 5 offline drop;
alter database datafile 5 offline drop
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19151
Session ID: 191 Serial number: 3

exeron21 · 发表于 2017-9-14 14:30

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01122: database file 203 failed verification check
ORA-01110: data file 203: '/u01/app/oracle/oradata/jxkh/F_GL_GL_QT01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN

出现这种错误之后我想offline drop ,.就会报：
SQL> alter database datafile '/u01/app/oracle/oradata/jxkh/F_GL_GL_QT01.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/jxkh/F_GL_GL_QT01.dbf' offline drop
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 21177
Session ID: 191 Serial number: 3

oracle 实例就会挂掉，需要重新startup mount

无奈水平太菜，不知怎么继续，我只有反复的重建控制文件，再按楼主的步骤恢复。现在257个数据文件已经恢复绝大多数，只剩下这些了：
SQL> SELECT * FROM V$RECOVER_FILE;

   FILE# ONLINE  ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
   5 ONLINE  ONLINE  WRONG FILE CREATE       8513094279 13-SEP-17
57 OFFLINE OFFLINE FILE NOT FOUND       0
60 OFFLINE OFFLINE FILE NOT FOUND       0
   164 OFFLINE OFFLINE FILE NOT FOUND       0
   165 OFFLINE OFFLINE FILE NOT FOUND       0
   203 ONLINE  ONLINE  WRONG FILE CREATE          35587448 08-MAR-17
   256 ONLINE  ONLINE  WRONG FILE CREATE       8513094279 13-SEP-17

exeron21 · 发表于 2017-9-14 15:07

256个数据文件，还剩下两个文件恢复不了。无所谓了，这是公司内部开发用的，用得不多，好多年了，里面啥都有，。而且本来就是克隆出来的虚机，配置文件乱得很。
那两个找不到的文件，等遇到了重新导入数据就可以。

成就感爆棚，因为我是搞开发的不是DBA，前几天还不知道有startup nomout这种命令，数据库参数从来没改过，现在居然会恢复数据库了

这几天遇到的错有几十个吧，所有的解决方案和命令都是在网上找的

exeron21 · 发表于 2017-9-14 15:09

虽然已经这么多年过去了，我这边也是11.2的数据库了，楼主的方案还是靠谱的，比较神奇。

再次感谢

Yong Huang · 发表于 2017-9-14 23:03

Congratulations!

If you have used _allow_resetlogs_corruption and
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ADJUST_SCN LEVEL 1';
I strongly suggest you fully export all user data (i.e. everything other than sys and system's data) and import them to a brand new empty database. The reason is that the database forced to open with the undocumented methods may have issues that will be known in the future and the database has become unsupported.

ORA-3113 when offline-drop a datafile sounds like a bug. But for now, I can't find the exact bug.