Oracle get sql redo from log_history

Question

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.

Answer 1

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.

Answer 2

v$log_history may show the log switches for a long time ago, but as you said, the DB is not in archivelog mode, so this information is not helpful here.

With 9i, you cannot do flashback database (apart from that it requires archivelog mode also) - there is a slight chance that you can do flashback query far enough into the past, because it is using undo information. Try select * from tab as of timestamp systimestamp - interval 'n' day and increase n as much as you can. Good luck!

Answer 3

But there are only around 165MB of data and the UNDO tablespaces uses currently 1.2GB - there is no chance any information is in that?

Have you considered flashback instead of LogMiner?

Data in the undo tablespace can be overwritten as soon as its transaction has commited.

Not true, it depends on undo_retention

It is unlikely you would have the delete data available.

Worth a try though