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/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.