Logminer utility

Sometimes we need to see redo information about executed sql in the database.In order to do this, Oracle offers the Logminer utility.
This native tool analyzes the content of redo and archived redologs.

Note: If it’s configured and it’s not too late, it’s easier to use Flashback query.

Here is a simple example to use Oracle Logminer
— Prerequisite
EXECUTE_CATALOG_ROLE and select on V$LOGMNR_CONTENTS

— Specify a LogMiner dictionary.
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

— Specify a list of redo log files for analysis.
— to find which log file: search first_time field (the hour should be the last before the hour we need the logminer)
select * from v$archived_log where trunc(first_time) = to_date(sysdate)
and name like ‘%specific-file-system%’

— Add required archives (here 2)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>’+archives/my-db/archivelog/2017_10_28/thread_2_seq_366089.4743.958543215′, OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>’+archives/my-db/archivelog/2017_10_28/thread_1_seq_367742.3394.958545027′, OPTIONS => DBMS_LOGMNR.NEW);
[…]

— Start LogMiner.
— to retreive only commited transactions
EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’),ENDTIME => TO_DATE(’28-10-2017 06:10:00′,’DD-MM-YYYY HH24:MI:SS’), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

— to retreive both committed and uncommitted transactions
EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’),ENDTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

— Mine…
select count(*) from v$logmnr_contents a where a.TABLE_NAME = ‘MY_TABLE’;

create table my-schema.logmnr-result as
select * from v$logmnr_contents a where a.TABLE_NAME = ‘MY_TABLE’;

— end of the session
EXECUTE DBMS_LOGMNR.END_LOGMNR();

— Analyze┬ámy-schema.logmnr-result Table

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.