Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

Step By Step Logminer

Here are the steps a performed to enable Logminer;
Oracle 10GR2 Database:

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, or both, depending on the type of dictionary you plan to use.
2. 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).
3.Start LogMiner.
Use the DBMS_LOGMNR.START_LOGMNR procedure.
4. Request the redo data of interest.
Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view)

  • Step by Step :
  • 1- Supplemental Logging Database Supplemental Logging. To enable supplemental logging at database level:

    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ;
    Database altered.
    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
    SUPPLEMENTAL_LOG_DATA_MIN
    ————————-
    YES
    2- Logminer build procedure requires access to a directory where it can place the dictionary file. Because PL/SQL procedures do not normally access user directories, you must specify a directory for use by the DBMS_LOGMNR_D.BUILD procedure or the procedure will fail. To specify a directory, set the initialization parameter, UTL_FILE_DIR, in the initialization parameter file:

    SQL> show parameter UTL_FILE_DIR

    NAME TYPE VALUE
    ———————————— ———– ——————————
    utl_file_dir string

    —Create the directory from Sql plus:
    SQL> !mkdir /home/oracle/my_logminer

    — Must reatart your instance :
    SQL> alter system set utl_file_dir=’/home/oracle/my_logminer’ scope=spfile ;
    System altered
    SQL> startup force;
    ORACLE instance started.
    Total System Global Area 419430400 bytes
    …..

    3- Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file. For example, enter the following to create the file dictionary.ora in /home/oracle/my_logminer :

    SQL> EXECUTE DBMS_LOGMNR_D.BUILD(‘dictionary.ora’, –
    > ‘/home/oracle/my_logminer’, –
    > DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
    PL/SQL procedure successfully completed.
    SQL>

    4- To mine data in the redo log files, LogMiner needs information about which redo log files to mine. Changes made to the database that are found in these redo log files are delivered to you through the V$LOGMNR_CONTENTS view.
    You can direct LogMiner to automatically and dynamically create a list of redo log files to analyze, or you can explicitly specify a list of redo log files for LogMiner to Execute the DBMS_LOGMNR.START_LOGMNR procedure to start LogMiner.

    SQL>
    SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( –
    > STARTTIME => ’01-Mar-2010 14:48:00′, –
    > ENDTIME => ’01-Mar-2010 19:00:00′, –
    > OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + –
    > DBMS_LOGMNR.CONTINUOUS_MINE
    );
    PL/SQL procedure successfully completed.
    SQL>

    Oracle recommends that you specify a LogMiner dictionary option. If you do not, LogMiner cannot translate internal object identifiers and datatypes to object names and external data formats. Therefore, it would return internal object IDs and present data as binary data. Additionally, the MINE_VALUE and COLUMN_PRESENT functions cannot be used without a dictionary.

    SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( –
    > DICTFILENAME =>’/home/oracle/my_logminer/dictionary.ora’);
    PL/SQL procedure successfully completed.
    SQL>

    5- Querying V$LOGMNR_CONTENTS for Redo data : It’s useful to use the query below, especially, when you need to recover data for a given user and / or segment (=undo). Also, for data replication purposes.

    SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO
    2 FROM V$LOGMNR_CONTENTS
    3 WHERE SEG_OWNER = ‘SYS’
    4 AND SEG_NAME = ‘WRH$_SYSMETRIC_SUMMARY’
    5 ;

    OPERATION SQL_REDO SQL_UNDO
    ——————————– ——————————————————————————– ——————————————————————————–
    DELETE delete from “SYS”.”WRH$_SYSMETRIC_SUMMARY” where “SNAP_ID” = ‘12830’ and “DBID” insert into “SYS”.”WRH$_SYSMETRIC_SUMMARY”(“SNAP_ID”,”DBID”,”INSTANCE_NUMBER”,”B
    DELETE delete from “SYS”.”WRH$_SYSMETRIC_SUMMARY” where “SNAP_ID” = ‘12830’ and “DBID” insert into “SYS”.”WRH$_SYSMETRIC_SUMMARY”(“SNAP_ID”,”DBID”,”INSTANCE_NUMBER”,”B

    Leave a Comment »

    No comments yet.

    RSS feed for comments on this post. TrackBack URI

    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

    Create a free website or blog at WordPress.com.

    %d bloggers like this: