Logminer

Oracle logminer is a tool which enables you inspect the contents of the Oracle Redo logs. In particular it shows every DML statement (insert, update, delete, etc.) issued against the database, together with the necessary SQL statements for rolling them back. It can be very useful for debugging, auditing, or reversing specified transactions.

Oracle logminer is free and comes as part of the Oracle distribution. (Possibly Enterprise Edition only, I'm not sure).

This document gives a simple guide on how to install and use Oracle logminer.

Installing logminer

To install the logminer packages you need the run the scripts:
$ORACLE_HOME/rdbms/admin/dbmslm.sql and $ORACLE_HOME/rdbms/admin/dbmslmd.sql.
The first installs the DBMS_LOGMNR package and needs to be run on the database which you will use to analyse the logfiles.
The second installs the DBMS_LOGMNR_D package which builds the data-dictionary file, and needs to be run on the database from which the redo logs are generated.

Both packages need to run as SYS.

Installing the packages does not grant execution rights to other users (including SYSTEM) so, unless you do so, using logminer is restricted to the SYS user.


Using logminer
To use Oracle logminer, the following procedure should be followed:

Build a data-dictionary for logminer
This allows logminer to refer to columns and tables by their normal names, rather than by internal representations. Logminer can be used without a data-dictionary, but is then of limited use. The data-dictionary is a text file which is built using the DBMS_LOGMNR_D package. It only needs rebuilding if there have been any data-dictionary changes to the tables which are to be monitored. It is possible, and common, to analyse logs generated from one database on another. In this case, the data-dictionary file must, of course, be generated on the database from which the logs were generated.

The dictionary_location parameter must refer to a directory specified in the UTL_FILE_DIR init.ora parameter.

EXECUTE DBMS_LOGMNR_D.BUILD(
dictionary_filename => 'l_dictionary.ora',
dictionary_location => '/oracle/database');

This may take several minutes to run.


Declare the log_files to be monitored to the DBMS_LOGMNR package.
The log_files to be monitored must be placed in a directory specified in the UTL_FILE_DIR init.ora parameter.
eg.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
LogFileName => '/oracle/logs/log1.f',
Options => dbms_logmnr.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
LogFileName => '/oracle/logs/log2.f',
Options => dbms_logmnr.ADDFILE);

The "NEW" option (re)initialises the list of logfiles declared to logminer, the "ADDFILE" option adds to the current list.

Note: it is not wise to simply include the directory which contains your archived redo logs into the list of directories specified in the UTL_FILE_DIR parameter, as this would potentially allow any user with access to the UTL_FILE package to overwrite your redo logs.

Start logminer

EXECUTE DBMS_LOGMNR.START_LOGMNR(
DictFileName =>'/oracle/database/l_dictionary.ora');

Run queries against the logminer views

SELECT sql_redo
FROM V$LOGMNR_CONTENTS


Close logminer
EXECUTE DBMS_LOGMNR.END_LOGMNR();