1、利用undo闪回查询
根据undo信息,利用前镜像,可以把表置于一个删除前的时间点或SCN,从而找回数据
SQL> select *from emp as of timestamp to_timestamp('2023-10-15 00:00:00','yyyy-mm-dd hh24:mi:ss');
但此办法受限于undo_retention的配置
2、logminer挖掘
数据库所有DML的操作都会记录在redo日志中,只要归档文件还存在,那么所有DML的记录都可以找回
a、确定DML时间点日志信息,命令如下:
select t.THREAD#,t.SEQUENCE#,t.NAME from v$archived_log t where t.FIRST_TIME > to_date('2023-10-15 21:00:00','yyyy-mm-dd hh24:mi:ss') and
t.NEXT_TIME <= to_date('2023-10-15 21:20:00','yyyy-mm-dd hh24:mi:ss');
THREAD# SEQUENCE# NAME
---------- ---------- --------------------------------------------------------------------------------
1 44 /u01/app/oracle/product/19c/db_1/dbs/arch1_44_1128688004.dbf
1 45 /u01/app/oracle/product/19c/db_1/dbs/arch1_45_1128688004.dbf
1 46 /u01/app/oracle/product/19c/db_1/dbs/arch1_46_1128688004.dbf
b、安装logminer安装包,默认系统自带该安装包:
SQL> @?$ORACLE_HOME/rdbms/admin/dbmslm.sql
Session altered.
Package created.
Grant succeeded.
Synonym created.
c、添加挖掘日志,添加命令如下:
SQL> execute dbms_logmnr.add_logfile(LogFileName =>'/u01/app/oracle/product/19c/db_1/dbs/arch1_44_1128688004.dbf',options=>dbms_logmnr.NEW);
SQL> execute dbms_logmnr.add_logfile(LogFileName =>'/u01/app/oracle/product/19c/db_1/dbs/arch1_45_1128688004.dbf',options=>dbms_logmnr.addfile);
SQL> execute dbms_logmnr.add_logfile(LogFileName =>'/u01/app/oracle/product/19c/db_1/dbs/arch1_46_1128688004.dbf',options=>dbms_logmnr.addfile);
d、开启logminer,命令如下:
SQL> execute dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed
e、查询v$logmnr_contents视图获取挖掘信息:
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS where seg_owner='SCOTT';
<!--sql_redo用于记录当时DML的操作记录-->
SELECT SQL_UNDO FROM V$LOGMNR_CONTENTS where seg_owner='SCOTT';
<!--若是误操作回退,则可以使用sql_undo,执行还原操作-->
最终,我们可以根据sql_undo进行DML误操作恢复。