LogMiner

LogMiner enables the analysis of the contents of archived redo logs. It can be used to provide a historical view of the database without the need for point-in-time recovery. It can also be used to undo operations, allowing repair of logical corruption.

Create Dictionary File
Adding Logs
Starting LogMiner
Querying Log Information
Stopping LogMiner

Create Dictionary File

Without a dictionary file LogMiner displays all tables and columns using their internal object IDs and all values as hex data. The dictionary file is used to translate this data into a more meaningful format. For the dictionary file to be created the following initialization parameter must be set and the instance must be mounted or open.

UTL_FILE_DIR=C:\Oracle\Oradata\TSH1\Archive

The dictionary file is created using the BUILD procedure in the DBMS_LOGMNR_D package.

BEGIN
  DBMS_LOGMNR_D.build (
    dictionary_filename => 'TSH1dict.ora',
    dictionary_location => 'C:\Oracle\Oradata\TSH1\Archive');
END;
/

Adding Logs

A list of logs to be analyzed must be added to logminer using the DBMS_LOGMNR package. The first log in the list is added using the NEW procedure, while subsequent logs are added using the ADD_LOGFILE procedure.

BEGIN
  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.new,
    logfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\T001S00006.ARC');

  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.addfile,
    logfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\T001S00007.ARC');
END;
/

Starting LogMiner

At this point LogMiner can be started using the overloaded START_LOGMNR procedure. The analysis range can be narrowed using time or SCN.

BEGIN
  -- Start using all logs
  DBMS_LOGMNR.start_logmnr (
    dictfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1dict.ora');

  -- Specifiy time range
  DBMS_LOGMNR.start_logmnr (
    dictfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\dict.ora',
    starttime    => TO_DATE('01-JAN-2001 00:00:00', 'DD-MON-YYYY HH:MI:SS'),
    endtime      => TO_DATE('01-JAN-2001 10:00:00', 'DD-MON-YYYY HH:MI:SS'));

  -- Specifiy SCN range
  DBMS_LOGMNR.start_logmnr (
    dictfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\dict.ora',
    startscn     => 100,
    endscn       => 150);
END;
/

Querying Log Information

Once LogMiner is started, the contents of the logfiles can be queried using the following views:

V$LOGMNR_DICTIONARY - The dictionary file in use.
V$LOGMNR_PARAMETERS - Current parameter settings for LogMiner.
V$LOGMNR_LOGS - Which redo log files are being analyzed.
V$LOGMNR_CONTENTS - The contents of the redo log files being analyzed.

The following query displays the SQL issued along with the undo SQL to reverse it.

SELECT scn, operation, sql_redo, sql_undo
FROM   v$logmnr_contents;

The following query displays the number of hits for each object during the analyzed period.

SELECT seg_owner, seg_name, count(*) AS Hits
FROM   v$logmnr_contents
WHERE  seg_name NOT LIKE '%$'
GROUP BY seg_owner, seg_name;

Stopping LogMiner

Once the analysis is complete, logminer should be stopped using the END_LOGMNR procedure.

BEGIN
  DBMS_LOGMNR.end_logmnr
END;
/

For further information see:

Hope this helps. Regards Tim...

Back to the Top.