一次体验N种报错的Oracle数据库恢复（ORA-704 ORA-604 ORA-600[25016] ORA-376）

朋友数据库报错：

Fri Nov 07 16:34:24 2014
ALTER DATABASE OPEN
Fri Nov 07 16:34:29 2014
Expanded controlfile section 9 from 18692 to 186920 records
Requested to grow by 168228 records; added 576 blocks of records
Thread 1 advanced to log sequence 36 (thread open)
Thread 1 opened at log sequence 36
  Current log# 6 seq# 36 mem# 0: /oradata/TESTAPP/redo06.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Nov 07 16:34:29 2014
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.9b359f77):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_ora_8519700.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14_2547694043$" too small
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_ora_8519700.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14_2547694043$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 8519700): terminating the instance due to error 704
Instance terminated by USER, pid = 8519700
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (8519700) as a result of ORA-1092
Fri Nov 07 16:34:32 2014
ORA-1092 : opitsk aborting process

使用隐含参数拉库：

Fri Nov 07 21:29:00 2014
ALTER DATABASE RECOVER  database using backup controlfile until cancel  
Media Recovery Start
 started logmerger process
Fri Nov 07 21:29:00 2014
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 9 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 10 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 11 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 12 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 13 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 14 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 15 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 17 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 18 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 19 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 20 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 21 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 22 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 23 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 24 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 25 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 26 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 27 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 28 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 29 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 30 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 31 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 36 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 37 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 38 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 39 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 41 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 42 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 43 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 44 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 45 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 46 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 47 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 48 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 49 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 50 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 51 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
Parallel Media Recovery started with 16 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile until cancel  ...
ALTER DATABASE RECOVER    CANCEL  
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_pr00_9044064.trc:
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: '/oradata/TESTAPP/system01.dbf'
Slave exiting with ORA-1547 exception
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_pr00_9044064.trc:
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: '/oradata/TESTAPP/system01.dbf'
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Fri Nov 07 21:29:19 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 2603982708
Clearing online redo logfile 1 /oradata/TESTAPP/redo01.log
Clearing online log 1 of thread 1 sequence number 0
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradata/TESTAPP/redo02.log
Clearing online log 2 of thread 1 sequence number 0
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradata/TESTAPP/redo03.log
Clearing online log 3 of thread 1 sequence number 0
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 /oradata/TESTAPP/redo04.log
Clearing online log 4 of thread 1 sequence number 0
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 /oradata/TESTAPP/redo05.log
Clearing online log 5 of thread 1 sequence number 0
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 /oradata/TESTAPP/redo06.log
Clearing online log 6 of thread 1 sequence number 0
Clearing online redo logfile 6 complete
Online log /oradata/TESTAPP/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/TESTAPP/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/TESTAPP/redo03.log: Thread 1 Group 3 was previously cleared
Online log /oradata/TESTAPP/redo04.log: Thread 1 Group 4 was previously cleared
Online log /oradata/TESTAPP/redo05.log: Thread 1 Group 5 was previously cleared
Online log /oradata/TESTAPP/redo06.log: Thread 1 Group 6 was previously cleared
Fri Nov 07 21:29:25 2014
Setting recovery target incarnation to 2
Advancing SCN to 4294967296 according to _minimum_giga_scn
Fri Nov 07 21:29:25 2014
Assigning activation ID 264823552 (0xfc8e300)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oradata/TESTAPP/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Nov 07 21:29:25 2014
SMON: enabling cache recovery
[9568454] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:18950108 end:18950872 diff:764 (7 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TB_DHREP_TEMP' #7 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'EAST_TEMP' #30 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'ZFMI_TEMP' #36 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'RPT1' #47 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #32 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00032' in the controlfile.
This file can no longer be recovered so it must be dropped.
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_ora_9568454.trc  (incident=912216):
ORA-00600: 内部错误代码, 参数: [25016], [40], [43], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/testapp/TESTAPP/incident/incdir_912216/TESTAPP_ora_9568454_i912216.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 /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_ora_9568454.trc:
ORA-00600: 内部错误代码, 参数: [25016], [40], [43], [], [], [], [], [], [], [], [], []
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_ora_9568454.trc:
ORA-00600: 内部错误代码, 参数: [25016], [40], [43], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 9568454): terminating the instance due to error 600
Fri Nov 07 21:29:29 2014
Instance terminated by USER, pid = 9568454
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (9568454) as a result of ORA-1092
Fri Nov 07 21:29:29 2014
ORA-1092 : opitsk aborting process

这里看到由于他之前在OS上删除了文件，又重建了控制文件，因此数据字典中的文件信息和重建的控制文件不匹配
因此，报了上面的错误。
这时候使用使用隐含参数屏蔽system表空间检查，并屏蔽只读打开状态的字典检查，并使用gdb跳过数据字典检查，再次resetlog数据库试试看：

(CDKF177:oracle)/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期六 11月 8 18:24:56 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup nomount pfile=/tmp/spfile.bak
ORACLE 例程已经启动。

Total System Global Area 6413680640 bytes
Fixed Size                  2233480 bytes
Variable Size            3942648696 bytes
Database Buffers         2449473536 bytes
Redo Buffers               19324928 bytes
SQL> @controlfile

控制文件已创建。

SQL> recover database using backup controlfile until cancel;
ORA-00279: ?? 7516192769 (? 11/08/2014 17:58:22 ??) ???? 1 ????
ORA-00289: ??: /oradata/archivelog/1_1_863114302.dbf
ORA-00280: ?? 7516192769 (???? 1) ??? #1 ?


指定日志: {<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: '/oradata/TESTAPP/system01.dbf'


ORA-01112: ???????

这里是AIX系统：

(gdb) commands 
Type commands for when breakpoint 1 is hit, one per line.
End with a line saying just "end".
>set *0x0700000000021C80=0x0 
>cont 
>end 
(gdb) cont 

这是看到，已经跳过了数据字典检查，并且报错是ORA-00604 ORA-00376 ORA-01110
这个跟我以前处理自己的一次测试很相似了，参见《艰难的修复数据库过程，却发现Oracle 11.2果然强大》
alert信息如下：

Sat Nov 08 18:30:30 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 7516192769
Clearing online redo logfile 1 /oradata/TESTAPP/redo01.log
Clearing online log 1 of thread 1 sequence number 1
Clearing online redo logfile 1 complete
Resetting resetlogs activation ID 264909299 (0xfca31f3)
Online log /oradata/TESTAPP/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/TESTAPP/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/TESTAPP/redo03.log: Thread 1 Group 3 was previously cleared
Online log /oradata/TESTAPP/redo04.log: Thread 1 Group 4 was previously cleared
Online log /oradata/TESTAPP/redo05.log: Thread 1 Group 5 was previously cleared
Online log /oradata/TESTAPP/redo06.log: Thread 1 Group 6 was previously cleared
Sat Nov 08 18:30:30 2014
Setting recovery target incarnation to 2
Advancing SCN to 10737418240 according to _minimum_giga_scn
Sat Nov 08 18:30:30 2014
Assigning activation ID 264929176 (0xfca7f98)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oradata/TESTAPP/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Nov 08 18:30:30 2014
SMON: enabling cache recovery
[10223722] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:94615629 end:94615676 diff:47 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_ora_10223722.trc:
ORA-00604: ?? SQL ?? 1 ????
ORA-00376: ???????? 1
ORA-01110: ???? 1: '/oradata/TESTAPP/system01.dbf'
Errors in file /oracle/diag/rdbms/testapp/TESTAPP/trace/TESTAPP_ora_10223722.trc:
ORA-00604: ?? SQL ?? 1 ????
ORA-00376: ???????? 1
ORA-01110: ???? 1: '/oradata/TESTAPP/system01.dbf'
Error 604 happened during db open, shutting down database
USER (ospid: 10223722): terminating the instance due to error 604
Instance terminated by USER, pid = 10223722
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (10223722) as a result of ORA-1092
Sat Nov 08 18:30:32 2014
ORA-1092 : opitsk aborting process

再次尝试重建控制文件，使用gdb跳过字典检查，然后使用open upgrade尝试打开库（因为此时数据文件的scn都一致了）：

(gdb) commands
Type commands for when breakpoint 1 is hit, one per line.
End with a line saying just "end".
>set *0x0700000000021C80=0x0 
>cont
>end
(gdb) cont
Continuing.

尝试open数据库，发现数据库又出现了ORA-00600 [25016]

SQL> alter database open upgrade;
alter database open upgrade
*
第 1 行出现错误:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: '/oradata/TESTAPP/system01.dbf'


SQL> recover database;
完成介质恢复。
SQL> alter database open upgrade;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [25016], [40], [43], [], [], [], [],
[], [], [], [], []
进程 ID: 10879136
会话 ID: 570 序列号: 3


SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

这至少说明一个问题，没有使用resetlogs的时候，控制文件并没有损坏，因此，不重建控制文件，直接恢复数据库，然后open upgrade：

(CDKF177:oracle)/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 11月 9 00:15:24 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup restrict mount pfile=/tmp/spfile.bak
ORACLE 例程已经启动。

Total System Global Area 6413680640 bytes
Fixed Size                  2233480 bytes
Variable Size            3942648696 bytes
Database Buffers         2449473536 bytes
Redo Buffers               19324928 bytes
数据库装载完毕。
SQL> alter database open upgrade;
alter database open upgrade
*
第 1 行出现错误:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: '/oradata/TESTAPP/system01.dbf'


SQL> recover database;
完成介质恢复。
SQL> alter database open upgrade;

数据库已更改。

SQL> 

数据库已经open了。