Using LogMiner

Oracle LogMiner, which is part of Oracle Database, enables you to query online and archived redo log files through a SQL interface.

Because LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

Perform Initial Setup Activities

There are initial setup activities that you must perform before using LogMiner for the first time. You only need to perform these activities once, not every time you use LogMiner:

  • Enable the type of supplemental logging you want to use. At the very least, Oracle Corporation recommends that you enable minimal supplemental logging, as follows:
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
    

    See Supplemental Logging for more information.

  • Use the DBMS_LOGMNR_D.SET_TABLESPACE routine to re-create all LogMiner tables in an alternate tablespace. For example:
    SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');
Directing LogMiner Operations and Retrieving Data of Interest

You direct LogMiner operations using the DBMS_LOGMNR and DBMS_LOGMNR_D PL/SQL packages, and retrieve data of interest using the V$LOGMNR_CONTENTS view, as follows:

  1. Specify a LogMiner dictionary.

    Use the DBMS_LOGMNR_D.BUILD procedure or specify the dictionary when you start LogMiner (in Step 3), or both, depending on the type of dictionary you plan to use.       

              exec dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);

  1. Specify a list of redo log files for analysis.

    Use the DBMS_LOGMNR.ADD_LOGFILE procedure, or direct LogMiner to create a list of log files for analysis automatically when you start LogMiner (in Step 3). 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( –

  LOGFILENAME => ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG’,-

       OPTIONS => DBMS_LOGMNR.NEW);

  1. Start LogMiner.

    Use the DBMS_LOGMNR.START_LOGMNR procedure.

  2. Request the redo data of interest.

    Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view. Per email from Diana Lorentz/Doug Voss on 3/16/05)

  3. End the LogMiner session.

    Use the DBMS_LOGMNR.END_LOGMNR procedure.

You must have been granted the EXECUTE_CATALOG_ROLE role to use the LogMiner PL/SQL packages and to query the V$LOGMNR_CONTENTS view.

Complete Reference is available at link1 and link2

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: