Example 4: Using the LogMiner Dictionary in the Redo Log Files

This example shows how to use the dictionary that has been extracted to the redo log files. When you use the dictionary in the online catalog, you must mine the redo log files in the same database that generated them. Using the dictionary contained in the redo log files enables you to mine redo log files in a different database.

  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. The dictionary may be contained in more than one redo log file. Therefore, you need to determine which redo log files contain the start and end of the dictionary. Query the V$ARCHIVED_LOG view, as follows:

    1. Find a redo log file that contains the end of the dictionary extract. This redo log file must have been created before the redo log file 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 in the previous step:

      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_207_482701534.dbf   207          YES     NO
      
    3. Specify the list of the redo log files of interest. Add the redo log files that contain the start and end of the dictionary and the redo log file that you want to analyze. 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_208_482701534.dbf');
      EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
         LOGFILENAME => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
      
    4. Query the V$LOGMNR_LOGS view to display the list of redo log files to be analyzed, including their timestamps.

      In the output, LogMiner flags a missing redo log file. LogMiner lets you proceed with mining, provided that you do not specify an option that requires the missing redo log file for proper functioning.

  3. Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY and PRINT_PRETTY_SQL options.

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

    To reduce the number of rows returned by the query, 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 output shows three transactions: two DDL transactions and one DML transaction. The DDL transactions, 1.2.1594 and 1.18.1602, create the table oe.product_tracking and create a trigger on table oe.product_information, respectively. In both transactions, the DML statements done to the system tables (tables owned by SYS) are filtered out because of the query predicate.

    The DML transaction, 1.9.1598, updates the oe.product_information table. The update operation in this transaction is fully translated. However, the query output also contains some untranslated reconstructed SQL statements. Most likely, these statements were done on the oe.product_tracking table that was created after the data dictionary was extracted to the redo log files.

    (The next example shows how to run LogMiner with the DDL_DICT_TRACKING option so that all SQL statements are fully translated; no binary data is returned.)

    SELECT USERNAME AS usr, 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 "UNKNOWN"."OBJ# 33415"
                                  values
                                    "COL 1" = HEXTORAW('c2121e'),
                                    "COL 2" = HEXTORAW('7867010d110804'),
                                    "COL 3" = HEXTORAW('c151'),
                                    "COL 4" = HEXTORAW('800000053c');
    
    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 "UNKNOWN"."OBJ# 33415"
                                  values
                                    "COL 1" = HEXTORAW('c21829'),
                                    "COL 2" = HEXTORAW('7867010d110808'),
                                    "COL 3" = HEXTORAW('c149'),
                                    "COL 4" = HEXTORAW('800000053c');
    
    OE              1.9.1598     commit;
    
  5. Issue additional queries, if desired.

    Display all the DML statements that were executed as part of the CREATE TABLE DDL statement. This includes statements executed by users and internally by Oracle.

    Note:

    If you choose to reapply statements displayed by a query such as the one shown here, then reapply DDL statements only. Do not reapply DML statements that were executed internally by Oracle, or you risk corrupting your database. In the following output, the only statement that you should use in a reapply operation is the CREATE TABLE OE.PRODUCT_TRACKING statement.

    SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
       WHERE XIDUSN  = 1 and XIDSLT = 2 and XIDSQN = 1594;
    
    SQL_REDO
    --------------------------------------------------------------------------------
    set transaction read write;
    
    insert into "SYS"."OBJ$"
     values
        "OBJ#" = 33415,
        "DATAOBJ#" = 33415,
        "OWNER#" = 37,
        "NAME" = 'PRODUCT_TRACKING',
        "NAMESPACE" = 1,
        "SUBNAME" IS NULL,
        "TYPE#" = 2,
        "CTIME" = TO_DATE('13-jan-2012 14:01:03', 'dd-mon-yyyy hh24:mi:ss'),
        "MTIME" = TO_DATE('13-jan-2012 14:01:03', 'dd-mon-yyyy hh24:mi:ss'),
        "STIME" = TO_DATE('13-jan-2012 14:01:03', 'dd-mon-yyyy hh24:mi:ss'),
        "STATUS" = 1,
        "REMOTEOWNER" IS NULL,
        "LINKNAME" IS NULL,
        "FLAGS" = 0,
        "OID$" IS NULL,
        "SPARE1" = 6,
        "SPARE2" = 1,
        "SPARE3" IS NULL,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "SPARE6" IS NULL;
    
    insert into "SYS"."TAB$"
     values
        "OBJ#" = 33415,
        "DATAOBJ#" = 33415,
        "TS#" = 0,
        "FILE#" = 1,
        "BLOCK#" = 121034,
        "BOBJ#" IS NULL,
        "TAB#" IS NULL,
        "COLS" = 5,
        "CLUCOLS" IS NULL,
        "PCTFREE$" = 10,
        "PCTUSED$" = 40,
        "INITRANS" = 1,
        "MAXTRANS" = 255,
        "FLAGS" = 1,
        "AUDIT$" = '--------------------------------------',
        "ROWCNT" IS NULL,
        "BLKCNT" IS NULL,
        "EMPCNT" IS NULL,
        "AVGSPC" IS NULL,
        "CHNCNT" IS NULL,
        "AVGRLN" IS NULL,
        "AVGSPC_FLB" IS NULL,
        "FLBCNT" IS NULL,
        "ANALYZETIME" IS NULL,
        "SAMPLESIZE" IS NULL,
        "DEGREE" IS NULL,
        "INSTANCES" IS NULL,
        "INTCOLS" = 5,
        "KERNELCOLS" = 5,
        "PROPERTY" = 536870912,
        "TRIGFLAG" = 0,
        "SPARE1" = 178,
        "SPARE2" IS NULL,
        "SPARE3" IS NULL,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "SPARE6" = TO_DATE('13-jan-2012 14:01:05', 'dd-mon-yyyy hh24:mi:ss'),
    
    insert into "SYS"."COL$"
     values
        "OBJ#" = 33415,
        "COL#" = 1,
        "SEGCOL#" = 1,
        "SEGCOLLENGTH" = 22,
        "OFFSET" = 0,
        "NAME" = 'PRODUCT_ID',
        "TYPE#" = 2,
        "LENGTH" = 22,
        "FIXEDSTORAGE" = 0,
        "PRECISION#" IS NULL,
        "SCALE" IS NULL,
        "NULL$" = 1,
        "DEFLENGTH" IS NULL,
        "SPARE6" IS NULL,
        "INTCOL#" = 1,
        "PROPERTY" = 0,
        "CHARSETID" = 0,
        "CHARSETFORM" = 0,
        "SPARE1" = 0,
        "SPARE2" = 0,
        "SPARE3" = 0,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "DEFAULT$" IS NULL;
    
    insert into "SYS"."COL$"
     values
        "OBJ#" = 33415,
        "COL#" = 2,
        "SEGCOL#" = 2,
        "SEGCOLLENGTH" = 7,
        "OFFSET" = 0,
        "NAME" = 'MODIFIED_TIME',
        "TYPE#" = 12,
        "LENGTH" = 7,
        "FIXEDSTORAGE" = 0,
        "PRECISION#" IS NULL,
        "SCALE" IS NULL,
        "NULL$" = 0,
        "DEFLENGTH" IS NULL,
        "SPARE6" IS NULL,
        "INTCOL#" = 2,
        "PROPERTY" = 0,
        "CHARSETID" = 0,
        "CHARSETFORM" = 0,
        "SPARE1" = 0,
        "SPARE2" = 0,
        "SPARE3" = 0,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "DEFAULT$" IS NULL;
    
    insert into "SYS"."COL$"
     values
        "OBJ#" = 33415,
        "COL#" = 3,
        "SEGCOL#" = 3,
        "SEGCOLLENGTH" = 22,
        "OFFSET" = 0,
        "NAME" = 'OLD_LIST_PRICE',
        "TYPE#" = 2,
        "LENGTH" = 22,
        "FIXEDSTORAGE" = 0,
        "PRECISION#" = 8,
        "SCALE" = 2,
        "NULL$" = 0,
        "DEFLENGTH" IS NULL,
        "SPARE6" IS NULL,
        "INTCOL#" = 3,
        "PROPERTY" = 0,
        "CHARSETID" = 0,
        "CHARSETFORM" = 0,
        "SPARE1" = 0,
        "SPARE2" = 0,
        "SPARE3" = 0,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "DEFAULT$" IS NULL;
    
    insert into "SYS"."COL$"
     values
        "OBJ#" = 33415,
        "COL#" = 4,
        "SEGCOL#" = 4,
        "SEGCOLLENGTH" = 5,
        "OFFSET" = 0,
        "NAME" = 'OLD_WARRANTY_PERIOD',
        "TYPE#" = 182,
        "LENGTH" = 5,
        "FIXEDSTORAGE" = 0,
        "PRECISION#" = 2,
        "SCALE" = 0,
        "NULL$" = 0,
        "DEFLENGTH" IS NULL,
        "SPARE6" IS NULL,
        "INTCOL#" = 4,
        "PROPERTY" = 0,
        "CHARSETID" = 0,
        "CHARSETFORM" = 0,
        "SPARE1" = 0,
        "SPARE2" = 2,
        "SPARE3" = 0,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "DEFAULT$" IS NULL;
    
    insert into "SYS"."CCOL$"
     values
        "OBJ#" = 33415,
        "CON#" = 2090,
        "COL#" = 1,
        "POS#" IS NULL,
        "INTCOL#" = 1,
        "SPARE1" = 0,
        "SPARE2" IS NULL,
        "SPARE3" IS NULL,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "SPARE6" IS NULL;
    
    insert into "SYS"."CDEF$"
     values
        "OBJ#" = 33415,
        "CON#" = 2090,
        "COLS" = 1,
        "TYPE#" = 7,
        "ROBJ#" IS NULL,
        "RCON#" IS NULL,
        "RRULES" IS NULL,
        "MATCH#" IS NULL,
        "REFACT" IS NULL,
        "ENABLED" = 1,
        "CONDLENGTH" = 24,
        "SPARE6" IS NULL,
        "INTCOLS" = 1,
        "MTIME" = TO_DATE('13-jan-2012 14:01:08', 'dd-mon-yyyy hh24:mi:ss'),
        "DEFER" = 12,
        "SPARE1" = 6,
        "SPARE2" IS NULL,
        "SPARE3" IS NULL,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "CONDITION" = '"PRODUCT_ID" IS NOT NULL';
    
    create table oe.product_tracking (product_id number not null,
      modified_time date,
      old_product_description varchar2(2000),
      old_list_price number(8,2),
      old_warranty_period interval year(2) to month);
    
    update "SYS"."SEG$"
      set
        "TYPE#" = 5,
        "BLOCKS" = 5,
        "EXTENTS" = 1,
        "INIEXTS" = 5,
        "MINEXTS" = 1,
        "MAXEXTS" = 121,
        "EXTSIZE" = 5,
        "EXTPCT" = 50,
        "USER#" = 37,
        "LISTS" = 0,
        "GROUPS" = 0,
        "CACHEHINT" = 0,
        "HWMINCR" = 33415,
        "SPARE1" = 1024
      where
        "TS#" = 0 and
        "FILE#" = 1 and
        "BLOCK#" = 121034 and
        "TYPE#" = 3 and
        "BLOCKS" = 5 and
        "EXTENTS" = 1 and
        "INIEXTS" = 5 and
        "MINEXTS" = 1 and
        "MAXEXTS" = 121 and
        "EXTSIZE" = 5 and
        "EXTPCT" = 50 and
        "USER#" = 37 and
        "LISTS" = 0 and
        "GROUPS" = 0 and
        "BITMAPRANGES" = 0 and
        "CACHEHINT" = 0 and
        "SCANHINT" = 0 and
        "HWMINCR" = 33415 and
        "SPARE1" = 1024 and
        "SPARE2" IS NULL and
        ROWID = 'AAAAAIAABAAAdMOAAB';
    
    insert into "SYS"."CON$"
     values
        "OWNER#" = 37,
        "NAME" = 'SYS_C002090',
        "CON#" = 2090,
        "SPARE1" IS NULL,
        "SPARE2" IS NULL,
        "SPARE3" IS NULL,
        "SPARE4" IS NULL,
        "SPARE5" IS NULL,
        "SPARE6" IS NULL;
    
    commit;
    
  6. End the LogMiner session.

    EXECUTE DBMS_LOGMNR.END_LOGMNR();