Oracle ORA-600[4193] 解決方案 說明

ORA-600[41XX] 這種錯誤基本都於UNDO 有關係。關於ORA-600參數的更多說明，參考我的Blog：ORA-600 各個參數含義說明  

ORA-600[4193] 這個錯誤也是與UNDO 有關係，MOS 上有幾篇相關的說明文章.

一.MOS說明

1.1 ORA-600 [4193] WhenTrying To Open The Database [ID 763566.1]

Symptoms

Copying databasefrom one server to another server and getting an ORA-600 [4193] error whentrying to open the database on the destination server.

--copy 資料庫從一個server 到另一個server 後，嘗試開啟時報這個錯誤。

Cause

The online redologs were copied when the source database was open, online redo logsshould never be copied when the database is open.

--導致原因是因為在資料庫open時把online redo logs 也一起copy 過去了。 在資料庫open狀態，online redo log 不應該copy。

Solution

In this instancethe datafiles were being copied properly after the tablespaces were put in tobackup mode, however, online redo logs should only be copied if the sourcedatabase is shutdown first before copying the online redo logs.  Thesource database needed to remain open so, the datafiles were copied again (withthe tablespaces in backup mode) and then a number of archivelogs weretransferred over to the new server and after the last archivelog was appliedthe database could be opened with resetlogs and new online redo logs werecreated on the destination server.

--當資料表空間被設定為backup 模式之後，可以copy 資料檔案，但是onlineredo log 只能是在資料庫shutdown 之後才能copy，如果資料庫一直是open 狀態，那麼只能把datafile copy 過去，然後把歸檔檔案傳送過去，最後用openresetlogs的方式開啟資料庫，在open時online redo log 會自動重建。

1.2 Ora-600 [4193] WhenOpening Or Shutting Down A Database [ID 452662.1]

1.2.1 Symptoms

Errors in alert.log:

Tue Jul 17 13:38:13 2007 
Errors in file /home/Oracle/oracle/product/10.2.0/yms/rdbms/log/yms_smon_8337.trc: 
ORA-00607: Internal error occurred while making a change to a data block 
ORA-00600: internal error code, arguments: [4193], [3552], [3554], [], [], [] 

yms_smon_8337.trc:

SO: 0xdfaec728, type: 24, owner: 0xdf266580, flag: INIT/-/-/0x00 

          (buffer) PR: 0xdf1f1338 FLG: 0x1000 
          class bit: 0x80000 
          kcbbfbp: [BH: 0xded4bf40, LINK: 0xdfaec768] 
          kcbbfbx[0]: [BH: 0xdece41d8, LINK: 0xdfaec788] 
          where: ktuwh01: ktugus, why: 0 
        buffer tsn: 2 rdba: 0x00c00002 (3/2) 
        scn: 0x0000.03c95628 seq: 0x01 flg: 0x00 tail: 0x56280e01 
        frmt: 0x02 chkval: 0x0000 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS 
      BH (0xdece41d8) file#: 3 rdba: 0x00c003b6 (3/950) class: 20 ba: 0x11d6ba000 
        set: 6 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 0 
        dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 
        hash: [df870f70,df870f70] lru: [dece4488,dece4028] 
        obj-flags: object_ckpt_list 
        ckptq: [dedac4a0,ded47cb8] fileq: [dedac500,ded47cc8] objq: [ded47d78,db7bfd78] 
        use: [dfaec788,dfaec788] wait: [NULL] 
        st: XCURRENT md: EXCL tch: 0 
        flags: mod_started gotten_in_current_mode block_written_once 
        change state: ACTIVE 
        change count: 1 
        LRBA: [0xac3.4de07.0] HSCN: [0xffff.ffffffff] HSUB: [65535] 
        Using State Objects 
          ---------------------------------------- 
          SO: 0xdfaec728, type: 24, owner: 0xdf266580, flag: INIT/-/-/0x00 
          (buffer) PR: 0xdf1f1338 FLG: 0x1000 
          class bit: 0x80000 
          kcbbfbp: [BH: 0xded4bf40, LINK: 0xdfaec768] 
          kcbbfbx[0]: [BH: 0xdece41d8, LINK: 0xdfaec788] 
          where: ktuwh01: ktugus, why: 0 
        buffer tsn: 2 rdba: 0x00c003b6 (3/950) 
        scn: 0x0000.03be3c7d seq: 0x5a flg: 0x04 tail: 0x3c7d025a 
        frmt: 0x02 chkval: 0x0868 type: 0x02=KTU UNDO BLOCK 
      ---------------------------------------- 
Error 607 in redo application callback 
TYP:0 CLS:20 AFN:3 DBA:0x00c003b6 OBJ:4294967295 SCN:0x0000.03be3c7d SEQ: 90 OP:5.1 
ktudb redo: siz: 132 spc: 4462 flg: 0x0012 seq: 0x0de2 rec: 0x09  

UNDO BLK: 
xid: 0x0002.045.00006c61 seq:0xde0 cnt: 0x60 irb: 0x60 icl: 0x0 flg: 0x0000

1.2.2 Cause

When we try toapply redo to an undo block (forward changes are made by  the applicationof redo to a block) we check that the seq# in the undo  record matches theseq# in the redo record.

--資料庫在啟動時需要進行一個前滾的操作，在前滾時會應用redo 到undo block上，操作時會檢查undorecord裡的seq#和 redo record裡的seq#.

These seq#should be the  same because when we apply a redo record we must apply itto the correct version of the block.

--正常情況下，這2者的seq# 應該是一致的。

We can onlyapply a redo record to a  block that contains the same seq# as in the redorecord. 

--在一致的情況下，我們才應用redo record 到undo record。

If the seq# do not match then ORA-600[4193][a].[b] is raised. .

Arg [a] Undorecord seq number --> seq: 0xde0 = 3552
Arg [b] Redo record seq number --> seq: 0x0de2   = 3554

 --如果不一致就會出現ORA-600[4193][a][b]的錯誤。其中a 是undo 裡的seq#記錄，b是redo 裡的seq# 值。 這裡的值都是十六進程，我們可以通過to_number() 這個函數來轉換一下：

SYS@anqing1(rac1)>  Select to_number('de0','xxxx') from dual;

TO_NUMBER('DE0','XXXX')

-----------------------

                   3552

This implies some kind of block corruptionin either the redo or the undo block. 

--當redo record 和 undo record 不一致時，就會拋出ORA-600[4193]的錯誤。

相關的文章參考：

Oracledatafile block 格式 說明
Oracle 執行個體恢複時 前滾（roll forward） 後滾（roll back） 問題

1.2.3 Solution

1.2.3.1 If Database is opened:

--在db open 狀態下，解決的方法如下：

1) Find out the rollback segment, based onthe first part of the xid: 0x0002.045.00006c61

  usn=2 is the segment_id

    selectsegment_name,status from dba_rollback_segs where segment_id=2;

    RS_DATA1   ONLINE

 

2) Dump the transaction table of the rollbacksegment to see if all TX are commited:

    alter system dump undoheader RS_DATA1;

Oracle dumpundo 說明

3) check the trace file created underuser_dump_dest

     In the trace filesearch for the Keyword "TRN TBL" 

TRN TBL::  

index state cflags wrap#   uel   scn            dba 
----------------------------------------------------------------------------- 
0x00   9     0x00 0x21eb1 0x0023 0x0000.d28c43e9 0x00000000 ......

state=9 means transaction is committed

 

4) offline the rollback segment:

     alter rollbacksegment rs_data1 offline; 
      select status from dba_rollback_segs wheresegment_id=2; 

5)   if STATUS=OFFLINE

      droprollback segment RS_DATA1;

1.2.3.2 If Database doesn't open:

--如果資料庫不是open狀態，處理方法如下：

1.   a) If using rollbacksegments, remove the rollback_segments line from init.ora, and open database

      b) If usingundo segments set undo_management = manual in init.ora/spfile, and try to opendatabase.

2. If database opens means all transactionsare committed, and you can drop the rollback segment or the undo tablespace

1.3 bug 導致的ORA-600[4193]

MOS:

ORA-600 [4193] "seq# mismatch whileadding undo record" [ID 39282.1]

Bug 8240762 - Undo corruptions with ORA-600[4193]/ORA-600 [4194] or ORA-600 [4137] [ID 8240762.8]

Undo corruptionmay be caused after a shrink and the same undo block may be used for two different transactions causing several internal errors like:

  ORA-600 [4193] / ORA-600 [4194] for newtransactions

 ORA-600 [4137] for a transaction rollback

 

Undo segment shrink is internally done by Oracle.

--undo shrink 導致的undo corruptions

 

Workaround

      Drop the undo segment.

 

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions >= 10.2 but BELOW 11.2
Versions confirmed as being affected	10.2.0.4 10.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

11.2.0.1 (Base Release) 11.1.0.7.10 Patch Set Update 10.2.0.5 (Server Patch Set) 11.1.0.7 Patch 42 on Windows Platforms 10.2.0.4 Patch 40 on Windows Platforms

 

在Oracle 10.2 以上到11.2 的DB 會受Bug 8240762的影響導致undo 的corruption。在10.2.0.5 中已經修複了這個bug。如果出現這種問題，drop 對應的undo segment 即可。

1 2 下一頁