04 October 2013
Setting up Log Miner (notes)
Check utl_file_dir parameter is set (restart instance is required)
--Build dictionary to
join object IDs with the real names:
EXECUTE
DBMS_LOGMNR_D.BUILD('dictionary.ora', '/opt/oracle/diag/rdbms/db/DB/trace', OPTIONS=DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
--Add
logfile(s)
EXECUTE
DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23566_823518714.arc', -
OPTIONS =dbms_logmnr.NEW);
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23566_823518714.arc', -
OPTIONS =dbms_logmnr.NEW);
EXECUTE
DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23567_823518714.arc', -
OPTIONS =dbms_logmnr.ADDFILE);
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23567_823518714.arc', -
OPTIONS =dbms_logmnr.ADDFILE);
EXECUTE
DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23568_823518714.arc', -
OPTIONS =dbms_logmnr.ADDFILE);
LOGFILENAME ='/data1/oradata/archivelogs/DB/arch_1_23568_823518714.arc', -
OPTIONS =dbms_logmnr.ADDFILE);
EXECUTE
DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME = '/data1/oradata/archivelogs/DB/arch_1_23569_823518714.arc', -
OPTIONS =dbms_logmnr.ADDFILE);
LOGFILENAME = '/data1/oradata/archivelogs/DB/arch_1_23569_823518714.arc', -
OPTIONS =dbms_logmnr.ADDFILE);
select
LOG_ID,FILENAME,
TO_CHAR(LOW_TIME,'DD-MON HH24:MI:SS'),
TO_CHAR(HIGH_TIME,'DD-MON
HH24:MI:SS')
from v$logmnr_logs;
-- Start
log mining
EXECUTE
DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/opt/oracle/diag/rdbms/db/DB/trace/dictionary.ora', -
STARTTIME = to_date('26-Sep-2013 15:02:40', 'DD-MON-YYYY HH24:MI:SS'), -
ENDTIME = to_date('26-Sep-2013 15:15:00', 'DD-MON-YYYY HH24:MI:SS'));
STARTTIME = to_date('26-Sep-2013 15:02:40', 'DD-MON-YYYY HH24:MI:SS'), -
ENDTIME = to_date('26-Sep-2013 15:15:00', 'DD-MON-YYYY HH24:MI:SS'));
-- If you want to remove the log file from the mining group
select ' EXECUTE
DBMS_LOGMNR.REMOVE_LOGFILE(''',filename,''');' from v$logmnr_logs;
-- Mining
select operation,seg_owner,seg_name,count(*) from
v$logmnr_contents group by seg_owner,seg_name,operation;
select OPERATION, SQL_REDO, OBJECT_ID,REDO_VALUE,
SEG_OWNER,SEG_NAME, START_TIMESTAMP
FROM v$logmnr_contents
…
