******************************************* * Description: SQL for Archived Log Mining * Compatiablity: RDBMS 10g, 11g, 12c * Date: 17:00 PM EST, 01/11/2017 ******************************************* <1> Loading the archive logs: | |__ SQL> exec dbms_logmnr.add_logfile( logfilename => 'D:\oracle\fast_recovery_area\ARCHIVELOG\O1_MF_1_52167_D231KT7L_.ARC', options => dbms_logmnr.new ); |__ SQL> exec dbms_logmnr.add_logfile( logfilename => 'D:\oracle\fast_recovery_area\ARCHIVELOG\O1_MF_1_52168_D231LO86_.ARC', options => dbms_logmnr.addfile ); exec dbms_logmnr.add_logfile( logfilename => 'D:\ORACLE\FAST_RECOVERY_AREA\ARCHIVELOG\O1_MF_1_52171_D23532GN_.ARC', options => dbms_logmnr.addfile ); exec dbms_logmnr.add_logfile( logfilename => 'D:\ORACLE\FAST_RECOVERY_AREA\ARCHIVELOG\O1_MF_1_52172_D236VBP9_.ARC', options => dbms_logmnr.addfile ); exec dbms_logmnr.add_logfile( logfilename => 'D:\ORACLE\FAST_RECOVERY_AREA\ARCHIVELOG\O1_MF_1_52173_D238MMB7_.ARC', options => dbms_logmnr.addfile ); exec dbms_logmnr.add_logfile( logfilename => 'D:\ORACLE\FAST_RECOVERY_AREA\ARCHIVELOG\O1_MF_1_52174_D23BBOYS_.ARC', options => dbms_logmnr.addfile ); <2> Start mining: | |__ SQL> exec dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog ); | |__ SQL> exec dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_redo_logs ); <3> Select mining result from v$logmnr_content: | |__ SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE seg_owner = 'L' and seg_name='APPLICATION_REPORT'; | |__ SQL> SELECT username AS USR, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE seg_owner = '' and seg_name=' ' and sql_redo like '%DELETE%' or sql_redo like '%TRUNCATE%'; | |__ SQL> SELECT username AS USR, SQL_REDO, OS_username, timestamp, machine_name, sequence# FROM V$LOGMNR_CONTENTS WHERE seg_owner = ' ' and seg_name=' ' and ( sql_redo like '%DELETE%' or sql_redo like '%TRUNCATE%'); <4> Terminating log miner process: | |__ SQL> exec dbms_logmnr.end_logmnr; <5> Reference: http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019
Your Comments