Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, November 29, 2010

View redo logs by logminer

Step 1: Check supplemental log is enabled for database,  if not enable it
connect / as sysdba;
 SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; -- it should be yes for logmining
 alter database add supplemental log data; --so that db can store additional data for logmining
Step 2: Run the following block to add logs and start logminer .
BEGIN
     DBMS_LOGMNR.ADD_LOGFILE ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG', DBMS_LOGMNR.NEW);     
    DBMS_LOGMNR.ADD_LOGFILE ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG', DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.ADD_LOGFILE ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG', DBMS_LOGMNR.ADDFILE);
    DBMS_LOGMNR.START_LOGMNR ( options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
END;
Step 3: Mine the logs
Use the database control file to find and add redo log files that satisfy your specified time or SCN range to the LogMiner redo log file list.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

BEGIN
     DBMS_LOGMNR.START_LOGMNR(STARTTIME => '01-Mar-2010 15:30:00',ENDTIME => '02-Mar-2010 15:45:00',
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
END;

Step 4: Choose any object to view the logs about it
select sql_redo from v$logmnr_contents where table_name = 'CUST_PAYMENT_INFO' and operation='INSERT';

SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
   WHERE
   SEG_NAME = 'CUST_PAYMENT_INFO' AND
   SEG_OWNER = 'OE' AND
   OPERATION = 'UPDATE' AND
   DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'OE.ACTIVE_CARD') !=   DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'OE.ACTIVE_CARD');

No comments: