Oracle logmining

1.select supplemental_log_data_min  from v$database;

If supplemental log data is not enabled then

2.ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


3.  To check the max of logfile.

SELECT NAME FROM V$ARCHIVED_LOG
       WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

4.For checking log files in specific time and date:
SELECT *
  FROM (SELECT MIN (
                     'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '''
                  || val.name
                  || ''', OPTIONS => DBMS_LOGMNR.NEW);')
          FROM V$ARCHIVED_LOG val
        WHERE     val.first_time BETWEEN TO_TIMESTAMP (
                                             '&&START_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
                                      AND TO_TIMESTAMP (
                                             '&&END_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
               AND val.dest_id = 1
               )
UNION ALL
(SELECT    'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '''
        || val.name
        || ''', OPTIONS => DBMS_LOGMNR.ADDFILE);'
   FROM V$ARCHIVED_LOG val
  WHERE     val.first_time BETWEEN TO_TIMESTAMP (
                                             '&&START_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
                               AND TO_TIMESTAMP (
                                             '&&END_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
       AND val.dest_id = 1
        );

It will ask Start Date & time and End date and time. (Ex : 28APR2013070000)

Take the output....and execute plsql procedures completely.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/disk05/oradata/DEVT/archive/1_7315_613831530.arc',OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/disk05/oradata/DEVT/archive/1_7316_613831530.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
.
.
.
.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/disk05/oradata/DEVT/archive/1_7330_613831530.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

       
5.Start log miner:
To collect also the commit timestamp -> EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

6.  Collect the row IDs for particular seg_name and seg_owner

select distinct row_id from v$logmnr_contents where seg_name='CMS_INVENTORY' and seg_owner='LILYPLU';


7.create table  as
  select timestamp,commit_timestamp, audit_sessionid,seg_owner,seg_name,username,session#,serial#,operation_code,sql_redo,sql_undo,row_id  FROM V$LOGMNR_CONTENTS where seg_owner = 'LILYPLU' and seg_name = 'COATING_ORDER' and
  row_id in ('AAAeHJAAQAAJ0f5AAU',
'AAAeHJAAQAAJ0eLAAg',
'AAAeHJAAQAAJ0f5AAO',
'AAAeHJAAQAAJ0f2AAU',
'AAAeHJAAQAAJ0ftAAi',
'AAAeHJAAQAAJ0fZAAe',
'AAAeHJAAQAAJ0f5AAR',
'AAAeHJAAQAAJ0f2AAV',
'AAAeHJAAQAAJ0fpAAk',
'AAAeHJAAQAAJ0f5AAk',
'AAAeHJAAQAAJ0fTAAL',
'AAAeHJAAQAAJ0fpAAg',
'AAAeHJAAQAAJ0ftAAl',
'AAAeHJAAQAAJ0f5AAS'
)

8.Below query gives number of Update,insert & delete for particular owner for particular table.

select seg_owner, seg_name, username, operation, count(*) from V$LOGMNR_CONTENTS where seg_name='RUN' and seg_owner='LILYPGO'group by seg_owner, seg_name, username, operation

9. To find the user session on the audit log when the information at the logminer is not complete
==============================================================================================-=

    select * from dba_audit_session where sessionid = '123'

    :Where 123 is the value of column AUDIT_SESSIONID you took from the V$LOGMNR_CONTENTS table


select distinct audit_sessionid from v$logmnr_contents where seg_name='RUN' and seg_owner='LILYPGO';

10.Turn off the minering

    EXECUTE DBMS_LOGMNR.END_LOGMNR();


NOTES:  When we are using sql developer, it is better to divide the time period, create table, and  turn off log miner. Then start for second set of log mining session. (Example : If there is more than 1000 rows for row IDs we can't execute the script on sqldeveloper)


To get the visible timestamps:
select TO_CHAR (timestamp ,'YYYY-MON-DD HH24:MI:SS') as time,TO_CHAR (COMMIT_TIMESTAMP ,'YYYY-MON-DD HH24:MI:SS') as commit_time, AUDIT_SESSIONID,SEG_OWNER,SEG_NAME,USERNAME,session#,SERIAL#,OPERATION_CODE,SQL_REDO,SQL_UNDO,ROW_ID  from babus.miner01 where SEG_OWNER = 'LILYPLU' and SEG_NAME = 'CMS_INVENTORY' and
  row_id in ('AAAeGXAAQAAAv2GAAA');


Comment me if you find this post is useful also let me know if you need any new topics.