對資料庫而言，最重要寶貴的内容就是其中的資料。資料安全性和完整性是任何商業資料庫存在的底線。審計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、問題的解決往往是綜合性的方案确定過程。技術是一個重要方面，但是絕不是唯一的方面。對業務知識、系統特點進行深入了解，對開發流程和測試流程進行梳理分析，才能借助一絲線索解決問題。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-690120/，如需轉載，請注明出處，否則将追究法律責任。
轉載于:http://blog.itpub.net/17203031/viewspace-690120/
![ACS基本配置-權限等級管理[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)