志當存高遠,功到自然成!

少年強則中國強,少年進步則中國進步!

BlogJava

管理

53 Posts :: 2 Stories :: 2 Comments :: 0 Trackbacks

常用鏈接

留言簿(5)

隨筆分類

隨筆檔案

搜索

閱讀排行榜

評論排行榜

使用Oracle9i的新特性Flashback Query恢復誤刪除數據

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.

posted on 2008-05-25 20:13 存鷹之心于高遠,取鷹之志而凌云,習鷹之性以涉險,融鷹之神在山巔. 閱讀(215) 評論(0) 編輯收藏所屬分類: Oracle

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: ORACLE在線重建索引語句通過分析SQL語句的執行計劃優化SQL(總結) Oracle調優(入門及提高篇) ORACLE9i連接SYBASE的透明網關的配置 Oracle的換行符從sybase的存儲過程轉向oracle的存儲過程的不同點使用Oracle9i的新特性Flashback Query恢復誤刪除數據

志當存高遠,功到自然成!

常用鏈接

留言簿(5)

隨筆分類

隨筆檔案

文章檔案

DB

應用服務器管理與調優

腳本技術

搜索

最新評論

閱讀排行榜

評論排行榜