Analyze Redo Log With Oracle LogMiner

Imagine an application user gave accidental salary hike of 50% instead of 5% to all employees! To initiate a database point-in-time recovery, we must know the exact time the query was executed against the database. Hence Oracle LogMiner!

Oracle LogMiner is used to read the contents of Redo / Archive log files. In this article we will understand Oracle LogMiner configuration and query the contents of Redo / Archive log files.

• Enable Supplemental Logging

• Add Log Files

• Start LogMiner

• Query V$LOGMNR_CONTENTS

• End LogMiner

• Filtering LogMiner Contents

You can run LogMiner to read the contents of Redo / Archive log files on the same database or on a completely separate database!

Enable Supplemental Logging

The supplemental logging records additional information regarding each transaction into redo log files. You must enable supplemental logging before generating the redo log files that will be analysed by Oracle LogMiner

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

Add Log Files

Oracle LogMiner can mine both Redo / Archive log files. Let us assume we would like to analyse all the redo logs inside the database

Notice the DBMS_LOGMNR.NEW parameter specifies the first log file to be analysed. The subsequent log files are defined with DBMS_LOGMNR.ADDFILE option

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => '/u01/db_files/testdb/redo01.log', -
   OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => '/u01/db_files/testdb/redo01.log', -
   OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => '/u01/db_files/testdb/redo01.log', -
   OPTIONS => DBMS_LOGMNR.ADDFILE);

Alternatively, you can directly give the name of the archive log file

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => '/u01/FRA/TESTDB/archivelog/o1_mf_1_3027_k6dcc33y_.arc', -
   OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => '/u01/FRA/TESTDB/archivelog/o1_mf_1_3028_k6dcc33y_.arc', -
   OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => '/u01/FRA/TESTDB/archivelog/o1_mf_1_3029_k6dcc33y_.arc', -
   OPTIONS => DBMS_LOGMNR.ADDFILE);

Start LogMiner

If you are starting LogMiner on the same source database, then simply issue

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

Now you are ready to query V$LOGMNR_CONTENTS view that allows you to see the contents of Redo / Archive log files.

Query V$LOGMNR_CONTENTS

Let us check redo log files for any queries run against EMPLOYEES table

SELECT username, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
WHERE seg_owner = 'HR' and seg_name like 'EMPLOYEES';

Every time you query V$LOGMNR_CONTENTS view, Oracle LogMiner must be running to present the data. The data is not stored anywhere inside the database when you query V$LOGMNR_CONTENTS.

End LogMiner

Oracle LogMiner takes system resources and it does not release those resources until you stop it

EXECUTE DBMS_LOGMNR.END_LOGMNR

Filtering LogMiner Contents

When you add log files and start LogMiner, you can view all the contents of the log files. If the log files are huge, then it's a good idea to use some filters to find out specific transactions.

Filter with SCN Number

You can filter the log file contents between particular SCN numbers (if you know 😜)

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
    STARTSCN => 280389, -
    ENDSCN   => 351390, -
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Filter with Date & Time

You can filter the log file contents between particular date & time

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
    STARTTIME => '23-Nov-2001 11:23:00', -
    ENDTIME => '23-Nov-2001 11:43:00'-
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Showing Only Committed Transactions

By default, LogMiner will show both committed and non-committed transactions from the log files. Just in case you would like to filter and see only committed transactions

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