Flashback query(闪回查询)原理
从Oracle 9i开始,Oracle提供闪回查询功能,Oracle根据undo信息,利用undo数据,类似一致性读取方法,可以把表置于一个删除前的时间点(或SCN),从而将数据找回。
Flashback query(闪回查询)前提:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
其中undo_management = auto,设置自动undo管理(AUM),该参数默认设置为:auto;
undo_retention = n(秒),设置决定undo最多的保存时间,其值越大,就需要越多的undo表空间的支持,如果undo足够大或者系统产生的undo足够少,数据在undo里面保留的时间会比undo_retention设置的时间长。
修改undo_retention的命令如下:
SQL> alter system set undo_retention = 3600;
System altered
闪回实现方式
1.获取数据删除前的一个时间点或scn,如下:
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
------------------- ----------------------------------------
2013-12-26 15:58:13 302044764
2.查询该时间点(或scn)的数据,如下:
SQL> delete from t_user where isvalid=0;
11163 rows deleted
SQL> commit;
Commit complete
SQL> select count(*) from t_user as of timestamp to_timestamp('2013-12-26 15:58:13', 'yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
28614
SQL> select count(*) from t_user as of scn 302044764;
COUNT(*)
----------
28614
3.将查询到的数据,新增到表中。也可用更直接的方法,如:
SQL> create table t_test as select * from t_user as of timestamp to_timestamp('2013-12-26 15:58:13', 'yyyy-mm-dd hh24:mi:ss') where isvalid=0;
Table created
SQL> insert into t_user select * from tab_test;
11163 rows inserted
SQL> commit;
Commit complete
SQL> select count(1) from t_user;
COUNT(1)
----------
28614
附:
查询5分钟前的数据
SQL> select count(*) from t_user as of timestamp(systimestamp - interval '5' minute);
COUNT(*)
----------
17451
将10分钟前的t_user表与1小时前的t_user表以id为条件进行关联(Join):
select e.username, d.username
from t_user as of timestamp(systimestamp - interval '10' minute) e,
t_user as of timestamp(systimestamp - interval '1' hour) d
where e.id = d.id;
将 admin 用户的EMAIL字段修改为15分钟前的值:
update t_user
set email =
(select email
from t_user as of timestamp(systimestamp - interval '15' minute)
where username = 'admin')
where username = 'admin';
注:推荐使用scn,由于oracle9i中,因为scn与时间点的同步需要5分钟,如果最近5分钟之内的数据需要Falshback query查询,可能会查询丢失,而scn则不存在这个问题。Oracle10g中这个问题已修正(scn与时间点的大致关系,可以通过logmnr分析归档日志获得)。
Falshback query查询的局限:
1. 不能Falshback到5天以前的数据。
2. 闪回查询无法恢复到表结构改变之前,因为闪回查询使用的是当前的数据字典。
3. 受到undo_retention参数的影响,对于undo_retention之前的数据,Flashback不保证能Flashback成功。
4. 对drop,truncate等不记录回滚的操作,不能恢复。
5. 普通用户使用dbms_flashback包,必须通过管理员授权。命令如下:
SQL>grant execute on dbms_flashback to scott;
闪回时间点查询的另一种方法是首先用PL/SQL包dbms_flashback的enable_at_time或enable_at_scn存储过程锁定一个会话级别的闪回时间目标,即进入闪回模式,随后的查询命令就可以省略“as of”,但也能达到使用“as of”的闪回效果,直到调用dbms_flashback. disable存储过程关闭闪回模式为止。
比如,将闪回模式会话定格在15分钟前:
SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute);
PL/SQL procedure successfully completed.
现在,虽然没有“as of”子句,以下查询命令的含义就是查看15分钟前的t_user表:
SQL> select count(*) from t_user;
COUNT(*)
----------
17451
需要注意此时若访问SYSDATE、SYSTIMESTAMP等日期函数,它们的返回值还是正常的当前的值,而不是静止在15分钟以前。另外,处于闪回会话模式时,执行dml和ddl将报错:
SQL> update t_user u set u.realname='test' where id=1;
update t_user u set u.realname='test' where id=1
ORA-08182: operation not supported while in Flashback mode
还有,SYS用户不能调用enable_at_time和enable_at_scn:
SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute);
BEGIN dbms_flashback.enable_at_time(systimestamp - interval '15' minute); END;
*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS
ORA-06512: at "SYS.DBMS_FLASHBACK", line 3
ORA-06512: at line 1
在回到过去对所有感兴趣的表查询一番后,再调用disable存储过程关闭闪回会话模式回到正常状态:
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed
从Oracle 9i开始,Oracle提供闪回查询功能,Oracle根据undo信息,利用undo数据,类似一致性读取方法,可以把表置于一个删除前的时间点(或SCN),从而将数据找回。
Flashback query(闪回查询)前提:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
其中undo_management = auto,设置自动undo管理(AUM),该参数默认设置为:auto;
undo_retention = n(秒),设置决定undo最多的保存时间,其值越大,就需要越多的undo表空间的支持,如果undo足够大或者系统产生的undo足够少,数据在undo里面保留的时间会比undo_retention设置的时间长。
修改undo_retention的命令如下:
SQL> alter system set undo_retention = 3600;
System altered
闪回实现方式
1.获取数据删除前的一个时间点或scn,如下:
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
------------------- ----------------------------------------
2013-12-26 15:58:13 302044764
2.查询该时间点(或scn)的数据,如下:
SQL> delete from t_user where isvalid=0;
11163 rows deleted
SQL> commit;
Commit complete
SQL> select count(*) from t_user as of timestamp to_timestamp('2013-12-26 15:58:13', 'yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
28614
SQL> select count(*) from t_user as of scn 302044764;
COUNT(*)
----------
28614
3.将查询到的数据,新增到表中。也可用更直接的方法,如:
SQL> create table t_test as select * from t_user as of timestamp to_timestamp('2013-12-26 15:58:13', 'yyyy-mm-dd hh24:mi:ss') where isvalid=0;
Table created
SQL> insert into t_user select * from tab_test;
11163 rows inserted
SQL> commit;
Commit complete
SQL> select count(1) from t_user;
COUNT(1)
----------
28614
附:
查询5分钟前的数据
SQL> select count(*) from t_user as of timestamp(systimestamp - interval '5' minute);
COUNT(*)
----------
17451
将10分钟前的t_user表与1小时前的t_user表以id为条件进行关联(Join):
select e.username, d.username
from t_user as of timestamp(systimestamp - interval '10' minute) e,
t_user as of timestamp(systimestamp - interval '1' hour) d
where e.id = d.id;
将 admin 用户的EMAIL字段修改为15分钟前的值:
update t_user
set email =
(select email
from t_user as of timestamp(systimestamp - interval '15' minute)
where username = 'admin')
where username = 'admin';
注:推荐使用scn,由于oracle9i中,因为scn与时间点的同步需要5分钟,如果最近5分钟之内的数据需要Falshback query查询,可能会查询丢失,而scn则不存在这个问题。Oracle10g中这个问题已修正(scn与时间点的大致关系,可以通过logmnr分析归档日志获得)。
Falshback query查询的局限:
1. 不能Falshback到5天以前的数据。
2. 闪回查询无法恢复到表结构改变之前,因为闪回查询使用的是当前的数据字典。
3. 受到undo_retention参数的影响,对于undo_retention之前的数据,Flashback不保证能Flashback成功。
4. 对drop,truncate等不记录回滚的操作,不能恢复。
5. 普通用户使用dbms_flashback包,必须通过管理员授权。命令如下:
SQL>grant execute on dbms_flashback to scott;
闪回时间点查询的另一种方法是首先用PL/SQL包dbms_flashback的enable_at_time或enable_at_scn存储过程锁定一个会话级别的闪回时间目标,即进入闪回模式,随后的查询命令就可以省略“as of”,但也能达到使用“as of”的闪回效果,直到调用dbms_flashback. disable存储过程关闭闪回模式为止。
比如,将闪回模式会话定格在15分钟前:
SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute);
PL/SQL procedure successfully completed.
现在,虽然没有“as of”子句,以下查询命令的含义就是查看15分钟前的t_user表:
SQL> select count(*) from t_user;
COUNT(*)
----------
17451
需要注意此时若访问SYSDATE、SYSTIMESTAMP等日期函数,它们的返回值还是正常的当前的值,而不是静止在15分钟以前。另外,处于闪回会话模式时,执行dml和ddl将报错:
SQL> update t_user u set u.realname='test' where id=1;
update t_user u set u.realname='test' where id=1
ORA-08182: operation not supported while in Flashback mode
还有,SYS用户不能调用enable_at_time和enable_at_scn:
SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute);
BEGIN dbms_flashback.enable_at_time(systimestamp - interval '15' minute); END;
*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS
ORA-06512: at "SYS.DBMS_FLASHBACK", line 3
ORA-06512: at line 1
在回到过去对所有感兴趣的表查询一番后,再调用disable存储过程关闭闪回会话模式回到正常状态:
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9399028/viewspace-1065008/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9399028/viewspace-1065008/