Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux


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.

Related articles.

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.


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

    dictionary_filename => 'TSH1dict.ora',
    dictionary_location => 'C:\Oracle\Oradata\TSH1\Archive');

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.

  DBMS_LOGMNR.add_logfile (
    options     =>,
    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');

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.

  -- 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);

Querying Log Information

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

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.


For more information see:

Hope this helps. Regards Tim...

Back to the Top.