Oracle Tips/Questions and Answers:

Question: 

How Can I use LogMiner to Analyze Redo Log Files?

Answer:

How to use LogMiner to Analyze Redo Log Files

You can use the Oracle LogMiner utility to enable you to query redo log files through a SQL interface.

Dictionary

You can use dictionary so when you query information from log files, you don't see reference object id instead of object name.

You can use the following method to extract object name instead of object id.

Extracting the Dictionary to a Flat File or to Redo Log Files

Using the Online Catalog

LogMiner Restrictions

The following restrictions apply:

• The following are not supported:
  • Data types LONG and LOB
  • Simple and nested abstract data types ( ADTs)
  • Collections (nested tables and VARRAYs)
  • Object Refs
• Index Organized Tables (IOTs)

Steps to use logminer:

Create a Dictionary Flat File for an Oracle9i Database

Add the UTL_FILE_DIR parameter to the init.ora file and then stop and start the database:

UTL_FILE_DIR = /oracle/logminor

Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD to create dictionary with the following options:

DBMS_LOGMNR_D.STORE_IN_FLAT _FILE

DBMS_LOGMNR_D.STORE_IN_REDO _LOGS

--with flat file option

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(

     DICTIONARY_FILENAME => 'mydictionaryname.ora',

     DICTIONARY_LOCATION => '/usr/mydictionary/location');

--OR

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(

     'flatdictionary.ora', 

     '/oracle/logminor/',

     options => DBMS_LOGMNR_D.STORE_IN_FLAT _FILE);

--with redo logs option

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(

     options => DBMS_LOGMNR_D.STORE_IN_REDO _LOGS);

Adding or Removing the Redo Log Files for Analysis

You can specify the redo log files as following examples with the mount or nomount option of startup.

-- for new log file or the first one.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(

     LOGFILENAME => '/oracle/archivelogs/log_01 _132_6576654328.ora',

     OPTIONS => DBMS_LOGMNR.NEW);

-- for adding an additional log file.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(

     LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',

     OPTIONS => DBMS_LOGMNR.ADDFILE);

-- for removing a log file.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(

     LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',

     OPTIONS => DBMS_LOGMNR.REMOVEFILE);

Starting LogMiner

After you have create a dictionary file and specify which redo log files to analyze, you can start LogMiner and begin your analysis. Take the following steps:

--To start Log Miner with flat dictionary:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(

     DICTFILENAME =>'/oracle/database/dictionary .ora');

--To start Log Miner with using dictionary from redo logs:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(

OPTIONS =>DBMS_LOGMNR.DICT_FROM_REDO _LOGS);

--To start Log Miner with using Online Catalog Dictionary:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(

OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE _CATALOG);

--To start Log Miner using starting and ending time:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(

      DICTFILENAME => '/oracle/flatdictionary.ora',

STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS')

ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

--To start Log Miner using the SCN number:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(

      DICTFILENAME => '/oracle/dictionary.ora',

      STARTSCN => 100,

      ENDSCN => 150);

--To start Log Miner using the following OPTIONs:

COMMITTED_DATA_ONLY

SKIP_CORRUPTION

DDL_DICT_TRACKING

NO_DICT_RESET_ONSELECT

DICT_FROM_ONLINE_CATALOG

DICT_FROM_REDO_LOGS

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(

      OPTIONS =>

      DBMS_LOGMNR.DDL_DICT_TRACKING +

      DBMS_LOGMNR.NO_DICT_RESET _ONSELECT +

      DBMS_LOGMNR.DICT_FROM_REDO _LOGS);

Now you should be able to do this:

SQL> SELECT count(*) FROM v$logmnr_contents;

SQL> DESC v$logmnr_contents

Querying LogMiner

(EXAMPLES of how to read from v$logmnr_contents)

To read the log file, you need to do the following query.

SQL> COL table_name FORMAT a20

SQL> SELECT sql_redo FROM SYS.V$LOGMNR_CONTENTS;

To query the V$LOGMNR_CONTENTS view to see changes done by a specific user:

SQL> SELECT sql_redo, sql_undo FROM V$LOGMNR_CONTENTS

     WHERE USERNAME = 'SAASUB' AND TABLE_NAME = 'EVENT';

SQL> SELECT rownum, sql_redo

     FROM V$LOGMNR_CONTENTS

     WHERE sql_redo like '%SAABUD%' and

           sql_redo NOT like '%SYS%' and

           rownum < 10;

--with time stamp

SQL> SELECT 'Row Number: ' || rownum,

            'Date-Time: ' || to_char(timestamp,'DD-MM HH24:MI:SS'),

            'Transaction on table: ' ||

            table_name || '--->' ||

            SUBSTR(sql_redo,1,20)  

     FROM V$LOGMNR_CONTENTS

     WHERE sql_redo like '%SAABUD%' AND

           sql_redo NOT like '%SYS%' AND

           rownum < 10;

To determine which tables were modified in the range of time.

SQL> SELECT seg_owner, seg_name, count(*) AS Hits

     FROM V$LOGMNR_CONTENTS WHERE seg_name NOT LIKE '%$'

     GROUP BY seg_owner, seg_name;

SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')

            as "Date/Time",

            table_name,

            SUBSTR(sql_redo,1,40)  

     FROM V$LOGMNR_CONTENTS

     WHERE sql_redo like '%SAABUD%' AND

           sql_redo NOT like '%SYS%';

To determine who drop any objects.

SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')

            as "Date/Time",

            table_name,

            SUBSTR(sql_redo,1,40)  

     FROM V$LOGMNR_CONTENTS

     WHERE sql_redo like '%SAABUD%' AND

           sql_redo NOT like '%SYS%' AND

           UPPER(sql_redo) like '%DROP%';

Ending LogMiner

To end the log miner.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

Good Luck!