Example 5: Tracking DDL Statements in the Internal Dictionary

By using the DBMS_LOGMNR.DDL_DICT_TRACKING option, this example ensures that the LogMiner internal dictionary is updated with the DDL statements encountered in the redo log files.

  1. Determine which redo log file was most recently archived by the database.

    SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG 
       WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
    
    NAME                                           SEQUENCE#
    --------------------------------------------   --------------
    /usr/oracle/data/db1arch_1_210_482701534.dbf   210
    
  2. Because the dictionary may be contained in more than one redo log file, you need to determine which redo log files contain the start and end of the data dictionary. Query the V$ARCHIVED_LOG view, as follows:

    1. Find a redo log that contains the end of the data dictionary extract. This redo log file must have been created before the redo log files that you want to analyze, but should be as recent as possible.

      SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end
         FROM V$ARCHIVED_LOG
         WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG
         WHERE DICTIONARY_END = 'YES' and SEQUENCE# < 210);
      
      
      NAME                                           SEQUENCE#    D_BEG  D_END
      --------------------------------------------   ----------   -----  ------
      /usr/oracle/data/db1arch_1_208_482701534.dbf   208          NO     YES
      
    2. Find the redo log file that contains the start of the data dictionary extract that matches the end of the dictionary found by the previous SQL statement:

      SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end
         FROM V$ARCHIVED_LOG
         WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG
         WHERE DICTIONARY_BEGIN = 'YES' and SEQUENCE# <= 208);
      
      NAME                                           SEQUENCE#    D_BEG  D_END
      --------------------------------------------   ----------   -----  ------
      /usr/oracle/data/db1arch_1_208_482701534.dbf   207          YES     NO
      
  3. Ensure that you have a complete list of redo log files.

    To successfully apply DDL statements encountered in the redo log files, ensure that all files are included in the list of redo log files to mine. The missing log file corresponding to sequence# 209 must be included in the list. Determine the names of the redo log files that you need to add to the list by issuing the following query:

    SELECT NAME FROM V$ARCHIVED_LOG
       WHERE SEQUENCE# >= 207 AND SEQUENCE# <= 210 
       ORDER BY SEQUENCE# ASC;
    
    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  
    
  4. Specify the list of the redo log files of interest.

    Include the redo log files that contain the beginning and end of the dictionary, the redo log file that you want to mine, and any redo log files required to create a list without gaps. You can add the redo log files in any order.

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
       LOGFILENAME => '/usr/oracle/data/db1arch_1_210_482701534.dbf', -
    
           OPTIONS => DBMS_LOGMNR.NEW);
    
    EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
       LOGFILENAME => '/usr/oracle/data/db1arch_1_209_482701534.dbf');
    EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
       LOGFILENAME => '/usr/oracle/data/db1arch_1_208_482701534.dbf');
    EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
       LOGFILENAME => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
    
  5. Start LogMiner by specifying the dictionary to use and the DDL_DICT_TRACKING, COMMITTED_DATA_ONLY, and PRINT_PRETTY_SQL options.

    EXECUTE DBMS_LOGMNR.START_LOGMNR(-
       OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
                  DBMS_LOGMNR.DDL_DICT_TRACKING + -
                  DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                  DBMS_LOGMNR.PRINT_PRETTY_SQL);
    
  6. Query the V$LOGMNR_CONTENTS view.

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

    The query returns all the reconstructed SQL statements correctly translated and the insert operations on the oe.product_tracking table that occurred because of the trigger execution.

    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;
    
  7. End the LogMiner session.

    EXECUTE DBMS_LOGMNR.END_LOGMNR();