One of my databases started to spew tracefiles as if it’s life depended on it:
Unix process pid: 22450, image: oracle@server1 (MMON) *** 2013-02-15 16:40:05.655 *** SESSION ID:(1873.1) 2013-02-15 16:40:05.655 *** CLIENT ID:() 2013-02-15 16:40:05.655 *** SERVICE NAME:() 2013-02-15 16:40:05.655 *** MODULE NAME:() 2013-02-15 16:40:05.655 *** ACTION NAME:() 2013-02-15 16:40:05.655 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 *** 2013-02-15 16:45:08.169 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 *** 2013-02-15 16:50:09.519 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 *** 2013-02-15 17:00:12.288 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 *** 2013-02-15 17:05:13.881 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 *** 2013-02-15 17:15:16.582 minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000
One of my astute colleagues pointed my attention to the following MOS document:
Minact-Scn Master-Status: Grec-Scn Messages In Trace File [ID 1361567.1] which refers to
MINACT-SCN Errors in Alert and Trace Files Generated by MMON [ID 1297450.1] which refers to
How to Purge a Distributed Transaction from a Database [ID 159377.1]
It went a little like this:
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID
----------------------
GLOBAL_TRAN_ID
--------------------------------------------------------------------------------
TO_CHAR(FAIL_TIME,'D STATE MIX
-------------------- ---------------- ---
8.19.321913
1463898948.0000013CDE8005110000000179F68840A089FFFEE644B640AED6B02438B2F39D9665F
AFB
15-feb-2013 16:37:41 prepared no
SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;
LOCAL_TRAN_ID IN_ I
---------------------- --- -
DATABASE
--------------------------------------------------------------------------------
8.19.321913 in N
jdbc_11
SQL> rollback force '8.19.321913';
Rollback complete.
SQL> execute dbms_transaction.purge_lost_db_entry('8.19.321913');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;
no rows selected
Be aware that the support document 159377.1 does not speak of the rolback force. If you don’t execute that particular command it will not work.