Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

Introduction to LogMiner - (Oracle 8i)

by Alex Gaethofs, Oracle Specialist, Telco Consultants N.V

Introduction

Problem	Action
Megabytes of log files are being generated on my database server.	I propose to use Oracle8I's new log-analysis tool, LogMiner.

What is Oracle writing into it's redo log files ?

To find out what Oracle is writing into the redo log files, use Oracle8i's new log-analysis tool, LogMiner.

Log files store all the data needed to perform a database recovery and record every changes made to the database structure. With "every change" we mean every INSERT, UPDATE and DELETE !!!

Until Oracle8I there was no built-in tool for helping administrators read and interpret the redo-log files. Sending a dump-file of the logfiles to Oracle Support was the only solution. Oracle8i provides a powerful new tool, LogMiner.

LogMiner is actually a set of PL/SQL packages and dynamic (V$) views. The packages are installed with the installation of the database. The views however will be created on the moment you startup LogMiner by using the DBMS_LOGMNR.START_LOGMNR procedure.

LogMiner can be used against Online or Offline log files from either the 'current' database or a 'foreign' database.

The most important package is DBMS_LOGMNR.START_LOGMNR. This one will create the necessary views you can query later on. To be able to read the views you will have to setup a meta-data file before starting LogMiner.

The procedure DBMS_LOGMNR_D.BUILD uses the database package UTL_FILE. Therefore you need to add a parameter UTL_FILE_DIR in the init<SID>.ora file pointing to a directory on the server.

Eg. UTL_FILE_DIR = (C:\Oracle\UtlFileDir)

Restart your database and create the meta-data file.

SQL> CONNECT SYS/ 
SQL> EXECUTE dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\Oracle\UtilFileDir');

Scenario 1 : Check the online redo log files

A.	Create List :
----------------------

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,LogFileName=>'F:\Oracle\Oradata\plnts\Redo01.log');

B.   Add other logfiles to the list :	
--------------------------------------------
SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'F:\Oracle\Oradata\plnts\Redo02.log');

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'F:\Oracle\Oradata\plnts\Redo03.log');

Scenario 2 : Check the offline log files

A.	Create List :
----------------------

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,
 
LogFileName=>'G:\Oracle\Oraarch\plnts\ARCARC07108.001');

B.   Add other logfiles to the list :	
--------------------------------------------
SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,
 
LogFileName=>' 'G:\Oracle\Oraarch\plnts\ARCARC07109.001');

SQL> EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,
 
LogFileName=>' 'G:\Oracle\Oraarch\plnts\ARCARC07110.001');

Continue of step 2...

About the list. You decide yourself which logfile, and how many logfiles you will examine. I suggest to create a list of just one logfile and to check the contents of this one before adding another one.

You will find out LogMiner is not that easy. LogMiner is production but is missing a GUI-interface.

Note: You can remove a log file from the list by using 'dbms_logmnr.removefile' instead of using 'dbms_logmnr.addfile'.

Scenario 1 : no limitations

SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'C:\Oracle\UtilFileDir\Dictionary.ora');

Scenario 2 : limit the search to a specific time range during which someone made his changes to the database

SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'C:\Oracle\UtilFileDir\Dictionary.ora'
                      , StartTime=>TO_DATE('15-Jan-2000 08:00:00','DD-MON-RRRR HH:MI:SS')
                      , EndTime=>TO_DATE('15-Jan-2000 08:30:00','DD-MON-RRRR HH:MI:SS')
                     );

Parameters of "dbms_logmnr.start_logmnr" :

  StartScn      Default 0,
  EndScn        Default 0,
  StartTime     Default '01-Jan-1988',
  EndTime       Default '01-Jan-2988',
  DictFileName  Default '',
  Options       Default 0   (= a debug flag, not yet being used)

Step 4 : Examine the view "v$logmnr_contents"

FINALLY THE RESULTS OF ALL YOUR WORK !!!

Examine the view 'v$logmnr_contents' for more information

SELECT sql_redo FROM v$logmnr_contents;

If you want to know more information about the modification someone was doing on a specific table then use the following query :

SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='SCOTT' AND tablename='USERS';

It might be useful to describe the view 'v$logmnr_contents'.

SQL> DESC v$logmnr_contents

Remarks

The output from LogMiner is the contents of the view "v$logmnr_contents'. The output is only visible during the life of the session which runs the procedure 'dbms_logmrn.start_logmnr'. This is because all the LogMiner memory is in PGA memory, so it is neither visible to other sessions, not is it persistent.

So the moment you logoff or you invoke the procedure 'dbms_logmnr.end_logmnr' the PGA will be cleared and the information is no longer available.

Dbms_logmnr and dbms_logmnr_d call kernel C modules directly. There is very little of the functionality visible to the user in the dbmslogmnr.sql.

There is no GUI-interface.

Contact Information

Alex Gaethofs, Oracle Specialist
Telco Consultants N.V.
Leuvensesteenweg 509
1930 Zaventem

Tel.: +32-2-712.04.70
Fax.: +32-2-725.48.96
Email : info@telco.be
URL. : www.telco.be

My email : alex.gaethofs@pandora.be