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.
