1.從Oracle10g開始,必須先啓用supplemental log,才能確保可以挖掘到所有的異動.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 16 INACTIVE
2 17 CURRENT
3 15 INACTIVE
SQL> alter system archive log current;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 16 INACTIVE
2 17 ACTIVE
3 18 CURRENT
2.進行DDL/DML範例操作
SQL> create table hr.d1 as select * from hr.employees;
SQL> select rowid,employee_id,department_id from hr.d1 where department_id=10;
ROWID EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAM9eAAEAAAASMAAC 200 10
SQL> select rowid,employee_id,department_id from hr.d1 where employee_id in (100,206);
ROWID EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAM9eAAEAAAASMAAI 206 110
AAAM9eAAEAAAASMAAJ 100 90
SQL> alter table hr.d1 add constraint d1_id_pk primary key(employee_id);
SQL> delete hr.d1 where department_id=10;
已刪除 1 個資料列.
SQL> commit;
SQL> update hr.d1 set salary=10000 where employee_id=206;
已更新 1 個資料列.
SQL> update hr.d1 set salary=12345 where employee_id=100;
已更新 1 個資料列.
SQL> commit;
SQL> alter system switch logfile ;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 19 CURRENT
2 17 ACTIVE
3 18 ACTIVE
3.開始進行日誌挖掘
SQL> select name from v$archived_log where sequence#=18; --archived logfile
NAME
--------------------------------------------------------------------------------
C:\ORCL_ARCHIVE1\ARC00018_0720742289.001
C:\FRA_DIR\ORCL\ARCHIVELOG\2010_06_29\O1_MF_1_18_62MS4CC1_.ARC
SQL> select member from v$logfile where group#=1; --current online logfile
MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
3.1 加入準備被挖掘的logfile,可以為online logfile或archived logfile
SQL> execute dbms_logmnr.add_logfile('C:\ORCL_ARCHIVE1\ARC00018_0720742289.001',dbms_logmnr.addfile);
SQL> execute dbms_logmnr.add_logfile('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG',dbms_logmnr.addfile);
3.2 開始挖掘日誌,挖掘結果將顯示在v$logmnr_contents
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
--因為redo entry所記錄的內容是object id,所以必須透過data dictionary將object id與user id轉換為object name與username
--dbms_logmnr.dict_from_online_catalog表示直接使用目前的dictionary進行轉換
3.3 查詢v$logmnr_contents內容
SQL> select count(*) from v$logmnr_contents; --資料來源就是剛剛加入的online/archived logfile
COUNT(*)
----------
1645
SQL> desc v$logmnr_contents
名稱 空值? 類型
----------------------------------------- -------- ----------------------------
SCN NUMBER
CSCN NUMBER
TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
THREAD# NUMBER
LOG_ID NUMBER
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJD# NUMBER
SEG_OWNER VARCHAR2(32)
SEG_NAME VARCHAR2(256)
TABLE_NAME VARCHAR2(32)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(18)
SESSION# NUMBER
SERIAL# NUMBER
USERNAME VARCHAR2(30)
SESSION_INFO VARCHAR2(4000)
TX_NAME VARCHAR2(256)
ROLLBACK NUMBER
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SEQUENCE# NUMBER
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
REDO_VALUE NUMBER
UNDO_VALUE NUMBER
SQL_COLUMN_TYPE VARCHAR2(30)
SQL_COLUMN_NAME VARCHAR2(30)
REDO_LENGTH NUMBER
REDO_OFFSET NUMBER
UNDO_LENGTH NUMBER
UNDO_OFFSET NUMBER
DATA_OBJV# NUMBER
SAFE_RESUME_SCN NUMBER
XID RAW(8)
PXID RAW(8)
AUDIT_SESSIONID NUMBER
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
已更改階段作業.
SQL> select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents
2 where seg_owner='HR' and table_name='D1' and operation != 'DIRECT INSERT';
--將create table as select所產生的INSERT操作過濾掉(operation != 'DIRECT INSERT')
TIMESTAMP OPERATION
------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
2010-06-29:20:16:01 DDL
create table hr.d1 as select * from hr.employees;
2010-06-29:20:17:15 DDL
alter table hr.d1 add constraint d1_id_pk primary key(employee_id);
TIMESTAMP OPERATION
------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
2010-06-29:20:17:23 DELETE
delete from "HR"."D1" where "EMPLOYEE_ID" = '200' and "FIRST_NAME" = 'Jennifer'
and "LAST_NAME" = 'Whalen' and "EMAIL" = 'JWHALEN' and "PHONE_NUMBER" = '515.123
.4444' and "HIRE_DATE" = TO_DATE('1987-09-17:00:00:00', 'YYYY-MM-DD:HH24:MI:SS')
and "JOB_ID" = 'AD_ASST' and "SALARY" = '4400' and "COMMISSION_PCT" IS NULL and
"MANAGER_ID" = '101' and "DEPARTMENT_ID" = '10' and ROWID = 'AAAM9eAAEAAAASMAAC
';
insert into "HR"."D1"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMB
ER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID")
values ('200','Jennifer','Whalen','JWHALEN','515.123.4444',TO_DATE('1987-09-17:
00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),'AD_ASST','4400',NULL,'101','10');
2010-06-29:20:17:31 UPDATE
update "HR"."D1" set "SALARY" = '10000' where "EMPLOYEE_ID" = '206' and "SALARY"
= '8300' and ROWID = 'AAAM9eAAEAAAASMAAI';
update "HR"."D1" set "SALARY" = '8300' where "EMPLOYEE_ID" = '206' and "SALARY"
= '10000' and ROWID = 'AAAM9eAAEAAAASMAAI';
2010-06-29:20:17:38 UPDATE
update "HR"."D1" set "SALARY" = '12345' where "EMPLOYEE_ID" = '100' and "SALARY"
= '24000' and ROWID = 'AAAM9eAAEAAAASMAAJ';
update "HR"."D1" set "SALARY" = '24000' where "EMPLOYEE_ID" = '100' and "SALARY"
= '12345' and ROWID = 'AAAM9eAAEAAAASMAAJ';
3.4 結束日誌挖掘
SQL> execute dbms_logmnr.end_logmnr;
已順利完成 PL/SQL 程序.
SQL> select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents
2 where seg_owner='HR' and table_name='D1' and operation != 'DIRECT INSERT';
select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='HR' and table_name='D1' a
nd operation != 'DIRECT INSERT'
*
ERROR 在行 1:
ORA-01306: 必須在 v$logmnr_contents 選取之前啟動 dbms_logmnr.start_logmnr()
