在进行不完全恢复案例演练之前，我们要先学会如何使用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.


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html