Oracle Database 19c -- How to Use LogMiner

Problem

You want to use the LogMiner to investigate on operations performed on a database for a specific period of time

Solution

1) Ensure minimal supplemental redo logging was enabled prior using the LogMiner:

SQL> select supplemental_log_data_min from v$database;

Enable with:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2) Identify the required archived log files for the interested interval of time:

with times(start_time, end_time) as (
    select timestamp'YYYY-MM-DD HH24:MI:SS' start_time,
           timestamp'YYYY-MM-DD HH24:MI:SS' end_time
    from dual
)
select name, thread#, sequence#, first_time, next_time
from v$archived_log
where ( (select start_time from times) between first_time and next_time
     or (select end_time from times)   between first_time and next_time
     or ((select start_time from times) <= first_time
          and next_time <= (select end_time from times) ))
     and standby_dest='NO'
order by thread#, sequence#;

3) Add the identified log files to the LogMiner:

SQL> exec DBMS_LOGMNR.ADD_LOGFILE ('<name>');

4) Verify that all required log files successfully added:

SQL> select * from V$LOGMNR_LOGS;

4.1) Build the LogMiner dictionary:

create directory <directory> as '<path>';

BEGIN
  DBMS_LOGMNR_D.build (
    dictionary_filename => '<file_name>',
    dictionary_location => '<directory>');
END;
/

5) Start LogMiner:

BEGIN
    DBMS_LOGMNR.START_LOGMNR(
       STARTTIME    => timestamp'YYYY-MM-DD HH24:MI:SS',
       ENDTIME      => timestamp'YYYY-MM-DD HH24:MI:SS',
       DictFileName => '<path>/<file_name>');
END;

6) Query the LogMiner results:

SQL> select * from V$LOGMNR_CONTENTS;

7) Stop LogMiner:

SQL> exec DBMS_LOGMNR.END_LOGMNR;

Reference:

• ORA-600 [krvrdccs10] When Running LOGMNR With ONLINE DICTIONARY OPTION (Doc ID 2747083.1)