Flashback Query恢復誤刪除資料(轉）

yhj20041128001發表於2012-03-30
先確認資料庫的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 

當前的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.




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23757700/viewspace-719993/，如需轉載，請註明出處，否則將追究法律責任。

相關文章