Oracle ORA-600[4193] 解决方法 说明

Oracle ORA-600[4193] 解决方法 说明

分类： Oracle 故障解决案例 2012-02-07 11:18 152人阅读 评论(0) 举报

 

ORA-600[41XX] 这种错误基本都于UNDO 有关系。关于ORA-600参数的更多说明，参考我的Blog：

ORA-600 各个参数含义说明

http://blog.csdn.net/tianlesoftware/article/details/6645809

 

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 格式 说明

http://blog.csdn.net/tianlesoftware/article/details/6654786

 

Oracle 实例恢复时 前滚（roll forward） 后滚（roll back） 问题

http://blog.csdn.net/tianlesoftware/article/details/6286330

 

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 说明

http://blog.csdn.net/tianlesoftware/article/details/6672417

 

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
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

 

在Oracle 10.2 以上到11.2 的DB 会受Bug 8240762的影响导致undo 的corruption。在10.2.0.5 中已经修复了这个bug。如果出现这种问题，drop 对应的undo segment 即可。

 

 

二. 小结

       在第一部分列出了官方的一些解释，这里总结一下。

 

在启动数据库时会先进行一个forward 的操作，这时候会应用一些redo record 到undo block里，在apply 之前会先检查这2个block里面的seq#值，如果一致则ok，不一致，就会出现ORA-600[4193]的错误。

 

ORA-600[4193]的格式是：ORA-600[4193] [a].[b] ：

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

 

参数A 是undo 里的seq#, 参数B是redo 里的seq#值，这个值可以从trace里也查看，也可以直接dump 对应的块：

 

Oracle DumpRedo Log File 说明

http://blog.csdn.net/tianlesoftware/article/details/6670962

 

Oracle dumpundo 说明

http://blog.csdn.net/tianlesoftware/article/details/6672417

 

Oracledatafile block 格式 说明

http://blog.csdn.net/tianlesoftware/article/details/6654786

 

dump 出来的seq值是十六进制的，可以使用to_number 函数转换：

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

TO_NUMBER('DE0','XXXX')

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

     3552

 

导致ORA-600[4193]的错误原因，MOS 上提到了2个：

1.     在DB open状态下复制了online redo log。

在open 状态下的复制，不能复制onlien redo log，而应复制归档，然后用open resetlogs，这样onlineredo log 会自动重建。

2.     BUG 8240762 --shrink undo

ORA-600 [4193] /ORA-600 [4194] for new transactions

      ORA-600 [4137] for a transaction rollback

       这个bug 存在于Oracle 10.2.0.3,10.2.0.4 以及11gR1的版本里。

 

出现这种问题的解决方法是重建UNDO 表空间，现在使用SYSTEM下rollbacksegment 的系统基本是没有了。

 

在下面的Blog里列举了2种重建UNDO 的方法，这里列一种：

Currentonline Redo 和 Undo 损坏的处理方法

http://blog.csdn.net/tianlesoftware/article/details/6261475

 

（1）用spfile 创建pfile，然后修改参数：

#*.undo_tablespace='UNDOTBS1'

#*.undo_management='AUTO'

#*.undo_tablespace

#*.undo_retention

undo_management='MANUAL'

rollback_segments='SYSTEM'

 

（2）用修改之后的pfile，重启DB

SQL> STARTUP MOUNTpfile='F:/initorcl.ora' ;

 

（3）删除原来的表空间，创建新的UNDO 表空间

SQL> drop tablespace undotbs;

SQL> create undo tablespace undotbs1datafile '/u01/oradata/undotbs1.dbf' size 10M;

 

（4）关闭数据库，修改pfile参数，然后用新的pfile创建spfile，在正常启动数据库。

*.undo_tablespace='UNDOTBS1'

*.undo_management='AUTO'

#undo_management='MANUAL'

#rollback_segments='SYSTEM'

 

 

有关UNDO 表空间的更多说明，参考：

Oracle undo 管理

http://blog.csdn.net/tianlesoftware/article/details/4901666

 

Oracle undo 表空间管理

http://blog.csdn.net/tianlesoftware/article/details/5689558