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

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.