Flashback Query 恢復誤刪除的數據 - oracle -

Flashback Query 恢復誤刪除的數據

時間:2010-01-28 15:22:24   來源:   評論:加載中...   點擊:加載中...
如果你在工作中誤刪除了一部分重要的數據,并且已經提交,當你需要恢復數據的時候,你可以登陸到數據庫上仔細查看,如果是Oracle9iR2,你可...

如果你在工作中誤刪除了一部分重要的數據,并且已經提交,當你需要恢復數據的時候,你可以登陸到數據庫上仔細查看,如果是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.

 

注釋:至此,則可以滿足需要,找回誤刪除的部分數據,閃回恢復成功。



相關熱詞搜索:

 
上一篇:Oracle 11g分區功能新革命
下一篇:基于Oracle高性能動態SQL程序開發
收藏 將此文推薦給朋友
分享到:
10个数复式三中三多少组公式