1,背景描述
数据库版本:11.2.0.3,两节点的rac。节点1正常,节点2alert频繁出现如下报错:
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
2,查看恢复时使用的回滚段
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
--------------- --------------- ---------- ---------- ---------- ---------- ---------- ------------
_SYSSMU561_2257706629$ 2 3 561 6 51470 2 ACTIVE
Transaction id: XID_USN.XID_SLOT.XID_SEQ,这里的事务id:561.6.51470
There is an active dead transaction in _SYSSMU561_2257706629$ undo segment.
The dead transaction id is 561.6.51470 which is 231.6.c90e in hexadecimal.
事务ID 对应16进制转换
561.6.51470 231.6.c90e
3,通过dump undo header 寻找事务使用的undo segment
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU561_2257706629$';
select * from v$diag_info WHERE name='Default Trace File';
4,分析dump文件