[基础]9i新特性之五闪回查询

txfy

9i新特性之五闪回查询
                                               -----txfy
1:基础理论：
  这是9i中比较有用的一个新特性,可以不需要用热备份就能够恢复人为误操作导致的数据丢失.前半部分是基础知识和原理性介绍,后半部分借鉴了gototop老大ncn上的一篇文章,和brucelau在csdn上的一篇文章(在此表示感谢),写了两个实例,和一些注意事项以及一些探讨,希望大家有空也研究研究!有错误请指正.
这一新特性允许用户查看过去某一时间点数据库的映像视图。
用户可以利用系统时间或者system change number(scn)号来指定这个只读视图。只有在那个时间点已经提交的事务才能够被查到。
这个特性的可能应用有：
      ..自我维护过程中的修复
      ..恢复类似于email的应用程序包
      ..用于趋势分析的决策支持系统。
概述：
     数据库系统保证用户对数据库提交的所有查询将得到用户指定时间（服务器端系统时间）的一致的数据库映像，所有的在这个时间点之前提交的事务是可见的，之后提交的不可见。
     在这个某一时间点的数据库映像上可以执行应用。例如一个windows的消费者应用可以增加一个按钮允许用户返回先前的时间点显示当时的账目平衡状况。以前，应用必须存取很多信息。而这个特性允许访问过去的信息，甚至当这些信息并没有显式的指定出来。
     有几个原因导致用户可能需要查询过去的数据。一个重要的应用是自我维护修理，当一些重要的行被意外的从一个表中被删除了，用户希望恢复它。做这样一个修理，用户可以向后移动到一个时间点，查看丢失的行并把他们重新插入现在的表内。但是需要注意不要带来逻辑矛盾。
     另一个潜在的好处是在处理类似email和voice mail的应用包体上。当用户因为按错键意外的删除了一封mail或者声音信息时，他们可以通过移回到固定时间点来恢复删除的mail或者信息并将他们重新插入现在的信息箱内。
    oracle flashback
      ..oracle flashback 的实质是自动管理undo information。
    ..undo information在系统级的在一个特定的保持力时间间隔内被保存。
    ..确保在保持力时间间隔内的undo信息对重构映像是足够的。
    ..oracle flashback是在session级生效的。
    ..pl/sql游标将在flashback被关闭前打开，以用来执行dml语句。
    ..smon通过表smon_scn_time保证了系统时间和scn号的对应关系.
oracle flashback overview
   另外一些好处比如决策支持系统，联机分析等不得不执行一个长时间运行的事务。有时需要执行对过去数据的分析和建模，例如遮光剂随季节变化需求的变化。
   包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.
    oracle flashback的相关操作。
   
   ..DBA必须设定UNDO保持力足够大以能够重构需要闪回的数据。
   ALTER SYSTEM SET UNDO_RETENTION=<SECONDS>;;
   值为UNDO数据保持的秒数。
   ..包DBMS_FLASHBACK提供了需求接口。
   下面的例子介绍了如何应用这个包。
   call dbms_flashback.enable_at_time('9-nov-01:11:00:00');
   select * from employess;
   call dbms_flashback.disable();
  flashback overview
  flashback view是由undo retention interval来限制的，这个参数由数据库管理员指定，没有最大值限制。
  dbms_flashback提供了如下接口：
    ..enable_at_time:会话级的enable flashback.映像时间被设定为最接近指定时间戳的scn.
      ..enable_at_system_change_number:将数据库闪回到指定的scn号。
    ..get_system_change_number:返回当前的scn,利用这个接口，用户能够得到当前scn并储存起来为以后使用。
    ..disable:这个存储过程允许我们在整个会话内停止flashback并将你带回当前时间的数据状态。
  注意：dbms_flashback.enable存储过程不可以在有活动事务的时候执行，并且，这个包不能用sys身份执行。
   下面我们来看一个应用闪回的过程。
     ..在2：10用户可以从表中读出某些数据。
     ..在2：13某些行被意外删除。
     ..在2：15用户启用闪回，并创建视图来访问2：10的时候的数据。smon进程保存了一个转化表，并将这个指定时间转换为相应的scn.一个pl/sql游标被创建，它可以连接当前表和闪回的视图，找出丢失的行。
     ..在2：17，用户使用disable关闭闪回，然后利用游标内的数据重新将丢失数据插回表内。
  注意：闪回技术只能是闪回dml操作，而不能闪回ddl操作，例如drop,truncate都不能被闪回，这是因为rollback segments不能存储足够的信息来重建以前的object.另外闪回也不能应用于pl/sql包，存储过程，或者函数。

2、使用闪回查询查询误删除数据。

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>;
3、使用闪回查询恢复误删数据

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记录时，这些数据将无法恢复。

那么下面我们来看看记录scn和time对应关系的标smon_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
从这里我们看出确实是每5分钟记录一次对照，当我们指定时间后，smon利用这个表查找前面的最近的scn号。所以精确恢复纪录将比较困难，不过写到这我忽然想到，如果查询两个时间点的scn然后取中间的莫个值，多试几次，应该可以做到精确恢复所有记录，呵呵推翻了上面的不可以精确恢复的论断，由于时间关系，这个测试暂时不做，大家有兴趣可以试试。那么这个表是怎么保存记录的呢？经过研究，这个表每过5分钟自动删除最旧纪录，然后加入新纪录，记录总数一直
保持1440，也就是5天时间，所以如果我们通过ENABLE_AT_TIME是无法恢复5天前的数据的，这个时候就必须用scn.但是我们平时是不会纪录scn号的那么碰到这种情况怎么办呢，我以前写过一篇关于logminer的文章，利用logmnr包，我们可以做到这一点。
几点需要注意的：
1：在使用DBMS_FLASHBACK.ENABLE_AT_TIME前，你必须设定你的NLS_DATE_FORMAT的精确程度，Oracle默认的是精确到天，如果你不设定，像上面的例子你不会得到预期结果。
2：看下面的例子，当我们flashback时我们发现sysdate函数返回的时间并没有闪回
16:56:05 SQL>; exec DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2004-03-22 16:26:05','
yyyy-mm-dd hh24:mi:ss'));

PL/SQL 过程已成功完成
3：闪回查询无法恢复到表结构改变之前，因为闪回查询使用的当前的数据字典。
4：你只能在事务开始时进入闪回查询模式，如果之前有DML操作，则必须COMMIT。