本次案例来自北区某客户，数据库版本为11.2.0.4，alert频繁报错ORA-00600: internal error code, arguments: [4137]。通常ORA-00600[4XXX]错误都与undo息息相关。
分析smon trace文件
|
1 2 3 4 5 6 7 8 9 10 11 12 |
*** 2022-11-03 16:36:19.731 *** SESSION ID:(2273.1) 2022-11-03 16:36:19.731 *** CLIENT ID:() 2022-11-03 16:36:19.731 *** SERVICE NAME:(SYS$BACKGROUND) 2022-11-03 16:36:19.731 *** MODULE NAME:() 2022-11-03 16:36:19.731 *** ACTION NAME:() 2022-11-03 16:36:19.731 Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_3061.trc ORA-00600: internal error code, arguments: [4137], [175.25.212575], [0], [0], [], [], [], [], [], [], [], [] XID passed in =xid: 0x00af.019.00033e5f XID from Undo block =xid: 0x0080.005.000a76e7 |
该错误发生在事务回滚期间，从trace可以看到回滚的事务XID为0x00af.019.00033e5f，但是在undo block中事务XID却是0x0080.005.000a76e7，所以抛出了ORA-00600: internal error code, arguments: [4137]错误。
继续分析trace，首先找到undo段头块
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
TRN CTL:: seq: 0x2fb1 chd: 0x0010 ctl: 0x0001 inc: 0x00000000 nfb: 0x0000 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x36c16d7b.2f69.0f scn: 0x0001.35c2eb34 Version: 0x01 FREE BLOCK POOL:: uba: 0x00000000.2f69.0e ext: 0x8 spc: 0x1936 uba: 0x00000000.2f69.14 ext: 0x8 spc: 0x1506 uba: 0x00000000.2f69.23 ext: 0x8 spc: 0xb60 uba: 0x00000000.21b6.03 ext: 0x5 spc: 0xd9c uba: 0x00000000.21b6.03 ext: 0x5 spc: 0xd98 TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x33e40 0x000b 0x0001.35c2f3c6 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451599 0x01 9 0x00 0x33e64 0xffff 0x0001.35c300ba 0x36c16d7b 0x0000.000.00000000 0x00000001 0x00000000 1667451680 0x02 9 0x00 0x33e5a 0x000e 0x0001.35c2f88d 0x36c16d77 0x0000.000.00000000 0x00000001 0x00000000 1667451603 0x03 9 0x00 0x33e54 0x0015 0x0001.35c2eea9 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451525 0x04 9 0x00 0x33e62 0x0006 0x0001.35c2fbbf 0x36c16d78 0x0000.000.00000000 0x00000001 0x00000000 1667451629 0x05 9 0x00 0x33e4d 0x0001 0x0001.35c30089 0x36c16d7b 0x0000.000.00000000 0x00000001 0x00000000 1667451676 0x06 9 0x00 0x33e59 0x0008 0x0001.35c2fc27 0x36c16d78 0x0000.000.00000000 0x00000001 0x00000000 1667451631 0x07 9 0x00 0x33e61 0x001a 0x0001.35c2efcc 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451544 0x08 9 0x00 0x33e5f 0x001e 0x0001.35c2fd75 0x36c16d78 0x0000.000.00000000 0x00000001 0x00000000 1667451645 0x09 9 0x00 0x33e54 0x000c 0x0001.35c2f2dd 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451587 0x0a 9 0x00 0x33e4f 0x000f 0x0001.35c2f533 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451599 0x0b 9 0x00 0x33e5e 0x000a 0x0001.35c2f487 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451599 0x0c 9 0x00 0x33e64 0x0000 0x0001.35c2f31e 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451587 0x0d 9 0x00 0x33e5c 0x001d 0x0001.35c2f03e 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451563 0x0e 9 0x00 0x33e5d 0x0013 0x0001.35c2f8d4 0x36c16d77 0x0000.000.00000000 0x00000001 0x00000000 1667451607 0x0f 9 0x00 0x33e5a 0x0002 0x0001.35c2f6a7 0x36c16d77 0x0000.000.00000000 0x00000020 0x00000000 1667451600 0x10 9 0x00 0x33e5d 0x001b 0x0001.35c2ec7c 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451485 0x11 9 0x00 0x33e66 0x001f 0x0001.35c2fe5b 0x36c16d78 0x0000.000.00000000 0x00000001 0x00000000 1667451659 0x12 9 0x00 0x33e5a 0x0016 0x0001.35c2f0dd 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451569 0x13 9 0x00 0x33e64 0x0020 0x0001.35c2fb0c 0x36c16d78 0x0000.000.00000000 0x00000001 0x00000000 1667451626 0x14 9 0x00 0x33e5f 0x0021 0x0001.35c2f202 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451587 0x15 9 0x00 0x33e5c 0x0007 0x0001.35c2ef35 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451536 0x16 9 0x00 0x33e4c 0x001c 0x0001.35c2f175 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451587 0x17 9 0x00 0x33e66 0x0012 0x0001.35c2f090 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451563 0x18 9 0x00 0x33e5b 0x0003 0x0001.35c2ee59 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451520 0x19 10 0x10 0x33e5f 0x004a 0x0001.35c30123 0x36c343a7 0x0000.000.00000000 0xffff81de 0x00000000 0 0x1a 9 0x00 0x33e5e 0x000d 0x0001.35c2efe8 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451549 0x1b 9 0x00 0x33e42 0x0018 0x0001.35c2ed74 0x36c16d57 0x0000.000.00000000 0x00000001 0x00000000 1667451500 0x1c 9 0x00 0x33e64 0x0014 0x0001.35c2f1e1 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451587 0x1d 9 0x00 0x33e60 0x0017 0x0001.35c2f06b 0x36c16d58 0x0000.000.00000000 0x00000002 0x00000000 1667451563 0x1e 9 0x00 0x33e56 0x0011 0x0001.35c2fe5a 0x36c16d7b 0x0000.000.00000000 0x00000003 0x00000000 1667451659 0x1f 9 0x00 0x33e68 0x0005 0x0001.35c2ffcc 0x36c16d7b 0x0000.000.00000000 0x00000001 0x00000000 1667451671 0x20 9 0x00 0x33e55 0x0004 0x0001.35c2fb4a 0x36c16d78 0x0000.000.00000000 0x00000001 0x00000000 1667451628 0x21 9 0x00 0x33e65 0x0009 0x0001.35c2f26d 0x36c16d58 0x0000.000.00000000 0x00000001 0x00000000 1667451587 EXT TRN CTL:: usn: 175 sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000 sp5:0x00000000 sp6:0x7fff00000000 sp7:0x00000000 sp8:0x7f3900000000 EXT TRN TBL:: index extflag extHash extSpare1 extSpare2 --------------------------------------------------- 0x00 0x00000000 0x00000000 0x00000000 0x00000000 0x01 0x00000000 0x00000000 0x00000000 0x00000000 0x02 0x00000000 0x00000000 0x00000000 0x00000000 0x03 0x00000000 0x00000000 0x00000000 0x00000000 0x04 0x00000000 0x00000000 0x00000000 0x00000000 0x05 0x00000000 0x00000000 0x00000000 0x00000000 0x06 0x00000000 0x00000000 0x00000000 0x00000000 0x07 0x00000000 0x00000000 0x00000000 0x00000000 0x08 0x00000000 0x00000000 0x00000000 0x00000000 0x09 0x00000000 0x00000000 0x00000000 0x00000000 0x0a 0x00000000 0x00000000 0x00000000 0x00000000 0x0b 0x00000000 0x00000000 0x00000000 0x00000000 0x0c 0x00000000 0x00000000 0x00000000 0x00000000 0x0d 0x00000000 0x00000000 0x00000000 0x00000000 0x0e 0x00000000 0x00000000 0x00000000 0x00000000 0x0f 0x00000000 0x00000000 0x00000000 0x00000000 0x10 0x00000000 0x00000000 0x00000000 0x00000000 0x11 0x00000000 0x00000000 0x00000000 0x00000000 0x12 0x00000000 0x00000000 0x00000000 0x00000000 0x13 0x00000000 0x00000000 0x00000000 0x00000000 0x14 0x00000000 0x00000000 0x00000000 0x00000000 0x15 0x00000000 0x00000000 0x00000000 0x00000000 0x16 0x00000000 0x00000000 0x00000000 0x00000000 0x17 0x00000000 0x00000000 0x00000000 0x00000000 0x18 0x00000000 0x00000000 0x00000000 0x00000000 0x19 0x00000000 0x00000000 0x00000000 0x00000000 0x1a 0x00000000 0x00000000 0x00000000 0x00000000 0x1b 0x00000000 0x00000000 0x00000000 0x00000000 0x1c 0x00000000 0x00000000 0x00000000 0x00000000 0x1d 0x00000000 0x00000000 0x00000000 0x00000000 0x1e 0x00000000 0x00000000 0x00000000 0x00000000 0x1f 0x00000000 0x00000000 0x00000000 0x00000000 0x20 0x00000000 0x00000000 0x00000000 0x00000000 0x21 0x00000000 0x00000000 0x00000000 0x00000000 |
通过事务表可以找到事务回滚的起点undo block，rdba为0x36c343a7。
搜索36c343a7可以找到事务回滚起点的undo block
|
1 2 3 4 5 6 7 8 9 10 11 12 |
******************************************************************************** UNDO BLK: xid: 0x0080.005.000a76e7 seq: 0x57e3 cnt: 0x1b irb: 0x1 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1d7c 0x02 0x1d0c 0x03 0x1ca8 0x04 0x1a3c 0x05 0x19c8 0x06 0x1964 0x07 0x16e8 0x08 0x1674 0x09 0x1610 0x0a 0x13ac 0x0b 0x133c 0x0c 0x12d8 0x0d 0x1064 0x0e 0x0ff0 0x0f 0x0f8c 0x10 0x0cfc 0x11 0x0c88 0x12 0x0c24 0x13 0x0974 0x14 0x0900 0x15 0x089c 0x16 0x0628 0x17 0x05b8 0x18 0x0554 0x19 0x02ec 0x1a 0x0278 0x1b 0x0214 |
的确该undo块中的事务XID为0x0080.005.000a76e7，明显与要回滚的事务XID不匹配。
造成该问题的原因有很多，比如非一致性的open过数据库，写丢失等等，由于该库从未做过非一致性open数据库的操作，所以个人比较怀疑存在写丢失的现象。
处理该错误的方法可以使用“_corrupted_rollback_segments”标记该事务的undo段，从而禁用掉该回滚段的回滚操作，或者设置10513 event禁用整个实例的回滚，导出重建对象。
该case中，从undo chain中可以看到涉及对象为87229
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
*----------------------------- * Rec #0x1 slt: 0x05 objn: 87229(0x000154bd) objd: 87229 tblspc: 1(0x00000001) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x36c343a6 *----------------------------- KDO undo record: KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x36c343a6.57e3.19 KDO Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00816826 hdba: 0x00800972 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 22(0x16) size/delt: 256 fb: --H-FL-- lb: 0x0 cc: 78 null: 01234567890123456789012345678901234567890123456789012345678901234567890123456789 -----------NNN-------------------------------------------NN------------------- col 0: [ 3] c2 03 51 col 1: [ 6] c5 10 0a 3a 4e 54 col 2: [ 2] c1 02 col 3: [13] 37 6d 70 37 30 35 72 76 30 76 72 39 68 col 4: [ 1] 80 col 5: [ 1] 80 col 6: [ 8] 41 4c 4c 5f 52 4f 57 53 col 7: [ 6] c5 03 5b 41 41 12 col 8: [ 4] c3 06 44 4c col 9: [ 2] c1 02 col 10: [ 2] c1 02 col 11: *NULL* col 12: *NULL* col 13: *NULL* col 14: [ 1] 80 col 15: [ 2] c1 55 col 16: [ 2] 52 4d col 17: [ 1] 80 col 18: [ 1] 80 col 19: [ 4] c3 10 14 33 col 20: [ 3] c2 0b 17 col 21: [ 1] 80 col 22: [ 1] 80 col 23: [ 4] c3 10 14 33 col 24: [ 3] c2 0b 17 col 25: [ 1] 80 col 26: [ 1] 80 col 27: [ 2] c1 02 col 28: [ 1] 80 col 29: [ 1] 80 col 30: [ 1] 80 col 31: [ 4] c3 10 14 33 col 32: [ 3] c2 0b 17 col 33: [ 2] c1 2d col 34: [ 1] 80 col 35: [ 5] c4 04 08 3a 28 col 36: [ 4] c3 03 0a 5f col 37: [ 4] c3 10 14 33 col 38: [ 3] c2 0b 17 col 39: [ 4] c4 57 1e 33 col 40: [ 3] c3 3a 51 col 41: [ 5] c4 5a 08 21 0c col 42: [ 5] c4 05 49 4f 40 col 43: [ 3] c2 39 61 col 44: [ 1] 80 col 45: [ 1] 80 col 46: [ 1] 80 col 47: [ 1] 80 col 48: [ 1] 80 col 49: [ 4] c3 12 35 61 col 50: [ 2] c1 0b col 51: [ 1] 80 col 52: [ 1] 80 col 53: [ 5] c4 14 15 45 3e col 54: [ 4] c3 0d 2e 42 col 55: [ 1] 80 col 56: [ 1] 80 col 57: *NULL* col 58: *NULL* col 59: [ 2] c1 55 col 60: [ 1] 80 col 61: [ 1] 80 col 62: [ 4] c3 25 05 31 col 63: [ 1] 80 col 64: [ 2] c1 1a col 65: [ 1] 80 col 66: [ 4] c3 25 05 31 col 67: [ 1] 80 col 68: [ 1] 80 col 69: [ 1] 80 col 70: [ 1] 80 col 71: [ 1] 80 col 72: [ 1] 80 col 73: [ 1] 80 col 74: [ 1] 80 col 75: [ 1] 80 col 76: [ 1] 80 col 77: [ 1] 80 |
|
1 2 3 4 5 |
SQL> select name from obj$ where obj#=87229; NAME -------------------------------------------------------------------------------- WRH$_SQLSTAT |
对象为awr中sqlstat的基表，并不是业务表，也不是非常核心的内部字典表，处理就非常easy了，truncate掉即可。
over！