Using LogMiner – Part 2 – Striking Out

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.

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles