RE: lock held by in-doubt distributed transaction
- From: Tony.Adolph@xxxxxx
- To: "Powell, Mark D" <mark.powell@xxxxxxx>
- Date: Thu, 10 Feb 2005 16:29:34 +0100
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
