Install and use Oracle LogMiner (9i or later)

1. LogMiner Installation

Run the following two scripts as a SYS User:

$ Oracle_HOME/rdbms/admin/dbmslm. SQL

$ ORACLE_HOME/rdbms/admin/dbmslmd. SQL

2. Use LogMiner

Set a required parameter UTL_FILE_DIR = <path> to ensure that oracle users have read and write permissions under this <path>. This parameter does not support dynamic modification, so it can only be modified in pfile, then restart the database instance.

Create a LogMiner dictionary file and execute the following script. (Note-the symbols cannot be lost)

EXECUTE DBMS_LOGMNR_D.BUILD ('dictionary. ora ',-
'<Path> ',-
OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE );

Add the Log file that requires mine to LogMiner and execute the following script.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-
LOGFILENAME => '/oradata/redo01.log ',-
OPTIONS => dbms_logmnr.NEW); <========= the first creation

 

 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-
LOGFILENAME => '/oradata/redo02.log ',-
OPTIONS => dbms_logmnr.ADDFILE); <======== append

 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-
LOGFILENAME => '/oradata/redo02.log ',-
OPTIONS => dbms_logmnr.REMOVEFILE); <======== Delete

Log File Mining

The options you can select to filter log information

Meaning
================================

STARTSCN start number
ENDSCN end number
STARTTIME Start Time
ENDTIME End Time
Name of the DICTFILENAME dictionary file

LogMiner view for analysis

View name view information
========================================================== ==================================

V $ the dictionary file being used by LOGMNR_DICTIONARY
V $ LOGMNR_PARAMETERS LogMiner Parameters
V $ LOGMNR_LOGS: the log file being analyzed
V $ LOGMNR_CONTENTS

Example of Filtering:

EXECUTE DBMS_LOGMNR.START_LOGMNR (-
DICTFILENAME => '<path>/dictionary. ora ',-
STARTSCN => 100 ,-
ENDSCN => 150 );

Example of viewing log information:

SELECT operation, SQL _redo FROM v $ logmnr_contents;