LogMiner to analyze online or archived redo logs

We had a situation - yes, other than having a situation, why should I dig stuffs in archive logs! Log mining is not my hobby :-)

I had to find what were going on in database with nightly cron scheduler for a particular day. I looked at archive logs to get all the answer of my questions - LogMiner made my life easy!


What is LogMiner?

Anyone can easily guess, it a tool or one of the capabilities provided by Oracle to look at online redo logs or old archive logs. These logs keep history of activities performed in database. We can find all the activities those happened to database either by the application users or by the system itself - amazing! By initiating LogMiner, we can simply query some database views through SQL interface to find desired information.

It can be used as a data audit tool or for any other sophisticated data analysis.

It's nice to go through the LogMiner benefits -

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm#i1005606


Prerequisites

Supplemental logging must be enabled prior to the redo/archive logs are being generated - this option will put additional information to those logs which will be analyzed by LogMiner later.

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

How do I do that?

We need to do all these being a sys user. Otherwise, some special roles will be required explicitely - EXECUTE_CATALOG_ROLE and SELECT ANY TRANSACTION.

Step-1: Add logfile(s)

The procedure will add archive log file as the log mining candidate from where we might extract information.

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE('C:\oracle\product\10.2.0\flash_recovery_area\SHAWON_D\ARCHIVELOG\2009_03_15\O1_MF_1_10_4VRZ1DT4_.ARC', DBMS_LOGMNR.ADDFILE);

We could add as many as we want in the above way.

Step-2: Start LogMiner with data dictionary information

LogMiner requires data dictionary information to translate Object ID (kept in redo/archive logs) to Object Names when it returns data as a part of data analysis.

The dictionary options are -

1. Using the Online Catalog
2. Extracting a LogMiner Dictionary to the Redo Log Files
3. Extracting a LogMiner Dictionary to the Redo Log Files

I used the online catalog option as I could use the database during off peak hours for log analysis.

SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Step-3: Query LogMiner view to retrieve desired information

The main source of data is V$LOGMNR_CONTENTS. Just describe the view and queried as I wanted.

Forexample, I wanted to know all the operations within a specified period of time by a user -

SELECT OPERATION, SQL_REDO, SQL_UNDO, TIMESTAMP
FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'TEST'
AND TIMESTAMP
BETWEEN TO_DATE('03-15-2009 09:40:00 am','mm-dd-yyyy hh:mi:ss am')
AND TO_DATE('03-15-2009 09:50:00 am','mm-dd-yyyy hh:mi:ss am')
ORDER BY TIMESTAMP;


Step-4: Close LogMiner

SQL>EXECUTE DBMS_LOGMNR.END_LOGMNR;

That's it! You may or may not close. Witout closing the previous one, we can start another LogMinger session.


I used this detailed Oracle doc during my activities -

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm