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

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.

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.

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:

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 more information see:

Hope this helps. Regards Tim...

Back to the Top.