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

 

在进行不完全恢复案例演练之前，我们要先学会如何使用logminer工具。该工具主要对redo log 进行挖掘，找出在某个时间点所作的DDL 或DML 操作（包括：时间点、datablock scn 、sql语句）

1、对DML 分析

SQL> select * from scott.tb01;

ID

----------

1

2

3

3 rows selected.

SQL> delete from scott.tb01;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into scott.tb01 values(111);

1 row created.

SQL> insert into scott.tb01 values(222);

1 row created.

SQL> insert into scott.tb01 values(333);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.tb01;

ID

----------

111

222

333

3 rows selected.

（1）查看当前日志组

SQL> select * from v$log;

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

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

1          1          1  104857600          3 YES       INACTIVE          846224 2012-03-22 15:46:28

4          1          0  104857600          3 YES       UNUSED                 0

3          1          3  104857600          3 NO        CURRENT           847894 2012-03-22 16:30:10

2          1          2  104857600          3 YES       INACTIVE          846225 2012-03-22 15:47:06

SQL> alter system archive log current;——使当前日志组归档

System altered.

SQL> select * from v$log;

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

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

1          1          5  104857600          3 NO        CURRENT           849696 2012-03-22 17:36:01

4          1          4  104857600          3 YES       ACTIVE            849665 2012-03-22 17:34:34

3          1          3  104857600          3 YES       ACTIVE            847894 2012-03-22 16:30:10

2          1          2  104857600          3 YES       INACTIVE          846225 2012-03-22 15:47:06

4 rows selected.

*********************************以上接案例1***********************************

2、启用logmnr

（1）添加database补充日志

17:13:47 SQL> alter database add supplemental log data; ——不添加后面会出错

Database altered.

——查询日志（归档日志和当前日志）

SQL> select * from v$log;

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

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

1          1          5  104857600          3 NO        CURRENT           849696 2012-03-22 17:36:01

4          1          4  104857600          3 YES       ACTIVE            849665 2012-03-22 17:34:34

3          1          3  104857600          3 YES       ACTIVE            847894 2012-03-22 16:30:10

2          1          2  104857600          3 YES       INACTIVE          846225 2012-03-22 15:47:06

4 rows selected.

SQL> col member for a50;

SQL> select member from v$logfile;——当前日志

MEMBER

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

/disk3/oradata/anny/redo01a.log

/disk3/oradata/anny/redo03a.log

/disk3/oradata/anny/redo02a.log

/disk3/oradata/anny/redo04a.log

/disk1/oradata/anny/redo01b.log

/disk1/oradata/anny/redo02b.log

/disk1/oradata/anny/redo03b.log

/disk1/oradata/anny/redo04b.log

/disk2/oradata/anny/redo01c.log

/disk2/oradata/anny/redo02c.log

/disk2/oradata/anny/redo03c.log

/disk2/oradata/anny/redo04c.log

SQL> col name for a50

SQL> select name from v$archived_log;——查看归档日志信息

/disk1/arch/anny/arch_1_1_778514791.log

/disk1/arch/anny/arch_1_2_778514791.log

/disk1/arch/anny/arch_1_3_778514791.log

/disk1/arch/anny/arch_1_4_778514791.log

/disk1/arch/anny/arch_1_5_778514791.log

/disk1/arch/anny/arch_1_6_778514791.log

/disk1/arch/anny/arch_1_7_778514791.log

/disk1/arch/anny/arch_1_8_778514791.log

/disk1/arch/anny/arch_1_9_778514791.log

/disk1/arch/anny/arch_1_2_778607188.log

/disk1/arch/anny/arch_1_3_778607188.log

/disk1/arch/anny/arch_1_4_778607188.log

37 rows selected.

（2）添加日志，分析【添加的日志应该是切换前的日志，归档日志是最后一个日志】

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

（3）执行logmnr 分析

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

（4）查询分析结果

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

Session altered.

SQL> col username for a15

SQL> col sql_redo for a50

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TB01';

USERNAME               SCN TIMESTAMP           SQL_REDO

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

849589 2012-03-22 17:31:08 delete from "SCOTT"."TB01" where "ID" = '1' and RO

WID = 'AAACc0AAFAAAAAYAAA';

849589 2012-03-22 17:31:08 delete from "SCOTT"."TB01" where "ID" = '2' and RO

WID = 'AAACc0AAFAAAAAYAAB';

849589 2012-03-22 17:31:08 delete from "SCOTT"."TB01" where "ID" = '3' and RO

WID = 'AAACc0AAFAAAAAYAAC';

849599 2012-03-22 17:31:35 insert into "SCOTT"."TB01"("ID") values ('111');

849621 2012-03-22 17:32:41 insert into "SCOTT"."TB01"("ID") values ('222');

849623 2012-03-22 17:32:47 insert into "SCOTT"."TB01"("ID") values ('333');

6 rows selected.

（5）结束日志分析

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.