Using LogMiner - Part 2 - Striking Out

May 11, 2005

In a previous article on LogMiner, the steps for setting up your environment to use this tool were shown. Oracle's documentation lists several ways LogMiner can be used to recover lost data. Generally, "lost" in this case means a user error induced type of loss. Under the appropriate conditions, you can strike gold with LogMiner. In other situations, you run the risk of striking out. This article investigates a scenario that LogMiner may or may not be able to recover or fix. Let's start off by reviewing what Oracle says LogMiner can do.

Potential Uses for Data Stored in Redo Logs

(See Chapter 9 of the Oracle 9.2 Administrator's Guide)

  • Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun.
  • Detecting and whenever possible, correcting user error, which is a more likely scenario than logical corruption.
  • Determining what actions you would have to take to perform fine-grained recovery at the transaction level.
  • Performance tuning and capacity planning through trend analysis.
  • Performing post-auditing. The redo logs contain all of the information necessary to track any DML and DDL statements executed on the database, the order in which they were executed, and who executed them.

The next part of interest concerns the "what" as in what are in the redo logs that is of use for LogMiner. The last three items from the list are shown below.

  • The name of the user who issued the DDL or DML statement to make the change (USERNAME column).
  • Reconstructed SQL statements showing SQL that is equivalent (but not necessarily identical) to the SQL used to generate the redo records (SQL_REDO column). If a password is part of the statement in a SQL_REDO column, the password is encrypted.
  • Reconstructed SQL statements showing the SQL statements needed to undo the change (SQL_UNDO column). SQL_UNDO columns that correspond to DDL statements are always NULL. Similarly, the SQL_UNDO column may be NULL for some datatypes and for rolled back operations.