Oracle Flashback技术总结(三)—— Flashback Query

三、Flashback Query

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

1、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 后才开始提供支持。

2、Flashback Query的示例

As of timestamp 的示例：

SYS@ orcl >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'；

SYS@ orcl >select sysdate from dual;

SYSDATE

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

2013-05-23 01:14:45

SYS@ orcl >select * from test014;

ID

----------

 1

 2

模拟用户误操作，删除数据

SYS@ orcl >delete from test014;

2 rows deleted.

SYS@ orcl >commit;

Commit complete.

查看删除之前的状态：

oracle 11g不需要等5分钟，9i/10g需要等5分钟

SYS@ orcl >select * from test014 as of timestamp sysdate-5/1440;

ID

----------

 1

 2

或者：

SYS@ orcl >select * from test014 as of timestamp to_timestamp('2013-05-23 01:14:45','yyyy-mm-dd hh24:mi:ss');

ID

----------

 1

 2

         4

用Flashback Query恢复之前的数据：

SYS@ orcl >insert into test014 select * from test014 as of timestamp to_timestamp('2013-05-23 01:14:45','yyyy-mm-dd hh24:mi:ss');

              2 rows created.

SYS@ orcl >select * from test014;

ID

----------

 1

 2

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

As of scn 示例

查看SCN:

SELECT dbms_flashback.get_system_change_number FROM dual;

SELECT CURRENT_SCN FROM V$DATABASE;

SYS@ orcl >select current_scn from v$database;

CURRENT_SCN

-----------

    1825435

删除数据：

SYS@ orcl >delete from test014;

   1 rows deleted.

SYS@ orcl >commit;

    Commit complete.

查看删除之前的状态：获取scn值请参考[Flashback version Query]

SYS@ orcl >select * from test014 as of scn 1825516;

ID

----------

 5

用Flashback Query恢复之前的数据：

SYS@ orcl >insert into test014 select * from test014 as of scn 1825516;

1 row created.

SYS@ orcl >commit;

Commit complete.

SYS@ orcl >select * from test014;

ID

----------

 5

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

SYS@ orcl >desc sys.smon_scn_time;

 Name  Null?   Type

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

 THREAD             NUMBER

 TIME_MP            NUMBER

           IME_DP              DATE

 SCN_WRP           NUMBER

 SCN_BAS           NUMBER

 NUM_MAPPINGS   NUMBER

 TIM_SCN_MAP   RAW(1200)

 SCN            NUMBER

 ORIG_THREAD   NUMBER

每隔5 分钟(oracle 11g没有5分钟限制，10g有这个限制)，系统产生一次系统时间标记与scn 的匹配并存入sys.smon_scn_time 表，该表中记录了最近1440个系统时间标记与scn 的匹配记录，由于该表只维护了最近的1440 条记录，因此如果使用as of timestamp 的方式则只能flashback 最近5 天内的数据（假设系统是在持续不断运行并无中断或关机重启之类操作的话）。

注意理解系统时间标记与scn 的每5 分钟匹配一次这句话，举个例子，比如scn:339988,339989 分别匹配08-05-3013:52:00 和2008-13:57:00，则当你通过as of timestamp 查询08-05-30 13:52:00 或08-05-30 13:56:59 这段时间点

内的时间时，oracle 都会将其匹配为scn:339988 到undo 表空间中查找，也就说在这个时间内，不管你指定的时间点是什么，查询返回的都将是08-05-30 13:52:00 这个时刻的数据。

查看SCN 和 timestamp 之间的对应关系：

SYS@ orcl >select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time  from sys.smon_scn_time 

       SCN TIME

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

    832740 2011-02-17 12:51:28

    833372 2011-02-17 12:57:47

    833436 2011-02-17 12:58:22

四、Flashback version Query

1、Flash version query概念

    相对于Flashback Query 只能看到某一点的对象状态， Oracle 10g引入的Flashback Version Query可以看到过去某个时间段内，记录是如何发生变化的。 根据这个历史，DBA就可以快速的判断数据是在什么时点发生了错误，进而恢复到之前的状态。

先看一个伪列 ORA_ROWSCN.  所谓的伪列，就是假的，不存在的数据列，用户创建表时虽然没有指定，但是Oracle为了维护而添加的一些内部字段，这些字段可以像普通文件那样的使用。最熟悉的伪列就是 ROWID， 它相当于一个指针，指向记录在磁盘上的位置。ORA_ROWSCN 是Oracle 10g 新增的，暂且把它看作是记录最后一次被修改时的SCN。 Flashback Version Query 就是通过这个伪列来跟踪出记录的变化历史。

例如：

SYS@ orcl >insert into test014(id) values(3)

SYS@ orcl >commit;

SYS@ orcl >select ora_rowscn,id from test014;

ORA_ROWSCN   ID

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

   1893607    1

   1893607    3

2、获取更多的历史信息

SYS@ orcl>select versions_xid,versions_starttime,versions_endtime,

                versions_startscn,versions_endscn,versions_operation,id

                from test014  versions between scn minvalue and maxvalue

VERSIONS_XID  VERSIONS_STARTTIME     VERSIONS_ENDTIME      VERSIONS_STARTSCN VERSIONS_ENDSCN V   ID

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

0100010089030000  23-MAY-13 11.37.07 PM  1916937 D    3

0100010089030000  23-MAY-13 11.37.07 PM  1916937 D    1

    23-MAY-13 11.37.07 PM1916937                     1

    23-MAY-13 11.37.07 PM1916937                     3

Table 13-1 Oracle Flashback Version Query Row Data Pseudocolumns

Pseudocolumn NameDescription

VERSIONS_STARTSCN

VERSIONS_STARTTIME

Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This pseudocolumn identifies the time when the data first had the values reflected in the row version. Use this pseudocolumn to identify the past target time for Oracle Flashback Table or Oracle Flashback Query.

If this pseudocolumn is NULL, then the row version was created before start.

VERSIONS_ENDSCN

VERSIONS_ENDTIME

SCN or TIMESTAMP when the row version expired.

If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETEoperation.

VERSIONS_XID

Identifier of the transaction that created the row version.

VERSIONS_OPERATION

Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation.

For user updates of an index key, Oracle Flashback Version Query might treat an UPDATE operation as two operations, DELETE plusINSERT, represented as two version rows with a Dfollowed by an I VERSIONS_OPERATION.

3.ora_rowscn简介

下面我们来讲下伪列， Flashback Version Query 技术其实有很多伪列，但是ORA_ROWSCN是最重要。

它记录的是最后一次被修改时的SCN， 注意是被提交的修改。如果没有提交，这个伪列不会发生变化。ORA_ROWSCN 缺省是数据块级别的，也就是一个数据块内的所有记录都是一个ORA_ROWSCN，数据块内任意一条记录被修改，这个数据库块内的所有记录的ORA_ROWSCN都会同时改变。上例的查询结果以证明。不过我们可以在建表时使用关键字rowdependencies， 可以改变这种缺省行为，使用这个关键字后，每条记录都有自己的ORA_ROWSCN。 另外最后一次被修改时的SCN，如果没有提交，是不会变的，我们重做一下就清楚了。

举例：

SYS@ orcl>select ora_rowscn,id from test014;

ORA_ROWSCN   ID

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

   1922994    1

为了更好的让读者理解ora_rowscn本质：特别引用【作者：江枫  阿里巴巴数据库集团团】 http://www.taobaodba.com/html/243_about_ora_rowscn.html
Oracle10g引入了一个新的ORA_ROWSCN的伪列，可以查询表中记录最后变更的SCN。这个新的伪列在某些环境下会非常有用，比如执行乐观锁定，或者增量数据抽取的时候。但是，默认情况下，每行记录的ORA_ROWSCN是基于Block的，除非在建表的时候执行开启行级跟踪(create table … rowdependencies)。
先来简单理解一下ORA_ROWSCN的实现原理。我们知道，每个Block在头部是记录了该block最近事务的SCN的，所以默认情况下，只需要从block头部直接获取这个值就可以了，不需要其他任何的开销，Oracle就能做到这一点。但是这明显是不精确的，一个block中会有很多行记录，每次事务不可能影响到整个block中所有的行，所以这是一个非常不精准的估算值，同一个block的所有记录的ORA_ROWSCN都会是相同的，基本上没有多大的使用价值。
如果在建表的时候开启行级跟踪选项，Oracle则可以为每一行记录精确的SCN，那么显然不能再直接从block头部获取。要获得足够的信息，肯定要付出一定的代价，Oracle必须为每一行实际的存储这个SCN。所以这个行级跟踪的选项，只能在建表的时候指定，而不能通过alter table来修改现有的表，否则需要修改每一行记录的物理存储格式，代价是可想而知的。
简单的做个实验就可以知道开启行级跟踪以后块记录格式的不同。建两个表，一个norowdependencies（默认），一个rowdependencies，然后分别dump出相应的数据块：
create table t1(i int);insert into t1 values(1);insert into t1 values(2);commit;create table t2 rowdependencies as select * from t1;
norowdependencies：
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: –H-FL– lb: 0×1 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0x1f94
tl: 6 fb: –H-FL– lb: 0×1 cc: 1
col 0: [ 2] c1 03
end_of_block_dump
rowdependencies：
block_row_dump:
tab 0, row 0, @0x1f7c
tl: 12 fb: –H-FL– lb: 0×0 cc: 1
dscn 0×0000.00029ae4
col 0: [ 2] c1 02
tab 0, row 1, @0x1f6d
tl: 12 fb: –H-FL– lb: 0×0 cc: 1
dscn 0×0000.00029ae4
col 0: [ 2] c1 03
end_of_block_dump
得到行的SCN后，通过SCN_TO_TIMESTAMP函数可以转化为时间：
SQL>  select SCN_TO_TIMESTAMP(ora_rowscn) from t2 where rownum
乐观锁和ORA_ROWSCN
需要select … for update做悲观锁定的时候，通过使用ORA_ROWSCN可以改成乐观锁定。一开始select数据的时候将ORA_ROWSCN查出来，修改后如果要写回数据库之前再比对下最新的ORA_ROWSCN就可以知道这期间数据是否有发生变化。这个Tom在他的大著《Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions 》中也是有提到的。
增量数据抽取和ORA_ROWSCN
每次抽取后记录最大的ORA_ROWSCN，下次抽取再基于上一次的SCN来获得最近修改过的数据即可。在10g之前，很多系统要实现增量数据抽取，要么通过解析日志，要么加触发器，要么就在表上加一个时间截字段。ORA_ROWSCN其实就是第三种方式，只是这个字段由Oracle来维护，这样可以避免一些应用绕过时间截去更新其他字段带来的问题。不过，如果系统中使用了逻辑备库或者streams等逻辑复制的方案，而数据抽取又是基于逻辑备库的话，ORA_ROWSCN就可能对抽取后的数据分析有影响了，因为通过这个得到的时间是逻辑备库上记录变更的时间，而不是源库的时间了。当然，如果纯粹只是做数据抽取，而不需要使用这个时间来做分析，还是问题不大的，但还是要考虑一旦逻辑备库出现故障需要重做的，则这个增量抽取要怎么来处理的问题。
Metalink上搜一下ORA_ROWSCN可以看到不少相关的bug，所以在生产系统中使用的时候要小心。例如，我在Linux64平台上的一台测试库中，执行以下语句就会碰到ORA-07445的错误：
SQL> select ora_rowscn from x$bh where rownum

五、Flashback Transaction Query

Flashback Transaction Query也是使用UNDO信息来实现。利用这个功能可以查看某个事务执行的所有变化，它需要访问flashback_transaction_query 视图，这个视图的XID列代表事务ID，利用这个ID可以区分特定事务发生的所有数据变化。在Oracle Database 11g中，需要启用supplemental log data才能够得到UNDO SQL。

SYS@ orcl>alter database add supplemental log data;

示例：

SYS@ orcl>delete from test014

SYS@ orcl>commit;

查看视图，每个事务都对应相同的XID

SYS@ orcl>select xid,commit_scn,operation,undo_sql

               from flashback_transaction_query

                where xid in (select versions_xid from test014 versions between scn minvalue and maxvalue)

XID  COMMIT_SCN OPERATION  UNDO_SQL

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

07001200AB030000    1935325 DELETE     insert into "SYS"."TEST014"("ID") values ('2');

07001200AB030000    1935325 BEGIN

020005006B040000    1928700 UNKNOWN

020005006B040000    1928700 BEGIN

0A00010099030000    1922994 UNKNOWN

0A00010099030000    1922994 BEGIN

09001C0080040000    1935320 INSERT     delete from "SYS"."TEST014" where ROWID = 'AAASk0AAEAAAAJHAA A';

此处的UNDO_SQL显示为两行，其中一条为BEGIN，这是作为事务开始的标记。Oracle隐式的开始一个事务，在Reverse转换时，BEGIN被显示出来，而且在INSERT之后：