一、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的闪回特性极大地简化了恢复操作,同时缩减了类似故障对于业务的影响。