ORA-00600:[kcratr_nab_less_than_odr], 分析解决

今天，在测试库测试时，在alter database open 时发现报：
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227],[10276], [], [], [], [], [], [], []

导致原因：这可能是由于控制文件的缺失，或者在线日志文件在实例恢复时不完整
尝试以下操作均未成功：

点击(此处)折叠或打开

1. SQL> alter database open resetlogs;
   
2. alter database open resetlogs
   
3. *
   
4. ERROR at line 1:
   
5. ORA-01139: RESETLOGS option only valid after an incomplete database recovery
   
6.  
   
7.  
   
8. SQL> recover database;
   
9. ORA-00283: recovery session canceled due to errors
   
10. ORA-00264: no recovery required
    
11.  
    
12.  
    
13. SQL> alter database open;
    
14. alter database open
    
15. *
    
16. ERROR at line 1:
    
17. ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [3464], [12432], [12534], [], [], [], [], [], [], []

查看告警日志：

点击(此处)折叠或打开

1. LTER DATABASE OPEN
2. Beginning crash recovery of 1 threads
3. parallel recovery started with 3 processes
4. Started redo scan
5. Completed redo scan
6. read 9919 KB redo, 1763 data blocks need recovery
7. Errors in file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_7784.trc (incident=201788):
8. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
9. Incident details in: d:\app\chjayhsx\diag\rdbms\orcl\orcl\incident\incdir_201788\orcl_ora_7784_i201788.trc
10. Tue Sep 15 10:08:32 2015
11. Trace dumping is performing id=[cdmp_20150915100832]
12. Aborting crash recovery due to error 600
13. Errors in file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_7784.trc:
14. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
15. Errors in file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_7784.trc:
16. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
17. ORA-600 signalled during: ALTER DATABASE OPEN...
18. Tue Sep 15 10:08:52 2015
19. alter database open
20. Beginning crash recovery of 1 threads
21. parallel recovery started with 3 processes
22. Started redo scan
23. Completed redo scan
24. read 9919 KB redo, 1763 data blocks need recovery
25. Errors in file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_7784.trc (incident=201789):
26. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
27. Incident details in: d:\app\chjayhsx\diag\rdbms\orcl\orcl\incident\incdir_201789\orcl_ora_7784_i201789.trc
28. Aborting crash recovery due to error 600
29. Errors in file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_7784.trc:
30. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
31. Errors in file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_7784.trc:
32. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
33. ORA-600 signalled during: alter database open

查看相应的trace文件

点击(此处)折叠或打开

1. Trace file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_7784.trc
2. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
4. Windows NT Version V6.2
5. CPU : 4 - type 8664, 4 Physical Cores
6. Process Affinity : 0x0x0000000000000000
7. Memory (Avail/Total): Ph:1458M/3982M, Ph+PgF:4383M/8078M
8. Instance name: orcl
9. Redo thread mounted by this instance: 1
10. Oracle process number: 23
11. Windows thread id: 7784, image: ORACLE.EXE (SHAD)
14. *** 2015-09-15 10:08:29.402
15. *** SESSION ID:(191.3) 2015-09-15 10:08:29.402
16. *** CLIENT ID:() 2015-09-15 10:08:29.402
17. *** SERVICE NAME:() 2015-09-15 10:08:29.402
18. *** MODULE NAME:(sqlplus.exe) 2015-09-15 10:08:29.402
19. *** ACTION NAME:() 2015-09-15 10:08:29.402
21. Successfully allocated 3 recovery slaves
22. Using 45 overflow buffers per recovery slave
23. Thread 1 checkpoint: logseq 289, block 2, scn 7805163
25. *** 2015-09-15 10:08:29.590
26. cache-low rba: logseq 289, block 1978
27. on-disk rba: logseq 290, block 10276, scn 7808333
28. start recovery at logseq 289, block 1978, scn 0
30. *** 2015-09-15 10:08:30.067
31. Started writing zeroblks thread 1 seq 290 blocks 8227-8234
33. *** 2015-09-15 10:08:30.068
34. Completed writing zeroblks thread 1 seq 290
35. ==== Redo read statistics for thread 1 ====
36. Total physical reads (from disk and memory): 13999Kb
37. -- Redo read_disk statistics --
38. Read rate (ASYNC): 9920Kb in 0.54s => 17.94 Mb/sec
39. Longest record: 14Kb, moves: 0/36179 (0%)
40. Change moves: 2/32 (6%), moved: 0Mb
41. Longest LWN: 1024Kb, moves: 2/247 (0%), moved: 2Mb
42. Last redo scn: 0x0000.00772509 (7808265)
43. ----------------------------------------------
44. ----- Recovery Hash Table Statistics ---------
45. Hash table buckets = 32768
46. Longest hash chain = 2
47. Average hash chain = 1763/1726 = 1.0
48. Max compares per lookup = 2
49. Avg compares per lookup = 70120/71549 = 1.0
50. ----------------------------------------------
51. WARNING! Crash recovery of thread 1 seq 290 is
52. ending at redo block 8227 but should not have ended before
53. redo block 10276
54. Incident 201788 created, dump file: d:\app\chjayhsx\diag\rdbms\orcl\orcl\incident\incdir_201788\orcl_ora_7784_i201788.trc
55. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
58. *** 2015-09-15 10:08:33.089
59. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
60. ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [290], [8227], [10276], [], [], [], [], [], [], []
62. *** 2015-09-15 10:08:53.024
63. Successfully allocated 3 recovery slaves
64. Using 45 overflow buffers per recovery slave
65. Thread 1 checkpoint: logseq 289, block 2, scn 7805163
66. cache-low rba: logseq 289, block 1978
67. on-disk rba: logseq 290, block 10276, scn 7808333
68. start recovery at logseq 289, block 1978, scn 0

可以看出ORA-00600: internal error code, arguments:[kcratr_nab_less_than_odr], [1], [290], [8227], [10276], 和TRACE里的错误提示WARNING! Crash recovery of thread 1 seq290 is ending at redo block 12432 but should not have ended before redo block 12534 ，应该是由于我强制关闭电脑，导致LGWR写联机日志文件时失败，下次重新启动数据库时，需要做实例级恢复，而又无法从联机日志文件里获取到这些redo信息，因为上次关闭时，写日志失败了。


那么ORA-00600: internal error code, arguments:[kcratr_nab_less_than_odr], [1], [290], [8227], [10276]这些参数是什么意思呢？
实例需要恢复日志序列号为290的联机日志文件，需要恢复到编号为10276的日志块，而实际上只能恢复到第8227个日志块儿，所以库就启不来了。不过，从当前日志文件信息，可以看到，当前日志组的确是290：
ps：参见eygle博客->http://www.eygle.com/archives/2010/05/kcratr_nab_less_than_odr.html


解决办法：还原或者恢复这些文件
根据MOS Alter database open fails with ORA-00600 kcratr_nab_less_than_odr (Doc ID 1296264.1)

现在来尝试下进行控制文件的重建：

点击(此处)折叠或打开

1. SQL> Show parameter control_files
3. NAME TYPE VALUE
4. ------------------------------------ ----------- ------------------------------
5. control_files string D:\APP\CHJAYHSX\ORADATA\ORCL\C
6. ONTROL01.CTL, D:\APP\CHJAYHSX\
7. FLASH_RECOVERY_AREA\ORCL\CONTR
8. OL02.CTL
9. SQL> oradebug setmypid
10. 已处理的语句
11. SQL> Alter session set tracefile_identifier='controlfilerecreate' ;
13. 会话已更改。
15. SQL> Alter database backup controlfile to trace noresetlogs;
17. 数据库已更改。
19. SQL> oradebug tracefile_name ;
20. d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_3768_controlfilerecreate.trc
21. SQL> shutdown immediate
22. ORA-01109: 数据库未打开
25. 已经卸载数据库。
26. ORACLE 例程已经关闭。
27. SQL> startup nomount;
28. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
29. ORACLE 例程已经启动。
31. Total System Global Area 1670221824 bytes
32. Fixed Size 2176328 bytes
33. Variable Size 1291848376 bytes
34. Database Buffers 369098752 bytes
35. Redo Buffers 7098368 bytes
36. SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
37. 2 MAXLOGFILES 16
38. 3 MAXLOGMEMBERS 3
39. 4 MAXDATAFILES 100
40. 5 MAXINSTANCES 8
41. 6 MAXLOGHISTORY 292
42. 7 LOGFILE
43. 8 GROUP 1 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO01.LOG' SIZE 10M BLOCKSIZE 512,
44. 9 GROUP 2 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO02.LOG' SIZE 10M BLOCKSIZE 512,
45. 10 GROUP 3 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO03.LOG' SIZE 10M BLOCKSIZE 512,
46. 11 GROUP 4 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO04.LOG' SIZE 10M BLOCKSIZE 512,
47. 12 GROUP 5 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO05.LOG' SIZE 10M BLOCKSIZE 512,
48. 13 GROUP 6 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO06.LOG' SIZE 10M BLOCKSIZE 512,
49. 14 GROUP 7 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO07.LOG' SIZE 10M BLOCKSIZE 512
50. 15 -- STANDBY LOGFILE
51. 16 DATAFILE
52. 17 'D:\APP\CHJAYHSX\ORADATA\ORCL\SYSTEM01.DBF',
53. 18 'D:\APP\CHJAYHSX\ORADATA\ORCL\SYSAUX01.DBF',
54. 19 'D:\APP\CHJAYHSX\ORADATA\ORCL\UNDOTBS01.DBF',
55. 20 'D:\APP\CHJAYHSX\ORADATA\ORCL\USERS01.DBF',
56. 21 'D:\APP\CHJAYHSX\ORADATA\ORCL\EXAMPLE01.DBF',
57. 22 'D:\APP\CHJAYHSX\ORADATA\ORCL\RMAN_TBS01.DBF',
58. 23 'D:\APP\CHJAYHSX\ORADATA\ORCL\MANAGE_TEST.DBF',
59. 24 'D:\APP\CHJAYHSX\ORADATA\ORCL\TEST01.DBF',
60. 25 'D:\APP\CHJAYHSX\ORADATA\ORCL\SYSTEM02.DBF'
61. 26 CHARACTER SET ZHS16GBK
62. 27 ;
64. 控制文件已创建。
66. SQL> RECOVER DATABASE ;
67. 完成介质恢复。
68. SQL> alter database open;
70. 数据库已更改。
72. SQL>

PS：这个重建脚本怎么来的呢？
SQL> oradebug setmypid 
SQL> Alter session set tracefile_identifier='controlfilerecreate' ;
SQL> Alter database backup controlfile to trace noresetlogs;
SQL> oradebug tracefile_name ; --> This command will give the path and name of the trace file

执行这些后，会在trace文件目录下，生成一个新的trc文件，

点击(此处)折叠或打开

1. Trace file d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_3768_controlfilerecreate.trc
   
2. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
   
3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
4. Windows NT Version V6.2
   
5. CPU : 4 - type 8664, 4 Physical Cores
   
6. Process Affinity : 0x0x0000000000000000
   
7. Memory (Avail/Total): Ph:1030M/3982M, Ph+PgF:3770M/8078M
   
8. Instance name: orcl
   
9. Redo thread mounted by this instance: 1
   
10. Oracle process number: 23
    
11. Windows thread id: 3768, image: ORACLE.EXE (SHAD)
    
12. 
    
13. 
    
14. *** 2015-09-15 12:20:28.646
    
15. *** SESSION ID:(191.37) 2015-09-15 12:20:28.646
    
16. *** CLIENT ID:() 2015-09-15 12:20:28.646
    
17. *** SERVICE NAME:() 2015-09-15 12:20:28.646
    
18. *** MODULE NAME:(sqlplus.exe) 2015-09-15 12:20:28.646
    
19. *** ACTION NAME:() 2015-09-15 12:20:28.646
    
20.  
    
21. 
    
22. *** TRACE CONTINUED FROM FILE d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_3768.trc ***
    
23. 
    
24. 
    
25. *** 2015-09-15 12:20:28.642
    
26. -- The following are current System-scope REDO Log Archival related
    
27. -- parameters and can be included in the database initialization file.
    
28. --
    
29. -- LOG_ARCHIVE_DEST=''
    
30. -- LOG_ARCHIVE_DUPLEX_DEST=''
    
31. --
    
32. -- LOG_ARCHIVE_FORMAT=ARC%S_%R.%T
    
33. --
    
34. -- DB_UNIQUE_NAME="orcl"
    
35. --
    
36. -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
    
37. -- LOG_ARCHIVE_MAX_PROCESSES=4
    
38. -- STANDBY_FILE_MANAGEMENT=MANUAL
    
39. -- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS
    
40. -- FAL_CLIENT=''
    
41. -- FAL_SERVER=''
    
42. --
    
43. -- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
    
44. -- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
    
45. -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
    
46. -- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
    
47. -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
    
48. -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
    
49. -- LOG_ARCHIVE_DEST_STATE_1=ENABLE
    
50. --
    
51. -- The following commands will create a new control file and use it
    
52. -- to open the database.
    
53. -- Data used by Recovery Manager will be lost.
    
54. -- Additional logs may be required for media recovery of offline
    
55. -- Use this only if the current versions of all online logs are
    
56. -- available.
    
57. -- After mounting the created controlfile, the following SQL
    
58. -- statement will place the database in the appropriate
    
59. -- protection mode:
    
60. -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
    
61. STARTUP NOMOUNT
    
62. CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
    
63.     MAXLOGFILES 16
    
64.     MAXLOGMEMBERS 3
    
65.     MAXDATAFILES 100
    
66.     MAXINSTANCES 8
    
67.     MAXLOGHISTORY 292
    
68. LOGFILE
    
69.   GROUP 1 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO01.LOG' SIZE 10M BLOCKSIZE 512,
    
70.   GROUP 2 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO02.LOG' SIZE 10M BLOCKSIZE 512,
    
71.   GROUP 3 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO03.LOG' SIZE 10M BLOCKSIZE 512,
    
72.   GROUP 4 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO04.LOG' SIZE 10M BLOCKSIZE 512,
    
73.   GROUP 5 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO05.LOG' SIZE 10M BLOCKSIZE 512,
    
74.   GROUP 6 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO06.LOG' SIZE 10M BLOCKSIZE 512,
    
75.   GROUP 7 'D:\APP\CHJAYHSX\ORADATA\ORCL\REDO07.LOG' SIZE 10M BLOCKSIZE 512
    
76. -- STANDBY LOGFILE
    
77. DATAFILE
    
78.   'D:\APP\CHJAYHSX\ORADATA\ORCL\SYSTEM01.DBF',
    
79.   'D:\APP\CHJAYHSX\ORADATA\ORCL\SYSAUX01.DBF',
    
80.   'D:\APP\CHJAYHSX\ORADATA\ORCL\UNDOTBS01.DBF',
    
81.   'D:\APP\CHJAYHSX\ORADATA\ORCL\USERS01.DBF',
    
82.   'D:\APP\CHJAYHSX\ORADATA\ORCL\EXAMPLE01.DBF',
    
83.   'D:\APP\CHJAYHSX\ORADATA\ORCL\RMAN_TBS01.DBF',
    
84.   'D:\APP\CHJAYHSX\ORADATA\ORCL\MANAGE_TEST.DBF',
    
85.   'D:\APP\CHJAYHSX\ORADATA\ORCL\TEST01.DBF',
    
86.   'D:\APP\CHJAYHSX\ORADATA\ORCL\SYSTEM02.DBF'
    
87. CHARACTER SET ZHS16GBK
    
88. ;
    
89. -- Commands to re-create incarnation table
    
90. -- Below log names MUST be changed to existing filenames on
    
91. -- disk. Any one log file from each branch can be used to
    
92. -- re-create incarnation records.
    
93. -- ALTER DATABASE REGISTER LOGFILE 'D:\APP\CHJAYHSX\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_09_15\O1_MF_1_1_%U_.ARC';
    
94. -- ALTER DATABASE REGISTER LOGFILE 'D:\APP\CHJAYHSX\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_09_15\O1_MF_1_1_%U_.ARC';
    
95. -- ALTER DATABASE REGISTER LOGFILE 'D:\APP\CHJAYHSX\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_09_15\O1_MF_1_1_%U_.ARC';
    
96. -- Recovery is required if any of the datafiles are restored backups,
    
97. -- or if the last shutdown was not normal or immediate.
    
98. RECOVER DATABASE
    
99. -- All logs need archiving and a log switch is needed.
    
100. ALTER SYSTEM ARCHIVE LOG ALL;
     
101. -- Database can now be opened normally.
     
102. ALTER DATABASE OPEN;
     
103. -- Commands to add tempfiles to temporary tablespaces.
     
104. -- Online tempfiles have complete space information.
     
105. -- Other tempfiles may require adjustment.
     
106. ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\CHJAYHSX\ORADATA\ORCL\TEMP01.DBF' REUSE;
     
107. -- End of tempfile additions.
     
108. --
     
109. 
     
110. *** 2015-09-15 12:20:36.610
     
111. Processing Oradebug command 'tracefile_name '
     
112. 
     
113. *** 2015-09-15 12:20:36.610
     
114. Oradebug command 'tracefile_name ' console output:
     
115. d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_3768_controlfilerecreate.trc

这样就可以直接利用这个脚本重建控制文件了，然后打开数据库工作正常。

总结
方法一：利用在线日志还原
步骤：
SQL>Startup mount ;
SQL>Show parameter control_files
SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT' ;
SQL> Shutdown immediate;
（确保有备份文件）
SQL> Startup mount ;
SQL> recover database using backup controlfile until cancel ; (得开启快闪功能)
（利用control_file的查询值恢复）
SQL> Alter database open resetlogs ;

方法二：利用RMAN
把数据库起到mount状态
$ rman target /
rman> spool log to '/tmp/rman.log';
rman> list backup ;
rman> exit
SQL> Show parameter control_files 
SQL> oradebug setmypid 
SQL> Alter session set tracefile_identifier='controlfilerecreate' ;
SQL> Alter database backup controlfile to trace noresetlogs;
SQL> oradebug tracefile_name ; --> This command will give the path and name of the trace file
--Go to this location ,Open this trace file and select the controlfile recreation script with NOResetlogs option 
SQL> Shutdown immediate;
Rename the existing controlfile to <originalname>_old ---> This is Important as we need to have a backup of existing controlfile since we plan to recreate it
SQL> Startup nomount
Now run the Controlfile recreation script with NO Resetlogs option.
SQL> Alter database open ;
Rman> Catalog start with '<location of backupiece>' ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30430420/viewspace-1799925/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30430420/viewspace-1799925/