• Arun Kumar

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.

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

152 views

Recent Posts

See All

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta

The first step is to understand how instance size is configured. There are two main configurations: One Instance Size Separate SGA and PGA One Instance Size Oracle MEMORY_TARGET parameter defines the