IN-DOUBT TRANSACTIONS IN Distributed Environment:-

How to Handle IN-DOUBT TRANSACTIONS IN Distributed Environment:

A transaction is in-doubt when there is a failure during any aspect of the two-phase commit.

Distributed transactions become in-doubt in the following ways:

A server machine running Oracle software crashes.

A network connection between two or more Oracle databases involved in distributed processing is disconnected.

An unhandled software error occurs.

STATE Column of DBA_2PC_PENDING

collecting     This category normally applies only to the global coordinator or local coordinators. The node is currently collecting information from other database servers before it can decide whether it can prepare.
prepared     The node has prepared and may or may not have acknowledged this to its local coordinator with a prepared message. However, no commit request has been received. The node remains prepared, holding any local resource locks necessary for the transaction to commit.
committed     The node (any type) has committed the transaction, but other nodes involved in the transaction may not have done the same. That is, the transaction is still pending at one or more nodes.
forced commit     A pending transaction can be forced to commit at the discretion of a database administrator. This entry occurs if a transaction is manually committed at a local node by a database administrator. 
forced abort (rollback)    


Scenarios 1:

PD team will inform DBA to look into the indoubt transaction issue.
From database point of view we will not come across any issue regards to database.
Once we got information from PD on this, the following steps needs to be followed to rollback the indoubt transactions.

a) logon to the database and check the STATUS column of DBA_2PC_PENDING table.
We will find one or more indoubt transactions. Check for GLOBAL_TRAN_ID column.
b) Execute the below statement,

ROLLBACK FORCE ‘LOCAL_TRAN_ID’;
COMMIT;

c) If we check once again the DBA_2PC_PENDING table, this transaction will be rolled back.

Scenarios 2:

If the transaction did not rollback after the above steps in scenarios 1 then we need to bounce the database.

Though we are DBA on this application, we can startup and shutdown the database.

a) After executing the ROLLBACK FORCE ‘global_tran_id’ , still we find the entry in the DBA_2PC_PENDING table.
b) We will bounce the database after all the applications are brought down.
c) Again follow the steps mentioned in scenarios 1 point (a), (b) and (c).

BEGIN

   DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('LOCAL_TRAN_ID');

END;

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('LOCAL_TRAN_ID');