Transaction recovery: lock conflict caught and ignored
之前有用户在11g上alert.log出现大量Transaction recovery: lock conflict caught and ignored，同时产生大量redo重做日志；观察AWR可以发现大量db block changes发生在UNDO$基础表上。
对于该问题可以尝试如下几种方案：
1、找出Dead Transaction并清理相关对象，如 https://dba010.com/2013/04/30/transaction-recovery-lock-conflict-caught-and-ignored/
ALERT.LOG:
.....
Transaction recovery: lock conflict caught and ignored
.....
And also some incident files are being created in $ORACLE_BASE/diag/rdbms/dbname/instancename/incident folder.
In my case the error started after SUPPLEMENTAL LOGGING enabled in a RAC environment. After disabling it the messages have not disappeared, but incident files are no longer being created.
1. Dead Trasaction
SQL> select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks,
a.ktuxesta txstatus
from x$ktuxe a, undo$ b
where a.ktuxecfl like ‘%DEAD%’
and a.ktuxeusn = b.us#;
USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS TXSTATUS
_SYSSMU7_881277423$ 1 3 7 13 1829999 1 ACTIVE
_SYSSMU8_4204495590$ 1 3 8 32 3045564 1 ACTIVE
_SYSSMU10_1314081219$ 1 3 10 3 11844457 1 ACTIVE
Transaction id is XID_USN.XID_SLOT.XID_SEQ
So in our case, for the first row it will be 7.13.1829999
2. Read transaction table from undo header.
ALTER SYSTEM DUMP UNDO HEADER ‘_SYSSMU7_881277423$’;
….
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
————————————————————————————————
0x00 9 0x03 0x1bf45c 0x000b 0x0000.789de808 0x00c242eb 0x0000.000.00000000 0x00000001 0x00c242eb 1367258143
0x01 9 0x00 0x1c031b 0x0014 0x0000.789e6018 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225
0x02 9 0x00 0x1c147a 0x000e 0x0000.789e694b 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230
0x03 9 0x00 0x1c06f9 0x0016 0x0000.789e601c 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225
0x04 9 0x00 0x1c06c8 0x0009 0x0000.789e3566 0x00c242f9 0x0000.000.00000000 0x00000001 0x00000000 1367258192
0x05 9 0x00 0x1c1167 0x0015 0x0000.789e357f 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192
0x06 9 0x00 0x1c2716 0x0017 0x0000.789e69e1 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230
0x07 9 0x00 0x1c1045 0x000c 0x0000.789e1bdb 0x00c242eb 0x0000.000.00000000 0x00000001 0x00000000 1367258170
0x08 9 0x00 0x1c2614 0x0005 0x0000.789e357e 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192
0x09 9 0x00 0x1bfa03 0x0021 0x0000.789e3574 0x00c242f9 0x0000.000.00000000 0x00000001 0x00000000 1367258192
0x0a 9 0x00 0x1bf712 0x001e 0x0000.789e3246 0x00c242f1 0x0000.000.00000000 0x00000001 0x00000000 1367258190
0x0b 9 0x00 0x1c1e01 0x0007 0x0000.789e1bd9 0x00c242eb 0x0000.000.00000000 0x00000001 0x00000000 1367258170
0x0c 9 0x00 0x1c08e0 0x000a 0x0000.789e3244 0x00c242f1 0x0000.000.00000000 0x00000006 0x00000000 1367258190
0x0d 10 0x90 0x1bec6f 0x0038 0x0000.789e783e 0x00c242fb 0x0000.000.00000000 0x00000001 0x00c242fb 0
0x0e 9 0x00 0x1c068e 0x0010 0x0000.789e694d 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230
0x0f 9 0x00 0x1c151d 0x0012 0x0000.789e3578 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192
0x10 9 0x00 0x1c26bc 0x0006 0x0000.789e69df 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230
0x11 9 0x00 0x1c16eb 0x0000 0x0000.789cbd77 0x00c242eb 0x0000.000.00000000 0x00000001 0x00000000 1367257923
0x12 9 0x00 0x1c082a 0x001d 0x0000.789e357c 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192
0x13 9 0x00 0x1c1459 0x001f 0x0000.789e7891 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238
0x14 9 0x00 0x1c14b8 0x0003 0x0000.789e601a 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225
0x15 9 0x00 0x1c0457 0x0020 0x0000.789e39d3 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258195
0x16 9 0x00 0x1c1326 0x0002 0x0000.789e601d 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225
0x17 9 0x00 0x1c0db5 0x001c 0x0000.789e788a 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238
0x18 9 0x00 0x1bffe4 0x001b 0x0000.789e400d 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258200
0x19 9 0x00 0x1c16e3 0x0001 0x0000.789e5fd2 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225
0x1a 9 0x00 0x1bdbb2 0x0018 0x0000.789e400b 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258200
0x1b 9 0x00 0x1c1141 0x0019 0x0000.789e453a 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258204
0x1c 9 0x00 0x1bc9a0 0x0013 0x0000.789e788e 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238
0x1d 9 0x00 0x1c02ef 0x0008 0x0000.789e357d 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192
0x1e 9 0x00 0x1c0b6e 0x0004 0x0000.789e3250 0x00c242f9 0x0000.000.00000000 0x00000009 0x00000000 1367258190
0x1f 9 0x00 0x1c00ad 0xffff 0x0000.789e78a1 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238
0x20 9 0x00 0x1c166c 0x001a 0x0000.789e39dd 0x00c242fa 0x0000.000.00000000 0x00000002 0x00000000 1367258195
0x21 9 0x00 0x1c160b 0x000f 0x0000.789e3576 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192
EXT TRN CTL::
usn: 7
State# 10 means active transaction.
dba points to starting UNDO block address.
usn: Undo segment number
usn.index.wrap# gives transaction id.
An active transaction 0x0007.00d.001bec6f is available in slot 0x0d which has a dba of 0x00c242fb (12731131 in decimal)
3. Reading UNDO Block:
Identify fileID and blockID:
fileID:
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12731131) from x$dual;
3
blockID:
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12731131) from x$dual;
148219
Dumping block
alter system dump datafile 3 block 148219;
UNDO BLK:
xid: 0x0007.00d.001bec6f seq: 0x41f9 cnt: 0x6 irb: 0x5 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
—————————————————————————
0x01 0x1f98 0x02 0x1f2c 0x03 0x1d7c 0x04 0x1d10 0x05 0x1ca0
0x06 0x1bfc
*—————————–
* Rec #0x1 slt: 0x0d objn: 0(0x00000000) objd: 0 tblspc: 0(0x00000000)
* Layer: 5 (Transaction Undo) opc: 7 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*—————————–
uba: 0x00c242fa.41f9.37 ctl max scn: 0x0000.789b7668 prv tx scn: 0x0000.789bb8d7
txn start scn: scn: 0x0000.789e783e logon user: 88
prev brb: 12731116 prev bcl: 0
*—————————–
* Rec #0x2 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo User Undo Applied Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0012.01c.00322281 uba: 0x0102c5f0.3fa9.0a
flg: C— lkc: 0 scn: 0x0000.789ca3f4
KDO Op code: LKR row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x038180fc hdba: 0x018d64e2
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 14 to: 0
*—————————–
* Rec #0x3 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x02
Undo type: Regular undo User Undo Applied Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c242fb.41f9.02
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x038180fc hdba: 0x018d64e2
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 14(0xe) flag: 0x2c lock: 1 ckix: 0
ncol: 9 nnew: 6 size: 0
col 1: [ 7] 78 71 04 1d 13 01 01
col 2: [ 2] c1 13
col 3: [ 1] 80
col 4: [16] 10 e5 00 2e 10 d1 10 d0 10 d7 10 e3 10 db 10 d8
col 5: [174]
10 d0 10 ed 10 d0 10 e0 10 d8 10 e1 00 20 10 d0 00 2e 10 e0 00 2e 00 20 10
de 10 e0 10 dd 10 d9 10 e3 10 e0 10 d0 10 e2 10 e3 10 e0 10 d8 10 e1 00 20
10 e1 10 d0 10 d2 10 d0 10 db 10 dd 10 eb 10 d8 10 d4 10 d1 10 dd 00 20 10
dc 10 d0 10 ec 10 d8 10 da 10 d8 10 e1 00 20 10 e3 10 e4 10 e0 10 dd 10 e1
00 20 10 d2 10 d0 10 db 10 dd 10 db 10 eb 10 d8 10 d4 10 d1 10 d4 10 da 10
e1 00 20 10 d1 10 d0 10 e2 10 dd 10 dc 00 20 10 d2 10 d8 10 dd 10 e0 10 d2
10 d8 00 20 10 de 10 d4 10 e0 10 d0 10 dc 10 d8 10 eb 10 d4 10 e1 00 2e
col 6: [36]
00 54 00 01 04 0c 00 00 00 02 00 00 00 01 00 00 09 07 b0 63 00 10 09 00 00
00 00 00 00 00 00 00 00 00 00 00
*—————————–
* Rec #0x4 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x03
Undo type: Regular undo User Undo Applied Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x000c.017.000d65d6 uba: 0x0103df2c.22a5.20
flg: C— lkc: 0 scn: 0x0000.789c4694
KDO Op code: LKR row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03833994 hdba: 0x0181f832
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 7 to: 0
*—————————–
* Rec #0x5 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x04
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c242fb.41f9.04
KDO Op code: LMN row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x03833994 hdba: 0x0181f832
itli: 1 ispac: 0 maxfr: 4858
*—————————–
* Rec #0x6 slt: 0x0d objn: 89703(0x00015e67) objd: 92020 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x05
Undo type: Regular undo User Undo Applied Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*—————————–
KDO undo record:
irb points to last UNDO RECORD in UNDO block.
rci points to previous UNDO RECORD. if rci=0, it’s the first UNDO RECORD.
Recovery operation starts from irb and chain is followed by rci until rci is zero.
The transaction starts recovery from UNDO RECORD of 0x5.
4. Reading UNDO Records:
* Rec #0x5 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x04
….
* Rec #0x4 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x03
….
* Rec #0x3 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x02
…
* Rec #0x2 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006)
* Layer: 11 (Row) opc: 1 rci 0x00
…
objn means object id.
5. Find these objects
The following objects need recovery:
select * from dba_objects
where object_id in (89834,110769);
………………………………………………………..
This problem is Oracle Bug:9857702:
.....
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 11.1 but BELOW 12.1
Versions confirmed as being affected
•11.2.0.1
•11.1.0.7
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
•12.1 (Future Release)
•11.2.0.2 (Server Patch Set)
•11.1.0.7.8 Patch Set Update
•11.1.0.7 Patch 40 on Windows Platforms
.....
6. Workaround:
Recreate objects that need recovery.
Or drop them Smile
2、尝试设置 FAST_START_PARALLEL_ROLLBACK=HIGH 看是否能解决
3、尝试设置10513 LEVEL 2 事件，暂时屏蔽回滚事务
Comment