Oracle資料庫資料丟失恢複的幾種方法總結_oracle

根據oracle資料庫的特點和提供的工具，主要方法有以下幾種方法：

1.      利用邏輯備份使用import工具遺失資料的表
2.      利用物理備份來通過還原資料檔案並進行不完全恢複
3.      利用dbms_logmnr包從redo log檔案中恢複
4.      利用flashback特性恢複資料

前提

為了方便使用方法的介紹，上述恢複方法都將基於以下情境進行：系統管理員在前一天晚上11點用export對資料庫做了全庫邏輯備份，然後對所有資料檔案進行了熱備份。第二天上午10點，系統管理員在修改表TFUNDASSET的資料時，由於修改語句的條件寫錯了，導致一批記錄（幾千條）的ztm欄位被修改成了錯誤的值，而且已經提交。這個表是資產表，相對而言資料變化不頻繁。

一、利用邏輯備份使用import工具恢複丟失的資料

export/import是oracle提供的用於對資料庫進行邏輯備份的工具。該工具適用於備份那些資料量不大、業務量不多的資料庫系統。因為如果在前一天晚上11點用export做了邏輯備份，那麼當今天上午10點資料庫意外崩潰時，從備份起到資料庫崩潰的這段時間裡的資料修改操作（包括DDL和DML）都會丟失。如果遺失資料內的表上的資料是相對比較穩定，也就是說該表上基本沒有DML操作，例如標準代碼錶、分區表裡的曆史資料，那麼採用import來匯入該表可以比較完整的恢複資料。如果該表是經常變化的業務表，那麼這些丟失的資料只能根據業務情況從紙質記錄恢複，或者其他途徑恢複。

▲樣本如下：這個表是一個資產表。相對來說，今天系統運行中修改的資料較少，丟失的資料量可以承受或者可以從別的途徑恢複。那就可以用import來恢複。

方法一：

1、把這個表的資料備份到另一個表：

2、刪除該表的記錄:

3、執行下面的命令：

這個命令中在關鍵字tables中指定需要匯入的表名字，ignore=y表示忽略表已經存在的錯誤。

4、匯入結束後，檢查表中的記錄，並用適當的方法恢複當天的修改。

方法二：

1、 把需要恢複的表匯入到另一個使用者下面：

2、檢查資料以後，把原表記錄刪除：

3、然後從另一使用者表中插入回去：

4、 資料量比較大時可以採用如下方法：

二、利用物理備份來通過還原資料檔案並進行不完全恢複

如果資料庫運行在歸檔模式下，那麼可以通過使用以前的資料檔案備份進行還原，然後利用歸檔日誌進行前滾，直到復原到錯誤操作的時間點前，然後重設記錄檔開啟資料庫。

可以通過下列方法確認是否是運行在歸檔模式：

如果是如上所示，那麼就是運行在歸檔模式了。

▲假定在前一天晚上11點做了全庫物理備份，那麼可以考慮如下恢複：

1、關閉資料庫：

由於資料庫的不完全恢複必須在一個關閉的資料庫上實施，利用一箇舊的資料庫的備份還原，然後用日誌根據需要逐步前滾，而不能還原一個新的備份，再回退到某個時間點。

通知各用戶端資料庫將關閉，然後發出：

資料庫已經關閉。

已經卸載資料庫。

ORACLE 常式已經關閉。

2、確定錯誤操作的時間：

可以根據操作員的估計來確定不完全恢複需要前滾停止的時間，也可以利用LogMiner來分析記錄檔（這個工具將在後面介紹），找出錯誤操作的準確時間。

3、還原資料檔案：

先對當前的資料庫檔案進行備份，然後再用以前的最近一次備份覆蓋現有資料檔案。注意：不覆蓋現有的控制檔案。

4、基於時間點恢複，啟動資料庫到裝配狀態：

這樣資料庫就恢複到了2015年10月20日的9點58分零秒。

然後再利用業務資料補充這段時間內的資料。

三、利用dbms_logmnr包從log檔案中恢複

這個包是由Oracle提供，與dbms_logmnr_d包配合使用可以方便地分析聯機記錄檔和歸檔記錄檔，從這些記錄檔中提取出所有對資料庫的更改操作。

在使用這個包之前，需要先做一些設定和修改：

1、開啟initorcl.ora，修改初始化參數utl_file_dir，設定dbms_logmnr_d包將要使用的資料字典檔案的放置目錄。

然後重啟資料庫使參數生效。

2、以sys使用者串連到資料庫執行dbmslmd.sql指令碼重建dbms_logmnr_d這個包。

應用Logminer分析重做記錄檔的操作主要有以下步驟：

      ● 使用dbms_logmnr_d裡的預存程序build建立一個外部資料字典檔案；

      ● 使用dbms_logmnr裡的預存程序add_logfile添加要分析的記錄檔；

      ● 使用dbms_logmnr裡的預存程序start_logmnr啟動分析；

      ● 查詢與dbms_logmnr相關的幾個視圖來擷取記錄檔內容；

      ● 使用dbms_logmnr裡的預存程序end_logmnr結束分析。

▲下面詳細講述使用的過程

1、使用dbms_logmnr_d裡的預存程序build建立一個外部資料字典檔案：

2、使用dbms_logmnr裡的預存程序add_logfile添加要分析的記錄檔到待分析檔案清單：

如果沒有運行在歸檔模式，那麼由於重做記錄檔的迴圈使用可能導致記錄檔被覆蓋而無法擷取到所要尋找的恢複條目。如果運行在歸檔模式，則可以通過查看$ORACLE_HOME\admin\orcl\bdump目錄下的alert_orcl.log裡記錄檔歸檔的時間和錯誤操作的時間來確定加入哪些歸檔記錄檔到待分析的檔案清單中去。

注意：執行以上過程時logfilename參數需要寫記錄檔的全路徑，否則會報錯。重複以上操作直到把所有需要分析的檔案都加到列表中去。這樣就可以啟動進行分析。

3、使用dbms_logmnr裡的預存程序start_logmnr啟動分析；

這樣就可以通過下面的查詢來擷取記錄檔的內容了。

4、查詢與dbms_logmnr相關的幾個視圖來擷取記錄檔內容；

這樣就可以找出要恢複所需的語句。注意：v$logmnr_contents只對執行dbms_logmnr.start_logmnr的會話有效，如果通過其他會話或者使用dbms_logmnr.end_logmnr終止了分析，都將不能訪問v$logmnr_contents的資料。如果要使其他會話也能擷取到這些資料，可以通過另外建表來實現，如：

create table undo_sql as select * from v$logmnr_contents。

再對undo_sql進行授權，其他使用者就可以訪問v$logmnr_contents的資料了。

5、使用dbms_logmnr裡的預存程序end_logmnr結束分析。

使用完成以後用下面的命令來結束分析活動：exec dbms_logmnr.end_logmnr;

這樣就釋放了分配給logminer的資源（記憶體和資料結構）。

從上面的過程可知，如果是更新的資料量比較大，而記錄檔比較小，就可能會導致記錄檔的切換。如果沒有及時去挖掘記錄檔（沒有運行在歸檔模式），那麼可能會由於記錄檔的迴圈使用而導致資料不可恢複。如果運行在歸檔模式，也可能由於需要分析的記錄檔比較多而時間較長。

四、利用flashback新特性恢複資料

Oracle9i 開始提供了閃回查詢（Flashback Query）功能，對於誤刪除或者誤更新並且已經commit了的情況提供了簡便快捷的恢複方法；而在Oracle 提供閃回查詢之前，碰到這種情況只能通過備份來進行基於時間點的恢複或者使用logmnr挖掘日誌來恢複，無疑這比閃回查詢要麻煩而且費時。

使用這個Flashback Query特性的前提條件：

1. 資料庫必須處於Automatic Undo Management 狀態。

2. 最大可以閃回查詢的時間段由UNDO_RETENTION 初始化參數（單位為秒）指定

可以通過ALTER SYSTEM SET UNDO_RETENTION = <seconds>;來動態修改參數值。

▲如何使用Flashback Query來恢複資料呢？

1. 通過SQL

使用SELECT 語句的AS OF 來進行閃回查詢，文法如下：

使用AS OF 關鍵字來對錶，視圖或者物化視圖進行Flashback Query，如果指定了SCN，那麼expr 部分必須是一個數字，如果指定了TIMESTAMP，那麼expr 必須是一個timestamp類型的值。查詢結果將返回在指定的SCN 或者時間點上的資料。

下面我們使用scott 方案來作一個實驗。

如果想在update 的子查詢部分使用AS OF，那麼該查詢只能返回一條記錄，否則將會報錯。

可以通過添加一個暫存資料表作為中轉，然後再作更新，如下：

2．通過DBMS_FLASHBACK包來恢複

DBMS_FLASHBACK 包提供了以下幾個函數：

ENABLE_AT_TIME：設定當前SESSION 的閃回查詢時間

ENABLE_AT_SYSTEM_CHANGE_NUMBER：設定當前SESSION 的閃回查詢SCN

GET_SYSTEM_CHANGE_NUMBER：取得當前資料庫的SCN

DISABLE：關閉當前SESSION 的閃回查詢

當將一個SESSION 設定為閃回查詢模式之後，後續的查詢都會基於那個時間點或者SCN 的資料庫狀態，如果SESSION 結束，那麼即使沒有明確指定DISABLE，閃回查詢也會自動失效。

當SESSION 運行在閃回查詢狀態時，不允許進行任何DML 和DDL 操作。如果要用DML操作來進行資料恢複就必須使用PL/SQL 遊標。

▲樣本：

通過上面的例子可以看出，只要這個修改的時間不早於sysdate- (UNDO_RETENTION指定的秒數),就可通過這種方式來恢複資料。

對於問題中的批量資料，可以寫個過程來完成擷取到更改前的資料：

然後再用這個暫存資料表裡的資料來更新TFUNDASSET就可以了。

五、總結

比較以上幾種恢複資料的方法的使用過程，我們可以看出：

      ● exp/imp只適合於資料變化不大的表的資料丟失的情況，即使用這種方法處理後也需要從業務辦理資料中修正資料，否則導致資料丟失；

      ● 採用基於時間點的不完全恢複可以恢複丟失的資料，但是需要關關閉資料庫，減少系統可用時間，而且也會丟失恢復點以後的資料；

      ● 使用LogMiner可以較好的恢複資料，但是要求資料庫儘可能運行在歸檔模式，否則也可能導致資料丟失。好處是不用關閉系統，能夠從記錄檔中得到所有的資料。

      ● 使用Flashback最方便和簡潔，可以直接得到修改前的資料，但是需要依賴系統設定，並且需要佔用大量的復原資料表空間。

因此選擇什麼樣的方法來恢複資料，取決於你的系統內容和具體情況，不能生搬硬套。採用正確的方法才能最大程度的減少資料的丟失。

當然，最好是不需要用到這些恢複的辦法。前提是，你必須做好以下的工作：

1、 為不同環境建立不同的資料庫使用者、不同密碼（如果不能使用者不同，一定要密碼不同）；

2、 將owner和應用使用者分開，並做適度授權；

3、 在做DML前，先用同樣的條件做查詢，看根據結果集是否符合預期。

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的協助，如果有疑問大家可以留言交流。