Distributed Transactions in Prepared State Fails and can't rollback or commit or purge with event "free global transaction table entry"

Distributed Transactions in Prepared State Fails
The distributed transactions (dba_2pc_pending) will make below errors.
· There are two kind of errors will be appear in alert log,as shown as below
1. ORA-01591: lock held by in-doubt distributed transaction 103.10.39920
2. ORA-02050: transaction 9.2.446246 rolled back, some remote DBs may be in-doubt
ORA-03135: connection lost contact
Sun Aug 26 08:57:48 2012
DISTRIB TRAN db.ac.a68396a9.9.2.446246 is local tran 9.2.446246 (hex=09.02.6cf26)
insert pending collecting tran, scn=10238878734264 (hex=94f.ecbad7b8
(Note: - in this stage database automatically delete that transaction.)Other wise the transaction will go to dead mode (state of the transactions will be prepared ).
· There is chance hit high concurrency on database with buffer busy waits and enq:index contention too.
· The result of the blocking sessions wait event will be "Undo Segment Recovery".
· If you query the transaction from dba_2pc_pending,you can able to see that state is prepared.

select * from dba_2pc_pending where state='prepared' order by fail_time;
In this table, there is column called GLOBAL_TRAN_ID, use this column and track the transactions details from other database, like as below.

select * from dba_2pc_pending@db2 where GLOBAL_TRAN_ID='db2.232bc983.41.6.4224568';

Gateway database transaction state with committed and main database state has prepared mode.
But, some situation main database transaction state only prepared!

Solution

Oracle recommendations are to rollback or commit the transaction.

select * from dba_2pc_pending where local_tran_id='xx.39920';

103.10.xxx920 db1.a68396a9.103.10.39920 prepared

exec DBMS_TRANSACTION.rollback_force('local_trans_id');

example :- exec DBMS_TRANSACTION.rollback_force('6.1xxxx297');
or
commit force 'local_trans_id'
example :- commit force '103.10.39920';
If rollback or commit transaction hangs with wait event "free global transaction table entry", this situation try to purge transaction
exec dbms_transaction.purge_lost_db_entry('local_trans_id');
example :- exec dbms_transaction.purge_lost_db_entry('103.10.39920');.
This will be failed with error.
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

Rollback, commit and purge failed with error, and then only you can follow below steps.

Case 1:

Login to the database as sysdba

select * from dba_2pc_pending;

ensure that transaction is in PREPARED state

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
AND ktuxeusn=6
AND KTUXESLT=12
AND KTUXESQN=399297;

SELECT * FROM sys.pending_trans$;
SELECT * FROM sys.pending_sessions$;
SELECT * FROM sys.pending_sub_sessions$;

/* <== Replace this with your local tran id */
delete from sys.pending_trans$ where local_tran_id = '103.10.39920'; 
/* <== Replace this with your local tran id */
delete from sys.pending_sessions$ where local_tran_id = '103.10.39920'; 
/* <== Replace this with your local tran id */
delete from sys.pending_sub_sessions$ where local_tran_id ='103.10.39920'; 

commit;

alter system disable distributed recovery;

insert some dummy rows

insert into pending_trans$
(LOCAL_TRAN_ID,
GLOBAL_TRAN_FMT,
GLOBAL_ORACLE_ID,
STATE,
STATUS,
SESSION_VECTOR,
RECO_VECTOR,
TYPE#,
FAIL_TIME,
RECO_TIME)
values
('103.10.39920', /* <== Replace this with your local tran id */
   306206, /* */
   'XXXXXXX.12345.1.2.3', /* These values can be used without any */
   'prepared',
   'P', /* modification. Most of the values are */
   hextoraw('00000001'), /* constant. */
   hextoraw('00000000'), /* */
   0,
   sysdate,
   sysdate);

insert into pending_sessions$
values
  ('103.10.39920', /* <==Replace only this with your local tran id */
   1,
   hextoraw('05004F003A1500000104'),
   'C',
   0,
   30258592,
   '',
   146);

commit;

commit force '103.10.39920';

BEGIN
dbms_transaction.purge_lost_db_entry('103.10.39920');

END;

alter system enable distributed recovery;

Note: If the case 1 failed, Follow case2

Case 2:

Cause

ORA-02075 is caused because a commit force or rollback force was issued from a session in another instance and it is performing recovery of pending transactions.

Solution

SQL> alter system disable distributed recovery;
SQL> delete from sys.pending_trans$ where local_tran_id = 'LOCAL TRANSACTION ID';
SQL> delete from sys.pending_sessions$ where local_tran_id = 'LOCAL TRANSACTION ID';
SQL> delete from sys.pending_sub_sessions$ where local_tran_id = 'LOCAL TRANSACTION ID';
SQL> commit;

SQL> alter system enable distributed recovery;