使用flashback恢复删除的数据

环境:

OS:Red Hat Linux As5

DB:10.2.0.4

 

1.创建表并写入数据

SQL>connect hxl/hxl
SQL> Create Table tb_fb_test
  2  (
  3   Id Number,
  4   createtime Date Default Sysdate
  5  );

Table created.

SQL> Insert Into tb_fb_test Values (1,Sysdate);

1 row created.

SQL> Insert Into tb_fb_test Values (2,Sysdate);

1 row created.

SQL> Insert Into tb_fb_test Values (3,Sysdate);

1 row created.

SQL> Insert Into tb_fb_test Values (4,Sysdate);

1 row created.

SQL> Insert Into tb_fb_test Values (5,Sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tb_fb_test;

        ID CREATETIME
---------- ------------
         1 24-JUL-12
         2 24-JUL-12
         3 24-JUL-12
         4 24-JUL-12
         5 24-JUL-12

 

2.记录需要恢复的时间点

SQL> Select to_char(Sysdate,'YYYYMMDD HH24:MI:SS') sys_date From dual;

SYS_DATE
-----------------
20120724 10:12:53

 

3.删除表数据

SQL> delete from tb_fb_test;

5 rows deleted.

SQL> commit;

Commit complete.

 

4.恢复表数据

SQL> alter table tb_fb_test enable row movement;

Table altered.

SQL> flashback table hxl.tb_fb_test to timestamp to_timestamp('20120724 10:12:53', 'yyyymm-d hh24:mi:ss');

Flashback complete.

SQL> select * from tb_fb_test;

        ID CREATETIME
---------- ------------
         1 24-JUL-12
         2 24-JUL-12
         3 24-JUL-12
         4 24-JUL-12
         5 24-JUL-12

 

数据已经恢复.

 

说明:

使用flashback恢复数据通常有如下三种方法:

1.基于时间点

flashback table hxl.tb_fb_test to timestamp to_timestamp('20120724 10:12:53', 'yyyy-mm-dd hh24:mi:ss');

 

2.基于scn

flashback table hxl.tb_fb_test to scn 123456;

 

3.基于创建的restore point

flashback table hxl.tb_fb_test to restore point tab_delete;

 

注意truncate表是不能使用flashback恢复的.

 

SQL> truncate table tb_fb_test;

Table truncated.

 

SQL> flashback table hxl.tb_fb_test to timestamp to_timestamp('20120724 10:12:53', 'yyyy-mm-dd hh:mi:ss');

flashback table hxl.tb_fb_test to timestamp to_timestamp('20120724 10:12:53', 'yyyy-mm-dd hh:mi:ss')
                    *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

 

flashback表数据的限制条件:

1.The object must not be included the following categories: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

 

2.The structure of the table must not have been changed between the current time and the target flash back time.
  The following DDL operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (except adding a range partition).

 

3.Row movement must be enabled on the table, which indicates that rowids will change after the flashback occurs.
  This restriction exists because if rowids before the flashback were stored by the application, then there is no guarantee that the rowids correspond to the same rows after the flashback. If your application depends on rowids, then you cannot use Flashback Table.

 

4.The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.
  The point to which you can perform Flashback Table is determined by the undo retention period, which is the minimal time for which undo data is kept before being recycled, and tablespace characteristics. The undo data contains information about data blocks before they were changed. The flashback operation uses undo to re-create the original data.To ensure that the undo information is retained for Flashback Table operations, Oracle suggests setting the UNDO_RETENTION parameter to 86400 seconds (24 hours) or greater for the undo tablespace.

-- The End --