LOG MINING IN ORACLE DATABASE

Suppose data in a table has been deleted. Audit trail was not enabled, therefore we do not have a clue of the user who has performed deletion and there are around 30 users that have DELETE rights on that table. Management want to know the name of the user, here we can use Oracle LOG MINING feature to filter out the transactions performed during that period.

Note: Make sure the Archive log must be enabled previously, if you want to use LOG Mining technique.

CONSIDERATIONS

Database : ORCL

Schema : SCOTT Table : STUDENTS

Date of Deletion : 10-MAY-2014

1. Gather Archived Log 

Make archived logs of 10-MAY-2014 available in your local hard disk. In my case it is already available at the Flash Recovery Area.

2 . Create Tablespace for Log Miner 

By default LOG MINER tables resides in SYSAUX tablespace, but it is efficient to keep it in a separate tablespace for easy manageability & maintenance.

SQLPLUS SYS@ORCL AS SYSDBA

CREATE TABLESPACE TS_LOGMNR DATAFILE ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS_LOGMNR_01.DBF’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;

Use the following statement to move all of LOG MINER objects from SYSAUX to TS_LOGMNR tablespace.

 EXEC DBMS_LOGMNR_D.SET_TABLESPACE(TS_LOGMNR);

3) Start Log Mining Load Archive log file into Log Miner.

BEGIN DBMS_LOGMNR.ADD_LOGFILE(C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCLARCHIVELOG\2014_05_12O1_MF_1_2_7JY3ZNWV_.ARC, DBMS_LOGMNR.NEW);

 END;

/

Perform Log Mining 

BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+ DBMS_LOGMNR.COMMITTED_DATA_ONLY);

 END;

Format query results.

SET TRIM ON SET LINES 200

COL USERNAME FORMAT A15

COL TIMESTAMP FORMAT A20

COL SESSION_INFO FORMAT A200

SPOOL C:LOGMNR.TXT

SELECT USERNAME, TO_CHAR(TIMESTAMP,’DD-MON-YYYYY HH24:MI:SS’) TIMESTAMP, SESSION_INFO, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER='SCOTT’ AND SEG_NAME=’STUDENTS’ AND OPERATION=’DELETE’;

SPOOL OFF;

Stop Log Miner.

BEGIN DBMS_LOGMNR.END_LOGMNR;

END;

/

Open C:LOGMNR.TXT and look for DELETE statements. If you do not locate the problematic query,repeat the step 2 (above) with another archive log file, and keep repeating it until you caught the query and theuser that has performed the DELETE operation.

Note: V$LOGMNR_CONTENTS has many columns, and you can customize your results by modifying the abovequery.