Oracle闪回查询

Oracle闪回查询Flashback

参考

http://www.chinaunix.net/jh/19/286443.html

用途

自我维护过程中的修复

当一些重要的行被意外的从一个表中被删除了，用户希望恢复它。做这样一个修理，用户可以向后移动到一个时间点，查看丢失的行并把他们重新插入现在的表内。但是需要注意不要带来逻辑矛盾。

恢复类似于email的应用程序包

在处理类似email和voice mail的应用包体上。当用户因为按错键意外的删除了一封mail或者声音信息时，他们可以通过移回到固定时间点来恢复删除的mail或者信息并将他们重新插入现在的信息箱内。

用于趋势分析的决策支持系统

决策支持系统，联机分析等不得不执行一个长时间运行的事务。有时需要执行对过去数据的分析和建模，例如遮光剂随季节变化需求的变化。

概述

这一特性允许用户查看过去某一时间点数据库的映像视图

用户可以利用系统时间或者System change number(SCN)来指定这个只读视图。所有的在这个时间点之前提交的事务市可见的，之后提交的不可见

Oracle flashback的实质是自动管理undoubtedly information

Undo information在系统级的一个特定的保持力时间间隔内被保存（需要确保在保持力时间间隔内的undo信息对重构映像市足够的）

Oracle flashback是在session级生效的

Pl/sql游标可在flashback被关闭前打开，以执行dml语句

Smon通过表smon_scn_time保证了系统时间和scn号的对应关系

包dbms_flashback提供了闪回到指定的系统时间或者scn的功能。一旦flashback在session级被enable,用户可以执行任意查询或者pl/sql包。所有的对于数据库的查询都是运行在指定时间或者scn的数据库中。因此，用户的会话纪录了基于指定时间的数据库映像，当一个会话断开或者另一个会话连接上来，闪回则自动关闭。用户也可以利用包的disable显式的关闭闪回。这个包可以用在登陆触发器里自动使闪回升效，而不需要更改程序代码。

 
   在flashback状态下打开的pl/sql游标可以记录下当游标打开时系统的基于scn或者时间的闪回状态的数据。不同的并发会话或者连接可以将数据库闪回到不同的时间点或者scn,dml,ddl操作和分布式操作在会话运行于flashback状态时,将被禁止运行，在flashback disable之前打开的pl/sql游标能够被用来执行dml.

当我们用基于时间点的enabling flashback时，数据库自动选择一个在这个指定时间之前的5分钟内产生的一个scn,作为闪回的基点。对于细粒度闪回用户可以用scn来enable flashback.

闪回技术只能是闪回dml操作，而不能闪回ddl操作，例如drop,truncate都不能被闪回，这是因为rollback segments不能存储足够的信息来重建以前的object.另外闪回也不能应用于pl/sql包，存储过程，或者函数。

相关操作

DBA必须设定undo保持力足够大以能够重构需要闪回的数据

ALTER SYSTEM SET UNDO_RETENTION=<SECONDS>;seconds值委undo数据保持的秒数。

Flashback view是由undo retention interval来限制的。

包DBMS_FLASHBACK提供了需求接口

call dbms_flashback.enable_at_time(‘9-nov-01:11:00:00’);

select * from employees;

call dbms_flashback.disable();

enable_at_time：会话级的enable flashback，映像时间被设定为最接近指定时间戳的scn

enable_at_system_change_number:将数据库闪回到指定的scn号。

get_system_change_number:返回当前的scn。

disable：这个存储过程允许我们在整个会话内停止flashback并将你带回当前时间的数据状态。

dbms_flashback.enable存储过程不可以在有活动事务的时候执行，并且，这个包不能用sys身份执行。

在使用DBMS_FLASHBACK.ENABLE_AT_TIME前，你必须设定你的NLS_DATE_FORMAT的精确程度，Oracle默认的是精确到天

示例

1、使用闪回查询查询误删除数据。 
SQL>; create user flashback identified by flashback; 
用户已创建 
SQL>; grant connect, resource to flashback; 
授权成功。 
SQL>; grant execute on dbms_flashback to flashback; 
授权成功。 
SQL>; conn 
请输入用户名:  flashback/flashback 
已连接。 
SQL>; set echo on 
SQL>; create table t(a date); 
表已创建。 
15:40:18 SQL>; select * from t; 
未选定行 
15:40:22 SQL>; insert into t select sysdate from dual; 
已创建 1 行。 
15:40:35 SQL>; insert into t select sysdate from dual; 
已创建 1 行。 
15:40:42 SQL>; insert into t select sysdate from dual; 
已创建 1 行。 
15:40:46 SQL>; commit; 
提交完成。 
15:40:59 SQL>; select to_char(a,'yyyy-mm-dd hh:mi:ss') from t; 
TO_CHAR(A,'YYYY-MM- 
------------------- 
2004-03-22 03:40:35 
2004-03-22 03:40:42 
2004-03-22 03:40:46 

15:41:06 SQL>; 
15:42:06 SQL>; 
15:45:37 SQL>; 
15:54:18 SQL>; delete from t where rownum<2; 
已删除 1 行。 
15:54:41 SQL>; commit; 
提交完成。 
15:54:42 SQL>; select * from t; 
A 
---------- 
22-3月 -04 
22-3月 -04 

15:55:55 SQL>; execute DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2004-03-22 15:50:00 
','yyyy-mm-dd hh24:mi:ss')); 
PL/SQL 过程已成功完成。 
15:56:32 SQL>; select * from t; 
A 
---------- 
22-3月 -04 
22-3月 -04 
22-3月 -04 

15:56:38 SQL>; select to_char(a,'yyyy-mm-dd hh:mi:ss') from t; 
TO_CHAR(A,'YYYY-MM- 
------------------- 
2004-03-22 03:40:35 
2004-03-22 03:40:42 
2004-03-22 03:40:46 
15:56:45 SQL>; 

2、使用闪回查询恢复误删数据 
16:18:57 SQL>; insert into t select sysdate from dual; 
已创建 1 行。 
16:19:05 SQL>; insert into t select sysdate from dual; 
已创建 1 行。 
16:19:08 SQL>; commit; 
提交完成。 
16:19:10 SQL>; 
16:20:58 SQL>; 
16:24:01 SQL>; 
16:25:39 SQL>; insert into t select sysdate from dual; 
已创建 1 行。 
16:25:44 SQL>; insert into t select sysdate from dual; 
已创建 1 行。 
16:25:45 SQL>; commit; 
提交完成。 
16:25:47 SQL>; select to_char(a,'yyyy-mm-dd hh:mi:ss') from t; 
TO_CHAR(A,'YYYY-MM- 
------------------- 
2004-03-22 04:25:45 
2004-03-22 04:19:05 
2004-03-22 04:19:08 
2004-03-22 04:25:44 

16:26:05 SQL>; select to_char(a,'yyyy-mm-dd hh24:mi:ss') from t; 
TO_CHAR(A,'YYYY-MM- 
------------------- 
2004-03-22 16:25:45 
2004-03-22 16:19:05 
2004-03-22 16:19:08 
2004-03-22 16:25:44 

16:26:15 SQL>; delete from t where rownum<4; 
已删除3行。 
16:26:38 SQL>; commit; 
提交完成。 
16:26:40 SQL>; select to_char(a,'yyyy-mm-dd hh24:mi:ss') from t; 
TO_CHAR(A,'YYYY-MM- 
------------------- 
2004-03-22 16:25:44 

16:26:42 SQL>; declare 
16:27:36   2  cursor flash_recover is 
16:27:36   3  select * from t; 
16:27:36   4  t_recode t%rowtype; 
16:27:36   5  begin 
16:27:36   6  DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2004-03-22 16:26:05','yyyy 
mm-dd hh24:mi:ss')); 
16:27:36   7  open FLASH_RECOVER; 
16:27:36   8  DBMS_FLASHBACK.DISABLE; 
16:27:36   9  loop 
16:27:36  10  FETCH FLASH_RECOVER INTO t_recode; 
16:27:36  11  EXIT WHEN FLASH_RECOVER%NOTFOUND; 
16:27:36  12  insert into t values (t_recode.a); 
16:27:36  13  end loop; 
16:27:36  14  CLOSE FLASH_RECOVER; 
16:27:36  15  commit; 
16:27:36  16  end; 
16:27:36  17  / 
PL/SQL 过程已成功完成。 
16:27:37 SQL>; select to_char(a,'yyyy-mm-dd hh24:mi:ss') from t; 
TO_CHAR(A,'YYYY-MM- 
------------------- 
2004-03-22 16:19:05 
2004-03-22 16:19:08 
2004-03-22 16:25:44 

16:27:48 SQL>;

我们可以看到利用游标我们可以将丢失的数据找回来，但是我们同时也发现，在找回数据的时候我们丢失了一条数据，这个就是我们前面介绍的在应用基于时间点的闪回的时候，oracle数据库自动选择一个在这个指定时间之前的5分钟内产生的一个scn,作为闪回的基点。在我上面操作的例子中，由于我在第二次提交后两条数据和删除之间的时间间隔太短，而这里面的scn和系统时间对照表5分钟刷新一次，距离我闪回的时间最近的scn不是最后一次提交的时间，而是插入前两条记录后提交的时间，导致后面插入的所有数据无法闪回，所以如果发生插入纪录和删除记录时间间隔太短，中间恰好没有scn记录时，这些数据将无法恢复。

flashback简易用法

使用AS OF SCN 或 AS OF TIMESTAMP子句

select count(*) from emp AS OF SCN :scn;

表smon_scn_time记录scn和time的对应关系

这是一个sys用户的表。 
16:46:37 SQL>; select count(*) from sys.smon_scn_time; 
  COUNT(*) 
---------- 
      1440 
可以看出表内共1440条记录。 
16:47:17 SQL>; select to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_ 
time where rownum<10; 
TO_CHAR(TIME_DP,'YY 
------------------- 
2004-03-19 16:24:51 
2004-03-19 16:29:58 
2004-03-19 16:35:05 
2004-03-19 16:40:12 
2004-03-19 16:45:20 
2004-03-19 16:50:27 
2004-03-19 16:55:39 
2004-03-19 17:00:47 
2004-03-19 17:05:54

smon_scn_time每5分钟记录一次对照，当我们指定时间后，smon利用这个表查找前面的最近scn号。

这个表每过5分钟自动删除最旧记录，加入新纪录，记录总数一直保持1440，也就是5天时间，所以如果我们通过enable_at_time是无法恢复5天前的数据的，这个时候就必须使用scn。