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/techfaq360/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/techfaq360 as the directory where the dictionary file is placed, enter the following in the initialization parameter file:

UTL_FILE_DIR = /u02/techfaq360

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/techfaq360:

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u02/techfaq360/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/techfaq360/archive/1_127_658758571.dbf

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

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

130 /u02/techfaq360/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/techfaq360/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.