Oracle flashback之flashback query

Flashback Query

Flashback 是ORACLE 自9i 就开始提供的一项特性，在9i 中利用oracle 查询多版本一致的特点，实现从回滚段中读取表一定时间内操作过的数据，可用来进行数据比对，或者修正意外提交造成的错误数据，该项特性也被称为Flashback Query。

一、Flashback Query
正如前言中所提，Flashback Query 是利用多版本读一致性的特性从UNDO 表空间读取操作前的记录数据！什么是多版本读一致性?Oracle 采用了一种非常优秀的设计，通过undo 数据来确保写不堵塞读，简单的讲，不同的事务在写数据时，会将数据的前映像写入undo 表空间，这样如果同时有其它事务查询该表数据，则可以通过undo 表空间中数据的前映像来构造所需的完整记录集，而不需要等待写入的事务提交或回滚。flashback query 有多种方式构建查询记录集，记录集的选择范围可以基于时间或基于scn，甚至可以同时查询出记录在undo 表空间中不同事务时的前映象。用法与标准查询非常类似，要通过flashback query 查询undo 中的撤销数据，最简单的方式只需要在标准查询语句的表名后面跟上as of timestamp(基于时间)或as of scn(基于scn)即可。as of timestamp|scn 的语法是自9iR2 后才开始提供支持。

 

二，As of timestamp 的示例：
SQL>  alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';
会话已更改。

SQL> select sysdate from dual;

SYSDATE
-------------------
2012-12-30 11:39:50

 

SQL> conn hr/hr
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
       107

 

SQL> delete from emp;

107 rows deleted.

SQL> commit;

Commit complete.

再次查询，记录为空
SQL> select count(*) from emp;

  COUNT(*)
----------
         0

查看删除之前的状态：
假设当前距离删除数据已经有5 分钟左右的话：
SQL> select count(*) from emp as of timestamp sysdate-5/1440;

  COUNT(*)
----------
       107

或者：

SQL> select count(*) from emp as of timestamp to_timestamp('2012-12-30 11:39:50','YYYY-MM-DD hh24:mi:ss');

  COUNT(*)
----------
       107

用Flashback Query查询到的数据来恢复到emp被删除的记录：

SQL> Insert into emp select * from emp as of timestamp to_timestamp('2012-12-30 11:39:50','YYYY-MM-DD hh24:mi:ss');

107 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

  COUNT(*)
----------
       107

从上面可以看出，as of timestamp 非常易用，但是在某些情况下，我们建议使用as of scn 的方式执行flashback query，比如需要对多个相互有主外键约束的表进行恢复时，如果使用as of timestamp 的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，通过scn 方式则能够确保记录的约束一致性。

三，As of scn 示例

查看SCN:
SQL> SELECT dbms_flashback.get_system_change_number FROM dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  667122

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
     667126

删除数据：
SQL> delete from emp;

107 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

  COUNT(*)
----------
         0

查看删除之前的状态：

SQL> select count(*) from emp as of scn 667126;

  COUNT(*)
----------
       107

用Flashback Query恢复之前的数据：
SQL> insert into emp select * from emp as of scn 667126;

107 rows created.

恢复成功
SQL> select count(*) from emp;

  COUNT(*)
----------
       107

实际上,Oracle 在内部都是使用scn,即使你指定的是as of timestamp,oracle 也会将其转换成scn,系统时间标记与scn 之间存在一张表,它在SYS schema下的SMON_SCN_TIME

注：Falshback query的时候推荐使用scn，由于oracle9i中，因为scn与时间点的同步需要5分钟，如果最近5分钟之内的数据需要Falshback query查询，可能会查询丢失，而scn则不存在这个问题。Oracle10g中这个问题已修正,但是我的10g(version: 10.2.0.1.0) 还是5分钟同步一次。(scn与时间点的大致关系，可以通过logmnr分析归档日志获得)

关于 smon_scn_time的说明
1，10G 中的scn 与时间的转换是通过 smon_scn_time 来估算的
下面是timestamp 到scn的转换，实际基本一致
SQL> select timestamp_to_scn(to_timestamp('2012-12-30 11:24:58', 'yyyy-mm-dd hh24:mi:ss')) scn from dual;

       SCN
----------
    666651

SQL> select scn_to_timestamp(666651) time from dual;

TIME
---------------------------------------------------------------------------
2012-12-30 11:24:56.000000000

 

2，它记录5天的数据, 10G 3秒更新一次,跟着lgwr激活来的, 144000跳记录, 9i是5分钟一次, 不过我测试的库好像还是间隔为5分钟, 5天1440条记录，
 所以理论上支持的闪回最短为3秒钟
查看SCN 和 timestamp 之间的对应关系：
select THREAD, TIME_MP, TIME_DP, TIME_DP, SCN_BAS, scn from sys.smon_scn_time where rownum<=1000;
    THREAD    TIME_MP TIME_DP             TIME_DP                SCN_BAS        SCN
---------- ---------- ------------------- ------------------- ---------- ----------
         0  803384379 2012-12-30 10:19:39 2012-12-30 10:19:39     663744     663744
         0  803384707 2012-12-30 10:25:07 2012-12-30 10:25:07     663897     663897
         0  803385018 2012-12-30 10:30:18 2012-12-30 10:30:18     664954     664954
         0  803385337 2012-12-30 10:35:37 2012-12-30 10:35:37     665354     665354
         0  803385646 2012-12-30 10:40:46 2012-12-30 10:40:46     665455     665455
         0  803385955 2012-12-30 10:45:55 2012-12-30 10:45:55     665557     665557
         0  803386275 2012-12-30 10:51:15 2012-12-30 10:51:15     665754     665754
         0  803386616 2012-12-30 10:56:56 2012-12-30 10:56:56     665855     665855
         0  803386947 2012-12-30 11:02:27 2012-12-30 11:02:27     666015     666015
         0  803387269 2012-12-30 11:07:49 2012-12-30 11:07:49     666286     666286

    THREAD    TIME_MP TIME_DP             TIME_DP                SCN_BAS        SCN
---------- ---------- ------------------- ------------------- ---------- ----------
         0  803387599 2012-12-30 11:13:19 2012-12-30 11:13:19     666388     666388

每隔5 分钟，系统产生一次系统时间标记与scn 的匹配并存入sys.smon_scn_time 表，该表中记录了最近1440个系统时间标记与scn 的匹配记录，由于该表只维护了最近的1440 条记录，因此如果使用as of timestamp 的方式则只能flashback 最近5 天内的数据(假设系统是在持续不断运行并无中断或关机重启之类操作的话)注意理解系统时间标记与scn 的每5 分钟匹配一次这句话，举个例子，比如scn:666015,666286 分别匹配2012-12-30 11:02:27 和2012-12-30 11:07:49，则当你通过as of timestamp 查询2012-12-30 11:02:27 和2012-12-30 11:07:27 这段时间点内的时间时，oracle 都会将其匹配为scn:666015 到undo 表空间中查找，也就说在这个时间内，不管你指定的时间点是什么，查询返回的都将是2012-12-30 11:02:27 这个时刻的数据。

四，Falshback query查询的局限：

a． 不能Falshback到5天以前的数据。
b． 闪回查询无法恢复到表结构改变之前，因为闪回查询使用的是当前的数据字典。
c． 受到undo_retention参数的影响，对于undo_retention之前的数据，Flashback不保证能Flashback成功
d． 对drop,truncate等不记录回滚的操作，不能恢复。
e． 普通用户使用dbms_flashback包，必须通过管理员授权。命令如下:
SQL>grant execute on dbms_flashback to hr;