Example 1: Finding All Modifications in the Last Archived Redo Log File

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