logminer

logminor is an oracle utility. using logminor one can query the contents of online redolog files and archived log files . it can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.
steps for configuring logminer:
step 1:
To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled
SQL> SELECT LOG_MODE FROM V$DATABASE;
OR
SQL> ARCHIVE LOG LIST;
FOR LOGMINER archivelog must be in archive log mode,
for enabling the archive logmode and automatic archiving using following steps:
http://rajeshkumar-oracledba.blogspot.com/2009/04/archivelog-mode-enable-and-disable.html
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archivedestination D:\oracle\ora92\RDBMS
Oldest online log sequence 46
Next log sequence to archive 48
Current log sequence 48
Database Supplemental Logging:
Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the REDO operations associated with DML changes. It ensures that LogMiner (and any products building on LogMiner technology) have sufficient information to support chained rows and various storage arrangements such as cluster tables. In most situations, you should at least enable minimal supplemental logging.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP SUP
--- --- ---
NO NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
To enable identification key logging, execute the following statement:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP SUP
--- --- ---
YES YES YES
note: more information about supplemental log
http://web.deu.edu.tr/oracle/B10501_01/server.920/a96521/logminer.htm#25840
step:2
add the parameter utl_file_dir in pfile or spfile for creating directory for logminer dictionary.
SQL> alter system set utl_file_dir='d:\oracle\oradata\jay' scope=spfile;
System altered.
you can create logminer dictionary in any destination as your wish.
important:after this , shutdown and startup the database.
SQL> show parameter utl_file_dir;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string d:\oracle\oradata\jay
step:3
creating logminer dictionary.
SQL> exec dbms_logmnr_d.build('dictionary.ora','d:\oracle\oradata\jay');
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
2 order by sequence# desc;
THREAD# SEQUENCE# COMPLETION_TIME
------------ -------------- -------------------------
1 47 2009-04-20 17:01:07
1 46 2009-04-20 16:20:51
1 45 2009-04-20 16:03:50
1 44 2009-04-20 15:21:27
.......
.......
47 rows selected.
step:4
SQL> exec dbms_logmnr.add_logfile('D:\oracle\ora92\rdbms\ARC00047.001',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
step:5
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
------ ----------------------------------------
46 D:\oracle\ora92\rdbms\ARC00046.001
FIND THE SCN'S:
Using the below view's find the first scn and high scn to mine from the registered logs.
From the above out gather the details and add it to the below logminer session :
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
------------------- ------------------- ---------- ----------
2009-04-20 16:03:48 2009-04-20 16:20:42 522290 543854
SQL> exec dbms_logmnr.start_logmnr(dictfilename => 'd:\oracle\oradata\jay\dictionary.ora', starttime => to_date('2009-04-20 16:03:48','yyyy-mm-dd hh24:mi:ss'),endtime => to_date('2009-04-20 16:20:42','yyyy-mm-dd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> create table logmnr_table as select * from v$logmnr_contents;
Table created.
Example: Using LogMiner to Calculate Table Access Statistics
SQL> SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'SCOTT' AND SEG_NAME = 'EMPLOYEES';
SQL> SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME NOT LIKE '%$'
GROUP BY SEG_OWNER, SEG_NAME;
for more information and reference:
http://web.deu.edu.tr/oracle/B10501_01/server.920/a96521/logminer.htm