Oracle Logminer

LogMiner enables the analysis of the contents of archived redo logs. It can be used to provide a historical view of the database without the need for point-in-time recovery. It can also be used to undo operations, allowing repair of logical corruption

Here 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. 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.

For more information, please refer to
  Oracle® Database Utilities
  11g Release 2 (11.2)
  Chapter 19 Using LogMiner to Analyze Redo Log Files
  http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019

  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


SQL> set lines 200
col USERNAME for a20
col SQL_REDO for a40
col SESSION_INFO for a40
col TIMESTAMP for a25



Mention the Segment name on below select script, then we will get output like below format.

SQL> SELECT USERNAME,sql_redo,SESSION_INFO,TIMESTAMP FROM v$logmnr_contents WHERE  seg_name='OBS_ORDER_LOG';



USERNAME             SQL_REDO                                 SESSION_INFO            TIMESTAMP
-------------------- ---------------------------------------- ----------------------- --------------------
UNKNOWN              insert into "LGDMS_NEW"."OBS_ORDER_LOG"( UNKNOWN                 21-JUN-2014 20:57:27
                     "ORDER_ID","LOG_ID","LOG_DATE","ORDER_ST
                     ATUS","EXP_DELIVERY_DATE","CANCELLED_REA
                     SON","REJECTED_REASON","TRANSFERRED_REAS
                     ON","FROM_OUTLET_CODE","TO_OUTLET_CODE",
                     "FROM_ORDER_TYPE","TO_ORDER_TYPE","STATU
                     S_REMARKS","COMMENTS","CREATED_BY","CREA
                     TION_DATE","LAST_UPDATED_BY","LAST_UPDAT
                     ED_DATE","LAST_UPDATED_LOGIN","ATTRIBUTE
                     1","ATTRIBUTE2","ATTRIBUTE3","ATTRIBUTE4
                     ","ATTRIBUTE5","MODEL_NO","PRODUCT_CATEG


1 row selected.


If We want particular sql redo the mention like '%UPDATE%' or '%INSERT%' or '%DELETE%';

SQL> SELECT USERNAME,sql_redo,SESSION_INFO,TIMESTAMP FROM v$logmnr_contents WHERE  
seg_name='OBS_ORDER_LOG' and sql_redo like '%UPDATE%';

USERNAME             SQL_REDO                                 SESSION_INFO              TIMESTAMP
-------------------- ---------------------------------------- ------------------------- --------------------
UNKNOWN              insert into "LGDMS_NEW"."OBS_ORDER_LOG"( UNKNOWN                   21-JUN-2014 20:57:27
                     "ORDER_ID","LOG_ID","LOG_DATE","ORDER_ST
                     ATUS","EXP_DELIVERY_DATE","CANCELLED_REA
                     SON","REJECTED_REASON","TRANSFERRED_REAS
                     ON","FROM_OUTLET_CODE","TO_OUTLET_CODE",
                     "FROM_ORDER_TYPE","TO_ORDER_TYPE","STATU
                     S_REMARKS","COMMENTS","CREATED_BY","CREA
                     TION_DATE","LAST_UPDATED_BY","LAST_UPDAT
                     ED_DATE","LAST_UPDATED_LOGIN","ATTRIBUTE
                     1","ATTRIBUTE2","ATTRIBUTE3","ATTRIBUTE4
                     ","ATTRIBUTE5","MODEL_NO","PRODUCT_CATEG



1 rows selected.

7. End the LogMiner session.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

References
NOTE:111886.1 - How to Setup LogMiner



######################################################  All The Best ######################################################