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');
Monday, November 29, 2010
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment