RE: lock held by in-doubt distributed transaction

For all / any that are interested Oracle Support fixed my problem by 
inserting dummy rows into pending_trans$ and pending_sessions$
They recommend the following note, but none of the scenarios here require 
what they advocated in our case:  Note: 126069.1 Manually Resolving 
In-Doubt Transactions

but anyway, here's what happened:

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 4;

  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS  
---------- ---------- ---------- ---------------- ------------------------ 
 
         4         18       4935 PREPARED         SCO|COL|REV|DEAD  

SQL> insert into pending_trans$
  2  ( 
LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME)
  3  values( '4.18.4935',306206, /* */ 'XXXXXXX.12345.1.2.3', /* These 
values can be used without any */
  4  'prepared','P', /* modification. Most of the values are */
  5  hextoraw( '00000001' ), /* constant. */
  6  hextoraw( '00000000' ), /* */
  7  0, sysdate, sysdate );

1 row created.

SQL> insert into pending_sessions$ values('4.18.4935',1,
  2  hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);

1 row created.

commit;

SQL> select * from pending_sessions$

LOCAL_TRAN_ID          SESSION_ID  
---------------------- ----------  
BRANCH_ID  
--------------------------------------------------------------------------------
I      TYPE# PARENT_DBID  
- ---------- ----------------  
PARENT_DB  
--------------------------------------------------------------------------------
 DB_USERID  
----------  
4.18.4935                       1  
05004F003A1500000104  
C          0 30258592  
  
       146  
  

SQL> select * from pending_trans$;

LOCAL_TRAN_ID          GLOBAL_TRAN_FMT  
---------------------- ---------------  
GLOBAL_ORACLE_ID  
----------------------------------------------------------------  
GLOBAL_FOREIGN_ID  
--------------------------------------------------------------------------------
TRAN_COMMENT  
--------------------------------------------------------------------------------
STATE            S H SESSION_ RECO_VEC      TYPE# FAIL_TIME  
---------------- - - -------- -------- ---------- ------------------  
HEURISTIC_TIME     RECO_TIME          TOP_DB_USER  
------------------ ------------------ ------------------------------  
TOP_OS_USER  
----------------------------------------------------------------  
TOP_OS_HOST  
--------------------------------------------------------------------------------
TOP_OS_TERMINAL  
--------------------------------------------------------------------------------
GLOBAL_COMMIT#       SPARE1 SPARE2                             SPARE3  
---------------- ---------- ------------------------------ ----------  
SPARE4  
------------------------------  
4.18.4935                       306206  
XXXXXXX.12345.1.2.3  
  
  
prepared         P   00000001 00000000          0 10-FEB-05  
                   10-FEB-05  
  
SQL> rollback force '4.18.4935';

Rollback complete.

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935'); END;

*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1 


SQL> select count(*) from tracking.tracked_lacosa_backend_request;
select count(*) from tracking.tracked_lacosa_backend_request
                              *
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 10.15.4693 


SQL> alter session set "_smu_debug_mode" = 4;

Session altered.

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935');

PL/SQL procedure successfully completed.

SQL> select count(*) from tracking.tracked_lacosa_backend_request;
select count(*) from tracking.tracked_lacosa_backend_request
                              *
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 10.15.4693 


----*************  Notice here the transaction id changed ************* 
-----

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;

  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS  
---------- ---------- ---------- ---------------- ------------------------ 
 
        10         15       4693 PREPARED         SCO|COL|REV|DEAD  

SQL> insert into pending_trans$ ( 
LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,
  2  STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME)
  3  values( '10.15.4693',
  4  306206, /* */
  5  'XXXXXXX.12345.1.2.3', /* These values can be used without any */
  6  'prepared','P', /* modification. Most of the values are */
  7  hextoraw( '00000001' ), /* constant. */
  8  hextoraw( '00000000' ), /* */
  9  0, sysdate, sysdate );

1 row created.

SQL> insert into pending_sessions$ values('10.15.4693',1,
  2  hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);

1 row created.

SQL> commit;

Commit complete.

SQL> rollback force '10.15.4693';

Rollback complete.

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.15.4693');

PL/SQL procedure successfully completed.

SQL> select count(*) from tracking.tracked_lacosa_backend_request;

  COUNT(*)  
----------  
     98697  

Yee Ha!

Tony




"Powell, Mark D" <mark.powell@xxxxxxx> 
02/09/2005 02:13 PM

To
"'Tony.Adolph@xxxxxx'" <Tony.Adolph@xxxxxx>, oracle-l@xxxxxxxxxxxxx
cc

Subject
RE: lock held by in-doubt distributed transaction






Is tracking.tracked_lacosa_request a local table or remote?  If remote did 
you check the dba_pending* views on the remote system.  The bug I was 
thinking of showed entries in the pending views but your problem does not 
so it is likely a different bug.
 
I checked metalink for the ORA-01591error and got around 47 hits.  I 
glanced at a few but I did not see a perfect match.  I think you will need 
to open an iTAR and get help directly from Oracle.  Please let the board 
know how this is resolved.
 
HTH -- Mark D Powell --
 
-----Original Message-----
From: Tony.Adolph@xxxxxx [mailto:Tony.Adolph@xxxxxx]
Sent: Wednesday, February 09, 2005 3:37 AM
To: mark.powell@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
Subject: RE: lock held by in-doubt distributed transaction


Hi Mark, 

Thanks for the feedback. I tried... 

SQL> alter session set "_smu_debug_mode" = 4; 

Session altered. 

SQL> exec dbms_transaction.purge_lost_db_entry('4.18.4935'); 
BEGIN dbms_transaction.purge_lost_db_entry('4.18.4935'); END; 

* 
ERROR at line 1: 
ORA-06510: PL/SQL: unhandled user-defined exception 
ORA-06512: at "SYS.DBMS_TRANSACTION", line 94 
ORA-06512: at line 1 

:-( 

Tony 


"Powell, Mark D" <mark.powell@xxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 
02/08/2005 06:48 PM 

Please respond to
mark.powell@xxxxxxx



To
oracle-l@xxxxxxxxxxxxx 
cc

Subject
RE: lock held by in-doubt distributed transaction








I believe this is a bug.  The first solution I saw was support telling
people to convert their systems to using regular rollback segments but I
believe that you can issue ' alter session set "_smu_debug_mode" = 4; ' to
get around the problem.  I do not have the bug number so before you alter
your session you may want to check metalink on the hidden parameter.
HTH -- Mark D Powell --


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tony.Adolph@xxxxxx
Sent: Tuesday, February 08, 2005 11:55 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: Maxim.Demenko@xxxxxx
Subject: lock held by in-doubt distributed transaction


Hi folks,
One of our developers has just reported this error after a simple select:

select COUNT(*) FROM tracking.tracked_lacosa_request
                             *
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 4.18.4935

So I looked for the in doubt transaction as follows:

select * from dba_2pc_neighbors;

and

select * from dba_2pc_pending;

but both returned no rows.

I tried the same query and also get the same error.  I bounced the 
database (immediate) and still get the error and all the time with the 
same transaction id 4.18.4935.  I got the developers to bounce their jboss 

but no joy. 

I tried dbms_transaction.purge_lost_db_entry:

SQL> exec dbms_transaction.purge_lost_db_entry('4.18.4935');
BEGIN dbms_transaction.purge_lost_db_entry('4.18.4935'); END;

*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1

Any ideas folks?

Cheers
Tony

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Other related posts: