dbcommando 2019-03-11
Oracle 的闪回版本查询功能(Flashback Version Query)提供了一个审计行改变的查询功能,它能找到所有已经提交了行的记录,这样可以清楚地看到何时执行了何操作。
SELECT <column1>...FROM <TABLE>... VERSION BETWEEN [scn | TIMESTAMP] [<EXPR > | MAXVALUE] AND <EXPR>| MINVALUE] |AS OF [SCN | TIMESTAMP] <EXPR>
举例:
SQL>delete from test where id=666; SQL>commit; SQL>delete from test where id=777; SQL>commit;
对 test 表的操作进行审计查询:
SQL>select id,name,versions_operation,versions_xid,versions_starttime from test versions between timestamp minvalue and maxvalue order by id;
Flashback 是 ORACLE 自 9i 就开始提供的一项特性,在 9i 中利用 oracle 查询多版本一致的特点,实现从回滚段中读取表一定时间内操作过的数据,可用来进行数据比对,或者修正意外提交造成的错误数据,该项特性也被称为 Flashback Query。
Oracle 采用了一种非常优秀的设计,通过 undo 数据来确保写不堵塞读,简单的讲,不同的事务在写数据时,会将数据的前映像写入 undo 表空间,这样如果同时有其它事务查询该表数据,则可以通过 undo 表空间中数据的前映像来构造所需的完整记录集,而不需要等待写入的事务提交或回滚。
flashback query 有多种方式构建查询记录集,记录集的选择范围可以基于时间或基于scn,甚至可以同时查询出记录在 undo 表空间中不同事务时的前映象。
用法与标准查询非常类似,要通过 flashback query 查询 undo 中的撤销数据,最简单的方式只需要在标准查询语句的表名后面跟上 as of timestamp(基于时间)或 as of scn(基于 scn)即可。
SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss'; SQL> select sysdate from dual; SQL> select * from A; --模拟用户误操作,删除数据 SQL> delete from A; SQL> commit; SQL> select * from A;
查看删除之前的状态:
假设当前距离删除数据已经有 5 分钟左右的话(这里最少要有5分钟,要不可能会报错:ORA-01466: unable to read data - table definition has changed)
SQL> select * from A as of timestamp sysdate-5/1440;
用 Flashback Query 恢复之前的数据:
SQL>insert into A select * from A as of timestamp to_timestamp('2019-03-11 23:48:02','YYYY-MM-DD hh24:mi:ss');。 SQL> COMMIT; SQL> select * from A;
如上述示例中所表示的,as of timestamp 的确非常易用,但是在某些情况下,我们建议使用 as of scn 的方式执行 flashback query,比如需要对多个相互有主外键约束的表进行恢复时,如果使用 as of timestamp 的方式,可能会由于时间点不统一的缘故造成数据选择或插入失败,通过 scn 方式则能够确保记录的约束一致性。
查看 SCN:
SQL>SELECT dbms _flashback.get_system_change_number FROM dual; SQL> SELECT CURRENT _SCN FROM V$DATABASE; CURRENT_SCN ----------- 1095782
删除数据:
SQL> delete from A;
SQL> commit;
查看删除之前的状态:
SQL> select * from A as of scn 1095782;
用 Flashback Query 恢复之前的数据:
SQL> insert into A select * from A as of scn 1095782; 已创建 4 行。 SQL> commit; 提交完成。 SQL> select * from A;
事实上,Oracle 在内部都是使用 scn,即使你指定的是 as of timestamp,oracle 也会将其转换成 scn,系统时间标记与 scn 之间存在一张表,即 SYS 下的 SMON_SCN_TIME
每隔 5 分钟,系统产生一次系统时间标记与 scn 的匹配并存入 sys.smon_scn_time表,该表中记录了最近 1440 个系统时间标记与 scn 的匹配记录,由于该表只维护了最近的1440 条记录,因此如果使用 as of timestamp 的方式则只能 flashback 最近 5 天内的数据(假设系统是在持续不断运行并无中断或关机重启之类操作的话)。
查看 SCN 和 timestamp 之间的对应关系:
select scn,to _char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time;
Flashback Version Query和Flashback Query从技术基础上,都是相似的,也就是借助Oracle的Undo机制。其中Undo记录的是数据DML操作的前镜像,经典的Oracle事务模型中,一旦事务被commit,理论上之后SCN启动的读操作都不能读到之前的镜像数据。
Oracle于是利用Undo的机制,提供了短时间内的数据表旧版本查询。通过as of {timestamp | scn}指定时间点,就可以进行查询。当然,这个旧版本时间并不是无限长度,这就涉及到undo_retention这个争议参数(官方理解是:设置undo_retention之后,可以支持设置秒数的闪回数据查询。但是在实际工作中,却发现很多时候超过这个时间的数据也能检索到,但是有的时候没有到这个时间间隔旧版本数据,也不能找到)
其实,Undo_retention参数其实是用户建议Oracle数据库的一个“建议理想值”。试想一下,Undo数据是一个不断循环覆盖使用的空间,旧Undo前镜像一定会被新Undo前镜像覆盖。事务负载不同的系统,对Undo的使用情况也是不同的。
Flashback Query和Flashback Version Query,都是依赖Undo过期数据的来构建前镜像的操作。与Flashback Query不同的是,Flashback Version Query引入了一些数据表“伪列”,可以提供对数据版本的操作和检索。
后面会分享更多关于flashback的内容,感兴趣的朋友可以关注下!!