LogMiner


 
--@?/rdbms/admin/dbmslogmnrd.sql;
 -- related views
 SELECT * FROM V$LOGMNR_DICTIONARY;
 SELECT * FROM V$LOGMNR_PARAMETERS;
--contents of the logs
SELECT * FROM V$LOGMNR_CONTENTS;

--which redo logs are being analyzed in the current logminer session
 
SELECT * FROM V$LOGMNR_LOGS;

-- minimal supplemental logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE INDEX) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP
emp_parttime (empno, ename, deptno) ALWAYS;
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP
emp_parttime (empno, ename, deptno) ;
ALTER TABLE scott.emp DROP SUPPLEMENTAL LOG GROUP emp_parttime;
-- to create all logminer objects in to the specified tablespace
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');
EXECUTE DBMS_LOGMNR_D.BUILD ('dictionary.ora', '/oracle/database/',
OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log1orc1.ora',
OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log2orc1.ora',
OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log3orc1.ora',
OPTIONS => DBMS_LOGMNR.REMOVEFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
EXECUTE DBMS_LOGMNR.START_LOGMNR (
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
-- For example, to see all the DDLs executed by user SYS, you could issue
the following query
SELECT USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'SYS' AND OEPRATION = 'DDL';
-- filtering redo_log actions
EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.SKIP_CORRUPTION);
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/oracle/dictionary.ora',
STARTTIME => TO_DATE('23-OCT-2013 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('23-OCT-2013 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/oracle/dictionary.ora',
STARTSCN => 100,
ENDSCN => 150);
EXECUTE DBMS_LOGMNR.END_LOGMNR;
-- a session example
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log1orc1.ora',
OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log2orc1.ora',
OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR(
DICTFILENAME =>'/oracle/database/dictionary.ora');
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => 'orcldict.ora',
STARTTIME => TO_DATE('23-OCT-2013 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('23-OCT-2013 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: