DBMS_LOGMNR.START_LOGMNR – What was happening ? (1)

What was happening? Who did that? Who delete data?
Oracle LogMiner is one of answers. First, you have to be clear.
1) what it does
Go through online or archived logs from either the ‘current’ database or a ‘foreign’ database, and give you ‘low level’ SQL statement. It is not replacement of ‘real SQL’ tracing from SQL_Trace, event 10046 or oradebug .
2) What it cannot do.
i.e for Oracle 10g. LogMiner does not support these datatypes and table
storage attributes (unsupported Datatypes and Table Storage Attributes)

– BFILE datatype

– Simple and nested abstract datatypes (ADTs)

– Collections (nested tables and VARRAYs)

– Object refs

– XMLTYPE datatype

– Index-organized tables (IOTs) with LOB columns

– Tables using table compression

Procedure
1) build dictionary – optional
let logMiner know what objects in database, it get to know meta-data at this time.
choice: online catalog, redo logs or flat file with utl_file_dir.
2) add online/archivelogs to log miner
assign log miner to analyze these logs.
3) start log miner with conditions
It is the key. You have following choices:
.COMMITTED_DATA_ONLY
.SKIP_CORRUPTION
.DDL_DICT_TRACKING
.NO_DICT_RESET_ONSELECT
.DICT_FROM_ONLINE_CATALOG
.DICT_FROM_REDO_LOGS
4) query logminer data from v$logmnr_contents
object, username, session, timestamp…filter them out.
5) release resource by logminer
clean up stage.

Case

— feeding logs
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/u01/oracle/arch/arch9001.arc’, OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

–feeding more logs
SQL> host;
/u01/oracle/arch $ echo "">a.sql
/u01/oracle/arch $ for i in `ls -ltr |grep ‘Jul  2’|awk ‘{print $9}’`; do
> echo "EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ‘/u01/oracle/arch/${i}’,OPTIONS => DBMS_LOGMNR.ADDFILE);" >>a.sql
> done;
/u01/oracle/arch $ exit

SQL> @a.sql

PL/SQL procedure successfully completed.
…
–verify logs get in logminer
SQL> select count(*) from v$logmnr_logs;

  COUNT(*)
———-
        10
–from when to when
SQL> select min(low_scn),max(next_scn) from v$logmnr_logs;

MIN(LOW_SCN) MAX(NEXT_SCN)
———— ————-
    3630482      3653362
–let logminer find data for you
SQL> exec DBMS_LOGMNR.START_LOGMNR(STARTSCN =>3630482, ENDSCN =>3653362, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$Logmnr_contents;

  COUNT(*)
———-
    58467
–Filter out what you want
SQL> col seg_name for a20
SQL> col username for a10
SQL> col seg_owner for a10
SQL> col session_info for a100
SQL> col sql_redo for a120
SQL> col timestamp for a20
SQL> col operation for a10
SQL> set linesize 300

SQL> select timestamp, seg_owner, seg_name, operation, username, session_info, sql_redo from v$logmnr_contents where seg_name=’T1′ ;

TIMESTAMP                SEG_OWNER  SEG_NAME             OPERATION  USERNAME   SESSION_INFO   SQL_REDO
————————- —————  ——————— ————– —————– —————– ———-
2008/07/02 09:25:51    SCOTT           T1                             DELETE      SUPERUSER    login_username=SUPERUSER client_info= OS_username=administrator Machine_name=GROUPLUOKUS               delete from "SCOTT"."T1" where ***


See more in http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/logminer.htm.

Another way is launch OEM then play logminer GUI interface from 9i.