logminer

前两天发现评论表的在指定时间以前的数据都被删除，领导要知道是谁干的，但用户通过应用程序
删除也无法查到具体的人，因为数据库只记录和他直接相连的应用程序的信息，无法定位到客户端
的信息，但像我遇到的这样的情况，可能性大的就是个别人通过自己的pc机操作数据库，这时我们
就要靠oracle的logminer工具来分析日志了，下面我就来一起体验下logminer的威力吧，呵呵
步骤
1.数据库的supplemental的状态调整
2.创建数据库级别的触发器，用以记录客户的登录信息
3.安装logminer
4.创建logminer数据字典（如果没有创建数据自己，而是在线分析，看见的不是原始sql，而是
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>被oracle格式化的sql语句）<br> 5.添加需要分析的日志<br> 6.分析日志<br> 7.查看结果（v$logmnr_contents）<br><br> 一。<br> 前提准备：<br> 最好用sys用户，否则你要授权的<br> 要通过logminer查看到客户端的信息，我们就是查看v$database视图，看其supplemental_log_data_min的<br> 状态。<br> v$logmnr_contents中的客户端的信息。都是从v$session视图中读出的，要让v$session记录客户端的信息<br> 我们就要 设置<wbr><wbr>supplemental_log_data_min 为on<br> YES为打开状态，会记录session_info,username等信息<br> NO为关闭状态，不会记录sesion_info,username等信息<br><br> 1. 查看 supplemental 状态<br> SYS at skate&gt;<wbr><wbr>select name ,supplemental_log_data_min from v$database;<br> NAME<wbr><wbr><wbr><wbr><wbr><wbr>SUPPLEME<br> --------- --------<br> OMOVO<wbr><wbr><wbr><wbr>NO<br><br> 2. 修改supplemental的状态<br> SYS at skate&gt;<wbr><wbr>alter database add supplemental log data;<br> Database altered.<br><br> 3.确认supplemental的状态<br> SYS at skate&gt;<wbr><wbr>select<wbr><wbr>name,supplemental_log_data_min from v$database;<br> NAME<wbr><wbr><wbr><wbr><wbr><wbr>SUPPLEME<br> --------- --------<br> OMOVO<wbr><wbr><wbr><wbr>YES<br> 1 row selected.<br><br> 需要注意的是，这个参数信息是记录在控制文件的，所以10G数据库一旦被重建了控制文件，那么这个参数会再次恢复到缺省状态，也就是NO的状态。<br> 所以重建控制文件后，要记得修改这个参数<br><br> 二. 创建数据库级别的触发器<br> 若果要让v$logmnr_contents中的session_info记录客户端ip，但SESSION_INFO中我们并不能直接看到IP，<br> 不过我们还是有办法的，因为这个SESSION_INFO里面的内容其实是日志从V$SESSION视图里提取的，我们可以<br> 在生产数据库中创建一个追踪客户端IP地址的触发器：<br> create or replace trigger on_logon_trigger<br> after logon on database<br> begin<br> dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));<br> end;<br> /<br><br> 现在，我们就可以在V$SESSION视图的CLIENT_INFO列中看到新登录的客户端IP地址了。那么现在就可以在<br> session_info 中看客户端的ip了<br><br> 三。 安装logminer<br> 1.<br> logminer实际上是由两个pl/sql内建包(dbms_logmnr和dbms_logmnr_d)和4个v$动态性能视图组成的.<br> v$logmnr_logs<br> v$logmnr_contents<br> v$logmnr_parameters<br><wbr><wbr>安装logminer首先要以sys管理员的身份运行这个pl/sql脚本<br><wbr><wbr>sql&gt; conn /as sysdba<br><wbr><wbr>sql&gt; @/rdbms/admin/dbmslm.sql<br><wbr><wbr>sql&gt; @/rdbms/admin/dbmslmd.sql<br><br> 2. 修改参数 utl_file_dir参数<br> 这个参数是静态参数，修改后需要数据库，才会起作用，如果不重启数据库<br> 在创建数据字典时会报错，下面是创建的过程<br><br> SYS at skate&gt;<wbr><wbr>alter system set utl_file_dir=/home/oracle/logminerlog scope=spfile;<br> alter system set utl_file_dir=/home/oracle/logminerlog scope=spfile<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>*<br> ERROR at line 1:<br> ORA-02095: specified initialization parameter cannot be modified<br><br> SYS at skate&gt;<wbr><wbr>alter system set utl_file_dir='/home/oracle/logminerlog' scope=spfile;<br> System altered.<br> SYS at skate&gt;<wbr><wbr>exec dbms_logmnr_d.build(dictionary_filename=&gt;'logminer',dictionary_location=&gt;'/home/oracle/logminerlog');<br> BEGIN dbms_logmnr_d.build(dictionary_filename=&gt;'logminer',dictionary_location=&gt;'/home/oracle/logminerlog'); END;<br> *<br> ERROR at line 1:<br> ORA-01336: specified dictionary file cannot be opened<br> ORA-29280: invalid directory path<br> ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3474<br> ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3552<br> ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12<br> ORA-06512: at line 1<br><br> SYS at skate&gt;<wbr><wbr>host mkdir /home/oracle/logminerlog<br> SYS at skate&gt;<wbr><wbr>exec dbms_logmnr_d.build(dictionary_filename=&gt;'logminer',dictionary_location=&gt;'/home/oracle/logminerlog');<br> BEGIN dbms_logmnr_d.build(dictionary_filename=&gt;'logminer',dictionary_location=&gt;'/home/oracle/logminerlog'); END;<br> *<br> ERROR at line 1:<br> ORA-01336: specified dictionary file cannot be opened<br> ORA-29280: invalid directory path<br> ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3474<br> ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3552<br> ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12<br> ORA-06512: at line 1<br> 虽然我修改了utl_file_dir,但还没起作用，所用要重启数据库<br> 重启数据库<br> SYS at skate&gt;<wbr><wbr>shutdown immediate<br><br> SYS at skate&gt;<wbr><wbr>startup<br> ORACLE instance started.<br> Total System Global Area<wbr><wbr>440401920 bytes<br> Fixed Size<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>1262260 bytes<br> Variable Size<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>306187596 bytes<br> Database Buffers<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>130023424 bytes<br> Redo Buffers<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>2928640 bytes<br> Database mounted.<br> Database opened.<br> 确认utl_file_dir参数值<br> SYS at omovo&gt;<wbr><wbr>show parameter utl_file_dir<br> NAME<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>TYPE<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>VALUE<br> ------------------------------------ ----------- ------------------------------<br> utl_file_dir<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>string<wbr><wbr><wbr><wbr><wbr><wbr>/home/oracle/logminerlog<br> 四.创建数据字典<br> SYS at skate&gt; exec dbms_logmnr_d.build(dictionary_filename =&gt;'dictionary.ora',dictionary_location =&gt; '/home/oracle/logminerlog');<br> dictionary_filename数据库字典的名字随便起<br><br> 4.添加分析日志<br><br> SYS at skate&gt; exec dbms_logmnr.add_logfile(logfilename=&gt;'/oracle_data/omovo/redo03.log',options=&gt;dbms_logmnr.new);<br> SYS at skate&gt; exec dbms_logmnr.add_logfile(logfilename=&gt;'/oracle_data/omovo/redo01.log',options=&gt;dbms_logmnr.andfile)；<br> 为了减少视图的数据量，加快分析的时间，可以把分析过的日志从视图中删除<br> SYS at skate&gt; exec dbms_logmnr.add_logfile(logfilename=&gt;'/oracle_data/omovo/redo03.log',options=&gt;dbms_logmnr.removefile);<br> SYS at skate&gt; exec dbms_logmnr.add_logfile(logfilename=&gt;'/oracle_data/omovo/redo01.log',options=&gt;dbms_logmnr.removefile)；<br><br> 四、分析<br> exec dbms_logmnr.start_logmnr(dictfilename=&gt;'C:\oracle\logs\dictionary.ora');<wbr><wbr>-----如果不用字典就不能看见原始sql，只能看见被oracle处理过的sql语句<br><br> exec dbms_logmnr.start_logmnr<wbr><wbr>-----只能看见被oracle处理过的sql语句，无法辨别他处理的具体内容<br><br> 例子：<br> select timestamp,commit_timestamp,table_space,session#,serial#,username,session_info,sql_redo,operation,table_name,seg_name,seg_owner<br> from v$logmnr_contents<br> where seg_owner=upper('movo_new')<br> and<wbr><wbr>operation=upper('insert')<br><br> select session_info,sql_redo from v$logmnr_contents where upper(operation) = 'UPDATE'<wbr><wbr>and upper(sql_redo) like '%M_%'<br> 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';<br><br><br> 六、结束分析<br> SQL&gt; execute dbms_logmnr.end_logmnr;<br> －－待续<br> 七. 如何分析rman备份集中的日志<br><br> --- end ----</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>