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 that?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top