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.
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
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);
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.
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;
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;End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();