查詢凶手：一次logmin的紀錄和分析

昨天有個哥們和我聊天的時候，說他們公司某人把資料庫的表刪除了，問有沒有辦法檢視誰是凶手。
我說可以用logmin來看看，今天抽空做了個測試，紀錄一下
OS: Linux AS4
DB: Oracle 10gR2
案例：在2007-01-19的13點左右，對TEST下的一些表進行了刪除(delete,drop,truncate)

Last login: Fri Jan 19 15:00:31 2007 from 192.168.10.100
[oracle@testsrv1 ~]$ sqlplus /nolog
[uniread] Loaded history (3548 lines)

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 19 15:53:54 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn / as sysdba
Connected.

首先檢視當天8點之後的archived log
sys@DW> select name,first_time,next_time from v$archived_log where first_time>=to_date('2007-01-19 08:00:00','yyyy-mm-dd hh24:mi:ss');

NAME FIRST_TIME NEXT_TIME
---------------------------------------- ------------------- -------------------
/oracle/oradata/DW/arc/arc_1_709_5969003 2007-01-19 09:28:41 2007-01-19 13:30:56
06.log

/oracle/oradata/DW/arc/arc_1_710_5969003 2007-01-19 13:30:56 2007-01-19 13:32:40
06.log

然後看當前log情況和logfile
sys@DW> select group#,sequence#,archived,status,first_time from v$log;

GROUP# SEQUENCE# ARC STATUS FIRST_TIME
---------- ---------- --- ---------------- -------------------
1 710 YES INACTIVE 2007-01-19 13:30:56
2 711 NO CURRENT 2007-01-19 13:32:40
3 709 YES INACTIVE 2007-01-19 09:28:41

sys@DW> col member for a60
sys@DW> select member from v$logfile where group#=2;

MEMBER
------------------------------------------------------------
/oracle/oradata/DW/redo02a.log
/data1/oradata/DW/redo02b.log

從上面資料可以看到我們需要的是seq為709，710的歸檔日誌和711的線上日誌檔案。
在logmnr裡面加入上面幾個檔案（第一個用dbms_logmnr.new引數，其他用dbms_logmrn.addfile）

sys@DW> exec dbms_logmnr.add_logfile(logfilename=>'/oracle/oradata/DW/arc/arc_1_709_596900306.log',o
ptions=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

sys@DW> exec dbms_logmnr.add_logfile(logfilename=>'/oracle/oradata/DW/arc/arc_1_710_596900306.log',o
ptions=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

sys@DW> exec dbms_logmnr.add_logfile(logfilename=>'/oracle/oradata/DW/redo02a.log',options=>dbms_log
mnr.addfile);

PL/SQL procedure successfully completed.

sys@DW> select filename from v$logmnr_logs;

FILENAME
--------------------
/oracle/oradata/DW/arc/arc_1_709_596900306.log
/oracle/oradata/DW/arc/arc_1_710_596900306.log
/oracle/oradata/DW/redo02a.log

用online_catalog啟動logmrn session，並且只查詢committed資料(因為刪除資料一定是commit了)
sys@DW> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);


查詢有特定關鍵字的sql_redo，因為是刪除，所以關心delete/drop table/truncate table
sys@DW> col username for a10
sys@DW> col sub_sql_redo for a40
sys@DW> select scn,username,substr(sql_redo,1,60) sub_sql_redo from v$logmnr_contents where seg_owne
r='TEST' and lower(sql_redo) like '%delete%' or lower(sql_redo) like '%drop table%' or lower(sql_red
o) like '%truncate table%';

SCN USERNAME SUB_SQL_REDO
---------- ---------- ----------------------------------------
23615614 TEST delete from "TEST"."T_LM1" where "X" = '
1' and ROWID = 'AAAQ

23615614 TEST delete from "TEST"."T_LM1" where "X" = '
2' and ROWID = 'AAAQ

23615614 TEST delete from "TEST"."T_LM1" where "X" = '
3' and ROWID = 'AAAQ

23615614 TEST delete from "TEST"."T_LM1" where "X" = '
4' and ROWID = 'AAAQ

23615614 TEST delete from "TEST"."T_LM1" where "X" = '
5' and ROWID = 'AAAQ

23617508 TEST drop table t1 AS "BIN$J168AlDUzf3gQAB/AQ
Aavw==$0" ;

23618383 TEST TRUNCATE TABLE t3
;

23618894 SYS update "SYS"."WRH$_SQLTEXT" set "SQL_TEX
T" = 'select usernam

23621161 SYS update "SYS"."WRH$_SQLTEXT" set "SQL_TEX
T" = 'select scn,use


9 rows selected.

到這裡已經可以發現有幾個scn是我需要關心的，分別是23615614,23617508和23618383
我們分別檢視一下執行這幾個語句的session info

sys@DW> select session_info from v$logmnr_contents where scn=&scn;
Enter value for scn: 23618383
old 1: select session_info from v$logmnr_contents where scn=&scn
new 1: select session_info from v$logmnr_contents where scn=23618383

SESSION_INFO
--------------------
login_username=TEST client_info= OS_username=Administrator Machine_name=WORKGROUPFREE-MAN
可以看到是機器名稱WORKGROUPFREE-MAN的機器執行了truncate（滅哈哈，其實就是偶拉）

sys@DW> select session_info from v$logmnr_contents where scn=&scn;
Enter value for scn: 23617508
old 1: select session_info from v$logmnr_contents where scn=&scn
new 1: select session_info from v$logmnr_contents where scn=23617508

SESSION_INFO
------------------------------------------------------------------------------------------------------------------------
login_username=TEST client_info= OS_username=oracle Machine_name=testsrv1 OS_terminal=tty OS_process_id=6846 OS_program_
name=sqlplus@testsrv1 (TNS V1-V3)
機器testsrv1執行了drop table
總結一下：
1。dbms_logmnr可以使用三種模式，分別是dbms_logmnr.dict_from_flat_file,
dbms_logmnr.dict_from_redo_logs和dbms_logmnr.dict_from_online_catalog。前
2個都需要提前匯出資料字典資訊，online_catalog可以直接使用，相對簡單。但是
缺點是僅僅能反映資料庫最近的狀態，如果是很久以前刪除的物件，就不能準確反映。

2。在dbms_logmnr.add_logfile中，有一個偷懶的辦法是，只用dbms_logmnr.new
引數指定一個開始的log，然後用dbms_logmrn.continuous_mine引數來start。這樣，
在有很多log需要分析的時候，就不用每個都add一次。 (我在實際使用中，發現這種
辦法在後面查詢的時候非常慢，一直處於log sequential read的等待事件中，目前
原因不明)

3。查詢v$logmnr_contents的session_info欄位，只能反映機器名稱，不能查詢準
確的IP地址。這個估計和v$session的紀錄有關，v$session中也是僅僅紀錄機器名稱。
不知道有沒有辦法得到IP?