Monish's DBA Blog

What is LogMiner?

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.

Log Miner Configuration:

The three basic objects in a LogMiner configuration:

• Source Database
• LogMiner Directory
• Redo log files

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.

SQL> archive log list

Database log mode                     Archive Mode

Automatic archival                    Enabled

Archive destination                   /u02/monish/archive

Oldest online log sequence            129

Next log sequence to archive          131

Current log sequence                  131

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..

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

Session altered.

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

THREAD#  SEQUENCE# COMPLETION_TIME

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

1        130 2008-07-10 09:35:20

1        129 2008-07-07 02:30:35

1        128 2008-07-06 15:30:23

1        127 2008-07-06 11:33:40

1        126 2008-07-06 11:33:12

1        125 2008-07-06 11:32:56

1        124 2008-07-06 11:32:41

1        123 2008-07-06 11:32:14

1        122 2008-07-03 16:31:17

1        121 2008-07-03 10:25:53

1        120 2008-07-01 13:30:41

1        119 2008-07-01 12:07:13

1        118 2008-07-01 12:05:39

1        117 2008-07-01 12:05:33

1        116 2008-07-01 12:05:25

1        115 2008-07-01 12:05:14

1        114 2008-07-01 12:05:01

1        113 2008-07-01 12:04:47

1        112 2008-07-01 12:04:33

1        111 2008-07-01 12:04:25

Set the initialization parameter, UTL_FILE_DIR, in the initialization parameter file. For example, to set UTL_FILE_DIR to use /u02/monish as the directory where the dictionary file is placed, enter the following in the initialization parameter file:

UTL_FILE_DIR = /u02/monish

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 /u02/monish:

SQL> execute dbms_logmnr_d.build('dictionary.ora','/u02/monish');

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u02/monish/archive/1_130_658758571.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u02/monish/archive/1_129_658758571.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u02/monish/archive/1_128_658758571.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u02/monish/archive/1_127_658758571.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

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

SQL>select log_id, filename from v$logmnr_logs;

LOG_ID FILENAME

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

127 /u02/monish/archive/1_127_658758571.dbf

128 /u02/monish/archive/1_128_658758571.dbf

129 /u02/monish/archive/1_129_658758571.dbf

130 /u02/monish/archive/1_130_658758571.dbf

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

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

Session altered.

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

LOW_TIME            HIGH_TIME              LOW_SCN   NEXT_SCN

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

2008-07-06 11:33:12 2008-07-06 11:33:39     620682     622400

2008-07-06 11:33:39 2008-07-06 15:30:22     622400     630098

2008-07-06 15:30:22 2008-07-07 02:30:34     630098     645556

2008-07-07 02:30:34 2008-07-10 09:35:19     645556     679141

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

SQL> execute dbms_logmnr.start_logmnr(dictfilename =>'/u02/monish/dictionary.ora',starttime => to_date('2008-07-06 11:33:39', 'yyyy-mm-dd hh24:mi:ss'), endtime => to_date('2008-07-10 09:35:19', 'yyyy-mm-dd hh24:mi:ss'));

PL/SQL procedure successfully completed.

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.

SQL> create table logmnr_table_1 as select * from v$logmnr_contents;

Table created.

Here I mentioned the exact steps what I performed in my test machine. I used RHEL 4 os and Oracle 10g R2 database. comments are appreciated.