LogMiner的一个案例
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.
-- check the relatived archived log
select name, first_time, next_time, thread#, sequence#
from v$archived_log
where first_time between to_date('2008-11-02 23:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2008-11-03 00:30:00', 'yyyy-mm-dd hh24:mi:ss');
Notice: 如果上面的查询中name字段为空，说明RMAN之类的备份程序已经把归档日志删除了，需要先恢复出需要的archive log文件
参考用restore archivelog从备份集中恢复归档日志 http://www.banping.com/2009/06/30/restore_archivelog/
首先查看当天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）
-- add the first log file
begin
dbms_logmnr.add_logfile(logfilename => '/dps05/oradata/flash_recovery_area/arch_1_21005_600963507.arc',
options => dbms_logmnr.new);
end;
/
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.
-- add the other log files sequencially
begin
for x in (select name, first_time, next_time
from v$archived_log
where first_time between
to_date('2008-11-2 23:02:52', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2008-11-03 00:30:00', 'yyyy-mm-dd hh24:mi:ss')) loop
dbms_logmnr.add_logfile(logfilename => x.name,
options => dbms_logmnr.addfile);
end loop;
end;
/
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
-- start logminer
begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
end;
/
用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);
-- query result
select scn, username, substr(sql_redo, 1, 60) sub_sql_redo, commit_timestamp
from v$logmnr_contents
where seg_owner = 'TEST'
and lower(sql_redo) like '%delete%';
-- more general
select l.scn, l.commit_timestamp, l.session_info, l.username, substr(l.sql_redo, 1, 60) sub_sql_redo
from v$logmnr_contents l
where lower(l.sql_redo) like '%drop %';
查 询有特定关键字的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=WORKGROUP\FREE-MAN
可 以看到是机器名称WORKGROUP\FREE-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
======================================================================================================================
如果是通过telnet到服务器然后执行的语句，不能直接定位机器名称。这种情况下，还需要到服务器端进行进一步查询。
OS: AIX
我们得到这样一条session info和相应的时间段
login_username=SYS client_info= OS_username=oracle Machine_name=napt2db1qa OS_terminal=pts/1 OS_process_id=553128 OS_program_name=sqlplus@napt2db1qa (TNS V1-V3)
2008-11-2 23:40:54
2008-11-3 0:10:53
通过AIX的登录日志查看这个时间段内有哪些用户登录
> last -t 200811022340
oracle pts/1 lax1bxcl009.regulusgroup.net Nov 02 23:36 - 02:12 (02:36)
oracle ftp nap21xcl047.regulusgroup.net Nov 02 23:33 - 23:55 (00:22)
oracle pts/3 dsm11xcl007.regulusgroup.net Nov 02 23:15 - 01:16 (02:01)
oracle pts/2 nap21xcl047.regulusgroup.net Nov 02 23:02 - 01:12 (02:09)
通过终端类型pts/1我们已经可以判断是lax1bxcl009.regulusgroup.net这个地址，然后查看IP
> ping lax1bxcl009.regulusgroup.net
PING lax1bxcl009.regulusgroup.net: (172.20.13.144): 56 data bytes
实际操作中，这里还有一个细节。这个IP是通过VPN拨号动态生成的，所以还需要网管的协助来查找真正的Client
总结一下：
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?
P.S 在10g中默认情况下LOGMNR已经不是一个可靠的数据获取的方式，希望通过这种方式获取丢失数据，则需要提前设置SUPPLEMENTAL LOG DATA
http://yangtingkun.itpub.net/post/468/464865
posted on 2012-01-30 18:13 wait4friend 阅读(344) 评论(1) 编辑 收藏 举报
