ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ORA-00600:[kcratr_nab_less_than_odr], 分析解决

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

原创 Oracle 作者：gydba 时间：2015-09-15 11:27:33 0 删除 编辑
今天，在测试库测试时，在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)


  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.  

  22. *** TRACE CONTINUED FROM FILE d:\app\chjayhsx\diag\rdbms\orcl\orcl\trace\orcl_ora_3768.trc ***


  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. --

  110. *** 2015-09-15 12:20:36.610
  111. Processing Oradebug command 'tracefile_name '

  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博客 ” ，链接：https://blog.itpub.net/30430420/viewspace-1799925/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle rac安装时先决条件检查
下一篇： 【优化思路集】临时表空间问题
请登录后发表评论 登录
全部评论
gydba

注册时间：2015-08-24

  • 博文量
    77
  • 访问量
    192120

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593