DB Version: 9.2.0.8

Recently I encounter an issue with in-doubt distributed transaction. A user had executed a process from application end which connects to another Database to fetch records. For some reason the session was killed. There was no process running at background but still when user executed the same for second time, he received error pointing to the same LOCAL_TRAN_ID ‘10.19.6462883’.

ORA-01591: lock held by in-doubt distributed transaction 10.19.6462883

I checked for entry with the local tran id and it was present.

SQL> column database format a22
SQL> column global_tran_id format a25
SQL> column global_name format a22
SQL> SELECT * from global_name;

GLOBAL_NAME
----------------------
DBSIT

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                TO_CHAR(FAIL_TIME,'DD-MON-YYYY STATE            MIX
---------------------- ----------------------------- ------------------------------ ---------------- ---
22.38.4444334          DBSIT.e9487d1c.22.38.4444334  11-feb-2012 11:04:03           collecting       no
10.19.6462883          DBSIT.e9487d1c.10.19.6462883  30-apr-2012 00:00:00           collecting       no
1.44.3809621           DBSIT.e9487d1c.1.44.3809621   07-oct-2011 16:39:17           collecting       no
3.47.5102063           DBSIT.e9487d1c.3.47.5102063   05-aug-2011 17:41:10           collecting       no

SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_ I DATABASE
---------------------- --- - ----------------------
22.38.4444334          in  N
10.19.6462883          in  N DBSIT
1.44.3809621           in  N
1.44.3809621           in  N DBSIT
1.44.3809621           in  N X
3.47.5102063           in  N
3.47.5102063           in  N DBSIT
3.47.5102063           out N X
3.47.5102063           out N X
3.47.5102063           out N X
3.47.5102063           out N DBS
3.47.5102063           out N X
3.47.5102063           out N X
3.47.5102063           out C X
3.47.5102063           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N DBS
1.44.3809621           out N X
1.44.3809621           out N X
10.19.6462883          out C X
22.38.4444334          out N X
22.38.4444334          out N X
22.38.4444334          out N X
22.38.4444334          out N DBS
10.19.6462883          out N X

29 rows selected.

Tried executing rollback force for the specific transaction ID. But that didn’t help, it got hang.

rollback force '10.19.6462883';

Then tried to purge the lost entry, but encounter with ORA-30019.

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883'); 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

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY is not supported when using AUM. This is due to fact that “set transaction use rollback segment…” cannot be done in AUM.

This can only be resolved with following process,

1.) alter session set “_smu_debug_mode” = 4;
2.) commit; — so that the call to DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY is the first
— step of the transaction
3.) execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘local_tran_id’);” — Oracle Doc

NOTE:”_smu_debug_mode” can not set with alter session on 10g.
If you try you will get error ORA-02096
Thus in 10g onwards use ALTER SYSTEM for setting _smu_debug_mode
NOTE:
In 9.2 alter session works
alter session set “_smu_debug_mode” = 4;

But in my case this didn’t help me. Still received the error but this time ORA-06510.

SQL> alter session set "_smu_debug_mode" = 4;

Session altered.

SQL> commit;

Commit complete.

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.19.6462883'); 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

Later I came across Oracle MOS ID “Rollback Force In-doubt Transaction in Prepared State Fails With ORA-02075 [ID 1413375.1]”

Checked out the lost entry transaction details.

SQL> SELECT * FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;/* <== This value is the txn undo seg# that is displayed in the first part of the transaction ID */

ADDR                   INDX    INST_ID   KTUXEUSN   KTUXESLT   KTUXESQN
---------------- ---------- ---------- ---------- ---------- ----------
KTUXERDBF  KTUXERDBB  KTUXESCNB  KTUXESCNW KTUXESTA
---------- ---------- ---------- ---------- ----------------
KTUXECFL                   KTUXEUEL  KTUXEDDBF  KTUXEDDBB  KTUXEPUSN  KTUXEPSLT
------------------------ ---------- ---------- ---------- ---------- ----------
KTUXEPSQN   KTUXESIZ
---------- ----------
00000001103944EC         19          1         10         19    6462883
71     132239 3672859349         49 PREPARED
SCO|COL|REV|DEAD                  4         71     132239          0          0
0          1

SQL> select * from sys.pending_trans$ where local_tran_id = '10.19.6462883';

SQL> select * from sys.pending_sessions$ where local_tran_id = '10.19.6462883';

LOCAL_TRAN_ID          SESSION_ID BRANCH_ID       I      TYPE# PARENT_DBID      PARENT_DB              DB_USERID
---------------------- ---------- --------------- - ---------- ---------------- ---------------------- ----------
10.19.6462883                   1 0000            N          0 DBSIT            DBSIT                           39

SQL> select * from sys.pending_sub_sessions$ where local_tran_id ='10.19.6462883';

LOCAL_TRAN_ID          SESSION_ID SUB_SESSION_ID I DBID             LINK_OWNER DBLINK          BRANCH_ID       SPARE
---------------------- ---------- -------------- - ---------------- ---------- --------------- --------------- ------
10.19.6462883                   1             15 N 878b3161                 39 INDB            0A001300A39D620 0010F
10.19.6462883                   1             14 C 878b3161                 39 IBM               0A001300A39D620 0010E

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA Status,KTUXECFL Flags FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;

KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
10         19    6462883 PREPARED         SCO|COL|REV|DEAD

Now when the purging process doesn’t provide any solution. We need to proceed with below steps to resolve the issue. Only thing we need to do is delete the record from below 3 tables and then try to remove the lost ID.
But before deleting the records, I will suggest to take a backup of record, by backup I mean record details for that particular id.

Getting the details:

select * from sys.pending_trans$ where local_tran_id = ‘10.19.6462883’;
select * from sys.pending_sessions$ where local_tran_id = ‘10.19.6462883’;
select * from sys.pending_sub_sessions$ where local_tran_id =’10.19.6462883′;

SQL> alter system disable distributed recovery;

SQL> delete from sys.pending_trans$ where local_tran_id = '10.19.6462883';

1 row deleted.

SQL> delete from sys.pending_sessions$ where local_tran_id = '10.19.6462883';

1 row deleted.

SQL> delete from sys.pending_sub_sessions$ where local_tran_id ='10.19.6462883';

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system enable distributed recovery;

Now we can cross verify whether the ID still exist in any of the 3 tables. This shows the trans ID is not present in the Database. But If we check the sys.x$ktuxe table the entry will still be available.

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA Status,KTUXECFL Flags FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;

KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
10         19    6462883 PREPARED         SCO|COL|REV|DEAD

Lets try to purge the trans ID.

SQL> Commit force '10.19.6462883';
Commit force '10.19.6462883'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 10.19.6462883

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

*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1

By googling in more deep, I came across a blog. It was said in such kind of scenario, we need to insert dummy record in all the 3 tables and try to purge the in-doubt transaction.
Well I even tried that. But unfortunately it made my instance terminate. I inserted dummy value in the tables and try to purge which didn’t help. So we tried with a Database bounce.
So Database stared, but it was hardy 30 seconds and the instance terminated. Inserting dummy value in the tables.

SQL> insert into sys.pending_trans$ (local_tran_id,GLOBAL_TRAN_FMT,state,status,SESSION_VECTOR,RECO_VECTOR,FAIL_TIME,RECO_TIME) values ('10.19.6462883',1,'A','A','A','A','30-APR-2012','30-APR-2012');

1 row created.

SQL> insert into sys.pending_sessions$ (local_tran_id,SESSION_ID,BRANCH_ID,INTERFACE,DB_USERID) values ('10.19.6462883',1,'A','A',1);

1 row created.

SQL> insert into sys.pending_sub_sessions$ (local_tran_id,SESSION_ID,SUB_SESSION_ID,INTERFACE,DBID,LINK_OWNER,DBLINK) values ('10.19.6462883',1,1,'A',1,1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> Commit force '10.19.6462883';
Commit force '10.19.6462883'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [18124], [], [], [], [], [], [], []

That is why I stated to keep previous record before going ahead with the deletion. Now just insert the actual records in the tables and try to purge the trans ID.

insert into sys.pending_trans$ values ('10.19.6462883',306206,'DBSIT.e9487d1c.10.19.6462883','','','prepared','P','null','00000001','00000001',0,'4-30-2012','','30-04-2012','BIF','ASPNET','BAN107693','KBDT07693','214126256853',,'',,'');

insert into sys.pending_sessions$ values ('10.19.6462883',1,'0000','N',0,'DBSIT','DBSIT',39);

insert into sys.pending_sub_sessions$ values ('10.19.6462883',1,15,'N','878b3161',39,'INDB','0A001300A39D6200010F','');
insert into sys.pending_sub_sessions$ values ('10.19.6462883',1,14,'C','878b3161',39,'IBM','0A001300A39D6200010E','');

Now lets try the whole process for purging the in-doubt transaction.

SQL> alter session set "_smu_debug_mode" = 4;

Session altered.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                TO_CHAR(FAIL_TIME,'DD-MON-YYYY  STATE            MIX
---------------------- ----------------------------- ------------------------------- ---------------- ---
22.38.4444334          DBSIT.e9487d1c.22.38.4444334  11-feb-2012 11:04:03            collecting       no
1.44.3809621           DBSIT.e9487d1c.1.44.3809621   07-oct-2011 16:39:17            collecting       no
3.47.5102063           DBSIT.e9487d1c.3.47.5102063   05-aug-2011 17:41:10            collecting       no

SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_ I DATABASE
---------------------- --- - ----------------------
22.38.4444334          in  N
1.44.3809621           in  N
1.44.3809621           in  N DBSIT
1.44.3809621           in  N X
3.47.5102063           in  N
3.47.5102063           in  N DBSIT
3.47.5102063           out N X
3.47.5102063           out N X
3.47.5102063           out N X
3.47.5102063           out N DBS
3.47.5102063           out N X
3.47.5102063           out N X
3.47.5102063           out C X
3.47.5102063           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N X
1.44.3809621           out N DBS
1.44.3809621           out N X
1.44.3809621           out N X
22.38.4444334          out N X
22.38.4444334          out N X
22.38.4444334          out N X
22.38.4444334          out N DBS

26 rows selected.

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA Status,KTUXECFL Flags FROM sys.x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;

no rows selected

SQL>

Well at last, the distributed transaction ‘10.19.6462883’ has been purged.

In normal scenario, DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY works fine.
How to Purge a Distributed Transaction from a Database [ID 159377.1]

But if it doesn’t help then this is the way to make it work.

For more reference can go through below MOS ID

Manually Resolving In-Doubt Transactions: Different Scenarios [ID 126069.1]
Rollback Force In-doubt Transaction in Prepared State Fails With ORA-02075 [ID 1413375.1]
ORA-02062: DISTRIBUTED RECOVERY RECEIVED DBID 01010101, EXPECTED DB21020 [ID 1077215.1]

Advertisements