使用logminer恢复delete的数据

最近有个客户需要恢复3个月之前delete的部分数据，由于没有备份了，所以使用logminer来恢复，先来做一个练习，等实际操作了再来分享。

参考文档：

https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019         ---官方文档介绍的，基础请看这里

https://blog.csdn.net/yes_is_ok/article/details/79296614

https://blog.csdn.net/iteye_16695/article/details/82328251

以下是模拟部分：

rac 11g redhat7

connect scott/scott
create table tab1 (id number, name varchar2(20));
begin
for i in 1..100 loop
insert into TAB1 values (i, 'test'||TO_CHAR (i));
end loop;
commit;
end;
/

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
12-MAY-2020 16:39:55
alter system switch logfile;
alter system switch logfile;

delete from tab1;
commit;

alter system switch logfile;
alter system switch logfile;
select sysdate from dual;
12-MAY-2020 16:41:43

1.确定删除的表名和用户
select owner,object_name,object_id,object_type,status from dba_objects where object_name='TAB1';
SQL> SQL> SQL>
SQL> select owner,object_name,object_id,object_type,status from dba_objects where object_name='TAB1';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID OBJECT_TYPE STATUS
---------- ------------------- -------
SCOTT
TAB1
88678 TABLE VALID
2.确定删除的大概时间，时间越精确越好，但是要在这个范围内。
12-MAY-2020 16:39:55----12-MAY-2020 16:41:43

3.开始
show parameter utl_file_dir
alter system set utl_file_dir='/u01/app/oracle/data' scope=spfile;
重启生效
srvctl stop database -d orcl11g
srvctl start database -d orcl11g
show parameter utl_file_dir

EXECUTE dbms_logmnr_d.build('dictionary.ora','/u01/app/oracle/data'); --------------注意最后没有/

按照上面提供的时间确定所需的归档：
select name from v$archived_log where dest_id=1 and first_time >=to_date('2020-MAY-12 16:39:55','yyyy-mm-dd hh24:mi:ss') and first_time <= to_date('2020-MAY-12 16:41:43','yyyy-mm-dd hh24:mi:ss');
+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_11.296.1040229645
+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_12.297.1040229687
+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_13.298.1040229691
+FRA/orcl11g/archivelog/2020_05_12/thread_2_seq_7.299.1040229693
+FRA/orcl11g/archivelog/2020_05_12/thread_2_seq_8.300.1040230101
+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_14.301.1040230159

execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_11.296.1040229645',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_12.297.1040229687',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_13.298.1040229691',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl11g/archivelog/2020_05_12/thread_2_seq_7.299.1040229693',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl11g/archivelog/2020_05_12/thread_2_seq_8.300.1040230101',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl11g/archivelog/2020_05_12/thread_1_seq_14.301.1040230159',options=>dbms_logmnr.addfile);

execute dbms_logmnr.start_logmnr(dictfilename=>'//u01/app/oracle/data/dictionary.ora');

查询结果
select count(*) from v$logmnr_contents;
create table mycontents as select * from v$logmnr_contents nologging;

set heading off feedback off
spool myinsert.sql

SELECT sql_undo
FROM mycontents
WHERE seg_owner = 'SCOTT' AND
table_name = 'TAB1' and
operation = 'DELETE';

spool off

退出logmnr
execute dbms_logmnr.end_logmnr();