While the dictionary is being extracted to the redo log stream, no DDL statements can be executed. Therefore, the dictionary extracted to the redo log files is guaranteed to be consistent (whereas the dictionary extracted to a flat file is not).

To extract dictionary information to the redo log files, execute the PL/SQL DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_REDO_LOGS option. Do not specify a file name or location.

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

The process of extracting the dictionary to the redo log files does consume database resources, but if you limit the extraction to off-peak hours, then this should not be a problem, and it is faster than extracting to a flat file. Depending on the size of the dictionary, it may be contained in multiple redo log files. If the relevant redo log files have been archived, then you can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the V$ARCHIVED_LOG view, as follows:

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

Specify the names of the start and end redo log files, and other redo logs in between them, with the ADD_LOGFILE procedure when you are preparing to begin a LogMiner session.

Oracle recommends that you periodically back up the redo log files so that the information is saved and available at a later date. Ideally, this will not involve any extra steps because if your database is being properly managed, then there should already be a process in place for backing up and restoring archived redo log files. Again, because of the time required, it is good practice to do this during off-peak hours.

To extract database dictionary information to a flat file, use the DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_FLAT_FILE option.

The following steps describe how to extract a dictionary to a flat file. Steps 1 and 2 are preparation steps. You only need to do them once, and then you can extract a dictionary to a flat file as many times as you want to.

The easiest way to examine the modification history of a database is to mine at the source database and use the online catalog to translate the redo log files. This example shows how to do the simplest analysis using LogMiner.

This example assumes that you know you want to mine the redo log file that was most recently archived. It finds all modifications that are contained in the last archived redo log generated by the database (assuming that the database is not an Oracle Real Application Clusters (Oracle RAC) database).

1. Determine which redo log file was most recently archived.

   SELECT NAME FROM V$ARCHIVED_LOG
      WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
   
   NAME                            
   -------------------------------------------
   /usr/oracle/data/db1arch_1_16_482701534.dbf

2. Specify the list of redo log files to be analyzed. In this case, it is the redo log file that was returned by the query in Step 1.

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
     LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
     OPTIONS => DBMS_LOGMNR.NEW);
   

3. Start LogMiner and specify the dictionary to use.

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

4. Query the V$LOGMNR_CONTENTS view.

   Note that there are four transactions (two of them were committed within the redo log file being analyzed, and two were not). The output shows the DML statements in the order in which they were executed; thus transactions interleave among themselves.

   SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, 
      SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');
   
   USR    XID          SQL_REDO                        SQL_UNDO
   ----   ---------  ----------------------------------------------------
   HR     1.11.1476  set transaction read write;
   
   HR     1.11.1476  insert into "HR"."EMPLOYEES"(     delete from "HR"."EMPLOYEES" 
                     "EMPLOYEE_ID","FIRST_NAME",       where "EMPLOYEE_ID" = '306'
                     "LAST_NAME","EMAIL",              and "FIRST_NAME" = 'Nandini'
                     "PHONE_NUMBER","HIRE_DATE",       and "LAST_NAME" = 'Shastry'
                     "JOB_ID","SALARY",                and "EMAIL" = 'NSHASTRY'
                     "COMMISSION_PCT","MANAGER_ID",    and "PHONE_NUMBER" = '1234567890'
                     "DEPARTMENT_ID") values           and "HIRE_DATE" = TO_DATE('10-JAN-2012
                     ('306','Nandini','Shastry',       13:34:43', 'dd-mon-yyyy hh24:mi:ss') 
                     'NSHASTRY', '1234567890',         and "JOB_ID" = 'HR_REP' and 
                     TO_DATE('10-jan-2012 13:34:43',   "SALARY" = '120000' and 
                     'dd-mon-yyyy hh24:mi:ss'),         "COMMISSION_PCT" = '.05' and
                     'HR_REP','120000', '.05',         "DEPARTMENT_ID" = '10' and
                     '105','10');                      ROWID = 'AAAHSkAABAAAY6rAAO';
        
   OE     1.1.1484   set transaction read write;
   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  update "OE"."PRODUCT_INFORMATION" 
                     set "WARRANTY_PERIOD" =            set "WARRANTY_PERIOD" = 
                     TO_YMINTERVAL('+05-00') where      TO_YMINTERVAL('+01-00') where
                     "PRODUCT_ID" = '1799' and          "PRODUCT_ID" = '1799' and
                     "WARRANTY_PERIOD" =                "WARRANTY_PERIOD" = 
                     TO_YMINTERVAL('+01-00') and        TO_YMINTERVAL('+05-00') and
                     ROWID = 'AAAHTKAABAAAY9mAAB';      ROWID = 'AAAHTKAABAAAY9mAAB'; 
                                                                                   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  update "OE"."PRODUCT_INFORMATION"
                     set "WARRANTY_PERIOD" =            set "WARRANTY_PERIOD" =
                     TO_YMINTERVAL('+05-00') where      TO_YMINTERVAL('+01-00') where
                     "PRODUCT_ID" = '1801' and          "PRODUCT_ID" = '1801' and
                     "WARRANTY_PERIOD" =                "WARRANTY_PERIOD" = 
                     TO_YMINTERVAL('+01-00') and        TO_YMINTERVAL('+05-00') and
                     ROWID = 'AAAHTKAABAAAY9mAAC';      ROWID ='AAAHTKAABAAAY9mAAC';
   
   HR     1.11.1476  insert into "HR"."EMPLOYEES"(     delete from "HR"."EMPLOYEES"
                     "EMPLOYEE_ID","FIRST_NAME",       "EMPLOYEE_ID" = '307' and 
                     "LAST_NAME","EMAIL",              "FIRST_NAME" = 'John' and
                     "PHONE_NUMBER","HIRE_DATE",       "LAST_NAME" = 'Silver' and
                     "JOB_ID","SALARY",                "EMAIL" = 'JSILVER' and 
                     "COMMISSION_PCT","MANAGER_ID",    "PHONE_NUMBER" = '5551112222'
                     "DEPARTMENT_ID") values           and "HIRE_DATE" = TO_DATE('10-jan-2012
                     ('307','John','Silver',           13:41:03', 'dd-mon-yyyy hh24:mi:ss') 
                      'JSILVER', '5551112222',         and "JOB_ID" ='105' and "DEPARTMENT_ID" 
                     TO_DATE('10-jan-2012 13:41:03',   = '50' and ROWID = 'AAAHSkAABAAAY6rAAP'; 
                     'dd-mon-yyyy hh24:mi:ss'),
                     'SH_CLERK','110000', '.05',
                     '105','50');                
   
   OE     1.1.1484   commit;
   
   HR     1.15.1481   set transaction read write;
   
   HR     1.15.1481  delete from "HR"."EMPLOYEES"      insert into "HR"."EMPLOYEES"(
                     where "EMPLOYEE_ID" = '205' and   "EMPLOYEE_ID","FIRST_NAME",
                     "FIRST_NAME" = 'Shelley' and      "LAST_NAME","EMAIL","PHONE_NUMBER",
                     "LAST_NAME" = 'Higgins' and       "HIRE_DATE", "JOB_ID","SALARY",
                     "EMAIL" = 'SHIGGINS' and          "COMMISSION_PCT","MANAGER_ID",
                     "PHONE_NUMBER" = '515.123.8080'   "DEPARTMENT_ID") values
                     and "HIRE_DATE" = TO_DATE(        ('205','Shelley','Higgins',
                     '07-jun-1994 10:05:01',           and     'SHIGGINS','515.123.8080',
                     'dd-mon-yyyy hh24:mi:ss')         TO_DATE('07-jun-1994 10:05:01',
                     and "JOB_ID" = 'AC_MGR'           'dd-mon-yyyy hh24:mi:ss'),
                     and "SALARY"= '12000'            'AC_MGR','12000',NULL,'101','110'); 
                     and "COMMISSION_PCT" IS NULL 
                     and "MANAGER_ID" 
                     = '101' and "DEPARTMENT_ID" = 
                     '110' and ROWID = 
                     'AAAHSkAABAAAY6rAAM';
   
   
   OE     1.8.1484   set transaction read write;
   
   OE     1.8.1484   update "OE"."PRODUCT_INFORMATION"  update "OE"."PRODUCT_INFORMATION"
                     set "WARRANTY_PERIOD" =            set "WARRANTY_PERIOD" = 
                     TO_YMINTERVAL('+12-06') where      TO_YMINTERVAL('+20-00') where
                     "PRODUCT_ID" = '2350' and          "PRODUCT_ID" = '2350' and
                     "WARRANTY_PERIOD" =                "WARRANTY_PERIOD" =
                     TO_YMINTERVAL('+20-00') and        TO_YMINTERVAL('+20-00') and
                     ROWID = 'AAAHTKAABAAAY9tAAD';       ROWID ='AAAHTKAABAAAY9tAAD'; 
   
   HR     1.11.1476  commit;
   

5. End the LogMiner session.

   SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

As shown in the first example, Example 1: Finding All Modifications in the Last Archived Redo Log File, LogMiner displays all modifications it finds in the redo log files that it analyzes by default, regardless of whether the transaction has been committed or not. In addition, LogMiner shows modifications in the same order in which they were executed. Because DML statements that belong to the same transaction are not grouped together, visual inspection of the output can be difficult. Although you can use SQL to group transactions, LogMiner provides an easier way. In this example, the latest archived redo log file will again be analyzed, but it will return only committed transactions.

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

   SELECT NAME FROM V$ARCHIVED_LOG
      WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
   
   NAME                            
   -------------------------------------------
   /usr/oracle/data/db1arch_1_16_482701534.dbf
   

2. Specify the redo log file that was returned by the query in Step 1. The list will consist of one redo log file.

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
      OPTIONS => DBMS_LOGMNR.NEW);

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

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
      OPTIONS => DBMS_LOGMNR.NEW);

4. Query the V$LOGMNR_CONTENTS view.

   Although transaction 1.11.1476 was started before transaction 1.1.1484 (as revealed in Example 1: Finding All Modifications in the Last Archived Redo Log File), it committed after transaction 1.1.1484 committed. In this example, therefore, transaction 1.1.1484 is shown in its entirety before transaction 1.11.1476. The two transactions that did not commit within the redo log file being analyzed are not returned.

   SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, SQL_REDO, 
      SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');
   ;
   USR    XID          SQL_REDO                        SQL_UNDO
   ----   ---------    ------------------------------- ---------------------------------
        
   OE     1.1.1484   set transaction read write;
   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  update "OE"."PRODUCT_INFORMATION" 
                     set "WARRANTY_PERIOD" =            set "WARRANTY_PERIOD" = 
                     TO_YMINTERVAL('+05-00') where      TO_YMINTERVAL('+01-00') where
                     "PRODUCT_ID" = '1799' and          "PRODUCT_ID" = '1799' and
                     "WARRANTY_PERIOD" =                "WARRANTY_PERIOD" = 
                     TO_YMINTERVAL('+01-00') and        TO_YMINTERVAL('+05-00') and
                     ROWID = 'AAAHTKAABAAAY9mAAB';      ROWID = 'AAAHTKAABAAAY9mAAB'; 
                                                                                   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  update "OE"."PRODUCT_INFORMATION"
                     set "WARRANTY_PERIOD" =            set "WARRANTY_PERIOD" =
                     TO_YMINTERVAL('+05-00') where      TO_YMINTERVAL('+01-00') where
                     "PRODUCT_ID" = '1801' and          "PRODUCT_ID" = '1801' and
                     "WARRANTY_PERIOD" =                "WARRANTY_PERIOD" = 
                     TO_YMINTERVAL('+01-00') and        TO_YMINTERVAL('+05-00') and
                     ROWID = 'AAAHTKAABAAAY9mAAC';      ROWID ='AAAHTKAABAAAY9mAAC';
   
   OE     1.1.1484   commit;
                               
   HR     1.11.1476  set transaction read write;
   
   HR     1.11.1476  insert into "HR"."EMPLOYEES"(     delete from "HR"."EMPLOYEES" 
                     "EMPLOYEE_ID","FIRST_NAME",       where "EMPLOYEE_ID" = '306'
                     "LAST_NAME","EMAIL",              and "FIRST_NAME" = 'Nandini'
                     "PHONE_NUMBER","HIRE_DATE",       and "LAST_NAME" = 'Shastry'
                     "JOB_ID","SALARY",                and "EMAIL" = 'NSHASTRY'
                     "COMMISSION_PCT","MANAGER_ID",    and "PHONE_NUMBER" = '1234567890'
                     "DEPARTMENT_ID") values           and "HIRE_DATE" = TO_DATE('10-JAN-2012
                     ('306','Nandini','Shastry',       13:34:43', 'dd-mon-yyyy hh24:mi:ss') 
                     'NSHASTRY', '1234567890',         and "JOB_ID" = 'HR_REP' and 
                     TO_DATE('10-jan-2012 13:34:43',   "SALARY" = '120000' and 
                     'dd-mon-yyy hh24:mi:ss'),         "COMMISSION_PCT" = '.05' and
                     'HR_REP','120000', '.05',         "DEPARTMENT_ID" = '10' and
                     '105','10');                      ROWID = 'AAAHSkAABAAAY6rAAO';
   
   HR     1.11.1476  insert into "HR"."EMPLOYEES"(     delete from "HR"."EMPLOYEES"
                     "EMPLOYEE_ID","FIRST_NAME",       "EMPLOYEE_ID" = '307' and 
                     "LAST_NAME","EMAIL",              "FIRST_NAME" = 'John' and
                     "PHONE_NUMBER","HIRE_DATE",       "LAST_NAME" = 'Silver' and
                     "JOB_ID","SALARY",                "EMAIL" = 'JSILVER' and 
                     "COMMISSION_PCT","MANAGER_ID",    "PHONE_NUMBER" = '5551112222'
                     "DEPARTMENT_ID") values           and "HIRE_DATE" = TO_DATE('10-jan-2012
                     ('307','John','Silver',           13:41:03', 'dd-mon-yyyy hh24:mi:ss') 
                      'JSILVER', '5551112222',         and "JOB_ID" ='105' and "DEPARTMENT_ID" 
                     TO_DATE('10-jan-2012 13:41:03',   = '50' and ROWID = 'AAAHSkAABAAAY6rAAP'; 
                     'dd-mon-yyyy hh24:mi:ss'),
                     'SH_CLERK','110000', '.05',
                     '105','50');                
   
   HR     1.11.1476  commit;
   

5. End the LogMiner session.

   EXECUTE DBMS_LOGMNR.END_LOGMNR();

As shown in Example 2: Grouping DML Statements into Committed Transactions , using the COMMITTED_DATA_ONLY option with the dictionary in the online redo log file is an easy way to focus on committed transactions. However, one aspect remains that makes visual inspection difficult: the association between the column names and their respective values in an INSERT statement are not apparent. This can be addressed by specifying the PRINT_PRETTY_SQL option. Note that specifying this option will make some of the reconstructed SQL statements nonexecutable.

1. Determine which redo log file was most recently archived.

   SELECT NAME FROM V$ARCHIVED_LOG
      WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
   
   NAME                            
   -------------------------------------------
   /usr/oracle/data/db1arch_1_16_482701534.dbf

2. Specify the redo log file that was returned by the query in Step 1.

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
      OPTIONS => DBMS_LOGMNR.NEW);

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_ONLINE_CATALOG + -
                 DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                 DBMS_LOGMNR.PRINT_PRETTY_SQL);
   

   The DBMS_LOGMNR.PRINT_PRETTY_SQL option changes only the format of the reconstructed SQL, and therefore is useful for generating reports for visual inspection.

4. Query the V$LOGMNR_CONTENTS view for SQL_REDO statements.

   SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, SQL_REDO 
      FROM V$LOGMNR_CONTENTS;
   
   USR    XID          SQL_REDO                     
   ----   ---------  -----------------------------------------------------
   
   OE     1.1.1484   set transaction read write;
   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  
                       set 
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') 
                       where
                         "PRODUCT_ID" = '1799' and          
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+01-00') and        
                         ROWID = 'AAAHTKAABAAAY9mAAB';  
                                                                                   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"
                       set 
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') 
                       where
                         "PRODUCT_ID" = '1801' and
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+01-00') and   
                         ROWID = 'AAAHTKAABAAAY9mAAC'; 
   
   OE     1.1.1484   commit;
                               
   HR     1.11.1476  set transaction read write;
   
   HR     1.11.1476  insert into "HR"."EMPLOYEES"
                      values
                        "EMPLOYEE_ID" = 306,
                        "FIRST_NAME" = 'Nandini',
                        "LAST_NAME" = 'Shastry',
                        "EMAIL" = 'NSHASTRY',
                        "PHONE_NUMBER" = '1234567890',
                        "HIRE_DATE" = TO_DATE('10-jan-2012 13:34:43', 
                        'dd-mon-yyyy hh24:mi:ss',
                        "JOB_ID" = 'HR_REP',
                        "SALARY" = 120000,
                        "COMMISSION_PCT" = .05,
                        "MANAGER_ID" = 105,
                        "DEPARTMENT_ID" = 10;
   
   HR     1.11.1476   insert into "HR"."EMPLOYEES"
                       values
                          "EMPLOYEE_ID" = 307,
                          "FIRST_NAME" = 'John',
                          "LAST_NAME" = 'Silver',
                          "EMAIL" = 'JSILVER',
                          "PHONE_NUMBER" = '5551112222',
                          "HIRE_DATE" = TO_DATE('10-jan-2012 13:41:03',
                          'dd-mon-yyyy hh24:mi:ss'),
                          "JOB_ID" = 'SH_CLERK',
                          "SALARY" = 110000,
                          "COMMISSION_PCT" = .05,
                          "MANAGER_ID" = 105,
                          "DEPARTMENT_ID" = 50;
   HR     1.11.1476    commit;
   

5. Query the V$LOGMNR_CONTENTS view for reconstructed SQL_UNDO statements.

   SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, SQL_UNDO 
      FROM V$LOGMNR_CONTENTS;
   
   USR   XID        SQL_UNDO                     
   ----   ---------  -----------------------------------------------------
   
        
   OE     1.1.1484   set transaction read write;
   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"  
                       set 
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+01-00') 
                       where
                         "PRODUCT_ID" = '1799' and          
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and        
                         ROWID = 'AAAHTKAABAAAY9mAAB';  
                                                                                   
   OE     1.1.1484   update "OE"."PRODUCT_INFORMATION"
                       set 
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+01-00') 
                       where
                         "PRODUCT_ID" = '1801' and
                         "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and   
                         ROWID = 'AAAHTKAABAAAY9mAAC'; 
   
   OE     1.1.1484   commit;
                               
   HR     1.11.1476  set transaction read write;
   
   HR     1.11.1476  delete from "HR"."EMPLOYEES"
                     where
                        "EMPLOYEE_ID" = 306 and
                        "FIRST_NAME" = 'Nandini' and
                        "LAST_NAME" = 'Shastry' and
                        "EMAIL" = 'NSHASTRY' and
                        "PHONE_NUMBER" = '1234567890' and
                        "HIRE_DATE" = TO_DATE('10-jan-2012 13:34:43',
                        'dd-mon-yyyy hh24:mi:ss') and
                        "JOB_ID" = 'HR_REP' and 
                        "SALARY" = 120000 and
                        "COMMISSION_PCT" = .05 and
                        "MANAGER_ID" = 105 and
                        "DEPARTMENT_ID" = 10 and
                        ROWID = 'AAAHSkAABAAAY6rAAO';
   
   HR     1.11.1476   delete from "HR"."EMPLOYEES"
                      where
                          "EMPLOYEE_ID" = 307 and
                          "FIRST_NAME" = 'John' and
                          "LAST_NAME" = 'Silver' and
                          "EMAIL" = 'JSILVER' and
                          "PHONE_NUMBER" = '555122122' and
                          "HIRE_DATE" = TO_DATE('10-jan-2012 13:41:03',
                          'dd-mon-yyyy hh24:mi:ss') and
                          "JOB_ID" = 'SH_CLERK' and
                          "SALARY" = 110000 and
                          "COMMISSION_PCT" = .05 and
                          "MANAGER_ID" = 105 and
                          "DEPARTMENT_ID" = 50 and
                          ROWID = 'AAAHSkAABAAAY6rAAP'; 
   HR     1.11.1476    commit;

6. End the LogMiner session.

   EXECUTE DBMS_LOGMNR.END_LOGMNR();

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();

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();

In the previous two examples, rows were filtered by specifying a timestamp-based predicate (timestamp > '10-jan-2012 15:59:53') in the query. However, a more efficient way to filter out redo records based on timestamp values is by specifying the time range in the DBMS_LOGMNR.START_LOGMNR procedure call, as shown in this example.

1. Create a list of redo log files to mine.

   Suppose you want to mine redo log files generated since a given time. The following procedure creates a list of redo log files based on a specified time. The subsequent SQL EXECUTE statement calls the procedure and specifies the starting time as 2 p.m. on Jan-13-2012.

   --
   -- my_add_logfiles
   -- Add all archived logs generated after a specified start_time.
   --
   CREATE OR REPLACE PROCEDURE my_add_logfiles (in_start_time  IN DATE) AS
     CURSOR  c_log IS 
       SELECT NAME FROM V$ARCHIVED_LOG 
         WHERE FIRST_TIME >= in_start_time;
   
   count      pls_integer := 0;
   my_option  pls_integer := DBMS_LOGMNR.NEW;
   
   BEGIN
     FOR c_log_rec IN c_log
     LOOP
       DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => c_log_rec.name, 
                               OPTIONS => my_option);
       my_option := DBMS_LOGMNR.ADDFILE;
       DBMS_OUTPUT.PUT_LINE('Added logfile ' || c_log_rec.name);
     END LOOP;
   END;
   /
   
   EXECUTE my_add_logfiles(in_start_time => '13-jan-2012 14:00:00');

2. Query the V$LOGMNR_LOGS to see the list of redo log files.

   This example includes the size of the redo log files in the output.

   SELECT FILENAME name, LOW_TIME start_time, FILESIZE bytes 
       FROM V$LOGMNR_LOGS;
   
   NAME                                START_TIME            BYTES
   ----------------------------------- --------------------  ----------------
   /usr/orcl/arch1_310_482932022.dbf    13-jan-2012 14:02:35  23683584
   /usr/orcl/arch1_311_482932022.dbf    13-jan-2012 14:56:35  2564096
   /usr/orcl/arch1_312_482932022.dbf    13-jan-2012 15:10:43  23683584
   /usr/orcl/arch1_313_482932022.dbf    13-jan-2012 15:17:52  23683584
   /usr/orcl/arch1_314_482932022.dbf    13-jan-2012 15:23:10  23683584
   /usr/orcl/arch1_315_482932022.dbf    13-jan-2012 15:43:22  23683584
   /usr/orcl/arch1_316_482932022.dbf    13-jan-2012 16:03:10  23683584
   /usr/orcl/arch1_317_482932022.dbf    13-jan-2012 16:33:43  23683584
   /usr/orcl/arch1_318_482932022.dbf    13-jan-2012 17:23:10  23683584

3. Adjust the list of redo log files.

   Suppose you realize that you want to mine just the redo log files generated between 3 p.m. and 4 p.m.

   You could use the query predicate (timestamp > '13-jan-2012 15:00:00' and timestamp < '13-jan-2012 16:00:00') to accomplish this. However, the query predicate is evaluated on each row returned by LogMiner, and the internal mining engine does not filter rows based on the query predicate. Thus, although you only wanted to get rows out of redo log files arch1_311_482932022.dbf to arch1_315_482932022.dbf, your query would result in mining all redo log files registered to the LogMiner session.

   Furthermore, although you could use the query predicate and manually remove the redo log files that do not fall inside the time range of interest, the simplest solution is to specify the time range of interest in the DBMS_LOGMNR.START_LOGMNR procedure call.

   Although this does not change the list of redo log files, LogMiner will mine only those redo log files that fall in the time range specified.

   EXECUTE DBMS_LOGMNR.START_LOGMNR(-
      STARTTIME => '13-jan-2012 15:00:00', -
      ENDTIME   => '13-jan-2012 16:00:00', -
      OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
                   DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                   DBMS_LOGMNR.PRINT_PRETTY_SQL);

4. Query the V$LOGMNR_CONTENTS view.

   SELECT TIMESTAMP, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,

    SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'OE';
   
   TIMESTAMP              XID          SQL_REDO
   ---------------------  -----------  --------------------------------
   13-jan-2012 15:29:31   1.17.2376    update "OE"."PRODUCT_INFORMATION"
                                         set
                                           "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00')
                                         where
                                           "PRODUCT_ID" = 3399 and
                                           "WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00') and
                                           ROWID = 'AAAHTKAABAAAY9TAAE';
   13-jan-2012 15:29:34   1.17.2376      insert into "OE"."PRODUCT_TRACKING"
                                           values
                                           "PRODUCT_ID" = 3399,
                                           "MODIFIED_TIME" = TO_DATE('13-jan-2012 15:29:34', 
                                           'dd-mon-yyyy hh24:mi:ss'),
                                           "OLD_LIST_PRICE" = 815,
                                           "OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00');
   
   13-jan-2012 15:52:43   1.15.1756      update "OE"."PRODUCT_INFORMATION"
                                           set
                                             "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00')
                                           where
                                             "PRODUCT_ID" = 1768 and
                                             "WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00') and
                                             ROWID = 'AAAHTKAABAAAY9UAAB';
   
   13-jan-2012 15:52:43   1.15.1756      insert into "OE"."PRODUCT_TRACKING"
                                           values
                                           "PRODUCT_ID" = 1768,
                                           "MODIFIED_TIME" = TO_DATE('13-jan-2012 16:52:43', 
                                           'dd-mon-yyyy hh24:mi:ss'),
                                           "OLD_LIST_PRICE" = 715,
                                           "OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00');
   

5. End the LogMiner session.

   EXECUTE DBMS_LOGMNR.END_LOGMNR();

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.

   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 and PRINT_PRETTY_SQL 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 + -
                       );

4. Query the V$LOGMNR_LOGS view.

   This step shows that the DBMS_LOGMNR.START_LOGMNR procedure 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();

This example shows how to specify an SCN range of interest and mine the redo log files that satisfy that range. You can use LogMiner to see all committed DML statements whose effects have not yet been made permanent in the data files.

Note that in this example (unlike the other examples) it is not assumed that you have set the NLS_DATE_FORMAT parameter.

1. Determine the SCN of the last checkpoint taken.

   SELECT CHECKPOINT_CHANGE#, CURRENT_SCN FROM V$DATABASE;

   CHECKPOINT_CHANGE#  CURRENT_SCN
   ------------------  ---------------
             56453576         56454208

2. Start LogMiner.

   EXECUTE DBMS_LOGMNR.START_LOGMNR(-

      STARTSCN => 56453576, -
      ENDSCN   => 56454208, -
      OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
                  DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                  DBMS_LOGMNR.PRINT_PRETTY_SQL + -
                  );
   

3. Display the list of archived redo log files added by LogMiner.

   SELECT FILENAME name, LOW_SCN, NEXT_SCN FROM V$LOGMNR_LOGS;

   NAME                                           LOW_SCN   NEXT_SCN
   --------------------------------------------   --------  --------
   /usr/oracle/data/db1arch_1_215_482701534.dbf   56316771  56453579
   

   Note that the redo log file that LogMiner added does not contain the whole SCN range. LogMiner adds the rest of the SCN range contained in the online redo log files automatically, as needed during the query execution. Use the following query to determine whether the redo log file added is the latest archived redo log file produced.

   SELECT NAME FROM V$ARCHIVED_LOG 
      WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);
   
   NAME
   -------------------------------------------- 
   /usr/oracle/data/db1arch_1_215_482701534.dbf 

4. Query the V$LOGMNR_CONTENTS view for changes made to the user tables.

   The following query does not return the SET TRANSACTION READ WRITE and COMMIT statements associated with transaction 1.6.1911 because these statements do not have a segment owner (SEG_OWNER) associated with them.

   Note that the default NLS_DATE_FORMAT, 'DD-MON-RR', is used to display the column MODIFIED_TIME of type DATE.

   SELECT SCN, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) as XID, SQL_REDO 
       FROM V$LOGMNR_CONTENTS
       WHERE SEG_OWNER NOT IN ('SYS', 'SYSTEM');
   
   
   SCN        XID        SQL_REDO
   ---------- ---------- -------------
   56454198   1.6.1911   update "OE"."PRODUCT_INFORMATION"
                           set
                             "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00')
                           where
                             "PRODUCT_ID" = 2430 and
                             "WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00') and
                             ROWID = 'AAAHTKAABAAAY9AAAC';
   
   56454199   1.6.1911   insert into "OE"."PRODUCT_TRACKING"
                           values
                             "PRODUCT_ID" = 2430,
                             "MODIFIED_TIME" = TO_DATE('17-JAN-03', 'DD-MON-RR'),
                             "OLD_LIST_PRICE" = 175,
                             "OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00');
   
   56454204   1.6.1911    update "OE"."PRODUCT_INFORMATION"
                            set
                              "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00')
                            where
                              "PRODUCT_ID" = 2302 and
                              "WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00') and
                              ROWID = 'AAAHTKAABAAAY9QAAA';
   56454206   1.6.1911    insert into "OE"."PRODUCT_TRACKING"
                            values
                              "PRODUCT_ID" = 2302,
                              "MODIFIED_TIME" = TO_DATE('17-JAN-03', 'DD-MON-RR'),
                              "OLD_LIST_PRICE" = 150,
                              "OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00');

5. End the LogMiner session.

   EXECUTE DBMS_LOGMNR.END_LOGMNR();

To specify that a query not finishes until some future time occurs or SCN is reached, set either the ENDTIME or ENDSCN option in your call to the DBMS_LOGMNR.START_LOGMNR procedure to a time in the future or to an SCN value that has not yet been reached.

This examples assumes that you want to monitor all changes made to the table hr.employees from now until 5 hours from now, and that you are using the dictionary in the online catalog.

1. Start LogMiner.

   EXECUTE DBMS_LOGMNR.START_LOGMNR(-

      STARTTIME => SYSDATE, -
      ENDTIME   => SYSDATE + 5/24, -
      OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

2. Query the V$LOGMNR_CONTENTS view.

   This select operation will not complete until it encounters the first redo log file record that is generated after the time range of interest (5 hours from now). You can end the select operation prematurely by pressing Ctrl+C.

   This example specifies the SET ARRAYSIZE statement so that rows are displayed as they are entered in the redo log file. If you do not specify the SET ARRAYSIZE statement, then rows are not returned until the SQL internal buffer is full.

   SET ARRAYSIZE 1;
   SELECT USERNAME AS usr, SQL_REDO FROM V$LOGMNR_CONTENTS
      WHERE  SEG_OWNER = 'HR' AND TABLE_NAME = 'EMPLOYEES';

3. End the LogMiner session.

   EXECUTE DBMS_LOGMNR.END_LOGMNR();

Specifically:

• Multibyte CLOB support requires the database to run at a compatibility of 10.1 or higher.

• IOT support without LOBs and Overflows requires the database to run at a compatibility of 10.1 or higher.

• IOT support with LOB and Overflow requires the database to run at a compatibility of 10.2 or higher.

• TDE and TSE support require the database to run at a compatibility of 11.1 or higher.

• Basic compression and advanced row compression require the database to run at a compatibility of 11.1 or higher.

• Hybrid Columnar Compression support is dependent on the underlying storage system and requires the database to run at a compatibility of 11.2 or higher.