Oracle LogMiner - Background and Steps to Run.. 1

Steps to Run LogMiner through SQL Plus. 1

Miscellaneous Remarks. 3

Scope of the v$logmnr_contents view.. 3

Allowing only committed transactions in the LogMiner View.. 3

Version Restriction. 3

References. 3

Oracle LogMiner - Background and Steps to Run

Oracle LogMiner, which is part of the Oracle Database, enables you to query on online and archived redo log files through a SQL interface. Redo log files contain information about the history of activity on a database. 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.

 

There are four basic objects in a LogMiner configuration that you should be familiar with: the source database, the mining database, the LogMiner dictionary, and the redo log files containing the data of interest:

 

• The source database is the database that produces all the redo log files that you want LogMiner to analyze.
• The mining database is the database that LogMiner uses when it performs the analysis.
• The LogMiner dictionary allows LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request. LogMiner uses the dictionary to translate internal object identifiers and data types to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs and presents data as binary data.
• The redo log files contain the changes made to the database or database dictionary.

 

Steps to Run LogMiner through SQL Plus

1. Log into Oracle with username SYS as SYSDBA (or with a user who has the SYSDBA privileges).

SQL> connect sys/<passwd>@wham4 as SYSDBA

 

            (It is necessary to log on as SYSDBA to perform the following operations)

 

2. Turn Supplemental logging ON if it isn't already.

 

To check if minimal supplemental logging is turned on or off:

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

 

            To enable minimal supplemental logging:

            SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

3. To ensure that ALL field values appear in the log in the case of an UPDATE, use:

 

SQL> alter database add supplemental log data (ALL) columns;

SQL> alter system archive log current;

 

4. Set parameter UTL_FILE_DIR to the directory where LogMiner will maintain the dictionary file.

 

SQL> alter system set UTL_FILE_DIR = D:\Oracle\Redologs scope = spfile

(If this gives "write to SPFILE requested but no SPFILE specified at startup" error, you might have to run the "create pfile from spfile" command. E.g.: create pfile='D:\oracle\srvm\admin\init.ora' from spfile='d:\oracle\dbs\SPFILEWHAM4.ORA';)

 

You can see the value of parameter UTL_FILE_DIR with the command:

SQL> show parameter UTL_FILE_DIR

OR

            SQL> select value from v$parameter where name = 'utl_file_dir';

 

5. Create the LogMiner dictionary.

SQL> execute dbms_logmnr_d.build( -

           Dictionary_filename => 'dictionary.ora',-

Dictionary_location => 'D:\oracle\Redologs');

 

6. Add Logfiles.

 

            You can query for the current online Redo log files by executing the following command:

            SQL> select * from v$logfile;

           

You can then add those log files to LogMiner by using the following commands:

 

SQL> exec dbms_logmnr.add_logfile( logfilename =>

'D:\ORACLE\ORADATA\WHAMORAC\REDO03.LOG', options =>dbms_logmnr.new);

 

SQL> exec dbms_logmnr.add_logfile( logfilename => 'D:\ORACLE\ORADATA\WHAMORAC\REDO02.LOG', options=>dbms_logmnr.addfile);

 

SQL> exec dbms_logmnr.add_logfile( logfilename => 'D:\ORACLE\ORADATA\WHAMORAC\REDO01.LOG', options => dbms_logmnr.addfile);

 

7. Start LogMiner

 

SQL> exec dbms_logmnr.start_logmnr( dictfilename -

                             => 'D:\oracle\Redologs\dictionary.ora');

 

            (Use same directory and filename that you used in the dbms_logmnr_d.build procedure)

 

8. Query the Redo File contents. For example:

SQL > select timestamp, sql_undo, sql_redo from v$logmnr_contents where username='SCOTT' and trunc(timestamp) = to_char(sysdate, 'DD-MON-YY')

 

9. End the LogMiner session:

            SQL> exec dbms_logmnr.end_logmnr;

Miscellaneous Remarks

Scope of the v$logmnr_contents view

The output from LogMiner is the contents of the view "v$logmnr_contents'. The output is only visible during the life of the session which runs the procedure 'dbms_logmrn.start_logmnr'. This is because all of the LogMiner memory is in PGA memory, so it is neither visible to other sessions, nor is it persistent.

Allowing only committed transactions in the LogMiner View

The Log Miner session can automatically filter out uncommitted transactions so that only committed transactions are seen. While starting LogMiner, set the OPTIONS parameter with the value DBMS_LOGMNR.COMMITTED_DATA_ONLY.

E.g. DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'D:\oracle\dict\dictionary.ora',

OPTIONS =>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

Version Restriction

LogMiner is only available in Oracle version 8.1 or later. It can only analyze redo log files (online or archived) from 8.0 or later databases.

 

References

1. Using LogMiner to Analyze Redo Logs (from Oracle).

 

2. Oracle 9i Logminer Technical White Paper (PDF from Oracle).

 

3. "Auditing Past Transactions with Oracle LogMiner" by Mike Hordila.
    
4. "Mining for Clues" by Arup Nanda (includes a bit about Remote Mining).