You have surely gone through something similar: someone dropped a table, index, user, deleted information or modified it and then, when the problems pop up, nobody confesses, “I wasn’t there”, “I haven’t connected to the database for a long time”, “it must be an Oracle bug”, are some of the things that will be said when we make the investigations. What do you do if you don’t have the auditing enabled or triggers to track it? Simple: use LogMiner.
LogMiner, part of Oracle Database, allows you to use SQL to look up information in the online and archived redo logs. Remember that the redo log files record information about database activity.
Here is an example of how we could benefit from it in this hypothetical case:
1. John connects to development and decides to drop the table he had copied for testing purposes.
JOHN@orcl> drop table hr.employees;
Table dropped.2. You start getting calls complaining about a failing application and checking the logs you find that the hr.employees table has been dropped. John feels a knot in his throat as he thinks to himself “could it be possible that I’ve logged into production instead of development?”
3. Everyone denies responsibility for such an error, so we will have to investigate with LogMiner. For this we first register the online redo logs.
SYS@orcl> begin
2 dbms_logmnr.add_logfile(
3 '/logs/redo01.log', dbms_logmnr.new );
4 dbms_logmnr.add_logfile(
5 '/logs/redo02.log', dbms_logmnr.addfile );
6 dbms_logmnr.add_logfile(
7 '/logs/redo03.log', dbms_logmnr.addfile );
8 end;
9 /
PL/SQL procedure successfully completed.4. We activate the mining.
SYS@orcl> execute dbms_logmnr.start_logmnr;
PL/SQL procedure successfully completed.5. We look for DDLs on the problem table.
SYS@orcl> select scn, timestamp, username, sql_redo
2 from v$logmnr_contents
3 where seg_owner='HR'
4 and seg_name='EMPLOYEES'
5 and operation = 'DDL';
SCN TIMESTAMP USERNAME
------- ------------------- ----------
SQL_REDO
----------------------------------------------------------------------------
3720771 04/09/2008 15:16:52 JOHN
ALTER TABLE "HR"."EMPLOYEES" RENAME TO "BIN$VheiNLbMC5bgQKjAMndYmA==$0" ;
3720771 04/09/2008 15:16:52 MARCO
drop table hr.employees AS "BIN$VheiNLbMC5bgQKjAMndYmA==$0" ;6. John, what did you say you were doing at about 3 pm?
We have just seen LogMiner in action, but this is only a part of what we can do. If you want to know more about its use and possibilities, you will find everything you need in the manual: Oracle Database Utilities.
