Log Mining (10g)

Please note the database parameter log_parallelism needs to be set at 1 for this to work

In this day and age of flashback configuration, log mining almost seems like an out-dated process, never to see the light of day. However, today it came in handy.

I was asked to check for any transactional rollbacks which may have been issued on the database during a certain time period. Unfortunately flashback was only configured for a short duration on the database and the time I had to check was out of bounds.

I therefore ....

• Copied across the archive log files from backup tape to the archive log directory.

• I then added to log miner the archive log files I wanted mined:

exec SYS.DBMS_LOGMNR.ADD_LOGFILE('pathname/filename.arc');

• The next step was to start log-mining based on the timestamps

  BEGIN
   SYS.DBMS_LOGMNR.START_LOGMNR(STARTTIME => TO_DATE('13-Apr-2010 08:00', 'DD-MON-YYYY HH24:MI')
                               ,ENDTIME => TO_DATE('13-Apr-2010 16:00', 'DD-MON-YYYY HH24:MI')
                               ,OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + SYS.DBMS_LOGMNR.CONTINUOUS_MINE);
  END;
  /
  

• Note: This step may be an alternative to the above timestamps:

execute DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.dict_from_online_catalog);

• This then gave me the information I needed:

SELECT timestamp, sql_redo FROM v$logmnr_contents;

• I concluded the task by clearing out logminer:

exec SYS.DBMS_LOGMNR.END_LOGMNR;