一.背景

oracle中当使用delete误删数据需要恢复数据时，如果归档文件还在，就可以利用logminer进行恢复，因为数据库所有的DML的操作都会记录在redo日志中。

二.恢复步骤

2.1.确定DML时间点日志信息

--模拟这条数据是误删数据
SQL> delete scott.dept where deptno=90;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.

SQL> select t.thread#,t.sequence#,t.name
  2  from v$archived_log t
  3  where t.first_time >= to_date('2022-05-05 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4  and t.next_time <= to_date('2022-05-05 23:06:00','yyyy-mm-dd hh24:mi:ss');

   THREAD#  SEQUENCE#				NAME
---------------------------------------------------------------------------
	 1	   59
/data/app/oracle/fast_recovery_area/ORCL/archivelog/2022_05_05/o1_mf_1_59_k77stw
jh_.arc
	 1	   60
/data/app/oracle/fast_recovery_area/ORCL/archivelog/2022_05_05/o1_mf_1_60_k77svp
hw_.arc
SQL> 

2.2.安装logminer安装包，默认系统自带该安装包

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql

Package created.


Grant succeeded.


Synonym created.

SQL> 

2.3.添加挖掘日志

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/data/app/oracle/fast_recovery_area/ORCL/archivelog/2022_05_05/o1_mf_1_59_k77stwjh_.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/data/app/oracle/fast_recovery_area/ORCL/archivelog/2022_05_05/o1_mf_1_60_k77svphw_.arc',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL>

注意：第一个添加日志选项是new，后续添加选项是addfile。

2.4.开启logminer

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

2.5.查询v$logmnr_contents视图来挖掘信息

--sql_redo  用于记录当时DML的操作记录
SQL> select sql_redo from v$logmnr_contents where seg_owner='SCOTT';
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delete from "SCOTT"."DEPT" where "DEPTNO" = '90' and "DNAME" = 'xue' and "LOC" = 'bai' and ROWID = 'AAAVRCAAEAAAACDAAA';

--sql_undo  可以使用sql_undo进行还原操作
SQL> select sql_undo from v$logmnr_contents where seg_owner='SCOTT';
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('90','xue','bai');

SQL> 

三.总结

3.1.恢复数据

当我们DML误操作时，如果归档还在，可以利用logminer来挖掘日志，利用sql_undo来恢复数据。

3.2.分析归档内容

同样，当我们发现归档量快速增长的时候，我们也可以通过logminer来挖掘归档，看到底是什么操作。

3.3.利用undo闪回查询

SQL> delete scott.emp;
14 rows deleted.
SQL> commit;
Commit complete.

SQL> select * from scott.emp as of timestamp to_timestamp('2022-05-05 23:30:00','YYYY-MM-DD HH24:MI:SS');

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

14 rows selected.

SQL> 

但是此方法受限于undo_retention的配置，默认值为900秒，即在删除数据900秒后，undo中的数据会过期。但如果业务比较繁忙，在undo表空间不足的情况下，即使镜像没有过期，数据还是会被覆盖。此时查询就会收到“ORA-08180:no snapshot found based on specified time”的错误信息。