http://www.oracle11ggotchas.com/articles/ProceduretoROLLBACKFORCEpendingin-doubttransaction.htm
Copied as it is from the above link.
Copied as it is from the above link.
Procedure to ROLLBACK FORCE pending in-doubt transaction
Below is the procedure necessary to force the rollback of a failed distributed transaction, known as an “in-doubt transaction”. Oracle uses a two phase commit (2PC) mechanism to commit changes locally and remotely in a distributed transaction.
The following error is normally associated with an in doubt transaction. Here, a network “glitch” has caused the ORA-03113.
ORA-03113: end-of-file on communication channel
In addition to the ORA-03113 is ORA-02050, stating that the local in-doubt transaction has been rolled back automatically, this can be seen in the local database instance alert log:
Error 3113 trapped in 2PC on transaction 70.31.1376339. Cleaning up.
Error stack returned to user:
ORA-02050: transaction 70.31.1376339 rolled back, some remote DBs may be in-doubt
ORA-03113: end-of-file on communication channel
Thu Jun 09 12:28:32 2011
DISTRIB TRAN REMDB.WORLD.f9784a67.3.9.924681
is local tran 70.31.1376339 (hex=46.1f.150053)
insert pending collecting tran, scn=6187118039 (hex=1.70c7edd7)
However, this is not always the case, as the transaction is seen as pending in the “pending two phase commit” view (DBA_2PC_PENDING)
SQL> select LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, FAIL_TIME, RETRY_TIME from dba_2pc_pending;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE FAIL_TIME RETRY_TIME
------------- ------------------------------- --------- --------- ----------
70.31.1376339 REMDB.WORLD.f9784a67.3.9.924681 collecting 09-JUN-11 09-JUN-11
If the state of the transaction is “prepared”, it is possible to force rollback the transaction by appending the transaction id to the command as follows (as sysdba):
SQL> ROLLBACK FORCE '70.31.1376339';
If the state of the transaction is “collecting”, you will suffer the following error:
SQL> ROLLBACK FORCE '70.31.1376339';
ROLLBACK FORCE '70.31.1376339'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 70.31.1376339
In this case, you need to execute the following procedure in the DBMS_TRANSACTION package to clear.
SQL> execute dbms_transaction.purge_lost_db_entry('70.31.1376339')
PL/SQL procedure successfully completed.
Rerun the query against DBA_2PC_PENDING to confirm the pending local transaction has gone.
SQL> select LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, FAIL_TIME, RETRY_TIME from dba_2pc_pending;
no rows selected
Also check DBA_2PC_NEIGHBORS to confirm the pending remote transaction has gone.
SQL> select database,local_tran_id,dbid,sess#,branch from dba_2pc_neighbors;
no rows selected
Further information may be found at http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_txnman006.htm#ADMIN12266
