Q

Getting back to a particular day's state after committing something incorrectly

Suppose I have commited something that is wrong, and later on I want to go back to that particular time or day's state. How do I do this using the redo logs?

Suppose I have commited something that is wrong, and later on I want to go back to that particular time or day's state. How do I do that?

Your possible solutions to your problem depend on your Oracle version and your configuration.

If you are using Oracle 8i and higher and you have your database configured to archive your online redo logs, then you can "mine" those archived redo logs for the transaction that was committed improperly. You will need to know the approximate time you performed this commit. You then use Oracle's Log Miner utility to search the archived redo log from that time. The V$LOGMINER_CONTENTS view contains the contents of your Log Miner session. Most important to you is the SQL_UNDO column which contains the SQL statements needed to undo or reverse your committed transaction.

If you are using Oracle 10g and you have configured a sufficient enough retention time in your UNDO tablespace, then you can perform a Flashback Table option. Flashback your table to a time before you committed the transaction. You will then be able to query the table's contents as it existed before the commit. Save the table's contents in another table. When you disable the Flashback Table operation, you can use the saved contents to put the data back in the table as required.

This was first published in December 2004

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close