Transaction recovery: lock conflict caught and ignored

Transaction recovery: lock conflict caught and ignored
环境：RAC 4节点、oracle 11.2.0.4、redhat 5.9 64bit

问题描述：

1.alert.log每隔一段时间就会报如下错误：

Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored

2.db1_smon_5641.trc

Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601

*** 2014-03-10 16:17:50.811
Serial Transaction recovery caught exception 30319
Serial Transaction recovery caught exception 30319

*** 2014-03-10 16:24:20.944
Serial Transaction recovery caught exception 30319

*** 2014-03-10 16:28:20.995
Serial Transaction recovery caught exception 30319

*** 2014-03-10 16:31:42.868
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601

3.smon消耗资源严重

每次alert.log中报上述问题时，smon进程消耗资源都会比较严重。

问题原因：

该现象发生在客户启动flashback功能之后。日志描述是有对象需要恢复，但是一直无法恢复完成。可能对象是有变动，比如结构变化，或者drop但是还在OBJ$中存在。

处理思路：

1.找出该对象

2.查看对象的具体状态。

处理过程：

1.通过该对象所使用的undo段

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
--------------------------------
_SYSSMU6_1263032392$ 1 3 6 32 1982 10959
ACTIVE

Comment:

Transaction id: XID_USN.XID_SLOT.XID_SEQ
There is an active dead transaction in _SYSSMU6_1263032392$ undo segment.

The dead transaction id is 6.32.1982 which is 006.20.7be in hexadecimal.

2.通过dump undo header 寻找事务使用的undo segment

ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU6_1263032392$';

查看DUMP文件位置：select * from v$diag_info WHERE name='Default Trace File';

TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x07c4 0x001b 0x0014.ce1da6e5 0x00c05551 0x0000.000.00000000 0x00000001 0x00000000 1393382971
0x01 9 0x00 0x07c4 0x000b 0x0014.df681a12 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393903956
0x02 9 0x00 0x07c5 0x0008 0x0014.d2c4211c 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393578496
0x03 9 0x00 0x07c6 0x000a 0x0014.ce1dec0f 0x00c05ad1 0x0000.000.00000000 0x00000001 0x00000000 1393383018
0x04 9 0x00 0x07c6 0x0003 0x0014.ce1de60c 0x00c05ad1 0x0000.000.00000000 0x00000001 0x00000000 1393383013
0x05 9 0x00 0x07c5 0x0011 0x0014.ce1dd264 0x09006d11 0x0000.000.00000000 0x00000001 0x00000000 1393383000
0x06 9 0x00 0x07c3 0x0015 0x0014.ce1dc031 0x09006d11 0x0000.000.00000000 0x00000001 0x00000000 1393382987
0x07 9 0x00 0x07c5 0x001a 0x0014.e064a048 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393937151
0x08 9 0x00 0x07c5 0x0019 0x0014.d2c4214d 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393578499
0x09 9 0x00 0x07b8 0x0004 0x0014.ce1ddfeb 0x00c05ad1 0x0000.000.00000000 0x00000001 0x00000000 1393383009
0x0a 9 0x00 0x07c5 0x0014 0x0014.d2c2f1d7 0x00c05ad1 0x0000.000.00000000 0x00000001 0x00000000 1393576638
0x0b 9 0x00 0x07c6 0x000e 0x0014.df697036 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393904286
0x0c 9 0x00 0x07c6 0x0012 0x0014.e016bf64 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393925817
0x0d 9 0x00 0x07c4 0x0018 0x0014.d2e6a190 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393616776
0x0e 9 0x00 0x07c4 0x001d 0x0014.e00ad3e8 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393924552
0x0f 9 0x00 0x07c2 0x0005 0x0014.ce1dcc29 0x09006d11 0x0000.000.00000000 0x00000001 0x00000000 1393382996
0x10 9 0x00 0x07aa 0x0001 0x0014.d2e6b497 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393616905
0x11 9 0x00 0x07c5 0x0009 0x0014.ce1dd8f6 0x09006d11 0x0000.000.00000000 0x00000001 0x00000000 1393383004
0x12 9 0x00 0x07c5 0x0013 0x0014.e018bdfc 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393926175
0x13 9 0x00 0x07c5 0x0021 0x0014.e01bc7f4 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393926844
0x14 9 0x00 0x07c5 0x0002 0x0014.d2c41da6 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393578478
0x15 9 0x00 0x078e 0x000f 0x0014.ce1dc5f4 0x09006d11 0x0000.000.00000000 0x00000001 0x00000000 1393382991
0x16 9 0x00 0x07c5 0x001f 0x0014.ce1db393 0x00c05551 0x0000.000.00000000 0x00000001 0x00000000 1393382979
0x17 9 0x00 0x07b7 0x0010 0x0014.d2e6b45a 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393616902
0x18 9 0x00 0x07c5 0x0017 0x0014.d2e6a1bd 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393616779
0x19 9 0x00 0x07c1 0x000d 0x0014.d2c5c95c 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393581458
0x1a 9 0x00 0x07c6 0xffff 0x0014.e53d0bd8 0x0900704f 0x0000.000.00000000 0x00000002 0x00000000 1393939241
0x1b 9 0x00 0x07c5 0x0016 0x0014.ce1dadc2 0x00c05551 0x0000.000.00000000 0x00000001 0x00000000 1393382975
0x1c 9 0x00 0x07c5 0x000c 0x0014.e013ee36 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393925363
0x1d 9 0x00 0x07c5 0x001c 0x0014.e00d0d8f 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393924583
0x1e 9 0x00 0x07c5 0x0000 0x0014.ce1da073 0x00c05551 0x0000.000.00000000 0x00000001 0x00000000 1393382966
0x1f 9 0x00 0x07c5 0x0006 0x0014.ce1db9b4 0x00c05551 0x0000.000.00000000 0x00000001 0x00000000 1393382983
0x20 10 0x90 0x07be 0x0036 0x0014.ce1cd255 0x00c05c1d 0x0000.000.00000000 0x00002acf 0x0900222d 0
0x21 9 0x00 0x07c5 0x0007 0x0014.e062fd04 0x0900704e 0x0000.000.00000000 0x00000001 0x00000000 1393936851
EXT TRN CTL::
usn: 6

Definitions:

State#10 means active transaction.
dba points to starting UNDO block address.
usn: Undo segment number
usn.index.wrap# gives transaction id.
Comment:

select to_number('00c05c1d','xxxxxxxxxxxxx') from dual;

An active transaction of 006.20.7be is available in the slot of 0x20, which has a dba of 0x00c05c1d, which is in decimal

3.dump 对应的undo块

■fileID:
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12606493) from dual;
■blockID:
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12606493) from dual;
■
alter system dump datafile <fileID> block <blockID>;

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12606493) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12606493)
----------------------------------------------
3

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12606493) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12606493)
-----------------------------------------------
23581
alter system dump datafile 3 block 23581;

UNDO BLK:
xid: 0x0006.020.000007be seq: 0x2e0 cnt: 0x3f irb: 0x32 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f8c 0x02 0x1ef0 0x03 0x1e84 0x04 0x1dfc 0x05 0x1d80
0x06 0x1d08 0x07 0x1c7c 0x08 0x1c10 0x09 0x1b74 0x0a 0x1b18
0x0b 0x1a6c 0x0c 0x1a20 0x0d 0x1970 0x0e 0x1924 0x0f 0x1878
0x10 0x181c 0x11 0x1780 0x12 0x1714 0x13 0x1688 0x14 0x160c
0x15 0x1590 0x16 0x1504 0x17 0x1498 0x18 0x13fc 0x19 0x13a0
0x1a 0x12f4 0x1b 0x12a8 0x1c 0x11f8 0x1d 0x11ac 0x1e 0x1100
0x1f 0x10a4 0x20 0x1008 0x21 0x0f9c 0x22 0x0f10 0x23 0x0e94
0x24 0x0e18 0x25 0x0d8c 0x26 0x0d20 0x27 0x0c84 0x28 0x0c28
0x29 0x0b7c 0x2a 0x0b30 0x2b 0x0a80 0x2c 0x0a34 0x2d 0x098c
0x2e 0x0930 0x2f 0x0898 0x30 0x082c 0x31 0x07a4 0x32 0x0728
0x33 0x06b0 0x34 0x0624 0x35 0x05bc 0x36 0x0520 0x37 0x04c8
0x38 0x041c 0x39 0x03d4 0x3a 0x0324 0x3b 0x02d8 0x3c 0x0230
0x3d 0x01d4 0x3e 0x013c 0x3f 0x00d0

--------------------------------------------------------------------------------
Definitions

■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.
Comment:

■The transaction of 006.20.7be starts recovery from UNDO RECORD of 0x20.

UNDO RECORDS:
Reading UNDO Records:

Data:

*-----------------------------
* Rec #0x1 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00

*-----------------------------
* Rec #0x2 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x01

*-----------------------------
* Rec #0x3 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x02

*-----------------------------
* Rec #0x4 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x03

*-----------------------------
* Rec #0x5 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x04

*-----------------------------
* Rec #0x6 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x05

*-----------------------------
* Rec #0x7 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x06

*-----------------------------
* Rec #0x8 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x07

*-----------------------------
* Rec #0x9 slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x08

*-----------------------------
* Rec #0xa slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x09

*-----------------------------
* Rec #0xb slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x0a

*-----------------------------
* Rec #0xc slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x0b

*-----------------------------
* Rec #0xd slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x0c

*-----------------------------
* Rec #0xe slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x0d

。。。

*-----------------------------
* Rec #0x3f slt: 0x20 objn: 88979(0x00015b93) objd: 88979 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x3e

--------------------------------------------------------------------------------

Definitions:

■objn means object id.
Comment:

■The objects need recovery:
select * from dba_objects where object_id =88979;

到此找到了，需要恢复的对象。

但是发现没有数据，说明object_id=88979没有记录在dba_objects中，
尝试着使用obj$查询该对象是否存在

SQL> select * from obj$ where obj#=88979;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE
---------- ---------- ---------- ------------------------------------------------------------ ----------
SUBNAME TYPE# CTIME MTIME STIME
------------------------------------------------------------ ---------- ----------------------- ----------------------- -----------------------
STATUS REMOTEOWNER
---------- ------------------------------------------------------------
LINKNAME
------------------------------------------------------------------------------------------------------------------------------------------------------
FLAGS OID$ SPARE1 SPARE2 SPARE3
---------- -------------------------------- ---------- ---------- ----------
SPARE4
------------------------------------------------------------------------------------------------------------------------------------------------------
SPARE5
------------------------------------------------------------------------------------------------------------------------------------------------------
SPARE6
-----------------------
88979 88979 95 BIN$80dCz0wkauXgQ4QCAQrtBA==$0 1

发现该对象数据ods(95)用户下的对象，但是已经被删除了，存放在回收站。

select * from dba_recyclebin a where a.object_name='BIN$80dCz0wkauXgQ4QCAQrtBA==$0'

OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
1 ODS BIN$80dCz0wkauXgQ4QCAQrtBA==$0 TAB_MOBILE_INFO DROP TABLE ETLDATA 2014-02-26:10:18:29 2014-02-26:10:50:47 89357422160 YES YES 88979 88979 88979 376832

查询TAB_MOBILE_INFO是否存在。

SQL> select * from dba_objects a where a.OBJECT_NAME='TAB_MOBILE_INFO';

OWNER
------------------------------------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
------------------------------------------------------------ ---------- -------------- -------------------------------------- -----------------------
LAST_DDL_TIME TIMESTAMP STATUS TE GE SE NAMESPACE
----------------------- -------------------------------------- -------------- -- -- -- ----------
EDITION_NAME
------------------------------------------------------------
ODS
TAB_MOBILE_INFO
89008 89008 TABLE 26-FEB-2014 10:58:16

OWNER
------------------------------------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
------------------------------------------------------------ ---------- -------------- -------------------------------------- -----------------------
LAST_DDL_TIME TIMESTAMP STATUS TE GE SE NAMESPACE
----------------------- -------------------------------------- -------------- -- -- -- ----------
EDITION_NAME
------------------------------------------------------------
04-MAR-2014 14:26:48 2014-02-26:10:58:16 VALID N N N 1

说明ODS.TAB_MOBILE_INFO被drop后重建，但是drop以前该表恢复没有完成，但是obj$表中一直存在着它的信息。

解决办法：
清空回收站 purge dba_recyclebin 清除该对象。

清空后不断释放UNDOBLOCKS

SQL> select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
2 xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks,
3 a.ktuxesta txstatus
from x$ktuxe a, undo$ b
where a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us# and a.ktuxesta = 'ACTIVE'; 4 5 6

SQL> /

SQL>
SQL>
SQL> /

no rows selected

alert日志中的"Transaction recovery: lock conflict caught and ignored"消失

秒客网

Transaction recovery: lock conflict caught and ignored

相关文章