Flashback Query
1、说明
flashback是oracle9i开始提供的特性。在9i中利用oracle查询多版本一致的特点,实现从回滚段中读取表一定时间内操作过的数据,可用来进行数据对比。或者修正意外提交造成的错误数据,该特性也被称为Flashback
Query。
分类:Flashback Query、Flashback Version
Query、Flashback Transaction Query。
2、分类说明
2.1 Flashback Query 是利用多版本读一致性的特性从UNDO表空间读取操作前的记录数据。
对V$tables、X$tables等的动态性能视图无效,不过对于dba_*、all_*、user_*等数据字典是有效的。该性能也完全支持访问远端数据库,比如:select
* from tbl@dblink as of scn
3600;的形式。
2.1.1多版本读一致性
不同的事物在写数据时,会将数据的前映像写入undo表空间,这样如果同时有其它事物查询该表数据,则可以通过undo表空间的前映像来构造所需的完整记录集,而不要等待写入的事物提交或回滚。
Flashback
query有多重方式构建查询记录集,记录集的选择范围可以基于时间或基于scn,甚至可以同时查询出记录在undo表空间中不同事物时的前映像。用法与标准查询非常类似,要通过Flashback
query查询undo中的撤销数据。最简单的方式只需要在标准查询语句的表名后面跟上as of timestamp(基于时间)或者 as
of scn(基于scn)即可。as of timestamp|scn的语法是自9iR2后才开始提供支持。
2.1.2 as of timestamp 的示例
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
delete from su; commit; --用户误操作删除了表中的数据
select * from su;
select * from su as of timestamp sysdatte-5/1440;
--分别查询当前的数据和5分钟之前的数据;
insert into su select * from su as of timestamp sysdatte-5/1440;
--恢复5分钟之前删除的数据
as of timestamp的确易用,但是在某些情况下,建议使用as of scn的方法执行flashback
query,比如需要对多个相互有主外键约束的表进行恢复时,如果使用as of timestamp
的方式,可能会由于时间点不统一的缘故造成数据选择或插入失败,scn方式能确保记录的约束一致性。
2.1.3 As of scn 示例
查看scn:select dbms_flashback.get_system_change_numberr from
dual;
select
current_scn from v$database;
delete from su; commit; --用户误操作删除了表中的数据
select * from su;
select * from su as of scn 18487032218; --分别查询当前的数据和之前的数据;
insert into su select * from as of scn 18487032218;
--恢复之前的数据
2.1.4 scn和timestamp的关系
在oracle内部都是使用scn,即使指定的是as of timestamp
,oracle也会将其转化成scn,系统时间标记与scn之间存在一张报,即SYS下的SMON_SCN_TIME.
每隔5分钟,系统产生一次系统时间标记与scn的匹配并存入sys.smon_scn_tim表。该表记录了最近1440个系统实际那标记与scn的匹配记录,由于该表只维护了最经的1440条记录,因此如果使用as
of timestamp的方式则只能flashback 最近5天内的数据(系统不间断运行并无中断或关机重启之类操作的话)
注:这里不一定是1440
注意理解系统时间标记与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 这个时刻的数据。
查询两者关系:select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from
sys.smon_scn_time;
2.1.5 flashback Query 函数、存储过程、包、触发器等对象
Flashback
Drop
可以闪回与表相关联的对象,如果是其他的对象,比如function、procdure、trigger等,这时候,就需要使用all_source表来进行Flashback
Query。
all_source:描述了当前用户可访问的存储对象的文本内容
DBA_user:描述了整个数据库所有的存储对象的文本内容
User_source:描述了属于当前用户下的所有的存储对象的文本内容
Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
NOT NULL
Owner of the object
NAME
VARCHAR2(30)
NOT NULL
Name of the object
TYPE
VARCHAR2(12)
Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY,
PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE
NUMBER
NOT NULL
Line number of this line of source
TEXT
VARCHAR2(4000)
Text source of the stored object
如果我们误删除了某些对象,如procduce,就可以使用all_source表进行恢复
查看表结构:desc dba_source;
查看所有的type:select distinct type from dba_source;
基于timestamp的恢复语句:select text from dba_source as of
timestamp('XXX','XXX') where owner='XXX' and name='你删除的对象' order by
line;
2.2 Flashback version Query
相对Flashback Query只能看到某一点的对象状态,oracle 10g引入的flashback version
Query可以看到去过某个时间段内,记录是如何发生变化的,根据这个历史,DBA就可以快速的判断数据是在什么时间点发生了错误,进而恢复到之前的状态。
先看一个伪列ORa_rowscn,是oracle 10g新增的,暂且把他看做是记录最后一次修改时的scn,Flashback
version Query就是通过这个伪列啦跟踪出记录的变化历史。
SQL> select * from A;
ID
----------
2
1
3
4
SQL> insert into A values(5);
已创建 1 行。
SQL> select * from A;
ID
----------
2
1
3
4
5
SQL> commit;
提交完成。
SQL> select ora_rowscn, id from A;
ORA_ROWSCN ID
---------- ----------
1098443 2
1098443 1
1098443 3
1098443 4
1098443 5
获取更多的历史信息
SQL>Select
versions_xid,versions_startscn,versions_endscn, DECODE(versions_operation,'I','Insert','U','Update','D','Delete',
'Original') "Operation", id from A versions between scn minvalue
and maxvalue;
或者
SQL>select xid,commit_scn,commit_timestamp,operation,undo_sql
from flashback_transaction_query q where q.xid in(select
versions_xid from B versions between scn 413946 and 413959);
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN
Operatio ID
---------------- ----------------- --------------- --------
----------
05001A0054020000 1099482 Update 3
05001A0054020000 1099482 Delete