不完全恢复之logminer工具的使用（二）

 

3、对DDL 操作分析

SQL> conn scott/tiger

Connected.

SQL> select * from tb01;

ID

----------

1

2

3

SQL> drop table tb01 purge;

Table dropped.

SQL> create table tb01(id int) tablespace test;

Table created.

SQL> insert into tb01 values(1);

1 row created.

SQL> commit;

Commit complete.

 

（1）设置logmnr 参数，存放数据字典文件

[oracle@work ]$ mkdir /export/home/oracle/logmnr

SQL> show parameter utl

NAME                                 TYPE        VALUE

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

create_stored_outlines               string

utl_file_dir                         string

SQL> alter system set utl_file_dir='/export/home/oracle/logmnr' scope=spfile;

System altered.

05:11:48 SQL> startup force

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> show parameter utl

NAME                                 TYPE                              VALUE

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

create_stored_outlines               string

utl_file_dir                         string                            /export/home/oracle/logmnr

（2）建立数据字典文件dict.ora

SQL> execute dbms_logmnr_d.build('dict.ora','/export/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);

PL/SQL procedure successfully completed.

——查看日志信息

SQL> col name for a50

SQL> select name,sequence# from v$archived_log;

NAME                                                SEQUENCE#

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

/disk1/arch/anny/arch_1_3_778691360.log                     3

/disk1/arch/anny/arch_1_4_778691360.log                     4

/disk1/arch/anny/arch_1_5_778691360.log                     5

45 rows selected.

SQL> col status for a12

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS       FIRST_CHANGE# FIRST_TIME

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

1          1          6  104857600          3 NO        CURRENT             851187 2012-03-23 15:43:36

4          1          4  104857600          3 YES       INACTIVE            851181 2012-03-23 15:43:30

3          1          3  104857600          3 YES       INACTIVE            851178 2012-03-23 15:43:29

2          1          5  104857600          3 YES       INACTIVE            851183 2012-03-23 15:43:31

4 rows selected.

SQL> col member for a50

SQL> select group#,member from v$logfile;

GROUP# MEMBER

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

1 /disk3/oradata/anny/redo01a.log

3 /disk3/oradata/anny/redo03a.log

2 /disk3/oradata/anny/redo02a.log

4 /disk3/oradata/anny/redo04a.log

1 /disk1/oradata/anny/redo01b.log

2 /disk1/oradata/anny/redo02b.log

3 /disk1/oradata/anny/redo03b.log

4 /disk1/oradata/anny/redo04b.log

1 /disk2/oradata/anny/redo01c.log

2 /disk2/oradata/anny/redo02c.log

3 /disk2/oradata/anny/redo03c.log

4 /disk2/oradata/anny/redo04c.log

12 rows selected.

（3）添加日志分析

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk3/oradata/anny/redo01a.log',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/anny/arch_1_5_778691360.log',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

（4）执行分析

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/export/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);

PL/SQL procedure successfully completed.

（5）查看分析结果

05:23:33 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col username for a10

SQL> col sql_redo for a50

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents

2  where username='SCOTT' and lower(sql_redo) like '%table%';

USERNAME          SCN TIMESTAMP           SQL_REDO

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

SCOTT          851229 2012-03-23 15:45:22 drop table tb01 purge;

SCOTT          851264 2012-03-23 15:45:45 create table tb01(id int) tablespace test;

2 rows selected.

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

***********************************以上接案例2*****************************