闪回之 Flashback Query (dml表、过程、函数、包等)、Flashback version Query

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

Flashback Query 种类:
Flashback Query 分 Flashback Query,Flashback Version Query, Flashback Transaction Query 三种。

flashback query 限制:
1. lashback query 对 v$tables,x$tables 等动态性能视图无效
2. 对于dba_*,all_*,user_*等数据字典是有效的


一 、 Flashback Query As of timestamp 的示例:

SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';
SQL> create table query as select * from user_objects;
SQL> select count(*) from query;
SQL> select sysdate from dual;
SYSDATE
-------------------
2015-03-13 16:29:13
SQL> delete from query;
SQL> commit;
SQL> select * from query;
no rows selected
--查看删除之前的状态:假设当前距离删除数据已经有 5 分钟左右的话:
SQL> select * from query as of timestamp sysdate-5/1440;
或者:
--知道误操作的准确时间,查看误操作之前的状态
SQL>select * from query as of timestamp to_timestamp('2015-03-13 16:29:13','YYYY-MM-DD hh24:mi:ss');
用 Flashback Query 恢复之前的数据:
SQL>Insert into query select * from query as of timestamp to_timestamp('2015-03-13 16:29:13','YYYY-MM-DD hh24:mi:ss');
14 rows created.
SQL> COMMIT;
SQL> select * from query;
14 rows selected.

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

补充:查看 SCN 和 timestamp 之间的对应关系:
select timestamp_to_scn(TO_TIMESTAMP_TZ('2015-03-13 16:52:30','YYYY-MM-DD HH24:MI:SS')) to_scn from dual;


二、 Flashback Query As of scn 的示例:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3473243

SQL> delete from query;
14 rows deleted.
SQL> commit;
--查看删除之前的状态:
SQL> select * from query as of scn 3473243;
14 rows selected.
用 Flashback Query 恢复之前的数据:
SQL> insert into query select * from query as of scn 3473243;
SQL> commit;
SQL> select count(*) from query;
COUNT(*)
----------
14

补充:查看 SCN 和 timestamp 之间的对应关系:
-- timestamp 转 scn 
select timestamp_to_scn(TO_TIMESTAMP_TZ('2015-03-13 17:26:42','YYYY-MM-DD HH24:MI:SS')) to_scn from dual;
-- scn 转 timestamp
SQL> select scn_to_timestamp(3474603) scn from dual;


三、 Flashback Query 函数,存储过程,包,触发器等对象:

背景
Flashback Drop 可以闪回与表相关联的对象, 如果是其他的对象,比如function,procedure,trigger 等。 这时候,就需要使用到 ALL_SOURCE 表来进行 Flashback Query。

查看 dba_source 的所有 type
SQL> select type from dba_source group by type;

TYPE
------------
PACKAGE
PACKAGE BODY
TYPE BODY
FUNCTION
JAVA SOURCE
PROCEDURE
LIBRARY
TRIGGER
TYPE

9 rows selected.

恢复操作流程:
--创建函数:
create or replace function fadd(pEndNumber int) return int
as
i int;
result int;
begin
i:=0;
result:=0;
while i<=pEndNumber loop result:=result+i;i:=i+1; end loop;
return result;
end;
/

--记录时间
SQL> select sysdate from dual;
SYSDATE
-------------------
2015-03-13 17:26:42
--查询函数:
SQL> set serveroutput on;
SQL> select fadd(100) from dual;

FADD(100)
----------
5050
--查询 dba_source 表:
SQL> select text from dba_source where name='FADD' order by line;
TEXT
--------------------------------------------------------------------------------
10 rows selected.

drop 函数,在查询,记录不存在
SQL> drop function fadd;
Function dropped.
SQL> select text from dba_source where name='FADD' order by line;
no rows selected

使用我们的 Flashback Query 查询:
SQL> 
select text from dba_source as of timestamp to_timestamp('2015-03-13 17:26:42','yyyy-mm-dd hh24:mi:ss') where name='FADD' order by line;

TEXT
--------------------------------------------------------------------------------
function fadd(pEndNumber int) return int
as
i int;
result int;
begin
i:=0;
result:=0;
while i<=pEndNumber loop result:=result+i;i:=i+1; end loop;
return result;
end;

10 rows selected.

text输出结果,重新执行一下就恢复回来了,其他的对象类推,不再演示。

四、 Flashback version Query:

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

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


实验流程:
SQL> create table andy (id int);
Table created.

SQL> insert into andy values(1);
1 row created.

SQL> insert into andy values(2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from andy;
ID
----------
1
2

SQL> select ora_rowscn, id from andy;
ORA_ROWSCN ID
---------- ----------
3476348 1
3476348 2


-- 查看历史信息
SQL>
Select versions_xid,versions_startscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original')
"Operation", id from andy versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ID
---------------- ----------------- --------------- -------- ----------
04000100830A0000 3476348 Insert 2
04000100830A0000 3476348 Insert 1
或者

ORA_ROWSCN 缺省是数据块级别的,也就是一个数据块内的所有记录都
是一个 ORA_ROWSCN,数据块内任意一条记录被修改,这个数据库块内的所
有记录的 ORA_ROWSCN 都会同时改变。

SQL> delete from andy where id>3;
2 rows deleted.

SQL> 
Select versions_xid,versions_startscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original')
"Operation", id from andy versions between scn minvalue and maxvalue;


VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ID
---------------- ----------------- --------------- -------- ----------
01000D00910A0000 3477149 Insert 5
01000D00910A0000 3477149 Insert 4
02000600000B0000 3477111 Insert 3
Original 1
Original 2

SQL> select * from andy;
ID
----------
1
2
3

SQL> commit;
Commit complete.

SQL> 
Select versions_xid,versions_startscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original')
"Operation", id from andy versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ID
---------------- ----------------- --------------- -------- ----------
06001900EF0A0000 3477710 Delete 5
06001900EF0A0000 3477710 Delete 4
01000D00910A0000 3477149 3477710 Insert 5
01000D00910A0000 3477149 3477710 Insert 4
02000600000B0000 3477111 Insert 3
Original 1
Original 2

7 rows selected.

SQL> select * from andy as of scn 3477710;
ID
----------
1
2
3

SQL> select * from andy as of scn 3477709;
ID
----------
1
2
3
4
5
SQL> insert into andy select * from andy as of scn 3477709;
5 rows created.

SQL> select * from andy;
ID
----------
1
2
3
1


本文转自 张冲andy 博客园博客,原文链接:   http://www.cnblogs.com/andy6/p/6707581.html,如需转载请自行联系原作者


上一篇:Zabbix 4.0 API 实践,主机/主机群组 批量添加模板和删除模板


下一篇:win10如何设置锁屏后不熄屏