A simple example of configuring Logminer to analyze the archive log or redo files

Logminer is a oracle tool to analyze the archive log files or log files in Oracle. In this article I have shown a very simple example how to setup a logminer.


Step 1
First create a dictionary.
BEGIN
  DBMS_LOGMNR_D.build (
    dictionary_filename => 'TSH1dict.ora',
    dictionary_location => '/usr/tmp');
END;



Step 2
Next add the log files in logminer.
BEGIN
  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.new,
    logfilename => '/D01/data/archive/PROD14346813073562.arc');

  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.addfile,
    logfilename => '/D01/data/archive/PROD14347813073562.arc');
END;


Step 3
Start the logminer process.
BEGIN
  -- Start using all logs
  DBMS_LOGMNR.start_logmnr (
    dictfilename => '/usr/tmp/TSH1dict.ora');
END;
/

Step 4
Analyze the log miner contents.

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

You can also create table with smaller amount of data for future analysis.
create table apps.log_miner
as 
SELECT *
FROM   v$logmnr_contents
where rownum<10000 font="">




Step 5
After all the analysis stop the logminer process.
BEGIN
  DBMS_LOGMNR.end_logmnr;
END;