POUWIEL|COM

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.

Error when installing CPU, could Oracle be more cryptic ?!? Read the rest of this entry »

We regularly get the request for a refresh of an OTA database.
Usually, the end-users would like for their settings to survive the refresh.

We use a couple of scripts to save these setting (eg. passwords and such), however, the database links didn’t survive last time.
Probably because the passwords aren’t visible anymore in 10g.
Update!: Not true, just had a peek in SYS.LINK$ ver 10.1.05 and column AUTHPWD still had (unencrypted!) values.
Update2!: Somewhat true, just had a peek in SYS.LINK$ ver 10.2.0.1 & 10.2.0.2 and column PASSWORDX has encrypted values.

It is possible to retrieve the passwords using dbms_metadata.get_ddl.
More after the jump:
Read the rest of this entry »

Here’s how:
Doc ID: Note:175258.1
Oracle Metalink

Ever wanted to know what that ORA-600 is about ?:
Oracle Metalink