查找凶手：一次logmin的纪录和分析

[@more@] 查找凶手：一次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?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/73920/viewspace-892978/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/73920/viewspace-892978/