Oracle 10g Logminer Example

Oracle 10g Logminer

To find out what Oracle is writing to the redo log files called LogMiner. The redo log files store all the data needed to perform a database recovery and record all changes (INSERT, UPDATE and DELETE) made to the database.

Below are the steps how to setup and mine a archive log :

1. Make sure you get the list of archives generated for the day using the below commnand. From the below output identify the archivelogs you are going to mine using logminer..

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select thread#, sequence#, completion_time from v$archived_log order by completion_time desc;

2. Now Build a Logminer dictionary , make sure you have the follwoing folder available and your unix account have acces to it :

execute dbms_logmnr_d.build('dictionary.ora','/d01/oracle');

3. Copy all the archivelog to a path and register all the logs as show below :

execute dbms_logmnr.add_logfile('/d02/archivelogs/backup/arch/8045.arc',dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile('/d02/archivelogs/backup/arch/8046.arc',dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile('/d02/archivelogs/backup/arch/8047.arc',dbms_logmnr.addfile);

4. Now from the below view , make sure you have all the registered logs available for mining.

select log_id, filename from v$logmnr_logs;

5.Using the below view's find the first scn and high scn to mine from the registered logs.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select low_time,high_time,low_scn,next_scn from v$logmnr_logs;

6.From the above out gather the details and add it to the below logminer session :

execute dbms_logmnr.start_logmnr(dictfilename =>'/d01/oracle/dictionary.ora',starttime => to_date('2008-04-21 10:00:14', 'yyyy-mm-dd hh24:mi:ss'), endtime => to_date('2008-04-21 12:11:07', 'yyyy-mm-dd hh24:mi:ss'));

7. As v$logmnr_contents is a temporary view, once you disconnect your session , you won't be able to see the content, so make sure you create a table of all the contents of the view.

Note : If you mine a bunch of logs , create table will take more time to get created.

create table logmnr_table_1 as select * from v$logmnr_contents;