Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 11, 2005

Using LogMiner - Part 2 - Striking Out

By Steve Callan

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.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM