How to use Oracle Logminer to analysis Logfile:

 

What is LogMiner?

LogMiner is an Oracle utility. Using LogMiner one can query the contents of online redo logfiles and archived log files. It can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

You can check your supplemental logging on or off by following commands:

If supplementallogging is off then on using following commands:

 

 

Now add Supplemental log Date

 

Set  the initialization parameter  UTL_FILE_DIR

In the initialization parameter file

For ex:- set  UTL_FILE_DIR  to /u004/logminer/ as  the directory where the dictionary file is placed.

 

 

Install the DBMS_LOGMNR package.

The next step is to install DBMS_LOGMNR package. If you have already installed then ignore this steps. You can install this package by running $ORACLE_HOME/rdbms/admin/dbmslm.sql script. If you create your database with dbca then this script run automatically. So you can ignore this step. However if you create database manually with CREATE DATABASE ... command then you must run the script before using logminer. That is ,

 

SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sql

 

 

This procedure creates the dictionary file. For example, enter the following to create the file

dictionary.ora  in /u004/logminer

 

Build LogMiner Dictionary

 

 

Add log that contains DML

Begin LogMiner session

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

select seg_name, sql_redo,TIMESTAMP from v$logmnr_contents

O/P:-

End the LogMiner session.