LogMiner Utility, useful stuff ?

[Vinod Sadanandan]

LogMiner Utility, useful stuff ?

The ability to provide a readable interface to the redo logs has been asked
for for a long time. The ALTER SYTSTEM DUMP LOGFILE interface
has been around for a long time, though its usefulness outside Support is
limited. There have been a number of third party products, e.g. BMC's PATROL
DB-Logmaster (SQL*Trax as was), which provide some functionality in this
area. With Oracle release 8.1 there is a facility in the Oracle kernel to do the same. LogMiner allows the DBA to audit changes to data and performs
analysis on the redo to determine trends, aid in Point-in-time Recovery etc.

The LogMiner feature is made up of three procedures in the LogMiner
(dbms_logmnr) package, and one in the Dictionary (dbms_logmnr_d).

These are built by the following scripts: (Run by catproc)

$ORACLE_HOME/rdbms/admin/dbmslogmnrd.sql
$ORACLE_HOME/rdbms/admin/dbmslogmnr.sql
$ORACLE_HOME/rdbms/admin/prvtlogmnr.plb

since 8.1.6:

$ORACLE_HOME/rdbms/admin/dbmslmd.sql
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/prvtlm.plb


1. dbms_logmnr_d.build

This procedure builds the dictionary file used by the main LogMiner
package to resolve object names, and column datatypes. It should be
generated relatively frequently, since otherwise newer objects will not
be recorded.

PARAMETERS
==========

1. The name of the dictionary file you want to produce.
2. The name of the directory where you want the file produced.

The Directory must be writeable by the server i.e. included in
UTL_FILE_DIR path.

EXAMPLE
=======

 
      BEGIN
         dbms_logmnr_d.build(
           dictionary_filename=> 'miner_dictionary.dic',
           dictionary_location => '/export/home/sme81/aholland/testcases
           /logminer'
                             );
      END;
      /
 
 

The dbms_logmnr package actually performs the redo analysis.

2. dbms_logmnr.add_logfile

Parameters
===========

1. The logfile to be analyzed.
2. Option
DBMS_LOGMNR.NEW (SESSION) First file to be put into PGA memory.
This initialises the V$logmnr_logs table.
and
DBMS_LOGMNR.ADDFILE
adds another logfile to the v$logmnr_logs PGA memory.
Has the same effect as NEW if there are no rows there
presently.

DBMS_LOGMNR.REMOVEFILE
removes a row from v$logmnr_logs.

Example
=======

Include all my online logs for analysis.........

 
      BEGIN
         dbms_logmnr.add_logfile(
            '/export/home/sme81/aholland/database/files/redo03.log',
                               DBMS_LOGMNR.NEW );
         dbms_logmnr.add_logfile(
            '/export/home/sme81/aholland/database/files/redo02.log',
                               DBMS_LOGMNR.ADDFILE );
         dbms_logmnr.add_logfile(
            '/export/home/sme81/aholland/database/files/redo01.log',
                               DBMS_LOGMNR.ADDFILE );
      END;
      /
 

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
           2 DICTFILENAME =>'/oracle/database/dictionary.ora'); 
 

This package populates V$logmnr_dictionary, v$logmnr_parameters,
and v$logmnr_contents.
dbms_logmnr.end_logmnr;

Thanks & Regards,
Vinod Sadanandan
Oracle DBA