History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: QA-52
Type: Oracle - Administration Oracle - Administration
Status: Closed Closed
Resolution: Answered
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

"Transaction recovery: lock conflict caught and ignored" messages in ALERT LOG.

Created: 30/Dec/11 12:56 PM   Updated: 16/Jan/12 03:28 PM
Fix Version/s: None

Product Version: 11.2.0.1.0 (RAC)
Operating System: HP-UX
Operating System Version: B.11.31
Host Name: .
Database Name: .


 Description  « Hide
The customer encounters the following messages:

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
.....

SMON TRACE:

.....
*** 2011-12-26 14:42:46.401
Serial Transaction recovery caught exception 30319
Serial Transaction recovery caught exception 601

*** 2011-12-26 14:46:25.455
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
.....

The customer said the error started after SUPPLEMENTAL LOGGING enabled. But, the messages have not disappeared after disabling it.



 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
ubTools Support - 30/Dec/11 01:09 PM - edited
DEAD TRANSACTIONS:

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#;

Data:

USEG	INSTID	STATUS	XID_USN	XID_SLOT	XID_SEQ	UNDOBLOCKS	TXSTATUS
_SYSSMU1209_1270276489$	1	3	1209	3	1100382	3033	ACTIVE
_SYSSMU1482_3325964579$	2	2	1482	16	496322	0	INACTIVE
_SYSSMU1681_4095893383$	2	2	1681	5	472365	0	INACTIVE
_SYSSMU2072_3213080551$	2	2	2072	2	120912	0	INACTIVE

Definition:

  • Transaction id: XID_USN.XID_SLOT.XID_SEQ

Comment:

  • There is an active dead transaction in _SYSSMU1209_1270276489$ undo segment.
  • The dead transaction id is 1209.3.1100382 which is 0x04B9.003.0010CA5E in hexadecimal.

ubTools Support - 30/Dec/11 01:27 PM - edited
UNDO HEADER:

Reading Transaction Table in the UNDO header:

SQL:

  • SQL> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU1209_1270276489$';

Data:

.....
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x10ca61  0x001a  0x001d.ac01b6ea  0x04c30103  0x0000.000.00000000  0x00000001   0x00000000  1324239683
   0x01    9    0x00  0x10c8f0  0x000e  0x001d.abff4525  0x04c30043  0x0000.000.00000000  0x00000001   0x00000000  1324239603
   0x02    9    0x00  0x10ca1f  0x0005  0x001d.abfad814  0x00c2e328  0x0000.000.00000000  0x00000003   0x00000000  1324239446
   0x03   10    0x90  0x10ca5e  0x0002  0x001d.ab7efd87  0x00c0f5ea  0x0000.000.00000000  0x00000bd9   0x04c1f938  0
   0x04    9    0x00  0x10c46d  0x000a  0x001d.abda8e80  0x04c28e5f  0x0000.000.00000000  0x00000001   0x00000000  1324238461
   0x05    9    0x00  0x10c91c  0x0015  0x001d.abfadb90  0x00c2e32d  0x0000.000.00000000  0x00000001   0x00000000  1324239447
   0x06    9    0x00  0x10cdbb  0x001d  0x001d.abd50f70  0x04c28e80  0x0000.000.00000000  0x00000001   0x00000000  1324238283
   0x07    9    0x00  0x10c77a  0x0004  0x001d.abd90c5b  0x00c29cd8  0x0000.000.00000000  0x00000001   0x00000000  1324238409
   0x08    9    0x00  0x10c229  0x0020  0x001d.abe1de1e  0x00c2a8d2  0x0000.000.00000000  0x00000001   0x00000000  1324238704
   0x09    9    0x00  0x10ca28  0x0006  0x001d.abd4dfb6  0x04c28e5f  0x0000.000.00000000  0x00000001   0x00000000  1324238278
   0x0a    9    0x00  0x10c6b7  0x0008  0x001d.abe1c7f3  0x00c2a8c2  0x0000.000.00000000  0x00000001   0x00000000  1324238701
   0x0b    9    0x00  0x10c9e6  0x0017  0x001d.abfdbd74  0x04c30007  0x0000.000.00000000  0x00000001   0x00000000  1324239554
   0x0c    9    0x00  0x10cb45  0x0011  0x001d.abfc5eea  0x04c2ff9d  0x0000.000.00000000  0x00000001   0x00000000  1324239502
   0x0d    9    0x00  0x10c444  0x001c  0x001d.abca9d1f  0x00c22bc1  0x0000.000.00000000  0x00000001   0x00000000  1324237948
   0x0e    9    0x00  0x10c7e3  0x0000  0x001d.abffbb9a  0x04c3005f  0x0000.000.00000000  0x00000001   0x00000000  1324239618
   0x0f    9    0x00  0x10ca72  0x0007  0x001d.abd82320  0x00c29c21  0x0000.000.00000000  0x00000001   0x00000000  1324238375
   0x10    9    0x00  0x10c501  0x001f  0x001d.abf33edd  0x00c2e03f  0x0000.000.00000000  0x00000001   0x00000000  1324239208
   0x11    9    0x00  0x10ca90  0x000b  0x001d.abfdbc34  0x04c30004  0x0000.000.00000000  0x00000001   0x00000000  1324239554
   0x12    9    0x00  0x10c2ef  0x0018  0x001d.ac09d85c  0x04c3036f  0x0000.000.00000000  0x00000001   0x00000000  1324239959
   0x13    9    0x00  0x10c8ae  0x0010  0x001d.abe83852  0x04c2ea99  0x0000.000.00000000  0x00000001   0x00000000  1324238911
   0x14    9    0x00  0x10c5ad  0x0016  0x001d.abd3e99a  0x04c28de0  0x0000.000.00000000  0x00000001   0x00000000  1324238242
   0x15    9    0x00  0x10c62c  0x000c  0x001d.abfb4d1a  0x04c2ff8d  0x0000.000.00000000  0x00000001   0x00000000  1324239464
   0x16    9    0x00  0x10c72b  0x001b  0x001d.abd4d238  0x04c28e4e  0x0000.000.00000000  0x00000001   0x00000000  1324238274
   0x17    9    0x00  0x10c2da  0x0001  0x001d.abff0f85  0x04c30029  0x0000.000.00000000  0x00000001   0x00000000  1324239598
   0x18    9    0x00  0x10c589  0xffff  0x001d.ad480910  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1324254620
   0x19    9    0x00  0x10c628  0x000d  0x001d.abca6bcb  0x00c22ba5  0x0000.000.00000000  0x00000001   0x00000000  1324237944
   0x1a    9    0x00  0x10c4a7  0x0012  0x001d.ac04e46b  0x04c30232  0x0000.000.00000000  0x00000001   0x00000000  1324239791
   0x1b    9    0x00  0x10c2e6  0x0009  0x001d.abd4df89  0x04c28e5c  0x0000.000.00000000  0x00000001   0x00000000  1324238277
   0x1c    9    0x00  0x10c755  0x0014  0x001d.abcb5957  0x04c28b14  0x0000.000.00000000  0x00000001   0x00000000  1324237971
   0x1d    9    0x00  0x10cd54  0x0021  0x001d.abd6f01b  0x00c29b7b  0x0000.000.00000000  0x00000001   0x00000000  1324238343
   0x1e    9    0x00  0x10c5e3  0x0019  0x001d.abca546a  0x00c22b90  0x0000.000.00000000  0x00000001   0x00000000  1324237940
   0x1f    9    0x00  0x10c232  0x0002  0x001d.abf7fc92  0x00c2e1be  0x0000.000.00000000  0x00000001   0x00000000  1324239355
   0x20    9    0x00  0x10c391  0x0013  0x001d.abe5ff89  0x04c2e999  0x0000.000.00000000  0x00000001   0x00000000  1324238832
   0x21    9    0x00  0x10cc70  0x000f  0x001d.abd77e3c  0x00c29bd6  0x0000.000.00000000  0x00000001   0x00000000  1324238361
  EXT TRN CTL::
  usn: 1209
.....

Definitions:

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

Comment:

An active transaction of 0x04b9.003.0010ca5e is available in the slot of 0x03, which has a dba of 0x00c0f5ea, which is 12645866 in decimal.


ubTools Support - 30/Dec/11 01:43 PM - edited
UNDO BLOCK:

Reading UNDO Block:

SQL:

  • fileID: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12645866) from x$dual;
  • blockID:select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12645866) from x$dual;
  • alter system dump datafile <fileID> block <blockID>;

Data:

.....
UNDO BLK:  
xid: 0x04b9.003.0010ca5e  seq: 0x1447 cnt: 0x2e  irb: 0x2c  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f8c     0x02 0x1dac     0x03 0x1d3c     0x04 0x1ccc     0x05 0x1c64     
0x06 0x1c0c     0x07 0x1b7c     0x08 0x1b0c     0x09 0x1a9c     0x0a 0x1a24     
0x0b 0x19cc     0x0c 0x183c     0x0d 0x17cc     0x0e 0x175c     0x0f 0x16e4     
0x10 0x168c     0x11 0x15fc     0x12 0x158c     0x13 0x151c     0x14 0x14b4     
0x15 0x145c     0x16 0x12f4     0x17 0x1284     0x18 0x1214     0x19 0x11ac     
0x1a 0x1154     0x1b 0x0f9c     0x1c 0x0f2c     0x1d 0x0ebc     0x1e 0x0e44     
0x1f 0x0dec     0x20 0x0c3c     0x21 0x0bcc     0x22 0x0b5c     0x23 0x0af4     
0x24 0x0a9c     0x25 0x08c4     0x26 0x0854     0x27 0x07e4     0x28 0x076c     
0x29 0x0714     0x2a 0x0604     0x2b 0x022c     0x2c 0x01c4     0x2d 0x0154     
0x2e 0x00e4     
.....

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 0x04b9.003.0010ca5e starts recovery from UNDO RECORD of 0x2c.

ubTools Support - 30/Dec/11 02:16 PM
UNDO RECORDS:

Reading UNDO Records:

Data:

.....

*-----------------------------
* Rec #0x2c  slt: 0x03  objn: 939468(0x000e55cc)  objd: 941274  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x2b   
.....
*-----------------------------
* Rec #0x2b  slt: 0x03  objn: 939468(0x000e55cc)  objd: 941274  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x2a   
.....
*-----------------------------
* Rec #0x2a  slt: 0x03  objn: 939468(0x000e55cc)  objd: 941274  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x29   
.....
*-----------------------------
* Rec #0x29  slt: 0x03  objn: 1126679(0x00113117)  objd: 1126679  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x28   
.....
*-----------------------------
* Rec #0x28  slt: 0x03  objn: 1123018(0x001122ca)  objd: 1123018  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x27   
.....
*-----------------------------
* Rec #0x27  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x26   
.....
*-----------------------------
* Rec #0x26  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x25   
.....
*-----------------------------
* Rec #0x25  slt: 0x03  objn: 939450(0x000e55ba)  objd: 939450  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x24   
.....
*-----------------------------
* Rec #0x24  slt: 0x03  objn: 1126696(0x00113128)  objd: 1126696  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x23   
.....
*-----------------------------
* Rec #0x23  slt: 0x03  objn: 1123035(0x001122db)  objd: 1123035  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x22   
.....
*-----------------------------
* Rec #0x22  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x21   
.....
*-----------------------------
* Rec #0x21  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x20   
.....
*-----------------------------
* Rec #0x20  slt: 0x03  objn: 939408(0x000e5590)  objd: 941229  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x1f   
.....
*-----------------------------
* Rec #0x1f  slt: 0x03  objn: 1126655(0x001130ff)  objd: 1126655  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x1e   
.....
*-----------------------------
* Rec #0x1e  slt: 0x03  objn: 1122994(0x001122b2)  objd: 1122994  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x1d   
.....
*-----------------------------
* Rec #0x1d  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x1c   
.....
*-----------------------------
* Rec #0x1c  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x1b   
.....
*-----------------------------
* Rec #0x1b  slt: 0x03  objn: 939429(0x000e55a5)  objd: 941242  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x1a   
.....
*-----------------------------
* Rec #0x1a  slt: 0x03  objn: 1126678(0x00113116)  objd: 1126678  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x19   
.....
*-----------------------------
* Rec #0x19  slt: 0x03  objn: 1123017(0x001122c9)  objd: 1123017  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x18   
.....
*-----------------------------
* Rec #0x18  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x17   
.....
*-----------------------------
* Rec #0x17  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x16   
.....
*-----------------------------
* Rec #0x16  slt: 0x03  objn: 939466(0x000e55ca)  objd: 941272  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x15   
.....
*-----------------------------
* Rec #0x15  slt: 0x03  objn: 1126681(0x00113119)  objd: 1126681  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x14   
.....
*-----------------------------
* Rec #0x14  slt: 0x03  objn: 1123020(0x001122cc)  objd: 1123020  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x13   
.....
*-----------------------------
* Rec #0x13  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x12   
.....
*-----------------------------
* Rec #0x12  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x11   
.....
*-----------------------------
* Rec #0x11  slt: 0x03  objn: 939420(0x000e559c)  objd: 941236  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x10   
.....
*-----------------------------
* Rec #0x10  slt: 0x03  objn: 1126647(0x001130f7)  objd: 1126647  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0f   
.....
*-----------------------------
* Rec #0xf  slt: 0x03  objn: 1122986(0x001122aa)  objd: 1122986  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x0e   
.....
*-----------------------------
* Rec #0xe  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0d   
.....
*-----------------------------
* Rec #0xd  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0c   
.....
*-----------------------------
* Rec #0xc  slt: 0x03  objn: 939418(0x000e559a)  objd: 941235  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x0b   
.....
*-----------------------------
* Rec #0xb  slt: 0x03  objn: 1126653(0x001130fd)  objd: 1126653  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0a   
.....
*-----------------------------
* Rec #0xa  slt: 0x03  objn: 1122992(0x001122b0)  objd: 1122992  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x09   
.....
*-----------------------------
* Rec #0x9  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x08   
.....
*-----------------------------
* Rec #0x8  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x07   
.....
*-----------------------------
* Rec #0x7  slt: 0x03  objn: 939438(0x000e55ae)  objd: 941251  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x06   
.....
*-----------------------------
* Rec #0x6  slt: 0x03  objn: 1126696(0x00113128)  objd: 1126696  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x05   
.....
*-----------------------------
* Rec #0x5  slt: 0x03  objn: 1123035(0x001122db)  objd: 1123035  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x04   
.....
*-----------------------------
* Rec #0x4  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x03   
.....
*-----------------------------
* Rec #0x3  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x02   
.....
*-----------------------------
* Rec #0x2  slt: 0x03  objn: 939448(0x000e55b8)  objd: 939448  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x01   
.....
*-----------------------------
* Rec #0x1  slt: 0x03  objn: 1126675(0x00113113)  objd: 1126675  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x00   
.....
KDO Op code: LMN row dependencies Disabled
.....

Definitions:

  • objn means object id.

Comment:

  • The objects need recovery:
    select * from dba_objects
    where object_id in (939468,1126679,1123018,1162285,1162273,939450,1126696,1123035,939408,
    1126655,1122994,939429,1126678,1123017,939466,1126681,
    1123020,939420,1126647,1122986,939418,1126653,1122992,939438,939448,1126675);
    
  • The first UNDO record includes LMN.
    --
    When running RAC and compatible 11.1 or higher, SMON could fail to
    recover transactions which had undo records for supplemental logging.
     
      (1) SMON is spinning
      (2) Must be RAC and compatible 11.1 or higher
      (3) Supplemental logging must have been enabled.
     
      If so, dump the undo for the transaction mentioned.  If the records
      show LMN entries, it is this bug.
    

    Ref: Bug 9489626 ORA-600 [4464] in RAC and SMON spins on cpu for a table with supplemental logging

ubTools Support - 30/Dec/11 02:22 PM - edited
ACTIONS:

Bug:

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  
.....

Ref: Bug 9857702 ORA-600 [4464] / ORA-600 [4139] by ROLLBACK for a table with supplemental logging enabled

Workaround:

  • Recreate objects that need recovery.

ubTools Support - 30/Dec/11 02:29 PM
Waiting for the customer action.

ubTools Support - 16/Jan/12 03:28 PM
The customer dropped the identified objects, and the problem disappeared.