Oracle Database alert showing ORA-02062

It showed up because failed distributed transaction cannot be committed or rolled back.

To identify failed transaction,

sys@DB01> alter session set nls_date_format='Mon-dd-yyyy HH24:MI:SS';

Session altered.

sys@DB01> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, FAIL_TIME, STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID            FAIL_TIME            STATE            MIXED

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

3.32.45182             DB01.d947e55d.3.32.45182  Jan-26-2010 09:42:09 collecting       no

                       

Wow, the issues has been there since Jan 26, 2010. Let's see who is the neighbor database (remote database),

sys@DB01> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_OUT INTERFACE DATABASE

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

3.32.45182             in     N

3.32.45182             out    N         DB02

Check with DBA, the remote database DB02 was gone long time ago (Yes, Jan-26-2010 is pretty 'long time ago').Therefor, it is safe to purge this pending transaction,

sys@DB01> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.32.45182');

PL/SQL procedure successfully completed.

sys@DB01> commit;

Commit complete.

Doubel check if the pending transcation is gone,

sys@DB01> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,FAIL_TIME,STATE, MIXED FROM DBA_2PC_PENDING;

no rows selected

Check alert log, there is no new ORA-02062 found.