Oracle 9i 闪回查询功能的使用方法及案例分享

一、Oracle 9i的闪回查询特性

从Oracle 9i开始Oracle引入了闪回查询（Flashback Query）的特性，通过闪回查询我们可以按照时间戳或SCN来向前查询，获取修改之前的数据镜像，再通过INSERT等操作就可以恢复数据。

闪回查询依赖于回滚段中存储的数据前镜像，在Oracle 9i以前的版本中，通常只要事务提交后，前镜像数据就可以被覆盖，空间可以重用；在Oracle 9i中Oracle引入了自动回滚段管理（Automatic Undo Management，简称AUM）的新特性，在AUM模式下，我们可以通过调整undo_retention参数来设置数据库UNDO信息的保留时间，只要前镜像没有被覆盖，那么闪回就是可能的。

但是需要注意的是，UNDO信息的保留还同UNDO表空间中的可用空间有关，如果空间紧张，为了满足事务的需要，非活动的前镜像信息是可以被覆盖的。

可以使用SELECT语句的AS OF子句来进行闪回查询，具体语法如下：

as of scn ( timestamp) expr 

通过关键词AS OF可以对表、视图、物化视图进行Flashback Query，可以指定SCN或者TIMESTAMP，其中TIMESTAMP是9i中引入的新的数据类型，精确到毫秒的时间单位。

二、Oracle 9i的闪回查询的使用示例

以下通过一个示例来说明如何使用Oracle 9i的闪回查询特性。
首先记录一下当前的时间戳，然后删除测试表中的两条数据并提交：

SQL> connect eygle/eygle
Connected.
SQL> select count(*) from eygle;
  COUNT(*)
----------
        12
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
10-MAR-07 08.37.24.157636 AM +08:00
SQL> delete from eygle where rownum <3;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from eygle;
  COUNT(*)
----------
        10

现在可以根据删除的估算时间向前推移查询，通过适当的时间估计，数据可以通过前镜像被很容易地查询回来：

SQL> select count(*) from eygle as of timestamp (systimestamp -interval'10'second);
  COUNT(*)
----------
        12

也可以获得当前的SCN，通过估算，减小SCN向前进行查询尝试，也可以获取删除之前的数据：

SQL> col scn for 999999999999999
SQL> select dbms_flashback.get_system_change_number scn from dual;
             SCN
----------------
     18995692584

SQL> select count(*) from eygle as of scn 18995690000;
  COUNT(*)
----------
        12

三、使用Oracle 9i闪回查询特性恢复案例

以下是一个生产环境中进行恢复的具体案例：

某日下午接到研发工程师的电话，报告说误删除了部分重要数据，并且已经提交，需要恢复。

登录到数据库上查看，由于是Oracle 9iR2，可以尝试使用flashback query闪回数据。

首先确认数据库的SCN变化，这可以通过v$archived_log视图来查询获得：

SQL> col fscn for 9999999999999999999
SQL> col nscn for 9999999999999999999
SQL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;
NAME                                FSCN          NSCN FIRST_TIME
--------------------------- ------------ ------------- -------------------
/mwarch/oracle/1_52413.dbf   12929941968   12929942881 2005-06-22 14:38:28
/mwarch/oracle/1_52414.dbf   12929942881   12929943706 2005-06-22 14:38:32
/mwarch/oracle/1_52415.dbf   12929943706   12929944623 2005-06-22 14:38:35
/mwarch/oracle/1_52416.dbf   12929944623   12929945392 2005-06-22 14:38:38
/mwarch/oracle/1_52417.dbf   12929945392   12929945888 2005-06-22 14:38:41
/mwarch/oracle/1_52418.dbf   12929945888   12929945965 2005-06-22 14:38:44
/mwarch/oracle/1_52419.dbf   12929945965   12929948945 2005-06-22 14:38:45
/mwarch/oracle/1_52420.dbf   12929948945   12929949904 2005-06-22 14:46:05
/mwarch/oracle/1_52421.dbf   12929949904   12929950854 2005-06-22 14:46:08
/mwarch/oracle/1_52422.dbf   12929950854   12929951751 2005-06-22 14:46:11
/mwarch/oracle/1_52423.dbf   12929951751   12929952587 2005-06-22 14:46:14
...................                                     
/mwarch/oracle/1_52498.dbf   12930138975   12930139212 2005-06-22 15:55:57
/mwarch/oracle/1_52499.dbf   12930139212   12930139446 2005-06-22 15:55:59
/mwarch/oracle/1_52500.dbf   12930139446   12930139682 2005-06-22 15:56:00
/mwarch/oracle/1_52501.dbf   12930139682   12930139915 2005-06-22 15:56:02
/mwarch/oracle/1_52502.dbf   12930139915   12930140149 2005-06-22 15:56:03
/mwarch/oracle/1_52503.dbf   12930140149   12930140379 2005-06-22 15:56:05
/mwarch/oracle/1_52504.dbf   12930140379   12930140610 2005-06-22 15:56:05
/mwarch/oracle/1_52505.dbf   12930140610   12930140845 2005-06-22 15:56:07
                            
14811 rows selected.

当前的SCN为：

SQL> select dbms_flashback.get_system_change_number fscn from dual;
                FSCN
--------------------
         12930142214

使用应用用户尝试闪回：

SQL> connect username/password
Connected.

现有数据如下：

SQL> select count(*) from hs_passport;
  COUNT(*)
----------
    851998

创建恢复表：

SQL> create table hs_passport_recov as select * from hs_passport where 1=0;
Table created.

选择适当的SCN向前进行闪回查询：

SQL> select count(*) from hs_passport as of scn 12929970422;
  COUNT(*)
----------
    861686

尝试多个SCN，获取最佳值（如果能得知具体时间，那么可以获得准确的数据闪回）：

SQL> select count(*) from hs_passport as of scn &scn;
Enter value for scn: 12929941968
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12929941968
  COUNT(*)
----------
    861684
SQL> /
Enter value for scn: 12929928784
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12929928784
  COUNT(*)
----------
    825110
SQL> /
Enter value for scn: 12928000000
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12928000000
select count(*) from hs_passport as of scn 12928000000
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

最后选择恢复到SCN为12929941968的时间点：

SQL> insert into hs_passport_recov select * from hs_passport as of scn 12929941968;
861684 rows created.
SQL> commit;
Commit complete.

研发人员确认，已经可以满足需要，找回了误删除的数据，至此闪回恢复成功完成。
可以看到Oracle的闪回特性极大地简化了恢复操作，同时缩减了类似故障对于业务的影响。