Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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.

share|improve this answer
    
Does this mean, LogMiner is out of the question but UNDO is possible until the dates described in V$LOG_HISTORY? –  ZoolWay Sep 7 '11 at 10:24
1  
It seems you don't have the archived logs since the DB is not recording them. You need them to run logMiner. –  Vincent Malgrat Sep 7 '11 at 10:27
    
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? –  ZoolWay Sep 7 '11 at 11:05
1  
UNDO data is used for active transactions that have not commited yet, so that they can be rolled back. Data in the undo tablespace can be overwritten as soon as its transaction has commited. It is unlikely you would have the delete data available. Even if it was still there, I don't know if a tool exists to read the UNDO tablespace content. –  Vincent Malgrat Sep 7 '11 at 11:55
    
Ok, this means that if the oldest online log sequence is 2250 and I would net to go back to 2221 according to V$LOG_HISTORY there is nothing which can be done? The V$LOG_HISTORY is just informative but has not any data attached to it? –  ZoolWay Sep 7 '11 at 12:02

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!

share|improve this answer

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

share|improve this answer
    
About undo_retention: if the data is still there, how do you query the data in the undo tablespace? Does a tool exist? –  Vincent Malgrat Sep 7 '11 at 16:05
    
The database needs to be in ARCHIVELOG mode to enable FLASHBACK. I don't think the DB is in this mode. If flashback was enabled, I agree this would a very good way to restore the DB at a point-in-time just before the DELETE. –  Vincent Malgrat Sep 7 '11 at 16:17
    
Is that true for "flashback query" as well as "flashback database"? I can't find the docs to support one conclusion or the other, and I can't easily test it myself. –  Jack Douglas Sep 7 '11 at 16:26
    
This seems to suggest otherwise (with caveats and not sure about 9.2) –  Jack Douglas Sep 7 '11 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.