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.