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

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

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. 添加日志至挖掘列表并开始挖掘

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

每次添加或移除日志到列表都需要重新执行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;

LogMiner救命稻草_找回误删除数据(一)