My Logminer Document

Oracle LogMiner


 Oracle Log miner , which is a part of oracle database , enables you to quey online and archived log files
 It can be used from the command line interface
 Also you can use Oracle Log miner viewer from the oracle enterprise manager
 For example, If you insert some wrong data in the tables or you deleted some of the important system tables, you can use logminer utility to recover the database to the previous state
 For that you need to query V$LOGMNR_CONTENTS table.




 Log miner can be used to track all the DDL and DML statements that are executed on the database


 Log miner configuration:

 There are four basic objects in the logminer configuration

1) Source database
2) Mining database
3) Log miner dictionary
4) Redolog files containing the data of interest


 Source Database: Source database is the database that produce all the redolog files containing the data of interest
 Mining Database: Mining database is the database that logminer uses when it analyzing the redologfiles
 LogMiner uses the dictionary to translate internal object identifiers and datatypes to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs and presents data as binary data.
For example, consider the following the SQL statement:
INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES('IT_WT','Technical Writer', 4000, 11000);

Without the dictionary, LogMiner will display:
insert into "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values
(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),
HEXTORAW('c229'),HEXTORAW('c3020b'));

 The redo log files contain the changes made to the database or database dictionary.

 You must enable supplemental logging prior to generating redo log files that will be analysed by log miner


To enable supplental logging

SQL > alter database add supplental log data;

Database altered

To check ,whether supplemental data logging is enabled or not

View the supplemental_log_min coloumn in the v$database view

If the value is Yes or implicit, supplemtal logging is enabled.


 Set utl_file_dir in the pfile or spfile , so that database can create files in the specified area , Here I mentoned like

Utl_file_dir=/usr/tmp, /d01/oracle


Then execute the dbms_logmnr_d.build(‘dictioanry.ora’,’/d01/oracle’);

Check that that file dictionary.ora is created in the specified directory you specified in the procedure .

Then create some archived redo logs by using

Sql> alter system archive log current;

Repeat this step for 4 -5 time to get 5 archived redo log files


Then register your archived log files by using

Execute
dbms_logmnr.add_logfile(‘/d01/oracle/oracle/archivedlog.arc’,dbms_logmnr.addfile);

Like this add all the archived redologfiles
 alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;


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

note down the values

and then execute dbms_logmnr.start_logmnr(dictfilename=>’dict location’,starttime=>to_Date(‘starttime ‘,’yyyy-mm-dd hh24:mi:ss’),’endtime=>to_date(endtime,’yyyy-mm-dd hh24:mi:ss))’;