Unable To Purge Distributed Transaction in Collecting Mode (Doc ID 1549307.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

Distributed transaction was not purged following the sqls below:
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mm-yyyy HH24:MI:SS') FAIL_TIME,STATE, MIXED FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID GLOBAL_TRAN_ID FAIL_TIME STATE MIXED 
79.0.856612 XXXX.a476b3a4.79.0.856612 07-04-2013 09:38:17 collecting no

SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;
LOCAL_TRAN_ID IN_OUT INTERFACE DATABASE 
79.0.856612 in N
79.0.856612 out N CDR_LINK
79.0.856612 out N XXX_LINK

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

SQL> exec dbms_transaction.purge_lost_db_entry('79.0.856612');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mm-yyyy HH24:MI:SS') FAIL_TIME,STATE, MIXED FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID GLOBAL_TRAN_ID FAIL_TIME STATE MIXED 
79.0.856612 XXX.a476b3a4.79.0.856612 07-04-2013 15:08:27 collecting no

set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$ where local_tran_id = '79.0.856612';
delete from sys.pending_sessions$ where local_tran_id = '79.0.856612';
delete from sys.pending_sub_sessions$ where local_tran_id = '79.0.856612';
commit;

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mm-yyyy HH24:MI:SS') FAIL_TIME,STATE, MIXED FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID GLOBAL_TRAN_ID FAIL_TIME STATE MIXED 
79.0.856612 XXX.a476b3a4.79.0.856612 07-04-2013 15:08:27 collecting no

Same issue

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms