Logminer Utility in Oracle

                  It’s a pl/sql based utility Used to mine data Queried by Users. This is most Important Ultitliy for DBA's

                  Follow the Below steps to Mine the data :

LogMiner is an Oracle utility. Using LogMiner one can query the contents of online redo log files and archived log files. 

·         It can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

·        

          The LogMiner tool can help the DBA to the find changed records in redo log files by using a set of PL/SQL procedures and functions.

·        

          Log Miner extracts all DDL and DML activity from the redo log files for viewing by a DBA via the dynamic performance view V$LOGMNR_CONTENTS

  Steps:

          

SQL> Alter database add supplemental log data;

1.      create a directory to store dictionary file.

$mkdir         /u01/user/demo/

2.      Specify the location of dictionary file at os level.

Sql>alter system set utl_file_dir=’/u01/user/demo’ scope=spfile;

3.      Bounce back the database.

Sql>startup force;

4.      create a dictionary file

       sql>exec dbms_logmnr_d.build(‘dfile’,’/u01/user/demo’ );

If You will get Below Error

ERROR at line 1:
ORA-01371: Complete LogMiner dictionary not found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

Solution 

Cross 'UTL_FILE_DIR' parameter , and Directory is present or not Physically on your System.

Also check,   ’/u01/user/demo’ Exist or Not

SQL>select member from v$logfile ; 

5.      connect to a sys user and specify the all logfiles to Logminer session.

Sql>exec dbms_logmnr.add_logfile(‘/u01/user/prod/redo/redo01.log’);

Sql>exec dbms_logmnr.add_logfile(‘/u01/user/prod/redo/redo02.log’);

Sql>exec dbms_logmnr.add_logfile(‘/u01/user/prod/redo/redo03.log’);        

6.      Start the minning process

Sql>exec           dbms_logmnr.start_logmnr(dictfilename=>’/u01/user/demo/dfile’);

                 

 Sql>spool abc.sql

Sql>select sql_undo,sql_redo from v$logmnr_contents  where seg_owner=’USER1’ and seg_name=’EMP’;

Sql>spool off

      

 

Log miner related data dictionary views

V$LOGMNR_CONTENTS - Shows changes made to user and table information.

V$LOGMNR_DICTIONARY - Shows information about the Log Miner

dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option.

V$LOGMNR_LOGS - Shows information about specified redo logs. There is one row for each redo log.

V$LOGMNR_PARAMETERS - Shows information about optional Log Miner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.