In-Doubt transactions are the result of a failure during a distributed transaction and can be caused by any of the followings:
– A network failure between the involved databases
– A server crash
– An unhandled software error
In-Doubt transactions can occur between Oracle databases or Oracle and other-vendor databases.
When we have an in-doubt transaction we can perform the following query:
SQL> select * from dba_2pc_pending;
All the pending transactions are going to be listed, the number we are interested in is the LOCAL_TRAN_ID.
To clean an entry we use the following procedure (as sysdba):
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('trans_id');
Note: Oracle recommend to wait for the RECO to recover pending transactions and does not recommend to perform this procedure unless:
* Total loss of the remote database
* Reconfiguration in software resulting in loss of two-phase commit capability
* Loss of information from an external transaction coordinator such as a TPMonitor
There are cases when the distributed transaction either commited or rollbacked on any of the nodes. To determined how to resolve the pending transaction you need to identify the remote DB (query the DBA_2PC_NEIGHBORS) and on every node check if any of the transactions have commited (check the COMMIT# column won’t be null on the DBA_2PC_PENDING view).
If any of the nodes have performed a commit then before executing the DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure you should force a commit on all the participating nodes.
SQL> COMMIT FORCE '1.93.29';
Carlos Acosta Alamo