flashback之flashback query

最新推荐文章于 2018-02-18 14:33:01 发布

cindy-berry

最新推荐文章于 2018-02-18 14:33:01 发布

阅读量566

点赞数

分类专栏： flashback

flashback 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

《三思笔记》--读书笔记

语法格式是在标准查询语句的from表名后面加上as of timestamp（基于时间）或as of scn（基于scn）

测试表：

SQL> conn cindy/cindy
Connected.
SQL> create table flash_tbl(id,vl) as 
  2  select rownum,oname from (select substr(object_name,1,1) oname from all_objects
  3  group by substr(object_name,1,1) order by 1)
  4  where rownum <=20;

Table created.

flashback query 这一特性，最常被应用的就是修复误操作的数据，这并不是说flashback query 能够恢复数据，flashback query 本身不会恢复任何操作或修改，也不能告诉我们做过什么操作或修改，实际上flashback query 特性实际应用时，是基于标准select 的扩展，借助该特性能够让用户查询到指定的时间点的表中的记录，相当于拥有了看到过去的能力

（1），基于时间的查询（as of timestamp）

删除几条数据

SQL> delete flash_tbl where id<10;

9 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from flash_tbl;

        ID VL
---------- --
        10 I
        11 J
        12 K
        13 L
        14 M
        15 N
        16 O
        17 P
        18 Q
        19 R
        20 S

11 rows selected.

删除的数据已经提交，若删除动作在5分钟之内，那么可以利用flashback query来恢复记录

首先找到数据

SQL> select * from flash_tbl as of timestamp sysdate-5/1440;

        ID VL
---------- --
         1 /
         2 A
         3 B
         4 C
         5 D
         6 E
         7 F
         8 G
         9 H
        10 I
        11 J

        ID VL
---------- --
        12 K
        13 L
        14 M
        15 N
        16 O
        17 P
        18 Q
        19 R
        20 S

20 rows selected.

下面快速将记录恢复

SQL> insert into flash_tbl
  2  select * from flash_tbl as of timestamp sysdate-5/1440    
  3  where id <10;

9 rows created.

SQL> select * from flash_tbl;

        ID VL
---------- --
        10 I
        11 J
        12 K
        13 L
        14 M
        15 N
        16 O
        17 P
        18 Q
        19 R
        20 S

        ID VL
---------- --
         1 /
         2 A
         3 B
         4 C
         5 D
         6 E
         7 F
         8 G
         9 H

20 rows selected.

数据被成功恢复。

as of timestamp 使用方便，但是某些情况下，需要对多个相互有主外键约束的表进行恢复时，如果使用as of timestamp的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，所以as of scn较好

（2）基于scn的查询（as of scn）

授予用户使用dbms_flashback包的权限

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_flashback to cindy;

Grant succeeded.

SQL> grant select on v_$database to cindy;

Grant succeeded.

首先，获取scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1775465

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1775466

删除数据，并提交

SQL> delete flash_tbl where id > 10;

10 rows deleted.

SQL> commit;

Commit complete.

执行select语句并附加as of scn子句，同时指定删除前的scn，就可以查询到指定scn时对象中的记录

SQL> select * from flash_tbl as of scn  1775466;

        ID VL
---------- --
        10 I
        11 J
        12 K
        13 L
        14 M
        15 N
        16 O
        17 P
        18 Q
        19 R
        20 S

        ID VL
---------- --
         1 /
         2 A
         3 B
         4 C
         5 D
         6 E
         7 F
         8 G
         9 H

20 rows selected.

执行Insert进行恢复

SQL> insert into flash_tbl   
  2  select * from flash_tbl as of scn  1775466 
  3  where id > 10;

10 rows created.

其实，Oracle内部都是通过scn来标记操作而不是时间

实际，时间转换后对应具体的scn，映射关系是通过sys.smon_scn_time

oracle 也要两个函数专门用来转换，如下

SQL> select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
                  1775729

SQL> select scn_to_timestamp( 1775466) from dual;

SCN_TO_TIMESTAMP(1775466)
---------------------------------------------------------------------------
02-SEP-14 05.06.56.000000000 PM

（3）使用flashback version query查询记录修改版本

在当前时间点和指定的过去时间点之间，对象可能做过多次修改

10g版本之后，通过在select语句之后附加versions between timestamp start ans end子句即可，通过versions between 能够查看指定时间段内undo表空间中记录的不同版本（只包含已提交的记录）

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1779487

SQL> update flash_tbl set id=id+100 where id>15;

5 rows updated.

SQL> commit;

Commit complete.

SQL> delete flash_tbl where id < 5;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into flash_tbl values (201,'A1');

1 row created.

SQL>  insert into flash_tbl values (202,'B1');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1779569

执行versions query，就可以看到flash_tbl表中的记录的变化情况了

SQL> select id,vl,versions_startscn,versions_endscn,versions_operation,versions_xid
  2  from flash_tbl versions between scn 1779487 and 1779569;

        ID VL VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID
---------- -- ----------------- --------------- - ----------------
         4 C            1779559                 D 09001200D2050000
         3 B            1779559                 D 09001200D2050000
         2 A            1779559                 D 09001200D2050000
         1 /            1779559                 D 09001200D2050000
       120 S            1779552                 U 0700040030050000
       119 R            1779552                 U 0700040030050000
       118 Q            1779552                 U 0700040030050000
       117 P            1779552                 U 0700040030050000
       116 O            1779552                 U 0700040030050000
         1 /                            1779559
         2 A                            1779559

        ID VL VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID
---------- -- ----------------- --------------- - ----------------
         3 B                            1779559
         4 C                            1779559
         5 D
         6 E
         7 F
         8 G
         9 H
        10 I
        11 J
        12 K
        13 L

        ID VL VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID
---------- -- ----------------- --------------- - ----------------
        14 M
        15 N
        16 O                            1779552
        17 P                            1779552
        18 Q                            1779552
        19 R                            1779552
        20 S                            1779552
       202 B1           1779567                 I 0A0019006E040000
       201 A1           1779567                 I 0A0019006E040000

31 rows selected.

（4）flashback transaction query查询事务信息

该功能对应一个视图flashback_transaction_query

首先删除一条数据,删除前记录scn

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1784111

SQL> delete flash_tbl where id =7;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1784124

SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql
  2  from flashback_transaction_query q where q.xid in (
  3  select versions_xid from flash_tbl versions between scn 1784111 and 1784124);

XID              COMMIT_SCN COMMIT_TIMES OPERATION
---------------- ---------- ------------ --------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
03001D00C0050000    1784122 02-SEP-14    UNKNOWN


03001D00C0050000    1784122 02-SEP-14    BEGIN

tip：如果查询5分钟之前的存储过程，可以使用dba_source去查