ORA-00600 [kcratr_nab_less_than_odr] [4194]錯誤解決辦法

業務場景：公司電纜被挖斷，突然斷電導致的宕機。

 [oracle@slave1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 17 11:09:59 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup mount  --載入到mount狀態

ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1023412336 bytes
Database Buffers          570425344 bytes
Redo Buffers                7360512 bytes
Database mounted.

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[199], [48589], [48614], [], [], [], [], [], [], []

[oracle@slave1 trace]$ cd /u01/app/oracle/diag/rdbms/zxdb/zxdb/trace

  --查看警報日誌，具體錯誤看日誌里寫的trc文件。伺服器異常斷電，導致LGWR寫聯機日誌文件時報錯，下次重啟資料庫時，需要實例級恢復，而又無法從聯機日誌文件中獲取到redo信息，因為上次斷電，寫日誌報錯。

Trace file /u01/app/oracle/diag/rdbms/chuzhouqyhx/chuzhouqyhx/trace/chuzhouqyhx_ora_6857.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      slave1
Release:        2.6.32-431.el6.x86_64
Version:        #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine:        x86_64
Instance name: chuzhouqyhx
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 6857, image: oracle@slave1 (TNS V1-V3)


*** 2017-04-13 09:38:01.921
*** SESSION ID:(191.3) 2017-04-13 09:38:01.921
*** CLIENT ID:() 2017-04-13 09:38:01.921
*** SERVICE NAME:() 2017-04-13 09:38:01.921
*** MODULE NAME:(sqlplus@slave1 (TNS V1-V3)) 2017-04-13 09:38:01.921
*** ACTION NAME:() 2017-04-13 09:38:01.921
 
Successfully allocated 3 recovery slaves
Using 45 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 199, block 2, scn 4644236
  cache-low rba: logseq 199, block 48589
    on-disk rba: logseq 199, block 48614, scn 4653640
  start recovery at logseq 199, block 48589, scn 0

*** 2017-04-13 09:38:01.930
Started writing zeroblks thread 1 seq 199 blocks 48589-48596

*** 2017-04-13 09:38:01.932
Completed writing zeroblks thread 1 seq 199
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 0Kb in 0.01s => 0.00 Mb/sec
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
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 199 is
ending at redo block 48589 but should not have ended before
redo block 48614
Incident 6154 created, dump file: /u01/app/oracle/diag/rdbms/chuzhouqyhx/chuzhouqyhx/incident/incdir_6154/chuzhouqyhx_ora_6857_i6154.trc
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [199], [48589], [48614], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [199], [48589], [48614], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [199], [48589], [48614], [], [], [], [], [], [], []

SQL> recover database until cancel using backup controlfile; --文件選擇的時候，日誌要選對，在alert日誌里有寫編號。

ORA-00279: change 4644236 generated at 04/12/2017 22:03:56 needed for thread 1
ORA-00289: suggestion :
/oradata/flash_recovery_area/CHUZHOUQYHX/archivelog/2017_04_13/o1_mf_1_199_%u_.a
rc
ORA-00280: change 4644236 for thread 1 is in sequence #199


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/chuzhouqyhx/redo01.log        
Log applied.
Media recovery complete.

SQL> alter database open;  --

alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

alter database open RESETLOGS; --

--解決方案

從這裡可以看到出現了ORA-600[4194]/[4193]，第一感覺就是undo出現問題。
4193：表示undo和redo不一致（Arg [a] Undo record seq number，Arg [b] Redo record seq number ）；
4194：表示也是undo和redo不一致（Arg [a] Maximum Undo record number in Undo block，Arg [b] Undo record number from Redo block）

所以出現4193錯誤，解決方法跟4194一樣。

Sat Apr 15 10:08:17 2017
Sweep [inc][11037]: completed
Sweep [inc2][11037]: completed
Sat Apr 15 14:07:16 2017
Errors in file /u01/app/oracle/diag/rdbms/chuzhouqyhx/chuzhouqyhx/trace/chuzhouqyhx_j000_29485.trc  (incident=11028):
ORA-01578: ORACLE data block corrupted (file # 2, block # 62520)
ORA-01110: data file 2: '/oradata/chuzhouqyhx/sysaux01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

[oracle@slave1 trace]$ tail -100f alert_chuzhouqyhx.log

Database altered.


Thu Apr 13 10:02:58 2017
Doing block recovery for file 3 block 1661
Resuming block recovery (PMON) for file 3 block 1661
Block recovery from logseq 1, block 53 to scn 4653766
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/chuzhouqyhx/redo01.log
Block recovery completed at rba 1.63.16, scn 0.4653770
Errors in file /u01/app/oracle/diag/rdbms/chuzhouqyhx/chuzhouqyhx/trace/chuzhouqyhx_pmon_6817.trc  (incident=6018):
ORA-00600: internal error code, arguments: [4194], [0x000000004], [0x000000000], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/chuzhouqyhx/chuzhouqyhx/trace/chuzhouqyhx_pmon_6817.trc:
ORA-00600: internal error code, arguments: [4194], [0x000000004], [0x000000000], [], [], [], [], [], [], [], [], []
PMON (ospid: 6817): terminating the instance due to error 472
Instance terminated by PMON, pid = 6817

--新建個臨時表空間，修改預設spflie里的undo_tablespace參數。記得手動修改下pflie的預設表空間參數。

SQL>CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oradata/chuzhouqyhx/undotbs02.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 8G;

SQL>alter system set undo_tablespace='UNDOTBS2' scope=both;

[oracle@slave1 dbs]$ cat spfilechuzhouqyhx.ora --查看下undo_tablespace參數

chuzhouqyhx.__java_pool_size=16777216
chuzhouqyhx.__large_pool_size=16777216
chuzhouqyhx.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
chuzhouqyhx.__pga_aggregate_target=536870912
chuzhouqyhx.__sga_target=1610612736
chuzhouqyhx.__shared_io_pool_size=0
chuzhouqyhx.__shared_pool_size=956301312
chuzhouqyhx.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/chuzhouqyhx/adump'
*.audit_trail='db'
*.compatible='11.2
CC"
|0.0.0'
*.control_files='/oradata/chuzhouqyhx/control01.ctl','/oradata/flash_recovery_area/chuzhouqyhx/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='chuzhouq'
*.db_recovery_file_dest='/oradata/flash_recovery_area'
*.db_recovery_file_dest_size=2936012800
*.db_unique_name='chuzhouqyhx'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chuzhouqyhxXDB)'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile
CC"
5f='EXCLUSIVE'
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS2'

SQL>shutdown immediate --關閉實例

SQL>startup --啟動資料庫