闪回查询主要是利用数据库撤销表空间中的存放的回退信息,根据指定的过去的一个时刻或SCN值,返回当时已经提交的数据快照。撤销表空间中有一个参数undo_retention,默认值是900s ,说明回退信息在撤销表空间中至少保留900s,超过900s后,如果空间不够用,将覆盖之前的回退信息。如果要将来回退信息保留更长时间,则撤销表空间需要更大的存储空间,一般可以采用下列公式计算撤销表空间:
撤销表空间大小=undo_retention X 每秒产生数据块的数量 X db_block_size + 冗余量
oracle 建议将undo_retention设置为86400秒,这样利用闪回查询可以查看过去24内的数据快照。由于undo_retention参数无法保证没有过期的回退信息不会被覆盖,所以还需要启用撤销表空间中的参数retention guarantee特性,保证过期的信息才会被覆盖。
在oracle数据库,闪回查询是基于SELECT语句中使用AS OF 子句实现的
测试:
一、基于AS OF TIMESTAMP的闪回查询:
步骤1:修改时间会话格式,查看时间
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-09-15 08:51:36
步骤2:模拟删除test表
SQL> select * from test;
ID NAME
---------- ----------
1 zhangs
2 lisi
3 wangjina
4 lijing
5 fangli
SQL> delete from test;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from test;
no rows selected
步骤5:用 as of语句查看之前删除的状态并恢复
SQL> select * from test as of timestamp sysdate-3/1440;
no rows selected
SQL> select * from test as of timestamp sysdate-5/1440;
ID NAME
---------- ----------
1 zhangs
2 lisi
3 wangjina
4 lijing
5 fangli
或用
SQL> select * from test as of timestamp to_timestamp('2016-09-15 08:50:16','YYYY-MM-DD hh24:mi:ss');
ID NAME
---------- ----------
1 zhangs
2 lisi
3 wangjina
4 lijing
5 fangli
SQL> insert into test select * from test as of timestamp to_timestamp('2016-09-15 08:50:16','YYYY-MM-DD hh24:mi:ss');
5 rows created
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- ----------
1 zhangs
2 lisi
3 wangjina
4 lijing
5 fangli
5 rows selected.
二、用AS OF SCN恢复
如需要对多个相互有主外键约束的表进行恢复时,如果使用as of timestamp 的方式,可能会由于时间点不统一的缘故造成数据选择或插入失败,通过scn 方式则能够确保记录的约束一致性。
SQL> conn / as sysdba
Connected.
SQL> SELECT dbms_flashback.get_system_change_number FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1162195
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1162201
SQL> select count(*) from scott.test;
COUNT(*)
----------
10
SQL> delete from scott.test;
10 rows deleted.
SQL> commit;
Commit complete
SQL> select * from scott.test as of scn 1162201;
ID NAME
---------- ----------
1 zhangs
2 lisi
3 wangjina
4 lijing
5 fangli
1 zhangs
2 lisi
3 wangjina
4 lijing
5 fangli
10 rows selected.
SQL> insert into scott.test select * from scott.test as of scn 1162201;
10 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.test;
COUNT(*)
----------
10
查看SCN 和 timestamp 之间的对应关系:
select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time;