用Oracle9i特性FlashbackQuery恢复误删数据-服务器专区

用Oracle9i特性FlashbackQuery恢复误删数据

作者：网络 eygle 编辑： IT168 2006-03-10 00:00

　　【IT168 服务器学院】下午接到研发工程师的电话，说误删除了部分重要数据，并且已经提交，需要恢复。

　　登陆到数据库上查看，由于是Oracle9iR2，首先尝试使用flashback query闪回数据。

　　首先确认数据库的SCN变化:

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
NAME　　　 FSCN　   NSCN FIRST_TIME
-------------------------------------------------- -------------------
/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: 12927633776
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12927633776
select count(*) from hs_passport as of scn 12927633776
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

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.

　　研发人员确认，已经可以满足需要，找回误删除部分数据，至此闪回恢复成功完成

关注我们