使用Oracle9i的新特性Flashback Query恢復誤刪除數據
作者：
eygle |
English Version
鏈接：
http://www.eygle.com/archives/2005/06/eoaoracle9iaeai.html
下午接到研發工程師的電話，說誤刪除了部分重要數據，并且已經提交，需要恢復。
登陸到數據庫上查看，由于是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.
|
研發人員確認，已經可以滿足需要，找回誤刪除部分數據，至此閃回恢復成功完成。