How to use Logminer to analyze the archive log

Here is a simple example of using logminer to analyze redo or archive logs.




Step 1. Connect with oracle using sysdba privilage.
$ sqlplus sys as sysdba


Step 2. Add the log files in DBMS_LOGMNR.
BEGIN
  DBMS_LOGMNR.add_logfile (
    options     => apps.DBMS_LOGMNR.new,
    logfilename => '/D01/DUMMY/archive/DUMMY16399813073562.arc');
 
     DBMS_LOGMNR.add_logfile (
    options     => apps.DBMS_LOGMNR.new,
    logfilename => '/D01/DUMMY/archive/DUMMY16400813073562.arc');

     DBMS_LOGMNR.add_logfile (
    options     => apps.DBMS_LOGMNR.new,
    logfilename => '/D01/DUMMY/archive/DUMMY16401813073562.arc');
END;
/


Step 3. Start the log miner utility.
BEGIN
   DBMS_LOGMNR.START_LOGMNR
   (options =>
dbms_logmnr.dict_from_online_catalog);
END;
/



Step 4. You can create some table for analysis.
SQL> create table apps.log_miner as select * from  v$logmnr_contents;



Step 5. The following query will list the objects as per the number of hits.
SQL> SELECT seg_owner, seg_name, count(*) AS Hits
FROM   apps.log_miner
WHERE  seg_name NOT LIKE '%$'
GROUP BY seg_owner, seg_name
order by Hits desc;



Step 6. End the log miner process.
BEGIN
  DBMS_LOGMNR.end_logmnr;
END;
/