某日，美创运维中心接到电话：****库误删数据了，怎么办？

’不慌不慌，慢慢说，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 的语句提交给客户，至此，大功告成！

美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。