LogMiner日志挖掘技术在有些时候可以被比作是命悬一线的救命稻草,比方说误删除数据(如delete执行不当),可以通过LogMiner挖掘日志文件将这些数据补插回来

使用LogMiner预览:

o LogMiner的原理就是找出日志文件(redo file),所对应的UNDO.如你新增1W行数据,在redo里以insert形式体现,对应的undo即为delete
o LogMiner所有操作要在单个会话里执行
o LogMiner是挖掘在线日志或归档日志,因此最好要知道误操作的具体(精确更好)时间,这样能够尽可能少的挖日志,恢复时间将更短
o LogMiner挖掘之后的数据将保存在V$LOGMNR_CONTENTS.但需要注意一点,它的数据是每次查询时候才去读取的,因此涉及的日志文件多的时候,查询将非常慢.因此建议将视图内容用CATS保存起来,便于后来查询
o 源库的日志文件可以在源库本地做挖掘,也可以在其他机器做挖掘,但有版本和系统要求: 目标操作系统要采用同一字节编码顺序(ENDIAN_FORMAT);数据库版本大于或等于源数据库版本,且字符集相同
o 所有计划纳入挖掘的日志,需要来自同一数据库,且基于同一个RESETLOGS SCN
o 既然LogMiner可以分析日志,那么就可以用于统计哪些表被增删改查最多,可以更深入地了解自身的应用和数据
o LogMiner可以基于time/SCN进行挖掘,精确
o 使用挖掘技术的用户需要被授权角色或权限: SELECT ANY TRANSACTION,EXECUTE_CATALOG_ROLE
o 如果表被误truncate或误drop,LogMiner恢复不了,可以通过ODU/DUL/PRM/AUL工具来恢复

简易的实验过程

以下模拟delete误删除全表的数据,如果通过LogMiner挖日志将数据找回

?

1. 启用数据库的Supplemental Logging

<span style="font-size:14px;">SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;</span>

LogMiner需要最小级别的Supplemental Logging

?

2. 准备测试表和测试数据

SQL> create table t1 (a number,b char(2));
SQL> create table t2 (c number,d char(2));
SQL> insert into t1 values(1,'r1');
SQL> insert into t1 values(2,'r2');
SQL> insert into t1 values(3,'r3');
SQL> insert into t2 values(1,'t2');
SQL> commit;        
SQL> SELECT * FROM T1;
 
         A B
---------- --
         1 r1
         2 r2
         3 r3
SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /home/oracle/arch
Oldest online log sequence     57
Next log sequence to archive   59
Current log sequence	       59  <<<<<<====
SQL> alter system archive log current;

?

3. 添加日志至挖掘列表并开始挖掘

?

<p>SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/home/oracle/arch/1_59_847657195.dbf',OPTIONS => DBMS_LOGMNR.NEW);</p><p>PL/SQL procedure successfully completed</p><p>SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
?
PL/SQL procedure successfully completed</p>

?

每次添加或移除日志到列表都需要重新执行START_LOGMNR
?

查询视图V$LOGMNR_CONTENTS

SQL> select scn,?????? timestamp,?????? (xidusn || '.' || xidslt || '.' || xidsqn) as xid,?????? info,?????? seg_owner,?????? seg_name,?????? operation,?????? sql_redo,?????? sql_undo
? from v$logmnr_contents
?where seg_name in ('T1','T2')
/

       SCN TIMESTAMP   XID               SEG_OWNER      SEG_NAME   OPERATION      SQL_REDO                                               SQL_UNDO
---------- ----------- ----------------- -------------- ---------- -------------- ------------------------------------------------------ --------------------------------------------------------------------------------
   1212893 2015/1/10 1 4.1.550           SYS            T1         DDL            create table t1 (a number,b char(2));                  
   1212907 2015/1/10 1 10.12.537         SYS            T2         DDL            create table t2 (c number,d char(2));                  
   1212913 2015/1/10 1 5.40.556          SYS            T1         INSERT         insert into "SYS"."T1"("A","B") values ('1','r1');     delete from "SYS"."T1" where "A" = '1' and "B" = 'r1' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556          SYS            T1         INSERT         insert into "SYS"."T1"("A","B") values ('2','r2');     delete from "SYS"."T1" where "A" = '2' and "B" = 'r2' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556          SYS            T1         INSERT         insert into "SYS"."T1"("A","B") values ('3','r3');     delete from "SYS"."T1" where "A" = '3' and "B" = 'r3' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556          SYS            T2         INSERT         insert into "SYS"."T2"("C","D") values ('1','t2');     delete from "SYS"."T2" where "C" = '1' and "D" = 't2' and ROWID = 'AAANBTAABAAAP

?

删除测试表数据

SQL> DELETE FROM T1;<<<=====删除全表数据 
3 rows deleted
 
SQL> COMMIT;
 
Commit complete
 
SQL> SELECT * FROM T1;
 
         A B
---------- --


SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
 
System altered

?

新增日志列表,重新开始挖掘

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/home/oracle/arch/1_60_847657195.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
 
PL/SQL procedure successfully completed

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
 
PL/SQL procedure successfully completed

?

查询V$LOGMNR_CONTENTS

select scn,timestamp,(xidusn || '.' || xidslt || '.' || xidsqn) as xid,info,seg_owner,seg_name,operation,sql_redo,sql_undo
  from v$logmnr_contents
 where seg_name in ('T1','T2')
/

SCN TIMESTAMP   XID            SEG_OWNER        SEG_NAME    OPERATION     SQL_REDO                                                                         SQL_UNDO
---------- ----------- -------------- ---------------- ----------- ------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
   1212893 2015/1/10 1 4.1.550        SYS              T1          DDL           create table t1 (a number,b char(2));                                            
   1212907 2015/1/10 1 10.12.537      SYS              T2          DDL           create table t2 (c number,d char(2));                                            
   1212913 2015/1/10 1 5.40.556       SYS              T1          INSERT        insert into "SYS"."T1"("A",'r1');                               delete from "SYS"."T1" where "A" = '1' and "B" = 'r1' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556       SYS              T1          INSERT        insert into "SYS"."T1"("A",'r2');                               delete from "SYS"."T1" where "A" = '2' and "B" = 'r2' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556       SYS              T1          INSERT        insert into "SYS"."T1"("A",'r3');                               delete from "SYS"."T1" where "A" = '3' and "B" = 'r3' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556       SYS              T2          INSERT        insert into "SYS"."T2"("C",'t2');                               delete from "SYS"."T2" where "C" = '1' and "D" = 't2' and ROWID = 'AAANBTAABAAAP
   1213003 2015/1/10 1 5.47.556       SYS              T1          DELETE        delete from "SYS"."T1" where "A" = '1' and "B" = 'r1' and ROWID = 'AAANBSAABAAAP insert into "SYS"."T1"("A",'r1');
   1213003 2015/1/10 1 5.47.556       SYS              T1          DELETE        delete from "SYS"."T1" where "A" = '2' and "B" = 'r2' and ROWID = 'AAANBSAABAAAP insert into "SYS"."T1"("A",'r2');
   1213003 2015/1/10 1 5.47.556       SYS              T1          DELETE        delete from "SYS"."T1" where "A" = '3' and "B" = 'r3' and ROWID = 'AAANBSAABAAAP insert into "SYS"."T1"("A",'r3');
 
9 rows selected

?

执行SQL_UNDO字段的SQL即可

SQL> insert into "SYS"."T1"("A",'r1');
 
1 row inserted
SQL> insert into "SYS"."T1"("A",'r2');
 
1 row inserted
SQL> insert into "SYS"."T1"("A",'r3');
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> 
SQL> select * from t1;
 
         A B
---------- --
         1 r1
         2 r2
         3 r3

至此,被误删除的数据就找回来了.

关闭LogMiner

SQL> EXECUTE DBMS_LOGMNR.end_logmnr;
 
PL/SQL procedure successfully completed