非归档遭遇ora-00600 [kcratr_nab_less_than_odr]的恢复

刚刚很早之前认识的一个朋友说他们公司的开发库挂了,让我QQ远程帮忙看看,花了10分钟解决了了这个问题，分享一下.

主要遇到了如下几个问题:

1. mount 发现控制文件异常,通过替换,用pfile mount成功，这个不说了.
2. open报了一个如下的错误：

Fri Jul 04 20:03:23 2014
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 229 KB redo, 0 data blocks need recovery
Errors in file d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\trace\yunhaoorcl_ora_3416.trc  (incident=160589):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [11783], [8], [1181], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\incident\incdir_160589\yunhaoorcl_ora_3416_i160589.trc
Aborting crash recovery due to error 600
Errors in file d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\trace\yunhaoorcl_ora_3416.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [11783], [8], [1181], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\trace\yunhaoorcl_ora_3416.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [11783], [8], [1181], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...
Fri Jul 04 20:03:25 2014
Trace dumping is performing id=[cdmp_20140704200325]
Fri Jul 04 20:03:27 2014
Sweep [inc][160589]: completed
Sweep [inc2][160589]: completed
复制

对于这个,比较少见,猜测可能是instance recovery的时候出现问题了。尝试手工recover database:

SQL> recover database;
完成介质恢复。
SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     11781
当前日志序列           11783
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [11783], [8],
[1181], [], [], [], [], [], [], []
复制

手工recover发现不行,看alert log报了一个error，看下对应的trace如下：

*** 2014-07-04 20:03:23.792
Successfully allocated 15 recovery slaves
Using 10 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 11782, block 2, scn 230294634
 cache-low rba: logseq 11782, block 52177
 on-disk rba: logseq 11783, block 1181, scn 230308328
 start recovery at logseq 11782, block 52177, scn 0

*** 2014-07-04 20:03:24.058
Started writing zeroblks thread 1 seq 11783 blocks 8-15

*** 2014-07-04 20:03:24.058
Completed writing zeroblks thread 1 seq 11783
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4322Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 229Kb in 0.11s => 2.04 Mb/sec
Longest record: 3Kb, moves: 0/269 (0{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b})
Change moves: 6/101 (5{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}), moved: 0Mb
Longest LWN: 5Kb, moves: 0/115 (0{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}), moved: 0Mb
Last redo scn: 0x0000.0dba37ae (230307758)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 65536
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
WARNING! Crash recovery of thread 1 seq 11783 is
ending at redo block 8 but should not have ended before
redo block 1181
复制

我们来仔细观察一下这个instance recovery的信息，得到如下的信息：
Thread 1 checkpoint: logseq 11782, block 2, scn 230294634
cache-low rba: logseq 11782, block 52177
on-disk rba: logseq 11783, block 1181, scn 230308328
start recovery at logseq 11782, block 52177, scn 0

线程检查点:    logseq 11782, block 2, scn 230294634
low cache rba: logseq 11782, block 52177
on disk rba:   logseq 11783, block 1181, scn 230308328

可以看到实例恢复的起点是low cache rba(实际上oracle会比较线程检查点和low cache rba，选择其中的较大者作为实例恢复的启点).

最后我们再来仔细分析下这个错误：
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [11783], [8],[1181], [], [], [], [], [], [], []

kcratr_nab_less_than_odr： 大胆猜测应该在进行比较某个值
[1] :     应该是指的thread number
[11783]： on disk rba的logseq 值
[8]： 这里未知
[1181]：  on disk rba的block号

最后搜了一下MOS,Oracle 给出的解释如下，关于这个ora-00600错误：

This caused a lost Write into the Online RedoLogs and so Instance Recovery is not possible and raising the ORA-600.

通俗一点讲,即online log的写丢失导致实例恢复无法恢复到指定的点，进而抛出这个ora-00600错误。

对于on disk rba,这个是oracle instance recovery必须要达到的值,如果无法恢复到该值，那么将会出现异常，类似这里的问题.

既然明白了这一点，那么恢复就很容易了,首先重建下controlfile:

SQL> CREATE CONTROLFILE REUSE DATABASE "YUNHAOOR" NORESETLOGS  NOARCHIVELOG
 2      MAXLOGFILES 16
 3      MAXLOGMEMBERS 3
 4      MAXDATAFILES 100
 5      MAXINSTANCES 8
 6      MAXLOGHISTORY 1168
 7  LOGFILE
 8    GROUP 1 'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
 9    GROUP 2 'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\SYSTEM01.DBF',
 14    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\SYSAUX01.DBF',
 15    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\UNDOTBS01.DBF',
 16    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\USERS01.DBF',
 17    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\FENG_SPACE.DBF',
 18    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\SYSTEM02.DBF',
 19    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\SYSTEM03.DBF',
 20    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\USERS2',
 21    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\SYSAUX1',
 22    'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\SYSTEM1'
 23  CHARACTER SET ZHS16GBK
 24  ;

控制文件已创建。

SQL> recover database;
完成介质恢复。
复制

最后打开数据库，发现又报错了，不过还好，这是一个只要是DBA知道怎么解决的错了，如下：

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [],
[], [], [], []
进程 ID: 6760
会话 ID: 534 序列号: 1


SQL>
复制

实际上查看alert log 还看到了ora-00600 [4193]错误。 这个处理方法一样，不累述。

(incident=171742):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\incident\incdir_171742\yunhaoorcl_smon_3884_i171742.trc
No Resource Manager plan active
Errors in file d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\trace\yunhaoorcl_ora_6760.trc  (incident=171790):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\incident\incdir_171790\yunhaoorcl_ora_6760_i171790.trc
Fri Jul 04 20:12:34 2014
Trace dumping is performing id=[cdmp_20140704201234]
Trace dumping is performing id=[cdmp_20140704201235]
Doing block recovery for file 3 block 217
Resuming block recovery (PMON) for file 3 block 217
Block recovery from logseq 11784, block 63 to scn 230347995
Recovery of Online Redo Log: Thread 1 Group 3 Seq 11784 Reading mem 0
 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\REDO03.LOG
Block recovery stopped at EOT rba 11784.67.16
Block recovery completed at rba 11784.67.16, scn 0.230347992
Doing block recovery for file 3 block 144
Resuming block recovery (PMON) for file 3 block 144
Block recovery from logseq 11784, block 63 to scn 230347989
Recovery of Online Redo Log: Thread 1 Group 3 Seq 11784 Reading mem 0
 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\REDO03.LOG
Block recovery completed at rba 11784.65.16, scn 0.230347991
Errors in file d:\app\administrator\diag\rdbms\yunhaoorcl\yunhaoorcl\trace\yunhaoorcl_smon_3884.trc:
ORA-01595: error freeing extent (2) of rollback segment (2))
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
复制

对于ora-00600 4193/4194错误,通过pfile指定undo_management=maual,然后启库重建undo即可,如下：

SQL> create undo tablespace undotbs2 datafile 'D:\APP\ADMINISTRATOR\ORADATA\YUNHAOORCL\undotbs2_01.dbf
 2  size 4096m;

表空间已创建。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount pfile='D:\1.ora'
ORACLE 例程已经启动。

Total System Global Area 9620525056 bytes
Fixed Size                  2183872 bytes
Variable Size            4395633984 bytes
Database Buffers         5200936960 bytes
Redo Buffers               21770240 bytes
数据库装载完毕。
SQL> alter database open;

数据库已更改。

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL> drop tablespace undotbs1 including contents and datafiles;

表空间已删除。
复制

备注：对于重建controlfile后，记得添加tempfile，不要给人接埋地雷！

ORA-600 [kcratr_nab_less_than_odr] during Instance Recovery after Database Crash (文档 ID 1299564.1)
Alter database open fails with ORA-00600 kcratr_nab_less_than_odr (文档 ID 1296264.1)