Oracle get sql redo from log_history I run into a problem with a customer which had no proper backup.

His last backup (meaning exp-dump) is from 1st Aug 2011.

However he lost due to batch DELETE statements all data.

My plan was now to get the redo-sql from the logs, import the dump and replay all sql from 1st Aug up to 2nd Sept (just before the batch DELETE happened).

However I am stuck because V$LOG_HISTORY shows me logs back to February 2011 but no V$ARCHIVED_LOG entries to know which logs to include into LogMiner.

So: How can i load this V$LOG_HISTORY logs into the LogMiner to get the redo-sql or how else can I get the redo-sql or replay it based on the dump-file fromn 1st Aug?

Side notes: This is a single alone (like an orphant) oracle 9.2 installation on a linux server.
First of all export is not backup. You will not recover a physical copy of a database from exp-dump, only an unconsistent copy of its data (you can make them consistent but the option is rarely used). I'm really not sure you can apply the contents of logMiner on such a copy.

Your best bet would be to apply undo statements to your current copy of the DB to return it to its original state, such as described in the documentation:


  If you fully understand and take into account existing dependencies, it may be possible to perform a table-specific undo operation to return the table to its original state. This is achieved by applying table-specific reconstructed SQL statements that LogMiner provides in the reverse order from which they were originally issued. See Scenario 1: Using LogMiner to Track Changes Made by a Specific User for an example.


Unfortunately it would seem that your customer is running its DB in NOARCHIVELOG mode since the view V$ARCHIVED_LOG is empty. V$LOG_HISTORY only records the switchs of the online redo logs which all DBs have. Only databases that are setup in ARCHIVELOG mode will archive redo logs.

I'm afraid your customer is out of luck.

At this point you should probably consider contacting Oracle support.