How to use Oracle LogMiner for tracing DDL

With the help of Oracle LogMiner we can trace the DDL fire. Please note that this is session dependent.

Below are the steps to be followed.

1)Set the parameter UTL_FILE_DIR (this is static parameter u need to take bounce of db to reflect it)

Sql>alter system set UTL_FILE_DIR=’D:\oracle\oradata\utl’ scope=spfile;

And take the bounce of database.

2)Add the logminer dictionary
Sql>
EXECUTE dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'D:\oracle\oradata\utl',OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

This will create one flat file 'Dictionary.ora' in the give UTL_FILE_DIR location.

3)Add the archive log file to Mine
Now u have to get the time between view u have created (approx ) and from ls –ltr u can see the archive log generated time.

Sql>
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,LogFileName=>'D:\oracle\oradata\archive\1_52_743911224.LOG');
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'D:\oracle\oradata\archive\1_51_743911224.LOG');
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'D:\ORACLE\ORADATA\ODSPROD\REDO01.LOG');

Now check whether this archived log register with logminer

Sql>select filename from V$LOGMNR_LOGS;

4) Start the logmining

Sql>
EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'D:\oracle\oradata\utl\Dictionary.ora',OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING);

5) Once its successfully executed now u can see the DDL statement.

Sql> SELECT USERNAME, SQL_REDOFROM V$LOGMNR_CONTENTS WHERE USERNAME = 'TEST' AND OPERATION = 'DDL';