DBMS_TRANSACTION
Если распределенную транзакцию нельзя отменить, надо её подменить и отменить!
In-doubt Transaction in Prepared State in DBA_2PC View - ORA-30019, ORA-06510
20
Sunday
May 2012
Posted by shrikant rao in Troubleshooting ≈ 6 Comments
1 Vote
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]
In-doubt Transaction in Prepared State in DBA_2PC View - ORA-30019, ORA-06510
20
Sunday
May 2012
Posted by shrikant rao in Troubleshooting ≈ 6 Comments
1 Vote
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]