Traveling past with Log Miner

Some times you may wish to see what happen in recent past. In a beautiful morning, you may discover that a misshape occure in your database :(. Unfortunately you did not enabled any auditing options earlier.

If your database is in archivelog mod and supplemental log is enabled then 'Log Miner' can save the day ! Oracle Log Miner is also capable of mining redo log, archive log of remote DB. Please see Oracle Utility Document for details.

In this post, we only consider a scenario in which Source DB and Mining DB is same. Lets describe how you can do this


STEP 1 : Create Log Miner User

CREATE USER logminer_adm identified by logminer_adm;

GRANT resource,connect to logminer_adm;
GRANT EXECUTE_CATALOG_ROLE,DBA to logminer_adm;

Step 2 : Enable Supplemental Login

Check whether supplemental login is enabled

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

If not, then you can't mine all transections. So enable it for future and follow the remaining steps to mine a subset of all transections.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


Step 3: Add Archieve Log Files to Log Miner

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/u/archivelog/2011_07_10/o1_mf_1_470_71m4jv90_.arc', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/u/archivelog/2011_07_10/o1_mf_1_471_71m4kn09_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

You can add as much files as you need

Step 4: Start Log Miner

EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME =>'10-JUL-2011 19:50:00',ENDTIME => '10-JUL-2011 19:55:00',OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+DBMS_LOGMNR.CONTINUOUS_MINE);

Step 5: View What Happen Last Night :)

SELECT SEG_OWNER ,OPERATION, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SYS';

Step 6: Stop Log Miner Session

EXECUTE DBMS_LOGMNR.END_LOGMNR;