Example 1: Mining Redo Log Files in a Given Time Range

This example is similar to "Example 4: Using the LogMiner Dictionary in the Redo Log Files", except the list of redo log files are not specified explicitly. This example assumes that you want to use the data dictionary extracted to the redo log files.

  1. Determine the timestamp of the redo log file that contains the start of the data dictionary.

    SELECT NAME, FIRST_TIME FROM V$ARCHIVED_LOG
    
        WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG 
        WHERE DICTIONARY_BEGIN = 'YES');
    
    NAME                                          FIRST_TIME
    --------------------------------------------  --------------------
    /usr/oracle/data/db1arch_1_207_482701534.dbf  10-jan-2012 12:01:34
    
  2. Display all the redo log files that have been generated so far.

    This step is not required, but is included to demonstrate that the CONTINUOUS_MINE option works as expected, as will be shown in Step 4.

    SELECT FILENAME name FROM V$LOGMNR_LOGS
       WHERE LOW_TIME > '10-jan-2012 12:01:34';
    
    NAME
    ----------------------------------------------
    /usr/oracle/data/db1arch_1_207_482701534.dbf
    /usr/oracle/data/db1arch_1_208_482701534.dbf
    /usr/oracle/data/db1arch_1_209_482701534.dbf
    /usr/oracle/data/db1arch_1_210_482701534.dbf
    
  3. Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY, PRINT_PRETTY_SQL, and CONTINUOUS_MINE options.

    EXECUTE DBMS_LOGMNR.START_LOGMNR(-
       STARTTIME => '10-jan-2012 12:01:34', -
         ENDTIME => SYSDATE, -
         OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
                    DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                    DBMS_LOGMNR.PRINT_PRETTY_SQL + -
                        DBMS_LOGMNR.CONTINUOUS_MINE);
    
  4. Query the V$LOGMNR_LOGS view.

    This step shows that the DBMS_LOGMNR.START_LOGMNR procedure with the CONTINUOUS_MINE option includes all of the redo log files that have been generated so far, as expected. (Compare the output in this step to the output in Step 2.)

    SELECT FILENAME name FROM V$LOGMNR_LOGS;
    
    NAME
    ------------------------------------------------------
    /usr/oracle/data/db1arch_1_207_482701534.dbf
    /usr/oracle/data/db1arch_1_208_482701534.dbf
    /usr/oracle/data/db1arch_1_209_482701534.dbf
    /usr/oracle/data/db1arch_1_210_482701534.dbf
    
  5. Query the V$LOGMNR_CONTENTS view.

    To reduce the number of rows returned by the query, exclude all DML statements done in the SYS or SYSTEM schema. (This query specifies a timestamp to exclude transactions that were involved in the dictionary extraction.)

    Note that all reconstructed SQL statements returned by the query are correctly translated.

    SELECT USERNAME AS usr,(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID, 
       SQL_REDO FROM V$LOGMNR_CONTENTS 
       WHERE SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM') AND
       TIMESTAMP > '10-jan-2012 15:59:53';
    
    USR             XID         SQL_REDO
    -----------     --------    -----------------------------------
    SYS             1.2.1594    set transaction read write;
    SYS             1.2.1594    create table oe.product_tracking (product_id number not null,
                                modified_time date,
                                old_list_price number(8,2),
                                old_warranty_period interval year(2) to month);
    SYS             1.2.1594    commit;
    
    SYS             1.18.1602   set transaction read write;
    SYS             1.18.1602   create or replace trigger oe.product_tracking_trigger
                                before update on oe.product_information
                                for each row
                                when (new.list_price <> old.list_price or
                                      new.warranty_period <> old.warranty_period)
                                declare
                                begin
                                insert into oe.product_tracking values 
                                   (:old.product_id, sysdate,
                                    :old.list_price, :old.warranty_period);
                                end;
    SYS             1.18.1602   commit;
    
    OE              1.9.1598    update "OE"."PRODUCT_INFORMATION"
                                  set
                                    "WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),
                                    "LIST_PRICE" = 100
                                  where
                                    "PRODUCT_ID" = 1729 and
                                    "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and
                                    "LIST_PRICE" = 80 and
                                    ROWID = 'AAAHTKAABAAAY9yAAA';
    OE              1.9.1598    insert into "OE"."PRODUCT_TRACKING"
                                  values
                                    "PRODUCT_ID" = 1729,
                                    "MODIFIED_TIME" = TO_DATE('13-jan-2012 16:07:03', 
                                    'dd-mon-yyyy hh24:mi:ss'),
                                    "OLD_LIST_PRICE" = 80,
                                    "OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00');
    
    OE              1.9.1598    update "OE"."PRODUCT_INFORMATION"
                                  set
                                    "WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),
                                    "LIST_PRICE" = 92
                                  where
                                    "PRODUCT_ID" = 2340 and
                                    "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and
                                    "LIST_PRICE" = 72 and
                                    ROWID = 'AAAHTKAABAAAY9zAAA';
    
    OE              1.9.1598    insert into "OE"."PRODUCT_TRACKING"
                                  values
                                    "PRODUCT_ID" = 2340,
                                    "MODIFIED_TIME" = TO_DATE('13-jan-2012 16:07:07', 
                                    'dd-mon-yyyy hh24:mi:ss'),
                                    "OLD_LIST_PRICE" = 72,
                                    "OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00');
    
    OE              1.9.1598     commit;
    
  6. End the LogMiner session.

    EXECUTE DBMS_LOGMNR.END_LOGMNR();