Q
Problem solve Get help with specific problems with your technologies, process and projects.

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?

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? I'm using an Oracle10g database. Thanks for your time and help. (Donna Bonderud says hi.)

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close