使用Oracle9i的新特性Flashback Query恢复误删除数据

原创 eygle 2005-06-22

482

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

登陆到数据库上查看，由于是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

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

使用Oracle9i的新特性Flashback Query恢复误删除数据

评论