Rewinding the Database

---

Perhaps the most difficult outage situations are those tricky logical errors introduced by the users themselves-when a user updates the wrong table, or updates the wrong values. These types of errors are tough to overcome because they are not perceived by the database as errors, but just another transaction. Typically, user errors do not occur in a vacuum; an erroneous update can occur alongside hundreds of correct updates. Pretty soon, the bad data is buried by thousands of additional updates. How can you fish just one of those transactions out? Can you 'rewind' the database back to a previous point in time?

Use Flashback Table to Restore a Table to a Previous State

In Oracle Database 10g, Oracle introduced the ability to rewind a table to a previous state without performing point-in-time recovery. This is called Flashback Table, and it's part of the Flashback Technologies discussed in Chapter 9.

You can also use LogMiner to review transactions in the archived redo logs in order to determine where exactly the bad data was entered, as well as to retrieve the good transactions entered after the bad transaction. LogMiner is discussed in Chapter 2.

At Horatio's Woodscrews, the problem was reported by the Accounting team. As they went through their month-end processing, they began to realize that the data seemed incorrect. All the roll-up values seemed to be impossibly high, even during a good month. They could not figure out what had happened, but the data had been incorrectly entered at some point in the morning. Now, in the afternoon, they came to the DBA and asked that he 'start over' all of their tables as they had looked in the morning.

The options for restoring just a few tables are limited, without rolling the entire database back to a previous point-in-time. Oracle has provided for a tablespace point-in-time recovery (TSPITR), where just a single tablespace is restored to a previous point. But that is labor-intensive, and the smallest unit that can be restored is the entire tablespace. The Accounting group does not want to redo all their work for the day, just the work in a few tables.

---