使用logminer 工具挖掘数据库归档日志,查找删除用户的记录

2月6日，客户丢失一用户，要求找出是谁在什么时间删除了这个用户。

通过之前的检查已经确定是在1月30日(周五)到2月2日(周一)之间。由于该数据库没开审计功能，下面通过LogMiner工具挖掘归档日志找出删除该用户的相关信息。

查看归档日志的路径：

SQL>archive log list;
Databaselog mode              Archive Mode
Automaticarchival             Enabled
Archivedestination            /opt/oracle/archivelog
Oldestonline log sequence     13391
Next logsequence to archive   13392
Currentlog sequence           13392

$ cd/opt/oracle/archivelog
$ ls-lrt
total 0

这时发现该路径下没有归档日志，需要在备份软件内恢复归档日志：

　　

 

　　

 

       

 

　　

 

      

 

      

 

     

$ cd/opt/oracle/archivelog
$ ls
1_13346_720546790.dbf  1_13350_720546790.dbf  1_13354_720546790.dbf  2_8861_720546790.dbf   2_8865_720546790.dbf   2_8869_720546790.dbf   2_8873_720546790.dbf
1_13347_720546790.dbf  1_13351_720546790.dbf  1_13355_720546790.dbf  2_8862_720546790.dbf   2_8866_720546790.dbf   2_8870_720546790.dbf   2_8874_720546790.dbf
1_13348_720546790.dbf  1_13352_720546790.dbf  1_13356_720546790.dbf  2_8863_720546790.dbf   2_8867_720546790.dbf   2_8871_720546790.dbf   2_8875_720546790.dbf
1_13349_720546790.dbf  1_13353_720546790.dbf  1_13357_720546790.dbf 　2_8864_720546790.dbf   2_8868_720546790.dbf 　 2_8872_720546790.dbf

 

查看2015-01-30到2015-02-02的归档序列号

SQL>select thread#,sequence# from v$archived_log where  COMPLETION_TIME <to_date('2015-02-02','YYYY-mm-dd')  and COMPLETION_TIME>to_date('2015-01-30','YYYY-mm-dd') order by thread#,sequence#;

   THREAD# SEQUENCE#

--------------------
         1     13349
         1     13350
         1     13351
         1     13352
         1     13353
         1     13354
         2      8865
         2      8866
         2      8867
         2      8868
         2      8869
         2      8870
         2      8871
         2      8872

14 rowsselected.

创建要分析的日志文件列表：

SQL> begin
  2  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/1_13349_720546790.dbf', options=>sys.dbms_logmnr.addfile);
  3  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/1_13350_720546790.dbf', options=>sys.dbms_logmnr.addfile);
  4  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/1_13351_720546790.dbf', options=>sys.dbms_logmnr.addfile);
  5  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/1_13352_720546790.dbf', options=>sys.dbms_logmnr.addfile);
  6  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/1_13353_720546790.dbf', options=>sys.dbms_logmnr.addfile);
  7  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/1_13354_720546790.dbf', options=>sys.dbms_logmnr.addfile);
  8  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8865_720546790.dbf', options=>sys.dbms_logmnr.addfile);
  9  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8866_720546790.dbf', options=>sys.dbms_logmnr.addfile);
 10  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8867_720546790.dbf', options=>sys.dbms_logmnr.addfile);
 11  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8868_720546790.dbf', options=>sys.dbms_logmnr.addfile);
 12  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8869_720546790.dbf', options=>sys.dbms_logmnr.addfile);
 13  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8870_720546790.dbf', options=>sys.dbms_logmnr.addfile);
 14  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8871_720546790.dbf', options=>sys.dbms_logmnr.addfile);
 15  sys.dbms_logmnr.add_logfile (logfilename=>'/opt/oracle/archivelog/2_8872_720546790.dbf', options=>sys.dbms_logmnr.addfile);
 16  end;
 17  
 18  /

PL/SQL procedure successfully completed.

启动LogMiner进行分析

SQL>begin
  2 sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
  3  end;
  4  /

PL/SQL procedure successfully completed.

查询分析结果

SQL>select * from  v$logmnr_contents  where sql_redo like '%drop user ZP%';

......

SQL>select to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from  v$logmnr_contents  where sql_redo like '%drop user ZP%';

TO_CHAR(TIMESTAMP,'
-------------------
2015-02-02 11:28:07

结束分析

SQL>begin
  2 sys.dbms_logmnr.end_logmnr;
  3  end;
  4  /

PL/SQL procedure successfully completed.