利用LOGMNR恢复DELETE的数据
测试如下：
create table ta.test3 as select * from user_objects;
select count(*) from ta.test3;
delet ta.test3;
commit;
1、假设由于时间的问题，用闪回报错。
SELECT count(*) from ta.test3 AS OF TIMESTAMP TO_TIMESTAMP('2011-06-09 15:29:00','YYYY-MM-DD HH24:MI:SS');
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"
too small
2、利用LOGMNR来恢复
一、备份原表
二、修改参数，用于挖日志，重启数据库
SQL> show parameter utl;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/product/10.2.0/dbs/spfileofdata.ora

SQL> alter system set utl_file_dir='/arclogs/' scope=spfile;
三、生成数据字典（用SYS用户执行）
需要注意的是，在Oracle 10g以下，LOGMNR的临时表v$logmnr_contents，使用的是system表空间，在Oracle 10g以后改为sysaux表空间。可以使用如下的命令，更改logmnr的特定表空间，防止system表空间出现空间不够。
sql> sys.dbms_logmnr_d.set_tablespace('USERS');
SQL> exec dbms_logmnr_d.build('dictionary.ora','/arclogs/');
PL/SQL procedure successfully completed
做这一步之前注意需要修改LD_LIBRARY_PATH和LIBPATH，使得lib的变量在lib32前面。不然会有报错ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
四、检查需要回滚的日志；
[oracle@tatuxtest arclogs]$ ll
-rw-r----- 1 oracle oinstall 199549952 07-22 13:30 archi_1_4717_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-22 13:33 archi_1_4718_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-22 16:04 archi_1_4719_714205044.dbf
-rw-r----- 1 oracle oinstall 199549440 07-22 16:07 archi_1_4720_714205044.dbf
-rw-r----- 1 oracle oinstall 199641088 07-24 08:21 archi_1_4721_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-26 15:38 archi_1_4722_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-26 15:56 archi_1_4723_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-27 10:54 archi_1_4724_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-29 11:39 archi_1_4725_714205044.dbf
-rw-r----- 1 oracle oinstall 158471168 07-31 12:26 archi_1_4726_714205044.dbf
-rw-r----- 1 oracle oinstall 1024 07-31 12:26 archi_1_4727_714205044.dbf
-rw-r----- 1 oracle oinstall 3584 07-31 12:26 archi_1_4728_714205044.dbf
-rw-r----- 1 oracle oinstall 299520 07-31 12:26 archi_1_4729_714205044.dbf
-rw-r----- 1 oracle oinstall 1024 07-31 12:26 archi_1_4730_714205044.dbf
-rw-r----- 1 oracle oinstall 3584 07-31 12:26 archi_1_4731_714205044.dbf
五、添加归档日志
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4726_714205044.dbf',Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4727_714205044.dbf',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4728_714205044.dbf',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4729_714205044.dbf',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4730_714205044.dbf',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4731_714205044.dbf',Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed
六：开始挖日志
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/arclogs/dictionary.ora');
PL/SQL procedure successfully completed
七、将logmnr的数据暂时保存在一个表里面，免得再次查询时候不用再次添加归档日志
SQL> create table test_logmnr nologging as select * from v$logmnr_contents where 1=2;

Table created

SQL> insert into test_logmnr select * from v$logmnr_contents;
419563 rows inserted
做这一步之前注意将nls_date_format改成'yyyy-mm-dd hh24:mi:ss'，不然乱码，时间会变成问号。
八、终止日志分析过程。
sql>exec sys.dbms_logmnr.end_logmnr；
九、利用脚本导入
declare
mysql varchar2(4000);
num number :=0;
begin
for c_tmp in (select sql_undo from test_logmnr where seg_name='TEST3' and OPERATION LIKE 'DELETE') loop
mysql := replace(c_tmp.sql_undo,';','');
execute immediate mysql;
num := num + 1;
if mod(num,1000)=0 then
commit;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line('errors');
end;
/
十、检查数据。
注意：如果是DROP或者TRUNCATE，不能用此方法。但可以用ODU恢复，当然10G，有闪回表的功能,ODU，或者利用恢复DATAGUARD。