ORA-02050: transaction rolled back, some remote DBs may be in-doubt

ORA-02050: transaction rolled back, some remote DBs may be in-doubt

Indicates that a communication error occurred during the two-phase commit.

The two phase-commit mechanism is used to ensure data integrity in a distributed transaction.

Example of handling the In-Doubt Transactions - in this example we are taking two servers ACCT and FIN.

 

Got error ORA-02050: transaction rolled back, some remote DBs may be in-doubt at FIN Server

Scenario- LOCAL_TRAN_ID and GLOBAL_TRAN_ID are same.

 

Execute below query in SYS user at FIN server.

SQL> SELECT LOCAL_TRAN_ID, STATE FROM dba_2pc_pending;

LOCAL_TRAN_ID  GLOBAL_TRAN_ID         STATE

-------------  --------------- ----------------

3.12.254        --------.3.12.254   collecting

 

Here local id and Global id is same, so it is the global coordinator.

State column is collecting means this server currently is waiting for a prepared message from a non-commit point site.

 

[ Execute below query in SYS user at ACCT server.

No output from both dba_2pc_pending and dba_2pc_neighbors since 'nothing' happened (commit/rollback) at ACCT SERVER end.

 

[The above error message tells us about the local transaction 3.4.270 which is rolled back. This means the global coordinator aborted the transaction because it never received a prepared state from the crashing non-commit point site. Note that there are no local distributed locks because of this, since this site never entered the prepared state and already rolled back the local portion of the transaction.]

 

A rollback force in this case will not work because this node is still in collecting state. A rollback/commit force will only work for nodes in a prepared state. Note also that the local portion of the transaction was already rolled back.

If we try the [SQL> EXECUTE Dbms_Transaction.ROLLBACK_FORCE('3.12.254')], we will received the following error:

ORA-02058: no prepared transaction found with ID...

 

All we can do here is to purge/clear the in-doubt transaction entry

 

To purge the in-doubt transaction entry at FIN Server-

SQL> EXECUTE Dbms_Transaction.PURGE_LOST_DB_ENTRY('3.12.254')

PL/SQL PROCEDURE successfully completed.

 

SQL> COMMIT;

COMMIT COMPLETE.

 

SQL> SELECT LOCAL_TRAN_ID, STATE FROM dba_2pc_pending;

no rows selected

 Some more articles you might also be interested in :-

   Distributed transaction in oracle

   ORA-02050: transaction rolled back, some remote DBs may be in-doubt

   ORA-01591: lock held by in-doubt distributed transaction

   ORA-31693: Table data object failed to load/unload in expdp