Donald K. Burleson Oracle Tips

Oracle LogMiner enhancements

The Oracle LogMiner utility started as a very simple tool reading Oracle’s archived redo logs, and has evolved over the past several years into a very useful auditing tool and recovery tool.

• Fine-grained recovery – Oracle LogMiner allows you to perform fine-grained recovery at the transaction level.

• Auditing – The redo logs provide a complete records of all database updates, and the archives can provide detailed audit information.

In many industries, Oracle professionals are required to provide complete audit trails for all update transactions within their systems.  The Oracle archived redo logs are very valuable tool for doing system auditing because they contain all of the “after” images for every change made within the Oracle database.

From an auditing perspective, the Oracle LogMiner has many significant enhancements to allow the Oracle professional to very quickly go into their system and see what changes were made to what tables, who made the changes, and the time to the changes were made.  Of course, LogMiner does not help with auditing for the viewing of information, since the redo logs contain only information on DDL and DML.

The most important enhancement to the Oracle LogMiner is the flexible GUI interface.  Using this new GUI interface makes it quite easy to track DDL changes, and audit specific changes to tables on a user by user basis.

Several new features have been added to LogMiner in Oracle to make it more flexible and robust:

• Track DDL Statements

• Track Data Dictionary Access

• DDL Dictionary Tracking

• Track Dictionary Staleness

• Identify Log Corruption

• Show Committed Data Only

We also see the following enhancements:

• A new LogMiner Viewer GUI in addition to the command line interface

• The ability to translate DML associated with Index Clusters

• The ability to mine for changes by value

• Support for chained and migrated rows

• Support for direct path inserts

• Ability to use an online dictionary

• Ability to extract the data dictionary into the redo log files to seamlessly integrate DDL changes

• DDL statement tracking

• Ability to skip log corruptions

• Ability to specify that only committed transactions be displayed

• Generate SQL_REDO and SQL_UNDO with primary key information to help the DBA undo changes

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.