oracle中怎么用闪回查询,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的闪回特性极大地简化了恢复操作，同时缩减了类似故障对于业务的影响。