利用logminer找回数据

/home/ahres/archilog

alter system set log_archive_dest_1='/home/ahres/archilog';
alter system set log_archive_dest_1='location=/home/ahres/archilog' scope=both;

alter system set utl_file_dir='/home/ahres/archilog' scope=spfile;

execute dbms_logmnr.add_logfile (LogFileName =>'/home/ahres/archilog/1_184_695305233.dbf',Options =>dbms_logmnr.new);

execute dbms_logmnr.add_logfile (LogFileName =>'/home/ahres/archilog/1_188_695305233.dbf',Options =>dbms_logmnr.addfile);

 

execute dbms_logmnr.add_logfile (LogFileName =>'/opt/oracle/app/oracle/OraHome_1/oradata/sxlttsioms/redo02.log',Options =>dbms_logmnr.addfile);

execute dbms_logmnr.start_logmnr (DictFileName =>'/home/ahres/archilog/logminer_dict.dat');

select timestamp,sql_redo from v$logmnr_contents  where operation='DELETE'and table_name='IOMS_INTERIOR_TD';
create table tmp_logmnr as
select operation,sql_redo,sql_undo  from v$logmnr_contents where table_name = 'HZ_TEST'and operation='DELETE';

SQL> declare
  2  mysql varchar2(4000);
  3  num number:=0;
  4  begin
  5  for c_tmp in (select sql_undo from tmp_logmnr where OPERATION='DELETE') loop
  6  mysql := replace(c_tmp.sql_undo,';','');
  7  dbms_output.put_line(mysql);
  8  execute immediate mysql;
  9  num := num + 1;
 10  if mod(num,1000)=0 then
 11  commit;
 12  end if;
 13  end loop;
 14  commit;
 15  exception
 16  when others then
 17  rollback;
 18  end;
 19  /
 
PL/SQL procedure successfully completed