oracle之DELETE後的恢復

liqilin0429發表於2012-02-13
Oracledelete

由於開發人員在早晨大概在9點左右不小心刪除了某張表的一些資料（delete from logmessage where where taskid='6471';），目前急需恢復刪除的資料，恢復步驟如下
第一：首先通過大概時間點來查詢SCN(將刪除時間轉換為scn)
SQL> select timestamp_to_scn(to_timestamp('2012-02-13 09:00:00','YYYY-MM-DD HH:MI:SS')) from dual;
 
TIMESTAMP_TO_SCN(TO_TIMESTAMP(
------------------------------
                      16360271
第二：通過SCN查詢被刪除的資料(將logmessage表中的scn點的資料取出,然後可以根據這個資料進行還原操作)
SQL> select * from logmessage as of scn 16360271 where taskid='6471';
 
LOGID                TASKID     LOGNAME                                                                          LOGTIME     TESTNET              TESTTYPE                                           TASKTYPE                                           TESTREGION                                                                       REGIONID             LOGDATAQUAN          EQUIPMENTNAME        SAVEPATH                                                                         ISDEL      UPDATENAME                                                                       UPUSER                                             TESTTIME    TASKNAME                                                                         TESTTIMELONG TESTDATAID
-------------------- ---------- -------------------------------------------------------------------------------- ----------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------- ----------- -------------------------------------------------------------------------------- ------------ ----------
10542                6471       20110314_中國_3網測試_市區測試與評估_市區mos及do上傳_0314-142146-2.loc           2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    23920640             ZTE AC8710           #Path/6471\20110314_中國_3網測試_市區測試與評估_市區mos及do上傳_0314-142146-2.loc            20110314_中國_3網測試_市區測試與評估_市區mos及do上傳_0314-142146-2.loc           924                                                2011-3-14   2月10日資料測試資料一                                                                7756.203 17043
10543                6471       20110314_中國_3網測試_市區測試與評估_市區mos及do上傳_v1_0314-092232-2.loc        2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    22413311             ZTE AC8710           #Path/6471\20110314_中國_3網測試_市區測試與評估_市區mos及do上傳_v1_0314-092232-2.loc            20110314_中國_3網測試_市區測試與評估_市區mos及do上傳_v1_0314-092232-2.loc        924                                                2011-8-10   2月10日資料測試資料一                                                                 6650.39 17044
10544                6471       20110314_中國_3網測試_市區測試與評估_市區資料下載_v1_0314-092029-4.loc           2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    16646143             ZTE AC8710           #Path/6471\20110314_中國_3網測試_市區測試與評估_市區資料下載_v1_0314-092029-4.loc            20110314_中國_3網測試_市區測試與評估_市區資料下載_v1_0314-092029-4.loc           924                                                2011-8-10   2月10日資料測試資料一                                                                6751.609 17045
10545                6471       20110314_中國_3網測試_市區測試與評估_市區資料下載_v1_0314-142703-4.loc           2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    19054591             ZTE AC8710           #Path/6471\20110314_中國_3網測試_市區測試與評估_市區資料下載_v1_0314-142703-4.loc            20110314_中國_3網測試_市區測試與評估_市區資料下載_v1_0314-142703-4.loc           924                                                2011-8-10   2月10日資料測試資料一                                                                7338.906 17046
10546                6471       20110318_中國_3網測試_市區測試與評估_市區倉山mos及do上傳_v1_0318-143629-2.loc    2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    4702207              ZTE AC8710           #Path/6471\20110318_中國_3網測試_市區測試與評估_市區倉山mos及do上傳_v1_0318-143629-2.loc            20110318_中國_3網測試_市區測試與評估_市區倉山mos及do上傳_v1_0318-143629-2.loc    924                                                2011-3-21   2月10日資料測試資料一                                                                2003.719 17047
10547                6471       20110321_中國_3網測試_市區測試與評估_市區資料下載_v1_0321-143424-4.loc           2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    21626879             ZTE AC8710           #Path/6471\20110321_中國_3網測試_市區測試與評估_市區資料下載_v1_0321-143424-4.loc            20110321_中國_3網測試_市區測試與評估_市區資料下載_v1_0321-143424-4.loc           924                                                2011-8-10   2月10日資料測試資料一                                                                6863.657 17048
10548                6471       20110322_中國_3網測試_市區測試與評估_市區mos及do上傳_v1_0322-090019-2.loc        2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    22724607             ZTE AC8710           #Path/6471\20110322_中國_3網測試_市區測試與評估_市區mos及do上傳_v1_0322-090019-2.loc            20110322_中國_3網測試_市區測試與評估_市區mos及do上傳_v1_0322-090019-2.loc        924                                                2011-3-21   2月10日資料測試資料一                                                                8096.219 17049
10549                6471       20110322_中國_3網測試_市區測試與評估_市區資料下載_v1_0322-090735-4.loc           2012-2-10 1 電信CDMA             語音                                               自動路測                                           市區                                                                             2                    28060278             ZTE AC8710           #Path/6471\20110322_中國_3網測試_市區測試與評估_市區資料下載_v1_0322-090735-4.loc            20110322_中國_3網測試_市區測試與評估_市區資料下載_v1_0322-090735-4.loc           924                                                2011-8-10   2月10日資料測試資料一                                                                8039.563 17050
 
8 rows selected

第三：確認查詢被刪除後表中總的有多少資料
SQL> select count(*) from logmessage;
 
  COUNT(*)
----------
      2515
SQL> select * from logmessage where where taskid='6471';這個查詢到並沒有taskid='6471'的值在logmessage表中

第四：通過SCN的值來恢復被刪除的資料（taskid='6471'）
SQL> insert into logmessage  select * from logmessage as of scn 16360271 where taskid='6471';
 
8 rows inserted

SQL> commit;
 
Commit complete


第五：確認被恢復的資料
SQL> select count(*) from logmessage;
 
  COUNT(*)
----------
      2523

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

相關文章