/* ******* 对于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.