日誌挖掘-對於DML操作的挖掘

--新增最小補充日誌，可以確保日誌挖掘可以有足夠的資訊來支援chained row和各種儲存管理,例如簇表。

sys@TESTDB11>alter database add supplemental log data;

Database altered.

--日誌挖掘要藉助於dbms_logmnr包，下面是dbms_logmnr包的描述

sys@TESTDB11>desc dbms_logmnr

PROCEDURE ADD_LOGFILE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

LOGFILENAME VARCHAR2 IN

OPTIONS BINARY_INTEGER IN DEFAULT

FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_REDO_UNDO NUMBER IN DEFAULT

COLUMN_NAME VARCHAR2 IN DEFAULT

PROCEDURE END_LOGMNR

FUNCTION MINE_VALUE RETURNS VARCHAR2

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_REDO_UNDO NUMBER IN DEFAULT

COLUMN_NAME VARCHAR2 IN DEFAULT

PROCEDURE REMOVE_LOGFILE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

LOGFILENAME VARCHAR2 IN

PROCEDURE START_LOGMNR

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

STARTSCN NUMBER IN DEFAULT

ENDSCN NUMBER IN DEFAULT

STARTTIME DATE IN DEFAULT

ENDTIME DATE IN DEFAULT

DICTFILENAME VARCHAR2 IN DEFAULT

OPTIONS BINARY_INTEGER IN DEFAULT

-- 設定SQL*Plus顯示時間，目的是為了確定操作發生的確切時間

scott@TESTDB11>set time on

17:05:54 scott@TESTDB11>select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 810 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1610 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1260 500 30

7566 JONES MANAGER 7839 02-APR-81 2985 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1260 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2860 30

7782 CLARK MANAGER 7839 09-JUN-81 2460 10

7788 SCOTT ANALYST 7566 19-APR-87 3010 20

7839 KING PRESIDENT 17-NOV-81 5010 10

7844 TURNER SALESMAN 7698 08-SEP-81 1510 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1110 20

7900 JAMES CLERK 7698 03-DEC-81 960 30

7902 FORD ANALYST 7566 03-DEC-81 3010 20

7934 MILLER CLERK 7782 23-JAN-82 1310 10

14 rows selected.

--本意是刪除一條記錄，結果刪除了所有的行，並進行了提交。注意：提交的時間

17:06:20 scott@TESTDB11>delete from emp1;

14 rows deleted.

17:07:04 scott@TESTDB11>commit;

Commit complete.

sys@TESTDB11>alter system switch logfile;

System altered.

--修改會話預設的日期格式

sys@TESTDB11>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

--檢視所有的歸檔日誌，根據每個歸檔中記錄的最早操作，最晚操作的時間，刪除操作的提交時間，來確定需要挖掘的日誌

sys@TESTDB11>col name for a30

sys@TESTDB11>select sequence#, name, first_time, next_time from v$archived_log order by first_time;

SEQUENCE# NAME FIRST_TIME NEXT_TIME

---------- ------------------------------ ------------------- -------------------

81 /archive1/1_81_813665348.dbf 2013-08-11 06:08:11 2013-08-11 06:29:02

81 /archive2/1_81_813665348.dbf 2013-08-11 06:08:11 2013-08-11 06:29:02

82 /archive1/1_82_813665348.dbf 2013-08-11 06:29:02 2013-08-11 06:29:05

82 /archive2/1_82_813665348.dbf 2013-08-11 06:29:02 2013-08-11 06:29:05

83 /archive1/1_83_813665348.dbf 2013-08-11 06:29:05 2013-08-11 06:29:25

83 /archive2/1_83_813665348.dbf 2013-08-11 06:29:05 2013-08-11 06:29:25

84 /archive1/1_84_813665348.dbf 2013-08-11 06:29:25 2013-08-11 06:29:27

84 /archive2/1_84_813665348.dbf 2013-08-11 06:29:25 2013-08-11 06:29:27

85 /archive1/1_85_813665348.dbf 2013-08-11 06:29:27 2013-08-11 06:30:07

85 /archive2/1_85_813665348.dbf 2013-08-11 06:29:27 2013-08-11 06:30:07

86 /archive1/1_86_813665348.dbf 2013-08-11 06:30:07 2013-08-11 06:30:22

86 /archive2/1_86_813665348.dbf 2013-08-11 06:30:07 2013-08-11 06:30:22

87 /archive1/1_87_813665348.dbf 2013-08-11 06:30:22 2013-08-11 06:36:43

87 /archive2/1_87_813665348.dbf 2013-08-11 06:30:22 2013-08-11 06:36:43

88 /archive1/1_88_813665348.dbf 2013-08-11 06:36:43 2013-08-11 06:36:58

88 /archive2/1_88_813665348.dbf 2013-08-11 06:36:43 2013-08-11 06:36:58

89 /archive1/1_89_813665348.dbf 2013-08-11 06:36:58 2013-08-11 06:53:19

89 /archive2/1_89_813665348.dbf 2013-08-11 06:36:58 2013-08-11 06:53:19

90 /archive1/1_90_813665348.dbf 2013-08-11 06:53:19 2013-08-11 06:53:37

90 /archive2/1_90_813665348.dbf 2013-08-11 06:53:19 2013-08-11 06:53:37

91 /archive1/1_91_813665348.dbf 2013-08-11 06:53:37 2013-08-11 06:53:41

91 /archive2/1_91_813665348.dbf 2013-08-11 06:53:37 2013-08-11 06:53:41

92 /archive1/1_92_813665348.dbf 2013-08-11 06:53:41 2013-08-11 06:53:44

92 /archive2/1_92_813665348.dbf 2013-08-11 06:53:41 2013-08-11 06:53:44

93 /archive1/1_93_813665348.dbf 2013-08-11 06:53:44 2013-08-11 06:53:45

93 /archive2/1_93_813665348.dbf 2013-08-11 06:53:44 2013-08-11 06:53:45

94 /archive1/1_94_813665348.dbf 2013-08-11 06:53:45 2013-08-11 06:53:46

94 /archive2/1_94_813665348.dbf 2013-08-11 06:53:45 2013-08-11 06:53:46

95 /archive1/1_95_813665348.dbf 2013-08-11 06:53:46 2013-08-11 07:58:15

95 /archive2/1_95_813665348.dbf 2013-08-11 06:53:46 2013-08-11 07:58:15

96 /archive1/1_96_813665348.dbf 2013-08-11 07:58:15 2013-08-11 08:28:34

96 /archive2/1_96_813665348.dbf 2013-08-11 07:58:15 2013-08-11 08:28:34

97 /archive1/1_97_813665348.dbf 2013-08-11 08:28:34 2013-08-11 12:25:43

97 /archive2/1_97_813665348.dbf 2013-08-11 08:28:34 2013-08-11 12:25:43

98 /archive1/1_98_813665348.dbf 2013-08-11 12:25:43 2013-08-11 14:46:05

98 /archive2/1_98_813665348.dbf 2013-08-11 12:25:43 2013-08-11 14:46:05

99 /archive1/1_99_813665348.dbf 2013-08-11 14:46:05 2013-08-11 17:04:13

99 /archive2/1_99_813665348.dbf 2013-08-11 14:46:05 2013-08-11 17:04:13

100 /archive1/1_100_813665348.dbf 2013-08-11 17:04:13 2013-08-11 17:07:51

100 /archive2/1_100_813665348.dbf 2013-08-11 17:04:13 2013-08-11 17:07:51

40 rows selected.

--確定需要分析的歸檔日誌的範圍為序號為100的日誌

--新增挖掘的檔案

sys@TESTDB11>exec dbms_logmnr.add_logfile(logfilename=>'/archive1/1_100_813665348.dbf', options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

--再多新增一個未歸檔的當前日誌

sys@TESTDB11>select sequence#, group#, status from v$log;

SEQUENCE# GROUP# STATUS

---------- ---------- ----------------

100 1 INACTIVE

101 2 CURRENT

99 3 INACTIVE

sys@TESTDB11>col member for a50

sys@TESTDB11>select group#, member from v$logfile;

GROUP# MEMBER

---------- --------------------------------------------------

3 /u01/app/oracle/oradata/TestDB11/redo03.log

2 /u01/app/oracle/oradata/TestDB11/redo02.log

1 /u01/app/oracle/oradata/TestDB11/redo01.log

sys@TESTDB11>exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/TestDB11/redo02.log', options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

--開始挖掘

sys@TESTDB11>exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

--檢視挖掘之後的資訊

sys@TESTDB11>select scn, timestamp, sql_redo, sql_undo from v$logmnr_contents

2 where seg_owner='SCOTT' and seg_name = 'EMP1';

SCN TIMESTAMP SQL_REDO SQL_UNDO

---------- ------------------- ---------------------------------------- ----------------------------------------

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7369' and "ENAME" = 'SMITH' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'CLERK' and "MGR" = '7902' and "HIRE DEPTNO") values ('7369','SMITH','CLERK',

DATE" = TO_DATE('1980-12-17 00:00:00', ' '7902',TO_DATE('1980-12-17 00:00:00', 'y

yyyy-mm-dd hh24:mi:ss') and "SAL" = '810 yyy-mm-dd hh24:mi:ss'),'810',NULL,'20');

' and "COMM" IS NULL and "DEPTNO" = '20'

and ROWID = 'AAAUyWAAEAAAAIzAAA';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7499' and "ENAME" = 'ALLEN' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'SALESMAN' and "MGR" = '7698' and "H DEPTNO") values ('7499','ALLEN','SALESMA

IREDATE" = TO_DATE('1981-02-20 00:00:00' N','7698',TO_DATE('1981-02-20 00:00:00',

, 'yyyy-mm-dd hh24:mi:ss') and "SAL" = ' 'yyyy-mm-dd hh24:mi:ss'),'1610','300','

1610' and "COMM" = '300' and "DEPTNO" = 30');

'30' and ROWID = 'AAAUyWAAEAAAAIzAAB';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7521' and "ENAME" = 'WARD' and "JOB" E","JOB","MGR","HIREDATE","SAL","COMM","

= 'SALESMAN' and "MGR" = '7698' and "HI DEPTNO") values ('7521','WARD','SALESMAN

REDATE" = TO_DATE('1981-02-22 00:00:00', ','7698',TO_DATE('1981-02-22 00:00:00',

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '1 'yyyy-mm-dd hh24:mi:ss'),'1260','500','3

260' and "COMM" = '500' and "DEPTNO" = ' 0');

30' and ROWID = 'AAAUyWAAEAAAAIzAAC';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7566' and "ENAME" = 'JONES' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'MANAGER' and "MGR" = '7839' and "HI DEPTNO") values ('7566','JONES','MANAGER

REDATE" = TO_DATE('1981-04-02 00:00:00', ','7839',TO_DATE('1981-04-02 00:00:00',

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '2 'yyyy-mm-dd hh24:mi:ss'),'2985',NULL,'20

985' and "COMM" IS NULL and "DEPTNO" = ' ');

20' and ROWID = 'AAAUyWAAEAAAAIzAAD';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7654' and "ENAME" = 'MARTIN' and "JO E","JOB","MGR","HIREDATE","SAL","COMM","

B" = 'SALESMAN' and "MGR" = '7698' and " DEPTNO") values ('7654','MARTIN','SALESM

HIREDATE" = TO_DATE('1981-09-28 00:00:00 AN','7698',TO_DATE('1981-09-28 00:00:00'

', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = , 'yyyy-mm-dd hh24:mi:ss'),'1260','1400'

'1260' and "COMM" = '1400' and "DEPTNO" ,'30');

= '30' and ROWID = 'AAAUyWAAEAAAAIzAAE';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7698' and "ENAME" = 'BLAKE' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'MANAGER' and "MGR" = '7839' and "HI DEPTNO") values ('7698','BLAKE','MANAGER

REDATE" = TO_DATE('1981-05-01 00:00:00', ','7839',TO_DATE('1981-05-01 00:00:00',

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '2 'yyyy-mm-dd hh24:mi:ss'),'2860',NULL,'30

860' and "COMM" IS NULL and "DEPTNO" = ' ');

30' and ROWID = 'AAAUyWAAEAAAAIzAAF';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7782' and "ENAME" = 'CLARK' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'MANAGER' and "MGR" = '7839' and "HI DEPTNO") values ('7782','CLARK','MANAGER

REDATE" = TO_DATE('1981-06-09 00:00:00', ','7839',TO_DATE('1981-06-09 00:00:00',

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '2 'yyyy-mm-dd hh24:mi:ss'),'2460',NULL,'10

460' and "COMM" IS NULL and "DEPTNO" = ' ');

10' and ROWID = 'AAAUyWAAEAAAAIzAAG';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7788' and "ENAME" = 'SCOTT' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'ANALYST' and "MGR" = '7566' and "HI DEPTNO") values ('7788','SCOTT','ANALYST

REDATE" = TO_DATE('1987-04-19 00:00:00', ','7566',TO_DATE('1987-04-19 00:00:00',

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '3 'yyyy-mm-dd hh24:mi:ss'),'3010',NULL,'20

010' and "COMM" IS NULL and "DEPTNO" = ' ');

20' and ROWID = 'AAAUyWAAEAAAAIzAAH';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7839' and "ENAME" = 'KING' and "JOB" E","JOB","MGR","HIREDATE","SAL","COMM","

= 'PRESIDENT' and "MGR" IS NULL and "HI DEPTNO") values ('7839','KING','PRESIDEN

REDATE" = TO_DATE('1981-11-17 00:00:00', T',NULL,TO_DATE('1981-11-17 00:00:00', '

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '5 yyyy-mm-dd hh24:mi:ss'),'5010',NULL,'10'

010' and "COMM" IS NULL and "DEPTNO" = ' );

10' and ROWID = 'AAAUyWAAEAAAAIzAAI';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7844' and "ENAME" = 'TURNER' and "JO E","JOB","MGR","HIREDATE","SAL","COMM","

B" = 'SALESMAN' and "MGR" = '7698' and " DEPTNO") values ('7844','TURNER','SALESM

HIREDATE" = TO_DATE('1981-09-08 00:00:00 AN','7698',TO_DATE('1981-09-08 00:00:00'

', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = , 'yyyy-mm-dd hh24:mi:ss'),'1510','0','3

'1510' and "COMM" = '0' and "DEPTNO" = ' 0');

30' and ROWID = 'AAAUyWAAEAAAAIzAAJ';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7876' and "ENAME" = 'ADAMS' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'CLERK' and "MGR" = '7788' and "HIRE DEPTNO") values ('7876','ADAMS','CLERK',

DATE" = TO_DATE('1987-05-23 00:00:00', ' '7788',TO_DATE('1987-05-23 00:00:00', 'y

yyyy-mm-dd hh24:mi:ss') and "SAL" = '111 yyy-mm-dd hh24:mi:ss'),'1110',NULL,'20')

0' and "COMM" IS NULL and "DEPTNO" = '20 ;

' and ROWID = 'AAAUyWAAEAAAAIzAAK';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7900' and "ENAME" = 'JAMES' and "JOB E","JOB","MGR","HIREDATE","SAL","COMM","

" = 'CLERK' and "MGR" = '7698' and "HIRE DEPTNO") values ('7900','JAMES','CLERK',

DATE" = TO_DATE('1981-12-03 00:00:00', ' '7698',TO_DATE('1981-12-03 00:00:00', 'y

yyyy-mm-dd hh24:mi:ss') and "SAL" = '960 yyy-mm-dd hh24:mi:ss'),'960',NULL,'30');

' and "COMM" IS NULL and "DEPTNO" = '30'

and ROWID = 'AAAUyWAAEAAAAIzAAL';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

SCN TIMESTAMP SQL_REDO SQL_UNDO

---------- ------------------- ---------------------------------------- ----------------------------------------

= '7902' and "ENAME" = 'FORD' and "JOB" E","JOB","MGR","HIREDATE","SAL","COMM","

= 'ANALYST' and "MGR" = '7566' and "HIR DEPTNO") values ('7902','FORD','ANALYST'

EDATE" = TO_DATE('1981-12-03 00:00:00', ,'7566',TO_DATE('1981-12-03 00:00:00', '

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '30 yyyy-mm-dd hh24:mi:ss'),'3010',NULL,'20'

10' and "COMM" IS NULL and "DEPTNO" = '2 );

0' and ROWID = 'AAAUyWAAEAAAAIzAAM';

2600994 2013-08-11 17:07:05 delete from "SCOTT"."EMP1" where "EMPNO" insert into "SCOTT"."EMP1"("EMPNO","ENAM

= '7934' and "ENAME" = 'MILLER' and "JO E","JOB","MGR","HIREDATE","SAL","COMM","

B" = 'CLERK' and "MGR" = '7782' and "HIR DEPTNO") values ('7934','MILLER','CLERK'

EDATE" = TO_DATE('1982-01-23 00:00:00', ,'7782',TO_DATE('1982-01-23 00:00:00', '

'yyyy-mm-dd hh24:mi:ss') and "SAL" = '13 yyyy-mm-dd hh24:mi:ss'),'1310',NULL,'10'

10' and "COMM" IS NULL and "DEPTNO" = '1 );

0' and ROWID = 'AAAUyWAAEAAAAIzAAN';

14 rows selected.

--結束挖掘

sys@TESTDB11>exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1153196/，如需轉載，請註明出處，否則將追究法律責任。

日誌挖掘-對於DML操作的挖掘

相關文章