基于SCN的精确恢复数据操作
--查看测试表及数据
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
--删除部分数据
SQL> delete from test where name='oraking';

3 rows deleted.
--提交删除操作
SQL> commit;

Commit complete.
--尝试回滚数据
SQL> rollback;

Rollback complete.
--回滚失败
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
--查看历史scn变化情况
SQL> select first_change# fscn,next_change# nscn,first_time from v$archived_log;
       FSCN        NSCN FIRST_TIME
----------- ----------- -------------------
2912652689 2912652692 2012-04-02 03:03:42
2912652692 2912652845 2012-04-02 03:03:44
2912652845 2912652848 2012-04-02 03:08:32
2912652848 2912652852 2012-04-02 03:08:34
2912652852 2912652855 2012-04-02 03:08:37
2912652855 2912653126 2012-04-02 03:08:37
2912653126 2912653146 2012-04-02 03:13:03
2912653146 2912677093 2012-04-02 03:13:36
--查看当前scn
SQL> select dbms_flashback.get_system_change_number fscn from dual;

FSCN
-----------
2912682363
--查看当前表数据
SQL> select count(*) from test;

COUNT(*)
----------
         3
--尝试scn找回表数据的最佳scn
SQL> select count(*) from test as of scn &scn;
Enter value for scn: 2912682240
old   1: select count(*) from test as of scn &scn
new   1: select count(*) from test as of scn 2912682240

COUNT(*)
----------
         3
SQL> select count(*) from test as of scn &scn;
Enter value for scn: 2912681240
old   1: select count(*) from test as of scn &scn
new   1: select count(*) from test as of scn 2912681240

COUNT(*)
----------
6
--创建备份表
SQL> create table test_bak as select * from test where 1=2;

Table created.
--按照最佳scn插入历史数据到备份表
SQL> insert into test_bak select * from test as of scn 2912681240
2 ;

6 rows created.

SQL> commit;

Commit complete.
--确认丢失之前的表数据
SQL> select * from test_bak;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
--开启数据行转移
SQL> alter table test enable row movement;

Table altered.
--按照最佳scn闪回到误删除前的表状态
SQL> flashback table test to scn 2912681240;

Flashback complete.
--确认表数据
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
--关闭表行转移
SQL> alter table test disable row movement;

Table altered.

SQL> commit;

Commit complete.

ITPub博客

基于scn的闪回

最新文章