Quick how to use LogMiner


Just as a quick note to myself, this is how i used Logminer before to retrieve detailed information on who did what from which machine.
Before you can really lookup OS_USERNAME, MACHINE, USERNAME , you need to enable “supplimentary logging” :

SQL>  SELECT name, supplemental_log_data_min FROM v$database;
NAME                       |SUPPLEMENTAL_LOG_DATA_MIN
---------------------------|--------------------------------
NOMT1                      |NO
SQL>

Enabling minimum extra logging to our redo logs:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL>

Output found in the alert log:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
SUPLOG: Previous supplemental logging attributes at scn = 122962030
SUPLOG:  minimal = OFF, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Fri Jun 20 08:53:28 2014
SUPLOG: New supplemental logging attributes at scn = 122962235
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Completed: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

Find you area of interest:

First try to narrow down the moment in which you like to digg into the redo logs of Oracle to find the point of origine for a certian action / behaviour.
You could use the alert log to find the scn nummer of the archivelog you need or the range to search in. This is also possible in the database using the v$archived_log.

Restore already backupped and removed archive logs as a result of your area of interest:
My area of interrest was lying in-between 13760 and 13763 logs.

 
nomtdb1) NOMT1 > rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 19 10:59:59 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: NOMT1 (DBID=1364584089)
RMAN> run {
         allocate channel t1 type 'sbt_tape';
         set archivelog destination to '/san/oracle/admin/NOMT1/temp';
         restore archivelog from logseq 13760 until logseq 13763;
         release channel t1;
}

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=238 device type=SBT_TAPE
channel t1: NetVault Oracle APM v.6.0.19
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 19-JUN-14
channel t1: starting archived log restore to user-specified destination
archived log destination=/san/oracle/admin/NOMT1/temp
channel t1: restoring archived log
archived log thread=1 sequence=13760
channel t1: restoring archived log
archived log thread=1 sequence=13761
channel t1: restoring archived log
archived log thread=1 sequence=13762
channel t1: restoring archived log
archived log thread=1 sequence=13763
channel t1: reading from backup piece nommbs1-nv:#4750:nomtdb1:4142_850478421:RMAN FULL DB NOMT1_1402999201
channel t1: piece handle=nommbs1-nv:#4750:nomtdb1:4142_850478421:RMAN FULL DB NOMT1_1402999201 tag=TAG20140617T120021
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:25
Finished restore at 19-JUN-14
released channel: t1
RMAN> exit

Register the restored archvelog to query
Pick an archive log and register it to logminer.

  
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(logfilename => '/san/oracle/admin/NOMT1/temp/0001_0000013761_829164633_5155e699.arch',options => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.

Start logminer to use it

  
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.

Validate that you are using the archivelog needed

 
SQL> select min(to_char(TIMESTAMP,'dd-mm-yyyy hh24:mm:ss')) as min , max(to_char(TIMESTAMP,'dd-mm-yyyy hh24:mm:ss')) as max from V$LOGMNR_CONTENTS ;
MIN                         | MAX
----------------------------------------------------
17-06-2014 08:06:00         | 17-06-2014 09:06:59

Query the log

 
SQL> SELECT count(1) FROM V$LOGMNR_CONTENTS WHERE upper(sql_redo) like upper('delete from "NOMITY"."MUTATE"%');
    COUNT(1)
------------
        3634