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

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]的错误。

相关的文章参考：

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;

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

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.

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