Read the Content of Redo Log using Logminer - Oracle Database

How to use Logminer in Oracle Database?

What is logminer in oracle database? How does logminer works? How to read the content of redo or archive logs in oracle database? Usage and Benefits of Logminer in Oracle Database.

Logminer in Oracle Database

There are times when you need to check what's going on inside your oracle database. Though you can achieve the same using Auditing feature in oracle database but in case you haven't configured the same and still want to know what happened last night OR what background process ran the previvous day causing huge archive generation and you don't know what really happened. In such scenarios, Oracle logminer is the utility you can use to find all the answers to your questions.

     Normally it is impossible to directly read a redo log or archive log file because they don't support to be opened with notepad, wordpad or any other document viewing utility since the format is different and Oracle doesn't allows you to do the same to maintain the security but if you still want to read the data inside your logfiles, go for the steps mentioned below.

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 ('<Username>');

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();