データベースの変更履歴を確認する最も簡単な方法は、ソース・データベースでマイニングし、オンライン・カタログを使用してREDOログ・ファイルを変換する方法です。この例では、LogMinerを使用して最も簡単に分析を行う方法を示します。
この例では、最後にアーカイブされたREDOログ・ファイルをマイニングするとします。これは、最後にアーカイブされたREDOログ(データベースで生成)に含まれているすべての変更を検索します(データベースは、Oracle Real Application Clusters (Oracle RAC)データベースでないものとします)。
最後にアーカイブされたREDOログ・ファイルを判別します。
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
分析するREDOログ・ファイルのリストを指定します。この場合は、手順1の問合せで返されたREDOログ・ファイルです。
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', - OPTIONS => DBMS_LOGMNR.NEW);
LogMinerを起動し、使用するディクショナリを指定します。
EXECUTE DBMS_LOGMNR.START_LOGMNR( - OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
V$LOGMNR_CONTENTS
ビューを問い合せます。
4つのトランザクションがあることに注意してください(そのうちの2つは、分析するREDOログ・ファイル内でコミット済であり、2つはコミットされていません)。出力には、実行された順序でDML文が表示されるため、トランザクションは交互に配置されます。
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;
LogMinerセッションを終了します。
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();