Posted by: Monish | July 12, 2008

LogMiner

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.


Responses

  1. Excellent 🙂

  2. Dear u done very well coz i had few confusions about LOG Minor but after read ur article i learned lot of new things….keep it up

  3. Thanks for the post

  4. well done , but can these be automated, so that users can run it using a report tool i.e they just need to enter the parameters such as date,username and other.


Leave a Reply to Muhammad IQBAL Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories

%d bloggers like this: