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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date