Knowledge increases exponentially. Today, you probably own more books than great universities of times past—Cambridge University owned less than two hundred books in the fifteenth century. First came the invention of writing, then alphabets, then paper, then the printing press, then mechanization. Each step caused an exponential increase in the collective human knowledge. In our generation, Al Gore invented the internet and the last barriers to the spread of knowledge have been broken. Today, everybody has the ability to contribute, communicate, and collaborate. We are all caught up in a tsunami, an avalanche, a conflagration, a veritable explosion of knowledge for the betterment of humankind. This is the blog of the good folks at Database Specialists, a brave band of Oracle database administrators from the great state of California. We bid you greeting, traveler. We hope you find something of value on these pages and we wish you good fortune in your journey.

Using LogMiner to find out who dropped a table

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!

1 comment to Using LogMiner to find out who dropped a table

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>