Oracle LogMiner

Dbvisit support may ask for a row history, also known as LogMiner dump to further analyse support issues. This means running LogMiner on the archive/online redo logs to obtain all changes on a particular row.

Setting up LogMiner

A date range or Oracle SCN is needed for the period that is of interest. This is often constrained by archive logs available (only archive logs still known to the database are used by default).

A SYSDBA connection is needed. Please execute the following query:

begin
 DBMS_LOGMNR.START_LOGMNR( 
   STARTTIME => sysdate-1,
   ENDTIME => sysdate,
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE
 );
end;

 

Querying the LogMiner data

After executing START_LOGMNR, the LogMiner data is available via the following command:

SELECT * FROM V$LOGMNR_CONTENTS;

Running this query in full and without any where clause can take a very long time. Also, it's not advisable to insert the full data back into the database, as it would cause lot of archive logs generated and the next query to the view would query these new logs, too.

The recommended approach is:

  1. Find one operation to the row in question; this is often done by using TABLE_NAME, XID (transaction ID, available in Replicate conflict log), timestamp or directly by searching for values in SQL_REDO.
  2. Obtain rowid of the row in question (in some cases, this is available directly and thus previous step is not needed)
  3. Query the v$logmnr_contents by this ROW_ID.
  4. Export the rows found to some usable and human-readable format - usually csv, xls or insert script work fine. Upload this to Dbvisit support.