Simple Steps to use LogMiner for finding high redo log generation

LogMiner is a tool that lets you use SQL statements to analyze events in the database log. With LogMiner, you can track transactions as they are processed or locate specific functions that result in data modifications. LogMiner was introduced with Oracle8i. LogMiner can be used, along with audit trails, to determine what has happened in your Oracle database.

In this post, we will see simple steps for mining the redo logs, for instance, to troubleshoot excessive redo generation.

1. Enable SUPPLEMENTAL Log to Database.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2. As sysdba, install the logminer package (if not installed by default installed) from following path

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

NOTE: You can simply check whether logminer is already available using:

SQL> desc dbms_logmnr

3. Create a list of logs by specifying the NEW option when executing the DBMS_LOGMNR.ADD_LOGFILE procedure.

NOTE: You can query the available archived redo log files from v$archived_log.

For example, enter the following:

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
     LOGFILENAME => '+FRA/v1120/archivelog/2012_11_09thread_1_seq_563.260.798899749', -
     OPTIONS => DBMS_LOGMNR.NEW);

4. If desired, add more logs by specifying the ADDFILE option.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
     LOGFILENAME => '+FRA/v1120/archivelog/2012_11_09/thread_1_seq_564.261.798899763', -
     OPTIONS => DBMS_LOGMNR.ADDFILE);

5. Start LogMiner and specify the dictionary to use.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
     OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

NOTE: Using DICT_FROM_ONLINE_CATALOG, the database must be open and only redo can be mined of the latest table versions.

6. Query the V$LOGMNR_CONTENTS view.

SQL> SELECT username AS USR, 
     (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, 
     operation, 
     SQL_REDO, 
     SQL_UNDO 
     FROM V$LOGMNR_CONTENTS 
     WHERE username IN ('');

NOTE: For other possible columns to query, please issue:

SQL> desc v$logmnr_contents

7. End the LogMiner session.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();