Analyze Redo Log With Oracle LogMiner
Use Oracle LogMiner to analyze redo logs and transactions.
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);