對資料庫而言，最重要寶貴的內容就是其中的資料。資料安全性和完整性是任何商業資料庫存在的底線。審計Audit資料變化、資料物件結構變化的功能在Oracle中是一個可選元件。在日常中，我們經常會有跟蹤審計資料變化的需要，這種時候Oracle Logminer元件就可以幫助我們實現這個目的。

中午臨吃飯前，同事LL提交了一個問題。他的一個開發小組成員在進行開發時，準備的資料總是不知被誰刪除，而且是進行反覆的刪除。開發成員很多，所以希望能夠將刪除的使用者確定出來，並且將資料恢復。

這個問題應該在一些開發團隊比較常見，開發環境特別是資料庫環境是共享的。大家進行開發中，難免會將他人的資料刪除破壞。筆者的解決方法也是兩個部分，資料恢復和刪除使用者定位。

環境說明

筆者開發環境是Oracle 11gR2。刪除操作設計的資料表有多個，但初步確定是同一批誤刪除過程，所以找一張資料表進行研究分析。

SQL> select * from v$version;

BANNER

----------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

SQL> select * from ref_PSB;

PSB_CODE CREATE_USER CREATE_DATE UPDATE_USER UPDATE_DATE PSB_NAME

-------- -------------------- ----------- -------------------- ----------- ----------

在筆者開始入手的時候，資料表ref_PSB已經全部被清除，沒有留下資料痕跡。

誤刪除資料恢復

根據開發人員反映，誤刪除資料操作是在一兩個小時內進行的。這樣選擇使用閃回資料的方法是最迅速方便的手段。對資料閃回，請參見《使用閃回挽救我們的資料》http://space.itpub.net/17203031/viewspace-683706。原理是利用Undo中的資料映像，對應指定時間或者SCN的時點進行查詢。

首先，嘗試閃回查詢到兩小時前的資料。

SQL> select * from ref_PSB as of timestamp to_timestamp('2011-03-21 10:00:00','yyyy-mm-dd hh24:mi:ss');

PSB_CODE CREATE_USER CREATE_DATE UPDATE_USER UPDATE_DATE PSB_NAME

-------- -------------------- ----------- ---------------- ----------

CN 1 2011/3/18 Test

AUAU Ansel 2011/3/21 Test

EN 1 2011/3/18 Test

定位到當天10:00時，資料表ref_PSB資料還是存在的，並且和開發團隊確認的確是被刪除的那部分資料。接下來可以嘗試進行恢復。

SQL> insert into ref_PSB select * from ref_PSB as of timestamp to_timestamp('2011-03-21 10:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> commit;

SQL> select * from ref_PSB; //資料被恢復

PSB_CODE CREATE_USER CREATE_DATE UPDATE_USER UPDATE_DATE PSB_NAME

-------- -------------------- ----------- ----------------- ------

CN 1 2011/3/18 Test

AUAU Ansel 2011/3/21 Test

EN 1 2011/3/18 Test

這樣，資料被成功恢復。其他資料表於此同理。

在此處注意一下，Oracle中有一個引數是undo_retention，預設是900s，也就是約15分鐘。有一種觀點認為這個是支援Undo閃回資料的時間，實際上是不準確的。Undo閃回查詢支援的範圍與Undo Tablespace的設定大小，系統繁忙程度和其他一些引數設定相關。很多時候，超過15分鐘的閃回也是支援的。如果閃回的時間太長，Oracle是會報錯的。

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

這樣，我們就利用閃回flashback“拯救”回我們被誤刪的資料。下一個問題略複雜，就是定位到是誰刪除了資料？

Logminer進行審計挖掘

對Oracle來說，只要進行ddl操作或者提交事務，資料的結構或者內容就發生了變化。所有的變化，都會記錄在redo log日誌中，以序列化方式進行儲存。Oracle進行所謂的先寫日誌的操作，保證所有的commit操作都會被落實在資料庫中，不會發生事務不完整的現象。同時，配合檢查點checkpoint機制，減少每次例項恢復過程的前滾量。

這樣一來無論是DDL操作還是DML操作，實際上都是能夠在Oracle redo log中找到對應的記錄。Redo Log就變成了一個審計資訊寶庫。如果開啟了歸檔模式archived mode，資料庫變化就可以進行連續的追蹤審計跟蹤。於是，Oracle提供了Logminer元件幫助我們進行挖掘分析工作。

首先，我們需要定位到進行誤刪除操作的redo log檔案是哪個？是否已經被歸檔。

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------

1 1 496 314572800 512 2 NO CURRENT 103856429 2011/3/21 7 281474976710

2 1 494 314572800 512 2 YES INACTIVE 103612266 2011/3/19 2 103679841 2011/3/20 6

3 1 495 314572800 512 2 YES INACTIVE 103679841 2011/3/20 6 103856429 2011/3/21 7

SQL> set heading off;

SQL> select * from v$log;

1 1 496 314572800 512 2 NO CURRENT 103856429 2011/3/21 7:01:02 281474976710655

2 1 494 314572800 512 2 YES INACTIVE 103612266 2011/3/19 23:09:30 103679841 2011/3/20 6:33:02

3 1 495 314572800 512 2 YES INACTIVE 103679841 2011/3/20 6:33:02 103856429 2011/3/21 7:01:02

注意，當前的online log組的第一條記錄（標紅），狀態為current，表示正在進行處理使用。該檔案對應的開始時間為2011/3/21 7:01:02，結束時間為極大值，說明未確定。發生錯誤刪除的時間是上午某個時間。所以，我們可以確定，進行誤刪除的語句記錄一定在redo log group 1中。下面定位到檔名。

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

---------- -------- ---------------------

1 ONLINE /BSNdata01/oradata/BSNDEV/redo01.log NO

1 ONLINE /BSNdata03/oradata/BSNDEV/redo01b.log NO

2 ONLINE /BSNdata01/oradata/BSNDEV/redo02.log NO

2 ONLINE /BSNdata03/oradata/BSNDEV/redo02b.log NO

（篇幅原因，省略部分…）

可見，group1日誌組有兩個online成員member。兩者內容相同，物理位置不同，互為備份使用。這樣，分析物件確定。

接下來，使用logminer元件。首先，載入分析檔案列表。

SQL> exec dbms_logmnr.add_logfile('/BSNdata01/oradata/BSNDEV/redo01.log',Options => dbms_logmnr.NEW);

第二步，因為在這個過程中，沒有發生資料字典刪除的情況，所以直接使用online當前字典就可以解決問題。

SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

其中，start_logmnr表示開始進行日誌分析，options中的Dict_Form_ONLINE_CATALOG表示使用當前的資料字典進行分析，修飾生成語句。

對分析完的Log日誌結果，可以透過查詢v$logmnr_contents，進行分析。

--先看一下有多少記錄

SQL> select count(*) from v$logmnr_contents;

COUNT(*)

----------

61048 –數目過大，可以應用一定的篩選條件，進行篩選。

SQL> select count(*) from v$logmnr_contents where upper(SQL_REDO) like '%REF_PSB%' and upper(SQL_REDO) like '%DELETE%' and seg_name='REF_PSB';

COUNT(*)

----------

12

SQL> select scn, timestamp, sql_redo, client_id from v$logmnr_contents where upper(SQL_REDO) like '%REF_PSB%' and upper(SQL_REDO) like '%DELETE%' and seg_name='REF_PSB';

SCN TIMESTAMP SQL_REDO CLIENT_ID

---------- -------------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------

104016620 2011/3/21 11:05:34 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'AUAU' and "CREATE_USER" =

104016620 2011/3/21 11:05:34 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'CN' and "CREATE_USER" = '

104016620 2011/3/21 11:05:34 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'EN' and "CREATE_USER" = '

104016665 2011/3/21 11:06:10 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'AUAU' and "CREATE_USER" =

104016665 2011/3/21 11:06:10 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'CN' and "CREATE_USER" = '

104016665 2011/3/21 11:06:10 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'EN' and "CREATE_USER" = '

104016676 2011/3/21 11:06:12 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'ABC' and "CREATE_USER" =

104017920 2011/3/21 11:17:19 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'AUAU' and "CREATE_USER" =

104017935 2011/3/21 11:17:22 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'ABC' and "CREATE_USER" =

104021463 2011/3/21 11:42:44 delete from "BSN_COMMON"."REF_PSB" where ROWID = 'AAAZpTAAYAAAAMwAAB';

104021705 2011/3/21 11:44:41 delete from "BSN_COMMON"."REF_PSB" where ROWID = 'AAAZpTAAYAAAAMWAAA';

104021907 2011/3/21 11:45:39 delete from "BSN_COMMON"."REF_PSB" where "PSB_CODE" = 'ABC' and "CREATE_USER" =

12 rows selected

經過一系列的篩選，我們獲取到一個12條記錄與REF_PSB有關刪除delete操作的結果集合。這裡面詳細記載了操作過程。

1、在“2011/3/21 11:05:34”有使用者執行了操作，將原有的三條資料進行刪除delete。反映在sql redo上的就是對應的delete操作語句，因為三條記錄的時間相同（SCN相同），必然是直接的delete ref_PSB語句。

2、在之後的“2011/3/21 11:06:10”和之後一些時間點，有使用者執行了全表的delete操作。並且將開發組其他資料進行刪除。值得注意的是一個PSB_CODE=‘ABC’的操作，這個資料並沒有出現在誤刪除之後，也沒有出現在誤刪除之前。

到此，我們藉助Logminer，發現了現場過程。唯一美中不足的是v$logmnr_contents中的client_id為空，沒有辦法顯示出執行客戶端的任何資訊。通常，如果我們直接使用客戶端進行連線，如sqlplus或者pl/sql developer，是會在這裡留下個人資訊痕跡。但是此處沒有留下，只有可能誤刪除使用者是透過前端應用程式進行刪除，就不會有痕跡留下。

問題似乎無解了。但是我們發現了PSB_CODE=’ABC’這個痕跡。ABC是開發組一個同事的姓名縮寫，難道是於此同事相關。結果透過溝通，發現原來是執行測試指令碼時，進行了全表刪除操作。單元測試資料應該做到的資料範圍獨立性被違背，所以通知相關人員進行修改。

至此，誤刪除使用者確定完成。

這個問題，我們得到幾個經驗。

1、進行資料恢復的手段很多。從最簡單的手段切入，往往可以達到意想不到的效果。不僅僅是對於DBA，普通開發人員也應該具備一定的資料恢復處理能力；

2、Oracle對於所有的資料庫變化操作，都會記錄redo log。分析redo log，善用logminer，可以幫助我們解決很多問題；

3、問題的解決往往是綜合性的方案確定過程。技術是一個重要方面，但是絕不是唯一的方面。對業務知識、系統特點進行深入瞭解，對開發流程和測試流程進行梳理分析，才能藉助一絲線索解決問題。

記一次刪除資料使用者定位

相關文章