How do I recover deleted data without writing over current data?
I am trying to restore a database where data was inadvertently deleted. I am recovering the deleted data from a backup. My question is how can I merge the two together without writing over my current data?
Depending on your backup and recovery strategy, you may have some data loss here. You can perform an incomplete...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.