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

时间:2023-01-06 07:43:23

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

  通过之前的检查已经确定是在130(周五)22(周一)之间。由于该数据库没开审计功能,下面通过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

 

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

   

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

  

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

  

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

  

 

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

 

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

 

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

 

 

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

 

  $ 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-302015-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/SQLprocedure successfully completed.

 

 

启动LogMiner进行分析

 

SQL>begin

  2 sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);

  3  end;

  4  /

 

PL/SQLprocedure 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-0211:28:07

 

结束分析

SQL>begin

  2 sys.dbms_logmnr.end_logmnr;

  3  end;

  4  /

 

PL/SQLprocedure successfully completed.