ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于scn的闪回

基于scn的闪回

原创 Linux操作系统 作者:YallonKing 时间:2012-04-04 16:33:13 0 删除 编辑
基于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博客 ” ,链接:http://blog.itpub.net/26143577/viewspace-720311/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 闪回误删除的表
下一篇: 基于时间的闪回
请登录后发表评论 登录
全部评论

注册时间:2011-08-07

  • 博文量
    72
  • 访问量
    247141