LogMiner utility in Oracle 10g

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 thedynamic performance view V$LOGMNR_CONTENTS. Internally Oracle uses the Log Miner technology for several other features,such as Flashback Transaction Backout,Streams, and Logical Standby Databases .Most often  LogMiner is used for recovery purposes when the data consists of just a few tables or a single code  change .

LogMiner Configurations : There  are  three  basic  objects  in  a  LogMiner  configuration .The  following are  as  below 

1.) Source Database :  The source  database  is  the  database  that  produces  all  the  redo  log  files  that we  want  LogMiner to  analyze.

2.) LogMiner Dictionary : LogMiner requires a dictionary to translate object  IDs into object names when it returns redo data to us . For  example,  consider the  following  the  SQL  statement 
SQL> insert into hr.jobs(job_id,job_tilte,min_salary,max_salary) values ('TT_WT','Technical Writer', 4000, 11000) ;

Without the dictionary, LogMiner will display:

SQL> insert   into   "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4")  values 

(HEXTORAW('45465f4748'), HEXTORAW('546563686e6963616c20577269746572'), 

HEXTORAW('c229'), HEXTORAW('c3020b')) ;

LogMiner provides three options for supplying the dictionary .The options are 

i.) Using the Online Catalog : Oracle  recommends  that  we  use  this  option  when  we  will  have  access to  the  source  database  from  which  the  redo  log  files  were  created  and  when  no  changes  to  the column  definitions in  the  tables  of  interest  are  anticipated. This  is  the  most  efficient  and  easy-to-use option. To direct LogMiner to use the dictionary currently in use for the database,  specifying  the  online catalog as our dictionary source when we start LogMiner, as follows:

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

ii.) Extracting a LogMiner Dictionary to the Redo Log Files : Oracle recommends that we use this option when we do not expect to have access to the source database from which the redo log files were created, or if we anticipate that changes will be made to the column definitions in the tables of interest.To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled. While the dictionary is being extracted to the redo log stream, no DDL statements can be executed. Therefore, the dictionary extracted to the redo log files is guaranteed to be consistent  To extract dictionary information to the redo log files, use the DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_REDO_LOGS option. Do not specify a filename or location.

SQL>execute dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs) ; 

iii.) Extracting the LogMiner Dictionary to a Flat File : This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that we use either the online catalog or extract the dictionary from redo log files instead.Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file. For example, enter the following to create the file dictionary.ora in /oracle/database:

SQL> execute dbms_logmnr_d.build('dictionary.ora', 'c:\dictionary', dbms_logmnr_d.store_in_flat_file);

3.) Redo log files : The redo log files contain the changes made to the database or database dictionary.

  

Here in this case i will demonstrate the "Extracting  the logminer Dictionary to a flat file" . In this case it requires a little bit of setup.

Step 1  :  Make a folder say  "dict"

C:\>mkdir c:\dict

Step 2 : Add the parameter utl_file_dir in initialiation file : This specifies the location where dictionary will be created.
SQL> alter system set utl_file_dir='C:\dict\'  scope=spfile ; 

SQL> shut immediate 

SQL> startup 
SQL> @ORACLE_HOME\RDBMS\ADMIN\utlfile.sql 

Normally oracle records the change vector in the redo log files i.e. just the information that is required to reconstruct the operation at recovery time. If we want additional information in the redo log then we need to enable supplemental logging prior to generating log files that will be analyzed by LogMiner. Therefore, at the very least, we will enable minimal supplemental logging, as the following SQL statement shows:

Step 3 :  Enable the supplemental  logging

SQL> select supplemental_log_data_min from v$database ;

SUPPLEME

--------

NO

Here minimum supplemental logging is not enabled, so enabling the supplement logging 

SQL> alter database  add supplemental log data ; 

Database altered.

Step 4 : Create the dictionary file : 

The dbms_logmnr_d.build procedure  requires  access  to  a directory  where  it  can place the dictionary  file. Because  PL/SQL  procedures  do not normally access user directories, we must specify a directory for use by the dbms_logmnr_d.build procedure or the procedure will fail. To specify a directory, set the initialization parameter,  utl_file_dir, in the initialization parameter file. The PL/SQL procedure  dbms_logmnr_d.build .Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file.

SQL> begin 

       dbms_logmnr_d.build 

       ( 

         dictionary_filename => 'dictionary.dic', 

         dictionary_location => 'C:\dict', 

         options             => dbms_logmnr_d.store_in_flat_file 

       );

     end;

     /

PL/SQL procedure successfully completed.

This has recorded the dictionary information into the file   "C:\dict\dictionary.dic.

Step 5 : Add list of redolog file to analyzed :

To mine data in the redo log files, LogMiner needs information about which redo log files to mine. Use the dbms_logmnr.add_logfile procedure to create a list of redo log files before we start LogMiner. After the first redo log file has been added to the list, each subsequently added redo log file must be from the same database and associated with the same database resetlogs scn . When using this method, LogMiner need not be connected to the source database.For example, 

SQL> execute dbms_logmnr.add_logfile ( logfilename=> 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\NOIDA\REDO03.LOG' ,  options=>dbms_logmnr.new) ;

To determine which redo log files are being analyzed in the current LogMiner session, we can query the v$logmnr_logs view, which contains one row for each redo log file. 

Step  6 : Start LogMiner :
dbms_logmnr.start_logmnr starts a LogMiner session. It will populate the dictionary view v$logmnr_contents .  v$logminer_contents is only accessible to the current session which has started LogMiner and only until the dbms_logmnr.end_logmnr is called. 


SQL> begin
       dbms_logmnr.start_logmnr 
       (    
        dictfilename => 'C:\dict\dictionary.dic',
        options      => dbms_logmnr.print_pretty_sql + 
                   dbms_logmnr.no_sql_delimiter + 
                dbms_logmnr.ddl_dict_tracking 
       );
     end;
     /
There could be many options provided with start_logmnr which affects the data representation in v$logmnr_contents e.g.

I.)   dbms_logmnr.print_pretty_sql will format the sql statements to enhance readability.

II.)  dbms_logmnr.no_sql_delimiter will omit the ";" from the end of the sql statements which is useful when sql are meant to be re-executed in PL/SQL routines.dbms_logmnr.

III.) ddl_dict_tracking tracks the DDL statements in the log files.

Step 7 : Copy contents of v$logmnr_contents to a user table : 

When the LogMiner session ends then v$logmnr_contents is no more accessible .  Its always better to copy contents of v$logmnr_contents to a user table and then perform  the analysis as it is quite expensive to query v$logmnr_contents . Moreover, the user table  can be indexed for better query performance .Let's the table name be "myLogAnalysis"

SQL> drop table  myLogAnalysis ;
Table dropped.

SQL> create table myLogAnalysis  as select * from v$logmnr_contents ;
Table created.

Step 8 :  Ends the LogMiner session  

dbms_logmnr.end_logmnr() ends the LogMiner session and v$logmnr_contents is no more accessible but our user table myLogAnalysis is still available which is a copy of v$logmnr_contents.
SQL> begin

       DBMS_LOGMNR.END_LOGMNR();

     end;

     /

PL/SQL procedure successfully completed.

Step 8 : Query the  v$logmnr_contents

The output below shows the system change number for the change, the segment on which the change was made, the sql statement to redo the change and the sql statement to undo the change.

SQL>select scn , seg_name , sql_redo , sql_undo from   myLogAnalysis  where username = 'SCOTT' AND (seg_owner is null OR seg_owner = 'SCOTT') ;

   SCN       SEG_NAME      SQL_REDO                          SQL_UNDO

--------    -----------        --------------------------------- ---------------------------------

639968      TEST_LOGMNR create table scott.test_logmnr

                   (id  number,

                    name varchar2(10)

                    )

640039             set transaction read write

640039 TEST_LOGMNR insert into "SCOTT"."TEST_LOGMNR" delete from "SCOTT"."TEST_LOGMNR"

                    values                            where

                       "ID" = 1,                         "ID" = 1 and

                       "NAME" = 'TEST1'                  "NAME" = 'TEST1' and

                                                         ROWID = 'AAAM7vAAEAAAALcAAA'

640041 TEST_LOGMNR insert into "SCOTT"."TEST_LOGMNR" delete from "SCOTT"."TEST_LOGMNR"

                    values                            where

                       "ID" = 2,                         "ID" = 2 and

                       "NAME" = 'TEST2'                  "NAME" = 'TEST2' and

                                                         ROWID = 'AAAM7vAAEAAAALcAAB'

640044             commit

640047             set transaction read write

640047 TEST_LOGMNR update "SCOTT"."TEST_LOGMNR"      update "SCOTT"."TEST_LOGMNR"

                     set                               set

                       "NAME" = 'TEST'                   "NAME" = 'TEST1'

                     where                             where

                       "NAME" = 'TEST1' and              "NAME" = 'TEST' and

                       ROWID = 'AAAM7vAAEAAAALcAAA'      ROWID = 'AAAM7vAAEAAAALcAAA'

640047 TEST_LOGMNR update "SCOTT"."TEST_LOGMNR"      update "SCOTT"."TEST_LOGMNR"

                     set                               set

                       "NAME" = 'TEST'                   "NAME" = 'TEST2'

                     where                             where

                       "NAME" = 'TEST2' and              "NAME" = 'TEST' and

                       ROWID = 'AAAM7vAAEAAAALcAAB'      ROWID = 'AAAM7vAAEAAAALcAAB'

640050             commit

640052             set transaction read write

640058 TEST_LOGMNR delete from "SCOTT"."TEST_LOGMNR" insert into "SCOTT"."TEST_LOGMNR"

                    where                             values

                       "ID" = 1 and                      "ID" = 1,

                       "NAME" = 'TEST' and               "NAME" = 'TEST'

                       ROWID = 'AAAM7vAAEAAAALcAAA'

640058 TEST_LOGMNR delete from "SCOTT"."TEST_LOGMNR" insert into "SCOTT"."TEST_LOGMNR"

                    where                             values

                       "ID" = 2 and                      "ID" = 2,

                       "NAME" = 'TEST' and               "NAME" = 'TEST'

                       ROWID = 'AAAM7vAAEAAAALcAAB'

640066             commit

Now we have more option to filter the data according to our requirement .Here are few examples:

Filtering Data By Time  : 

To filter data by time, set the STARTTIME and ENDTIME parameters. The procedure expects date values. Use the TO_DATE function to specify date and time, as in this example:

SQL> execute DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'c:\dict\dictionary.ora', STARTTIME => TO_DATE('20-Jul-2011 04:30:00', 'DD-MON-YYYY HH:MI:SS'),ENDTIME => TO_DATE('20-Jul-2011  04:45:00', 'DD-MON-YYYY HH:MI:SS')); 

If no STARTTIME or ENDTIME parameters are specified, the entire redo log is read from start to end, for each  SELECT statement issued.

The timestamps should not be used to infer ordering of redo records. we can infer the order of redo records by using the SCN.

Filtering Data By SCN : 

To filter data by SCN (system change number), use the STARTSCN and ENDSCN parameters, as in this example:

SQL> execute  DBMS_LOGMNR.START_LOGMNR (dictfilename=>'c:\dict\dictionary.ora',  STARTSCN => 100, ENDSCN => 150);

The STARTSCN and ENDSCN parameters override the STARTTIME and ENDTIME parameters in situations where all are specified.

If no STARTSCN or ENDSCN parameters are specified, the entire redo log is read from start to end, for each SELECT statement issued.

Accessing LogMiner Information : 

LogMiner information is contained in the following views. We can use SQL to query them as we would any other view.

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

V$LOGMNR_DICTIONARY : Shows information about the LogMiner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option. The information shown includes the database name and status information.

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

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

For more references

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm

http://oracleflash.com/28/Oracle-11g-Using-LogMiner-to-analyze-redo-log-files.html

Enjoy   :-)