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_REDOcolumn). If a password is part
of the statement in aSQL_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, theSQL_UNDOcolumn may be NULL for some datatypes
and for rolled back operations.
