日誌挖掘-對於DDL語句的挖掘

/* ******* 對於DDL語句的日誌挖掘 *****************************************************/

--使用的包為dbms_logmnr_d

sys@TESTDB11>desc dbms_logmnr_d

PROCEDURE BUILD

Argument Name Type In/Out Default?

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

DICTIONARY_FILENAME VARCHAR2 IN DEFAULT

DICTIONARY_LOCATION VARCHAR2 IN DEFAULT

OPTIONS NUMBER IN DEFAULT

PROCEDURE SET_TABLESPACE

Argument Name Type In/Out Default?

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

NEW_TABLESPACE VARCHAR2 IN

--方法1：需要配置一個初始化引數(指定匯出的資料字典資訊的檔案的路徑)，缺點：需要例項重新啟動

sys@TESTDB11>show parameter utl_file_dir

NAME TYPE VALUE

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

utl_file_dir string

--建立目錄

[oracle@S1011:/export/home/oracle]$ mkdir dict

--修改初始化引數

sys@TESTDB11>alter system set utl_file_dir = '/export/home/oracle/dict' scope = spfile;

System altered.

--需要重新啟庫

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup;

ORACLE instance started.

Total System Global Area 855982080 bytes

Fixed Size 2230792 bytes

Variable Size 641730040 bytes

Database Buffers 209715200 bytes

Redo Buffers 2306048 bytes

Database mounted.

Database opened.

--方法2：不需要重新啟庫，而將資料字典匯出到日誌檔案中（場景：物件已經在資料字典中不存在了; 是在生產庫外的庫上進行挖掘)

sys@TESTDB11>exec dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

PL/SQL procedure successfully completed.

--確定哪些日誌序列中包含logmnr需要的資料字典資訊

sys@TESTDB11>select sequence#, name, dictionary_begin, dictionary_end from v$archived_log order by first_change#;

SEQUENCE# NAME DIC DIC

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

81 /archive1/1_81_813665348.dbf NO NO

81 /archive2/1_81_813665348.dbf NO NO

82 /archive1/1_82_813665348.dbf NO NO

82 /archive2/1_82_813665348.dbf NO NO

83 /archive1/1_83_813665348.dbf NO NO

83 /archive2/1_83_813665348.dbf NO NO

84 /archive1/1_84_813665348.dbf NO NO

84 /archive2/1_84_813665348.dbf NO NO

85 /archive1/1_85_813665348.dbf NO NO

85 /archive2/1_85_813665348.dbf NO NO

86 /archive1/1_86_813665348.dbf NO NO

86 /archive2/1_86_813665348.dbf NO NO

87 /archive1/1_87_813665348.dbf NO NO

87 /archive2/1_87_813665348.dbf NO NO

88 /archive1/1_88_813665348.dbf NO NO

88 /archive2/1_88_813665348.dbf NO NO

89 /archive1/1_89_813665348.dbf NO NO

89 /archive2/1_89_813665348.dbf NO NO

90 /archive1/1_90_813665348.dbf NO NO

90 /archive2/1_90_813665348.dbf NO NO

91 /archive1/1_91_813665348.dbf NO NO

91 /archive2/1_91_813665348.dbf NO NO

92 /archive1/1_92_813665348.dbf NO NO

92 /archive2/1_92_813665348.dbf NO NO

93 /archive1/1_93_813665348.dbf NO NO

93 /archive2/1_93_813665348.dbf NO NO

94 /archive1/1_94_813665348.dbf NO NO

94 /archive2/1_94_813665348.dbf NO NO

95 /archive1/1_95_813665348.dbf NO NO

95 /archive2/1_95_813665348.dbf NO NO

96 /archive1/1_96_813665348.dbf NO NO

96 /archive2/1_96_813665348.dbf NO NO

97 /archive1/1_97_813665348.dbf NO NO

97 /archive2/1_97_813665348.dbf NO NO

98 /archive1/1_98_813665348.dbf NO NO

98 /archive2/1_98_813665348.dbf NO NO

99 /archive1/1_99_813665348.dbf NO NO

99 /archive2/1_99_813665348.dbf NO NO

100 /archive1/1_100_813665348.dbf NO NO

100 /archive2/1_100_813665348.dbf NO NO

101 /archive1/1_101_813665348.dbf NO NO

101 /archive2/1_101_813665348.dbf NO NO

102 /archive1/1_102_813665348.dbf YES YES

102 /archive2/1_102_813665348.dbf YES YES

44 rows selected.

--切換日誌

sys@TESTDB11>alter system switch logfile;

System altered.

--刪除表，確定DDL操作結束的時間

18:46:36 scott@TESTDB11>drop table emp1;

18:46:43 scott@TESTDB11>

Table dropped.

--切日誌2次，為了是讓日誌歸檔

sys@TESTDB11>alter system switch logfile;

System altered.

sys@TESTDB11> /

System altered.

--檢視挖掘時需要的日誌

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

SEQUENCE# NAME DIC DIC FIRST_TIME NEXT_TIME

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

101 /archive1/1_101_813665348.dbf NO NO 2013-08-11 17:07:51 2013-08-11 18:37:13

101 /archive2/1_101_813665348.dbf NO NO 2013-08-11 17:07:51 2013-08-11 18:37:13

102 /archive1/1_102_813665348.dbf YES YES 2013-08-11 18:37:13 2013-08-11 18:37:18

102 /archive2/1_102_813665348.dbf YES YES 2013-08-11 18:37:13 2013-08-11 18:37:18

103 /archive1/1_103_813665348.dbf NO NO 2013-08-11 18:37:18 2013-08-11 18:45:23

103 /archive2/1_103_813665348.dbf NO NO 2013-08-11 18:37:18 2013-08-11 18:45:23

104 /archive1/1_104_813665348.dbf NO NO 2013-08-11 18:45:23 2013-08-11 18:45:37

104 /archive2/1_104_813665348.dbf NO NO 2013-08-11 18:45:23 2013-08-11 18:45:37

105 /archive1/1_105_813665348.dbf NO NO 2013-08-11 18:45:37 2013-08-11 18:45:39

105 /archive2/1_105_813665348.dbf NO NO 2013-08-11 18:45:37 2013-08-11 18:45:39

106 /archive1/1_106_813665348.dbf NO NO 2013-08-11 18:45:39 2013-08-11 18:47:29

106 /archive2/1_106_813665348.dbf NO NO 2013-08-11 18:45:39 2013-08-11 18:47:29

107 /archive1/1_107_813665348.dbf NO NO 2013-08-11 18:47:29 2013-08-11 18:53:26

107 /archive2/1_107_813665348.dbf NO NO 2013-08-11 18:47:29 2013-08-11 18:53:26

108 /archive1/1_108_813665348.dbf NO NO 2013-08-11 18:53:26 2013-08-11 19:01:13

108 /archive2/1_108_813665348.dbf NO NO 2013-08-11 18:53:26 2013-08-11 19:01:13

109 /archive1/1_109_813665348.dbf NO NO 2013-08-11 19:01:13 2013-08-11 19:01:23

109 /archive2/1_109_813665348.dbf NO NO 2013-08-11 19:01:13 2013-08-11 19:01:23

58 rows selected.

--新增挖掘檔案

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

--開始挖掘

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

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

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

2607283 2013-08-11 18:46:43 ALTER TABLE "SCOTT"."EMP1" RENAME TO "BI

N$47WerktYBe/gRAgAJzxnug==$0" ;

2607286 2013-08-11 18:46:43 drop table emp1 AS "BIN$47WerktYBe/gRAgA

Jzxnug==$0" ;

--結束挖掘

sys@TESTDB11>exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

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

日誌挖掘-對於DDL語句的挖掘

相關文章