下午接到研发工程师的电话，说误删除了部分重要数据，并且已经提交，需要恢复。
登陆到数据库上查看，由于是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.dbf1292994196812929942881 2005-06-22 14:38:28
/mwarch/oracle/1_52414.dbf1292994288112929943706 2005-06-22 14:38:32
/mwarch/oracle/1_52415.dbf1292994370612929944623 2005-06-22 14:38:35
/mwarch/oracle/1_52416.dbf1292994462312929945392 2005-06-22 14:38:38
/mwarch/oracle/1_52417.dbf1292994539212929945888 2005-06-22 14:38:41
/mwarch/oracle/1_52418.dbf1292994588812929945965 2005-06-22 14:38:44
/mwarch/oracle/1_52419.dbf1292994596512929948945 2005-06-22 14:38:45
/mwarch/oracle/1_52420.dbf1292994894512929949904 2005-06-22 14:46:05
/mwarch/oracle/1_52421.dbf1292994990412929950854 2005-06-22 14:46:08
/mwarch/oracle/1_52422.dbf1292995085412929951751 2005-06-22 14:46:11
/mwarch/oracle/1_52423.dbf1292995175112929952587 2005-06-22 14:46:14
...................
/mwarch/oracle/1_52498.dbf1293013897512930139212 2005-06-22 15:55:57
/mwarch/oracle/1_52499.dbf1293013921212930139446 2005-06-22 15:55:59
/mwarch/oracle/1_52500.dbf1293013944612930139682 2005-06-22 15:56:00
NAME FSCN NSCN FIRST_TIME
------------------------------ -------------------- -------------------- -------------------
/mwarch/oracle/1_52501.dbf1293013968212930139915 2005-06-22 15:56:02
/mwarch/oracle/1_52502.dbf1293013991512930140149 2005-06-22 15:56:03
/mwarch/oracle/1_52503.dbf1293014014912930140379 2005-06-22 15:56:05
/mwarch/oracle/1_52504.dbf1293014037912930140610 2005-06-22 15:56:05
/mwarch/oracle/1_52505.dbf1293014061012930140845 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.
研发人员确认，已经可以满足需要，找回误删除部分数据，至此闪回恢复成功完成。