/* ******* 对于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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1153197/