恢复误删除的数据

有时，我们日常操作中不小心误删除了表里的部分或者全部数据，并已经提交。该如何恢复误删除的数据呢。可以通过如下两种方式：

一：找到误操作的时间点，通过类似这种方式insert into t1 select * from t1 as of scn 10670000; 

1.1 表现有数据 

 
SQL> select  * from t2;
 
ID
----------
 1
 2
 3
 4

1.2 模拟误操作

 

SQL> delete from t2 where id in (1,2);
 
2 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
 
System altered.
 
查看新生成的归档日志；
select name,first_time from v$archived_log
 
 
/disk/archfile/1_2_868421904.dbf	    13-JAN-15
 

 

1.3 使用Logmnr找到误操作时间点的SCN

1.3.1 建立数据字典分析文件

SQL> exec dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
 
PL/SQL procedure successfully completed.
 
1.3.2 添加日志分析(误操作时的归档日志)

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/disk/archfile/1_2_868421904.dbf',options=>dbms_logmnr.new);
 
PL/SQL procedure successfully completed.
 
1.3.3 执行分析

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
 
PL/SQL procedure successfully completed.
 
1.3.4 查看分析结果

SQL> COL sql_redo for a80;
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where lower(sql_redo) like '%delete%' and username = 'SCOTT';
 
USERNAME	       SCN TIMESTAMP    SQL_REDO
------------------------------ ---------- ------------ --------------------------------------------------------------------------------
SCOTT	   1982813 13-JAN-15    delete from "SCOTT"."T2" where "ID" = '1' and ROWID = 'AAAM1xAAGAAAAAMAAA';
SCOTT	   1982813 13-JAN-15    delete from "SCOTT"."T2" where "ID" = '2' and ROWID = 'AAAM1xAAGAAAAAMAAB';
 
 
1.3.5 验证是否恢复成功

SQL> select * from scott.t2 as of scn 1982813;
 
ID
----------
 1
 2
 3
 4
 

1.4 恢复数据

 

SQL> insert into scott.t2 select * from scott.t2 as of scn 1982813 where id in(1,2);
 
2 rows created.
 
SQL> select * from scott.t2;
 
ID
----------
 1
 2
 3
 4
 
SQL> commit;
 
Commit complete.

二：找到误操作的时间点，flashback table tablename to scn n;

2.1 表现有数据

SQL> select * from scott.t2;
 
ID
----------
 1
 2
 3
 4

2.2 模拟误操作

SQL> delete from scott.t2;
 
4 rows deleted.
 
SQL> commit;                         
 
Commit complete.
 
SQL> select * from scott.t2;
 
no rows selected

 

2.3 恢复数据

--由于方法一种已经找到了误操作时间点的SCN,这里直接使用。

 

SQL> alter table scott.t2 enable row movement;
 
Table altered.
 
SQL> flashback table scott.t2 to scn 1982813;
 
Flashback complete.

2.4 验证是否恢复成功

SQL> select * from scott.t2;
 
ID
----------
 1
 2
 3
 4