oracle跟踪用户

是谁偷了我的数据！！！！

time： 2008年5月15日 19:09:25
author： skate

前两天发现评论表的在指定时间以前的数据都被删除，领导要知道是谁干的，但用户通过应用程序

删除也无法查到具体的人，因为数据库只记录和他直接相连的应用程序的信息，无法定位到客户端

的信息，但像我遇到的这样的情况，可能性大的就是个别人通过自己的pc机操作数据库，这时我们

就要靠oracle的logminer工具来分析日志了，下面我就来一起体验下logminer的威力吧，呵呵

步骤

1.数据库的supplemental的状态调整
2.创建数据库级别的触发器，用以记录客户的登录信息
3.安装logminer
4.创建logminer数据字典（如果没有创建数据自己，而是在线分析，看见的不是原始sql，而是
                       被oracle格式化的sql语句）
5.添加需要分析的日志
6.分析日志
7.查看结果（v$logmnr_contents）

一。
前提准备：

最好用sys用户，否则你要授权的

要通过logminer查看到客户端的信息，我们就是查看v$database视图，看其supplemental_log_data_min的
状态。
v$logmnr_contents中的客户端的信息。都是从v$session视图中读出的，要让v$session记录客户端的信息

我们就要 设置  supplemental_log_data_min 为on

YES为打开状态，会记录session_info,username等信息
NO为关闭状态，不会记录sesion_info,username等信息

1. 查看 supplemental 状态

SYS at skate>  select name ,supplemental_log_data_min from v$database;

NAME      SUPPLEME
--------- --------
OMOVO     NO

 

2. 修改supplemental的状态

SYS at skate>  alter database add supplemental log data;

Database altered.

3.确认supplemental的状态

SYS at skate>  select  name,supplemental_log_data_min from v$database;

NAME      SUPPLEME
--------- --------
OMOVO     YES

1 row selected.

 

需要注意的是，这个参数信息是记录在控制文件的，所以10G数据库一旦被重建了控制文件，那么这个参数会再次恢复到缺省状态，也就是NO的状态。
所以重建控制文件后，要记得修改这个参数

二. 创建数据库级别的触发器

若果要让v$logmnr_contents中的session_info记录客户端ip，但SESSION_INFO中我们并不能直接看到IP，
不过我们还是有办法的，因为这个SESSION_INFO里面的内容其实是日志从V$SESSION视图里提取的，我们可以
在生产数据库中创建一个追踪客户端IP地址的触发器：

create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
/

现在，我们就可以在V$SESSION视图的CLIENT_INFO列中看到新登录的客户端IP地址了。那么现在就可以在
session_info 中看客户端的ip了

三。 安装logminer

1.

logminer实际上是由两个pl/sql内建包(dbms_logmnr和dbms_logmnr_d)和4个v$动态性能视图组成的.

v$logmnr_logs
v$logmnr_contents
v$logmnr_parameters

  安装logminer首先要以sys管理员的身份运行这个pl/sql脚本

  sql> conn /as sysdba

  sql> @/rdbms/admin/dbmslm.sql

  sql> @/rdbms/admin/dbmslmd.sql

2. 修改参数 utl_file_dir参数

这个参数是静态参数，修改后需要数据库，才会起作用，如果不重启数据库
在创建数据字典时会报错，下面是创建的过程

SYS at skate>  alter system set utl_file_dir=/home/oracle/logminerlog scope=spfile;
alter system set utl_file_dir=/home/oracle/logminerlog scope=spfile
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SYS at skate>  alter system set utl_file_dir='/home/oracle/logminerlog' scope=spfile;

System altered.

SYS at skate>  exec dbms_logmnr_d.build(dictionary_filename=>'logminer',dictionary_location=>'/home/oracle/logminerlog');
BEGIN dbms_logmnr_d.build(dictionary_filename=>'logminer',dictionary_location=>'/home/oracle/logminerlog'); END;

*
ERROR at line 1:
ORA-01336: specified dictionary file cannot be opened
ORA-29280: invalid directory path
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1

SYS at skate>  host mkdir /home/oracle/logminerlog

SYS at skate>   exec dbms_logmnr_d.build(dictionary_filename=>'logminer',dictionary_location=>'/home/oracle/logminerlog');
BEGIN dbms_logmnr_d.build(dictionary_filename=>'logminer',dictionary_location=>'/home/oracle/logminerlog'); END;

*
ERROR at line 1:
ORA-01336: specified dictionary file cannot be opened
ORA-29280: invalid directory path
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1

虽然我修改了utl_file_dir,但还没起作用，所用要重启数据库

重启数据库

SYS at skate>  shutdown immediate

SYS at skate>  startup
ORACLE instance started.

Total System Global Area  440401920 bytes
Fixed Size                  1262260 bytes
Variable Size             306187596 bytes
Database Buffers          130023424 bytes
Redo Buffers                2928640 bytes
Database mounted.
Database opened.

确认utl_file_dir参数值

SYS at omovo>  show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /home/oracle/logminerlog

四.创建数据字典

SYS at skate> exec dbms_logmnr_d.build(dictionary_filename =>'dictionary.ora',dictionary_location => '/home/oracle/logminerlog');

dictionary_filename数据库字典的名字随便起

4.添加分析日志

SYS at skate> exec dbms_logmnr.add_logfile(logfilename=>'/oracle_data/omovo/redo03.log',options=>dbms_logmnr.new);
SYS at skate> exec dbms_logmnr.add_logfile(logfilename=>'/oracle_data/omovo/redo01.log',options=>dbms_logmnr.andfile)；

为了减少视图的数据量，加快分析的时间，可以把分析过的日志从视图中删除

SYS at skate> exec dbms_logmnr.add_logfile(logfilename=>'/oracle_data/omovo/redo03.log',options=>dbms_logmnr.removefile);
SYS at skate> exec dbms_logmnr.add_logfile(logfilename=>'/oracle_data/omovo/redo01.log',options=>dbms_logmnr.removefile)；

四、分析

exec dbms_logmnr.start_logmnr(dictfilename=>'C:/oracle/logs/dictionary.ora');   -----如果不用字典就不能看见原始sql，只能看见被oracle处理过的sql语句

exec dbms_logmnr.start_logmnr   -----只能看见被oracle处理过的sql语句，无法辨别他处理的具体内容

 

例子：
select timestamp,commit_timestamp,table_space,session#,serial#,username,session_info,sql_redo,operation,table_name,seg_name,seg_owner
from v$logmnr_contents
where seg_owner=upper('movo_new')
and   operation=upper('insert')

select session_info,sql_redo from v$logmnr_contents where upper(operation) = 'UPDATE'  and upper(sql_redo) like '%M_%'

select to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') time,sql_redo from v$logmnr_contents where seg_owner='XYGDATA' and seg_name='T_ST_WEIGH' and upper(operation)='UPDATE';

 

六、结束分析
SQL> execute dbms_logmnr.end_logmnr;

－－待续

七. 如何分析rman备份集中的日志

 

--- end ----