Skip to content

undo

通过undo record找到对应回滚对象信息

通过下面的语句查到回滚的事务：

select * from v$fast_start_transactions;

或者

select * from x$ktuxe where KTUXECFL='DEAD' AND KTUXESTA!='INACTIVE'

根据上面的语句，我们可以查到事务的undo的segment id（USN或者KTUXEUSN），undo的slot（SLT或者KTUXESLT），和undo的sequence（SEQ或者KTUXESQN）。
根据USN，我们可以查到undo segment:

select * from v$rollname where usn=xxx

根据上面的语句，我们可以dump出undo block：

alter system dump undo block "<undo segment name>" XID <USN> <SLT> <SEQ>;

如

alter system dump undo block "_SYSSMU33$" XID 33 56 7463;

然后根据dump出来的trace file，可以判断object number（objn）或者object id（objd）


cat xxx.trc |grep objn

* Rec #0x45 slt:0x1f objn:223312(0x00092c2a) objd:223312 tblspc:20(0x00000014)

* Rec #0x44 slt:0x1f objn:223312(0x00092c2a) objd:223312 tblspc:20(0x00000014)

* Rec #0x43 slt:0x1f objn:223312(0x00092c2a) objd:223312 tblspc:20(0x00000014)

* Rec #0x42 slt:0x1f objn:223312(0x00092c2a) objd:223312 tblspc:20(0x00000014)

* Rec #0x41 slt:0x1f objn:223312(0x00092c2a) objd:223312 tblspc:20(0x00000014)

* Rec #0x40 slt:0x1f objn:223312(0x00092c2a) objd:223312 tblspc:20(0x00000014)

* Rec #0x3f slt:0x1f objn:223312(0x00092c2a) objd:223312 tblspc:20(0x00000014)

...

此时，我们根据object id，可以在数据库里面找到回滚的object的对象：

select * from dba_objects where object_id='223312';
Oracle Redo and Undo undo 数据技术 | Guang Cai Li |

ORA-00600: internal error code, arguments: [4193], [10837], [10841], [], [], [], [], []

大清早遭遇这个内部错误

Thu May 26 08:48:01 2011
Error 607 happened during db open, shutting down database
USER: terminating instance due to error 607
Thu May 26 08:48:01 2011
Errors in file d:oracleadmincqkfbdumpcqkf_smon_2904.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4193], [10837], [10841], [], [], [], [], []

600的4193错误是由于undo和redo块不一致造成的，一般伴随在2662之后

PURPOSE:           
  This article discusses the internal error “ORA-600 [4193]”, what
  it means and possible actions. The information here is only applicable
  to the versions listed and is provided only for guidance.

ERROR:             
  ORA-600 [4193] [a] [b]

VERSIONS:          
  versions 6.0 to 10.1

DESCRIPTION:       

  A mismatch has been detected between Redo records and Rollback (Undo)
  records.

  We are validating the Undo block sequence number in the undo block against
  the Redo block sequence number relating to the change being applied.

  This error is reported when this validation fails.

ARGUMENTS:
  Arg [a] Undo record seq number
  Arg [b] Redo record seq number

这里的解决办法很简单，重建表空间可以解决问题

1.设置undo为手动管理，然后设置参数_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)

这些回滚段可以在日志中找到：

SMON: enabling cache recovery
Thu May 26 08:47:48 2011
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined

………

然后启动数据库使用初始化参数文件，再重建回滚表空间，问题解决。

如果出现错误的回滚段存在与system表空间中，那么以上的方法无法解决，需要修改system回滚表空间的块信息才能够打开数据库，具体方法参考如下：
《使用bbed修改system undo segment修复4193》

Oracle Oracle恢复 undo | Guang Cai Li | |   4193