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