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

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

小编心里动作如下：

‘XXX，客户数据库误删数据了，可咋整？？’

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

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

’哦？远程给我，我上来瞅瞅‘

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

根据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'  );

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

模拟步骤如下，测试库为linux-11g：

delete from scott.emp;

commit;

alter system switch logfile ;

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

SQL> 

@$ORACLE_HOME/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

desc dbms_logmnr确认是否安装完成

添加第一个日志

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

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

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

查看日志中具体的内容，可以看到，小编执行的具体命令是 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.   

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