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 Example 1, 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 Step 1), 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, 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();

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.

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

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 can be contained in more than one redo log file, determine which redo log files contain the start and end of the data dictionary. To do this, 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 Example 4 and Example 5, you saw how to filter rows 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. To see the list of redo log files, query the V$LOGMNR_LOGS view.

   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 can use the query predicate (timestamp > '13-jan-2012 15:00:00' and timestamp < '13-jan-2012 16:00:00') to accomplish this goal. 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();

Suppose you want to determine all the changes that the user joedevo has made to the database in a specific time range. To perform this task, you can use LogMiner:

1. Connect to the database.

2. Create the LogMiner dictionary file.

   To use LogMiner to analyze joedevo's data, you must either create a LogMiner dictionary file before any table definition changes are made to tables that joedevo uses, or use the online catalog at LogMiner startup. This example uses a LogMiner dictionary that has been extracted to the redo log files.

3. Add redo log files.

   Assume that joedevo has made some changes to the database. You can now specify the names of the redo log files that you want to analyze, as follows:

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME => 'log1orc1.ora', -
      OPTIONS => DBMS_LOGMNR.NEW);
   

   If desired, add additional redo log files, as follows:

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME => 'log2orc1.ora', -
      OPTIONS => DBMS_LOGMNR.ADDFILE);
   

4. Start LogMiner and limit the search to the specified time range:

   EXECUTE DBMS_LOGMNR.START_LOGMNR( -
      DICTFILENAME => 'orcldict.ora', -
      STARTTIME => TO_DATE('01-Jan-1998 08:30:00','DD-MON-YYYY HH:MI:SS'), -
      ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
   

5. Query the V$LOGMNR_CONTENTS view.

   At this point, the V$LOGMNR_CONTENTS view is available for queries. You decide to find all of the changes made by user joedevo to the salary table. Execute the following SELECT statement:

   SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS 
      WHERE USERNAME = 'joedevo' AND SEG_NAME = 'salary';
   

   For both the SQL_REDO and SQL_UNDO columns, two rows are returned (the format of the data display will be different on your screen). You discover that joedevo requested two operations: he deleted his old salary and then inserted a new, higher salary. You now have the data necessary to undo this operation.

   SQL_REDO                              SQL_UNDO
   --------                              --------
   delete from SALARY                    insert into SALARY(NAME, EMPNO, SAL)
   where EMPNO = 12345                    values ('JOEDEVO', 12345, 500)
   and NAME='JOEDEVO'
   and SAL=500;
   
   insert into SALARY(NAME, EMPNO, SAL)  delete from SALARY
   values('JOEDEVO',12345, 2500)         where EMPNO = 12345
                                         and NAME = 'JOEDEVO'
   2 rows selected                       and SAL = 2500;
   

6. End the LogMiner session.

   Use the DBMS_LOGMNR.END_LOGMNR procedure to finish the LogMiner session properly:

   DBMS_LOGMNR.END_LOGMNR( );

In this example, assume you manage a direct marketing database, and you want to determine how productive the customer contacts have been in generating revenue for a 2-week period in January. In this case, we assume that you have already created the LogMiner dictionary, and added the redo log files that you want to search. To identify those contacts, search your logs by the time range in January, as follows:

1. Start LogMiner and specify a range of times:

   EXECUTE DBMS_LOGMNR.START_LOGMNR( -
      STARTTIME => TO_DATE('07-Jan-2012 08:30:00','DD-MON-YYYY HH:MI:SS'), -
      ENDTIME => TO_DATE('21-Jan-2012 08:45:00','DD-MON-YYYY HH:MI:SS'), -
      DICTFILENAME => '/usr/local/dict.ora');
   

2. Query the

   V$LOGMNR_CONTENTS

   view to determine which tables were modified in the time range you specified, as shown in the following example. (This query filters out system tables that traditionally have a

   $

   in their name.)

   SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits FROM
      V$LOGMNR_CONTENTS WHERE SEG_NAME NOT LIKE '%$' GROUP BY
      SEG_OWNER, SEG_NAME ORDER BY Hits DESC;
   

3. The following data is displayed. (The format of your display can be different.)

   SEG_OWNER          SEG_NAME          Hits
   ---------          --------          ----
   CUST               ACCOUNT            384
   UNIV               EXECDONOR          325
   UNIV               DONOR              234
   UNIV               MEGADONOR           32
   HR                 EMPLOYEES           12
   SYS                DONOR               12
   

   The values in the

   Hits

   column show the number of times that the named table had an insert, delete, or update operation performed on it during the 2-week period specified in the query. In this example, the

   cust.account

   table was modified the most during the specified 2-week period, and the

   hr.employees

   and

   sys.donor

   tables were modified the least during the same time period.

4. End the LogMiner session.

   Use the

   DBMS_LOGMNR.END_LOGMNR

   procedure to finish the LogMiner session properly:

   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.