I recently ran into a situation where some tables were accidentally dropped from a production database. I was able to restore the data pretty easily and afterwards, the obvious question came up: Who did it? If Database Auditing were enabled and configured, then likely I could have answered the question just by querying the dba_audit_trail. Unfortunately, this wasn’t the case. So I decided to use logminer to see if I could figure it out.

First, I needed to identify the logfiles that I thought might contain the information. We knew that there was an approximately 1 hour window when it occurred. Since this was a 4 node RAC cluster, I needed to take into consideration the redo log thread# as well as the sequence#. If I happened to know which instance this occurred on, I could look at just that thread. But of course,  no such luck.

select first_time, sequence#, thread#
from v$log_history
where first_time between '26-APR-2012 16:30:00'
and '26-APR-2012 17:40:00'
order by thread#,sequence# asc;

FIRST_TIME SEQUENCE# THREAD#
------------------ ---------- ----------
26-APR-12 16:37:24 89558 1
26-APR-12 16:55:04 89559 1
26-APR-12 17:22:12 89560 1
26-APR-12 16:47:28 81138 2
26-APR-12 16:55:02 81139 2
26-APR-12 17:20:01 81140 2
26-APR-12 16:38:24 83479 3
26-APR-12 16:55:03 83480 3
26-APR-12 17:13:25 83481 3
26-APR-12 17:27:05 83482 3
26-APR-12 17:38:21 83483 3
26-APR-12 16:46:10 78195 4
26-APR-12 16:55:03 78196 4
26-APR-12 17:27:07 78197 4

So, I used rman to restore each archived log file from backup:

RMAN> restore archivelog sequence 83479 thread=3;
RMAN> list archivelog sequence 83479 thread=3;

Starting restore at 27-APR-12 08:45:32
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=3 sequence=83479
channel ORA_DISK_1: reading from backup piece
channel ORA_DISK_1: restored backup piece 1
piece handle=+RECO_01/proddb/backupset/2012_04_26/annnf0_tag20120426t165515_0.1
100.781635517 tag=TAG20120426T165515
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
Finished restore at 27-APR-12 08:48:09

List of Archived Log Copies
Thrd Seq S Low Time Name
------- ---- ------- - ------------------ ----
3 83479 A 26-APR-12 16:38:24 +RECO_01/proddb/archivelog/2012_04_27/thread_3_seq_83479.552.781692457

So, now I have restored an archived logfile that may contain the statements I am looking for so  I can use Logminer to dig deeper:

conn / as sysdba
execute dbms_logmnr.add_logfile(
logfilename=>'+RECO_01/proddb/archivelog/2012_04_27/thread_3_seq_83479.552.781692457',
options => dbms_logmnr.new);
execute dbms_logmnr.start_logmnr;
select unique operation from v$logmnr_contents;

I did these steps for each logfile looking for any DDL operations, specifically “drop table” statements.  Finally, on the last logfile I found what I was looking for! There were 1168 DDL statements that occurred. This query gave me the details I needed:

select session_info, sql_undo, sql_redo,username, timestamp
from v$logmnr_contents
where operation='DDL' and upper(sql_redo) like 'DROP%' order by timestamp desc;

login_username=JSMITH client_info= OS_username=jsmith
Machine_name=jsmith_dev OS_program_name=sqlplus@jsmith_dev (TNS V1-V3)

DROP TABLE "AUTH_PERMISSION" CASCADE CONSTRAINTS AS "BIN$vp6UFqYresLgQEiKCdYy4w==$0" ;
JSMITH 26-APR-12 16:45:10

That is the answer I was looking for. Jsmith did it!