【备份恢复】 闪回技术之闪回查询

闪回技术之闪回查询：

当数据误删除，想要回到删除前的状态，可以通过闪回查询获得闪回点的数据，前后比对，最后进行DML操作，恢复到自己想要的数据
闪回查询有三中查询手段；基于timestamp；基于scn和基于 dbms_flashback 包






实验：
1) 基于 timestamp

SCOTT@ORA11GR2>create table t_fb as select * from dept;

 

Table created.

 

            SCOTT@ORA11GR2>select * from t_fb;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 ——开启时间显示：

SCOTT@ORA11GR2>set time on;

16:48:47 SCOTT@ORA11GR2>

16:48:48 SCOTT@ORA11GR2>delete t_fb where deptno=10;

 

1 row deleted.

 

16:49:09 SCOTT@ORA11GR2>commit;

 

Commit complete.

 

16:49:15 SCOTT@ORA11GR2>select * from t_fb;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

——进行基于时间点的闪回查询：

16:49:23 SCOTT@ORA11GR2>select * from t_fb as of timestamp sysdate-1/1440;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

——没有查询到删除前的数据，（因为我在这个时间点上等了一会慢了，实际时间已经往后推了），继续：

16:50:45 SCOTT@ORA11GR2>select * from t_fb as of timestamp sysdate-2/1440;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

             2) 基于 scn
             ——查询当前的scn：

SCOTT@ORA11GR2>select dbms_flashback.get_system_change_number as scn from dual;

 

       SCN

----------

   1755527

 

——进行操作：

SCOTT@ORA11GR2>create table t_tb1 as select * from dept;

 

Table created.

 

SCOTT@ORA11GR2>select * from t_tb1;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

——再次查询当前scn：

SCOTT@ORA11GR2>select dbms_flashback.get_system_change_number as scn from dual;

 

       SCN

----------

   1755690

 

——删除t_tb1表：

SCOTT@ORA11GR2>delete t_tb1;

 

4 rows deleted.

 

SCOTT@ORA11GR2>commit;

 

Commit complete.

 

SCOTT@ORA11GR2>select * from t_tb1;

 

no rows selected

 

——基于scn的闪回查询：（是基于第二次查询的scn的操作）

SCOTT@ORA11GR2>select * from t_tb1 as of scn 1755690;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

（也可以通过  select current_scn from v$database;获取当前 SCN）
 

             3) 基于 dbms_flashback 包

语法：
? 会话启用闪回指定时间：
DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);
? 会话启用闪回指定 SCN:
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);
? 关闭闪回：
DBMS_FLASHBACK.DISABLE;

 

——用户需要有执行 dbms_flashback 包的权限

SYS@ORA11GR2>grant execute on dbms_flashback to scott;

 

Grant succeeded.

 

SYS@ORA11GR2>conn scott/tiger;

Connected.

--创建测试表：

SCOTT@ORA11GR2>create table t_tb2 as select * from dept;

 

Table created.

 

SCOTT@ORA11GR2>select * from t_tb2;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

——获取当前的scn号：

SCOTT@ORA11GR2>select dbms_flashback.get_system_change_number as scn from dual;

 

       SCN

----------

   1756835

 

——做更新操作：

SCOTT@ORA11GR2>update t_tb2 set dname=null,loc=null;

 

4 rows updated.

 

SCOTT@ORA11GR2>commit;

 

Commit complete.

 

SCOTT@ORA11GR2>select * from t_tb2;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10

        20

        30

        40

 

执行基于 dbms_flashback包的操作：

SCOTT@ORA11GR2>exec dbms_flashback.enable_at_system_change_number(1756835);

 

PL/SQL procedure successfully completed.

 

SCOTT@ORA11GR2>

 

——再次查看测试表：（只是能查到，实际上还需要收到进行恢复等的操作）

SCOTT@ORA11GR2>select * from t_tb2;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

--注意的是，在闪回模式中不能进行 DML 操作：

SCOTT@ORA11GR2>insert into t_tb2 values(1,

  2  'a','a');

insert into t_tb2 values(1,

            *

ERROR at line 1:

ORA-08182: operation not supported while in Flashback mode

 

——结束dbms_flashback包的操作，执行如下过程：

SCOTT@ORA11GR2>exec dbms_flashback.disable;

 

PL/SQL procedure successfully completed.

 

SCOTT@ORA11GR2>

 

--查询测试表，恢复原貌，且可以进行 DML 操作

SCOTT@ORA11GR2>select * from t_tb2;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10

        20

        30

        40

 

SCOTT@ORA11GR2>delete t_tb2 where deptno>=20;

 

3 rows deleted.

 

SCOTT@ORA11GR2>commit;

 

Commit complete.

 

SCOTT@ORA11GR2>select * from t_tb2;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10

     -------------------------------------------------------------

注 sys 下不允许使用 dbms_flashback 包：

SCOTT@ORA11GR2>conn / as sysdba

Connected.

     SYS@ORA11GR2>exec dbms_flashback.enable_at_time(sysdate-5/1440);

BEGIN dbms_flashback.enable_at_time(sysdate-5/1440); END;

 

*

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

ORA-06512: at "SYS.DBMS_FLASHBACK", line 3

ORA-06512: at line 1

    ------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126600/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126600/