安全研究 >> 安全研究详情

运维日记|挖掘技术哪家强,logminer应称王

作者: 美创运维中心数据库服务团队发布日期: 01月07日

今日阳光甚好,应是天下太平。

然后,一个电话打破了平静。。。

小编心里动作如下:



‘XXX,客户数据库误删数据了,可咋整??’

’不慌不慌,慢慢说,delete 还是 truncate,drop?’

‘delete,但是近一个礼拜陆陆续续删除的,而且没有任何备份。‘

’哦?远程给我,我上来瞅瞅‘


遇到这种delete删除的情况,相对来说还比较好处理,小编心里稳如一坨老铁。



思路一:利用undo闪回查询


根据undo信息,利用undo数据的前镜像,类似一致性读取方法,可以把表置于一个删除前的时间点(或SCN),从而将数据找回。

例子如下:


1、查询对应时间点的表

select * from emp as of timestamp to_timestamp ('2019-11-05 08:00:00','YYYY-MM-DD HH:MI:SS');


2、可以选择直接创建一张新表插入查询出来的数据

create table test_tmp as select * from emp as of timestamp to_timestamp ( '2019-11-05 08:00:00','YYYY-MM-DD HH:MI:SS'  );


注意,这种方法受到undo_retention参数的限制


默认情况下,undo_retention的值为900s,即在删除数据后的900s之后,undo里面的数据会过期,但如果业务比较繁忙,undo表空间不足的情况下,即便之前的镜像没有过期,镜像数据还是会被新的数据覆盖。


如果查询表留在undo中的前镜像被覆盖的话,进行闪回查询操作的时候会有如下报错。

ORA-08180: no snapshot found based on specified time


遗憾的是,客户环境的undo_retention是900s,一个礼拜前的镜像早就被覆盖了,所以该思路pass。



思路二:logminer挖掘



oracle所有的dml操作都会记录在redo日志之中,所以只要归档文件在,那么之前delete的记录都会还在。


模拟步骤如下,测试库为linux-11g:


1、删除数据并切换归档

delete from scott.emp;

commit;

alter system switch logfile ;


2、找到相关归档日志

select t.THREAD#, t.SEQUENCE#, t.NAME

from v$archived_log t

where t.FIRST_TIME >=to_date ('2019-11-05 10:24:30', 'yyyy-mm-dd hh24:mi:ss')

and t.NEXT_TIME <=to_date ('2019-11-05 14:00:30', 'yyyy-mm-dd hh24:mi:ss');


   THREAD#  SEQUENCE#  NAME

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

1 2 /app_target/arch/1_2_1023532682.dbf

1 1 /app_target/arch/1_1_1023532682.dbf

1 3 /app_target/arch/1_3_1023532682.dbf


3、安装logminer安装包

SQL> 

@$ORACLE_HOME/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.


desc dbms_logmnr确认是否安装完成


4、添加日志

添加第一个日志

execute dbms_logmnr.add_logfile(logfilename=>'/app_target/easdb_dg/arch/1_2_1023532682.dbf',options=>dbms_logmnr.new);


继续添加

execute dbms_logmnr.add_logfile(logfilename=>'/app_target/easdb_dg/arch/1_1_1023532682.dbf',options=>dbms_logmnr.addfile);


execute dbms_logmnr.add_logfile(logfilename=>'/app_target/easdb_dg/arch/1_3_1023532682.dbf',options=>dbms_logmnr.addfile);


需要特别注意的是,继续添加OPTIONS选项是ADDFILE


5、开启 Logminer

execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);


6、查询 v$logmnr_contents 视图

注意要加条件,不然会有极其多系统表的记录


查看日志中具体的内容,可以看到,小编执行的具体命令是 delete from scott.emp;

在logminer视图中,他被转化成具体的一条一条的delete语句。


select sql_redo from v$logmnr_contents where SEG_OWNER='SCOTT';


delete from "SCOTT"."EMP" where "EMPNO" = '7369' and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR" = '7902' and "HIREDATE" = TO_DATE('17-DEC-80', 'DD-MON-RR') and "SAL" = '800' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAAVREAAE

AAAACXAAA';


delete from "SCOTT"."EMP" where "EMPNO" = '7499' and "ENAME" = 'ALLEN' and "JOB" = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE('20-FEB-81', 'DD-MON-RR') and "SAL" = '1600' and "COMM" = '300' and "DEPTNO" = '30' and ROWID = 'AAAVR

EAAEAAAACXAAB';

。。。

14 rows selected.


如果我们想直接插入还原的话,不需要将上面的查询的结果转化,贴心的logmnr提供了sql_undo这一列,sql_undo和sql_redo相对应,如下。

select sql_undo from v$logmnr_contents where SEG_OWNER='SCOTT';


insert into "SCOTT"."EMP" 

("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');


insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7499','ALLEN','SALESMAN','7698',TO_DATE('20-FEB-81', 'DD-MON-RR'),'1600','300','30');


insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7521','WARD','SALESMAN','7698',TO_DATE('22-FEB-81', 'DD-MON-RR'),'1250','500','30');

。。。

14 rows selected.   


注意,truncate和drop等ddl的语句的sql_undo都是空的,sql_redo也仅仅是操作的那一条语句,比如 drop table test;所以无法通过logmnr来恢复数据

   

最后,将 sql_undo 的语句提交给客户,至此,大功告成!




参考文献


Simple Steps to use Log Miner for finding high redo log generation (文档 ID 1504755.1)


服务热线:400-811-3777
Copyright ©2005-2020 杭州美创科技有限公司. All Rights Reserved. 浙ICP备12021012号-1