Depending on your backup and recovery strategy, you may have some data loss here. You can perform an incomplete...
recovery or even a Tablespace Point In Time Recovery (TSPITR). With incomplete recovery, you specify the point in time you recover to, but you will lose all transactions after that point. With TSPITR, you will recover a specific tablespace to a point in time, but you will lose all transactions in that tablespace after that point. So without knowing more about your current backup strategy, I am assuming that a recovery from the backup will lose some data changes that occurred after the accidental deletion.
There are some options that may be available to you. In Oracle 10g, you can specify a Flashback Area and you can flashback the database to right before the delete operation was performed. You can then query for the deleted data, save that data in a cursor, and then revert back to the current point in time. This lets you recover that deleted data. Flashback queries in some form have been around since Oracle 9i, but Oracle 10g greatly enhances the flashback functionality. I would suggest that this option is not available to you as your flashback data may have been overwritten in the time it took me to get a response back to you.
Another option is to mine your redo logs for the UNDO statements to your DELETE statements using Log Miner. Since you were talking about rolling forward (at least I'm assuming that's what you were talking about) then you can mine your archived redo logs for the Undo statements to your DELETE statements. You can save these Undo statements in a SQL script and then run the script to perform the INSERT operations to undo your DELETE operations. Chapter 17 of the Oracle 10gR2 Utilities guide contains the instructions for using the Log Miner. You can find a copy of this documentation on Technet. The site is free but you will need to register for an account. The direct link to Chapter 17 is here.
Tell Donna I said "Hi!!!" and if you want to email me directly, she has my email address.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.