技術討論區 > database 討論版

ORACLE Tips: 這事是誰做的? 快點過來自首吧!

(1/4) > >>

twu2:
ORACLE Tips: 這事是誰做的? 快點過來自首吧!
By Tommy Wu <tommy at teatime.com.tw>

我想各位都應該有那種資料被改了或被砍了, 但是沒有人要承認是他做的, 或者是那個程式設
計師不小心寫錯程式造成的, 這時, 老闆就會問你, 這到底是誰做的?

以前, 為了這個問題, 我們可能會在程式中加上一堆稽核的記錄, 記下一堆又臭又長的資料,
但是真的發生問題時, 找起來又不一定找的到罪魁禍首... 我記得每次用到最後, 都會把這部
份的功能關掉, 因為實在是沒什麼作用.

現在, 利用 ORACLE 所提供的 LOGMNR 的功能, 我們可以輕易的找到兇嫌了.

ORACLE 為了要做到 crash 時, 能夠由備份資料回復到之前的狀態, 所以對於每一筆交易, 都
會有記錄. 這些會存放在所謂的 archived log 之中, 在以前, 這個 log 的檔案, 只能用來做
為 recovery 使用. 但是現在, 我們也可以透過 ORACLE 所提供的功能來查詢裡面的內容, 這
個功能在 ORACLE 中叫做 LOGMNR.

要做這件事, 我們需要保留兩種檔案, 第一種當然是 archived log, 因為所有的交易記錄都放
在這類的檔案中. 另一種就是字典檔, 因為在 log 中並不存放物件的名稱, 為了要讓產生的資
料容易讀取, 必須要有字典檔來做對應.

Archived log 會隨著系統的交易而增加, 所以你必須決定要保留多久, 這個視你的需求而定,
以我們這兒為例, 原本保留一個月, 現在改成兩個月, 當然, 只要你的硬碟空間足夠, 要留多
久就可以留多久.

字典檔就必須自己產生, 有兩種方式, 一種是存到 ORACLE 的 control 檔案中, 一種是存放
指定的檔案中. 為了日後的查詢方便, 我們採用的是存到另外的檔案. 每天會產生一個新的字
典檔, 與 archived log 一樣, 保留下來, 以便於日後查詢使用.

產生字典檔的方法如下:


# 今天的日期
TODAY=`date +%Y%m%d`

sqlplus "/ as sysdba" <<EOF
execute dbms_logmnr_d.build('${ORACLE_SID}_dictionary.ora', '/db/dictionary/${TODAY}',
        options => dbms_logmnr_d.store_in_flat_file);
exit
EOF

上面的指令會產生一個字典檔放在 /db/dictionary/20020101 之類的路徑之中.
請注意上面的 execute 指令, 與下面那行是同一行, 請勿換行.

有了交易記錄檔與字典檔之後, 我們就可以查詢交易的內容了. 不過... 每日的交易檔可能數量是
很可觀的, 以我們這兒來說, 所定的每個交易檔的大小為 1MB, 每天約有 1000 個上下的交易檔產
生, 要找起來實在不是一件小工程. :-(

我們以下面的例子來說明:

假定我們要查的交易是在 arch0000093355.arc 這個交易記錄檔中, 就在 sqlplus 中執行下面的指
令:

execute DBMS_LOGMNR.ADD_LOGFILE('/log/arch0000093335.arc', dbms_logmnr.NEW);
       
如果還有其他的交易記錄檔, 如 arch0000093336.arc, 可以用下面的指令加上:
       
execute DBMS_LOGMNR.ADD_LOGFILE('/log/arch0000093336.arc', dbms_logmnr.ADDFILE);

依此類推, 你可以一次加上一堆記錄檔, 我通常習慣是每天的檔案都放在一起查...

把所有的交易記錄檔都加上之後, 就執行下面的指令啟動 LOGMNR 的功能:
       
execute dbms_logmnr.start_logmnr(DictFileName =>'/log/ORCL_dictionary.ora');

後面那個檔案就是你存下來的字典檔.
這個指令有另外的參數可以使用, 例如我們如果只要查詢有 commit 的交易時, 可以使用:

execute dbms_logmnr.start_logmnr(DictFileName =>'/log/ORCL_dictionary.ora',
        options => dbms_logmnr.COMMITTED_DATA_ONLY);
       
請注意上面的 execute 指令, 與下面那行是同一行, 請勿換行.
其他的參數, 可以參考 ORACLE 的文件說明.

這個動作可能會很久, 等做完之後, 系統就會有一個 v$logmnr_contents 可以查詢.

v$logmnr_contents 這個 view, 比較常用的欄位有:
   scn: 系統變更代碼
    timestamp: 時間
   username: 使用者名稱 (ORACLE 的使用者)
   row_id: 指令所異動到的資料的 ROWID.
   session#: 執行指令的 session 號碼.
   serial#: 執行指令的 serial 號碼.
   sql_redo: 執行的指令.
   sql_undo: 回復的指令.
   session_info: 這個 session 的資料. (可能是空的, 或許要查更早之前的 log, 看看這個
                 session 什麼時候登入的, 在登入後的那幾筆交易可以由這個看出那一個機
                 器連上來的, 使用那個 OS 的使用者.
   
直接使用 select 指令查詢相關的內容, 就可以得知在何時誰做了什麼事情...
例如, 有人下了 delete 指令, 我們可以查詢

select session_info, sql_redo from v$logmnr_contents
where sql_redo like 'delete%';

這個查詢會把這些交易記錄中所有 delete 開頭的指令取出來.

查詢後執行下面的指令, 結束 LOGMNR 的運作:

execute dbms_logmnr_end_logmnr();

查詢的技巧... 自己多試試就知道了, 你也可以 create 另一個 table, 把這個 view 的資料先
存過去, 再來查詢, 當資料多的時候, 可能會比較方便一些.

最後... 要提的就是, 雖然可以知道那一個使用者在那一台機器, 那個時間做了這件事, 但是實
際上... 還是有人要說不是他做的... 說可能有人用他的密碼, 在他的機器上做, 或者他開機後
離開位子, 不曉得誰過來用他的電腦... 這些事情, 已經不是電腦可以處理的了, 應該歸於行政
管理上面的要求.

請所有的使用者, 不要用太簡單的密碼, 也不要讓別人知道你的密碼, 在離開位子的時候, 也請
將電腦的畫面鎖住... 否則, 這事就算在你的頭上.


另外, 在 ORACLE 9.0.1.x 的版本中 (到 9.0.1.3 為止), 這個功能有點問題, 可能會造成你在
select 這個 view 的時候, 造成 ORACLE hang 住, 或產生 core dump. 這個問題據說已經被修
正, 但無法提供單一的 patch, 所以到等到 9.0.1.4 版本才可以使用, 或者是使用 9.2.0.1 這
個版本也可以.

我們這兒的解決方法就是裝一套 ORACLE 9.2.0.1 專門來處理這個問題.

   
本文的最新版本可以由下列的網頁取得:
 
http://www.teatime.com.tw/~tommy/doc/oracle_logmnr.txt

ZMAN:
以前總覺得醫院的MIS都不太....
現在需要改觀了

twu2:
哈哈... 應該說我這種人為什麼為跑來這當 MIS (其實我覺得我比較像打雜的...)
我遇到多數的同事, 的確都是除了熟悉健保局與 HIS 系統外, 其他的都不怎麼熟.

做這事有好有壞... 至少我有很多的時間可以玩我自己想玩的東西. :-)

huckly:
其實 我對 twu2 罪佩服的是從 programer  轉成 MIS

真好  什是都難不倒你

一般mis 最弱的 就是 寫程式

恐怖喔

twu2:

--- 引述: "huckly" ---其實 我對 twu2 罪佩服的是從 programer  轉成 MIS

真好  什是都難不倒你

一般mis 最弱的 就是 寫程式

恐怖喔
--- 引用結尾 ---


我認為我還是 programer... 且希望一直都是... :-)
其實我在這也不像是 MIS, 一樣常做 programming...

導覽

[0] 文章列表

[#] 下頁

前往完整版本