Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, April 15, 2010

Find who did it using Logminer

Step 1

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

EXECUTE SYS.DBMS_LOGMNR.START_LOGMNR(STARTTIME => '04/13/2010 10:25:00', -
ENDTIME => '04/13/2010 10:45:00', -
OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
SYS.DBMS_LOGMNR.CONTINUOUS_MINE + -
SYS.DBMS_LOGMNR.NO_SQL_DELIMITER);


Step 2

SQL> SET LINESIZE 132
SQL> SET PAGES 1000
SQL> COL OPERATION FORMAT A10
SQL> COL OBJ_NAME FORMAT A40
SQL> COL SQL_REDO FORMAT A60 WRAP
SQL> COL USERNAME FORMAT A15
SQL> COL SESSION_INFO FORMAT A40 WRAP
SQL>
SQL> SELECT OPERATION, SUBSTR(SEG_OWNER || '.' || SEG_NAME, 1, 40) OBJ_NAME, TIMESTAMP, USERNAME, SESSION#, SERIAL#, SQL_REDO, SESSION_INFO
2 FROM V$LOGMNR_CONTENTS
3 WHERE SEG_OWNER = 'ISA' AND SEG_NAME = 'I2_DISPO_ACTIVITY'
4 AND (OPERATION = 'DDL');

OPERATION OBJ_NAME TIMESTAMP USERNAME SESSION# SERIAL#
---------- ---------------------------------------- ------------------- --------------- ---------- ----------
SQL_REDO SESSION_INFO
------------------------------------------------------------ ----------------------------------------
DDL ISA.I2_DISPO_ACTIVITY 04/13/2010 10:43:47 UNKNOWN 0 0
DROP TABLE EMP


Step 3

EXECUTE DBMS_LOGMNR.END_LOGMNR;


To get the info like username and session info you should enable supplemental logging as below.

Verify supplemental logging is turned on
SQL>SELECT NAME,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

NAME SUPPLEME SUP SUP SUP SUP
--------- -------- --- --- --- ---
CM3P NO NO NO NO NO

Next, you must enable supplemental logging. Supplemental logging places additional column data into the redo log file whenever an UPDATE operation is performed. At the least, minimal database-level supplemental logging must be enabled for any Change Data Capture source database:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

more at :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm

No comments: