Using LogMiner to Analyze Log Files

Version 3
Visibility: Open to anyone

    Introduction

    Oracle LogMiner, which is part of Oracle Database, enables you to query online and archived redo log files through a SQL interface. Redo log files contain information about the history of activity on a database. Oracle Database 8i or Later support the Log Miner

    https://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1005606

     

    Prerequisites:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
    #ALTER DATABASE DROP SUPPLEMENTAL LOG DATA
    
    SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;   
    

    1. Specify a LogMiner dictionary

    EXECUTE DBMS_LOGMNR_D.BUILD( -
       OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
    

    2. Specify a list of redo log files for analysis.

     

    select thread#, name, first_time, sequence# from v$archived_log
    where first_time between to_date('28/10/2016 12:55:36','DD/MM/YYYY HH24:MI:SS')
    and to_date('28/10/2016 12:55:56','DD/MM/YYYY HH24:MI:SS');
    +ORADATA/BLRDB/ARCHIVELOG/2016_10_28/thread_1_seq_185.1125.926427397
    +ORADATA/BLRDB/ARCHIVELOG/2016_10_28/thread_1_seq_186.1126.926429777
    

     

    execute DBMS_LOGMNR.ADD_LOGFILE('+ORADATA/BLRDB/ARCHIVELOG/2016_10_28/thread_1_seq_185.1125.926427397',options=>dbms_logmnr.new);
    execute DBMS_LOGMNR.ADD_LOGFILE('+ORADATA/BLRDB/ARCHIVELOG/2016_10_28/thread_1_seq_186.1126.926429777',options=>dbms_logmnr.addfile);
    

    3. Start LogMiner.

     

    EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
      DBMS_LOGMNR.COMMITTED_DATA_ONLY);
    

     

    begin
       dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog );
    end;
    /
    

     

    4. Request the redo data of interest.

     

    alter session set nls_date_format='mm-dd-yyyy hh24:mi:ss';

    set pagesize 10000

    set termout off

    spool mine.out

    column scn format 9999999999999999

    column cscn format 9999999999999999

     

    SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,USERNAME,OPERATION,SQL_REDO,MACHINE_NAME,OS_USERNAME,THREAD#,timestamp FROM V$LOGMNR_CONTENTS;

    SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE OPERATION = 'DELETE' AND SQL_REDO LIKE '%YOUR_TBL%';

     

    5. End the LogMiner session.

    Use the DBMS_LOGMNR.END_LOGMNR procedure.

    EXECUTE SYS.DBMS_LOGMNR.END_LOGMNR; 
    

     

    Author:

    Hitesh Gondalia

    Lead Database Administrator

    OCE RACv11g,OCP v12c/11g/10g/9i

    CloudDB OTN Community - Master Tag

    https://hiteshgondalia.wordpress.com

     

    Reference

    http://www.oracleflash.com/24/How-to-read-or-analyze-redo-log-files-using-LogMiner.html

    https://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1016732

    Column USERNAME And SESSION_INFO Are UNKNOWN Or NULL In V$LOGMNR_CONTENTS (Doc ID 110301.1)