Chapter 9: Oracle Flashback: Surviving User-Induced Trauma

---

Overview

Media recovery, as outlined in Chapter 8, provides us with safeguards against all kinds of unforeseeable problems-block corruption, hardware failure, even complete database loss. But what you may have noticed is that Chapter 8 didn't talk at all about the largest cause of media recovery operations: user error. We know. We know. User-induced outages are the most frustrating, because we expect humans to catch their own mistakes, whereas hard drives and motherboards all fail. They just do. But user errors happen, too, and the HADBA must do something about them.

User errors can be roughly defined as errors caused by a human mistake, rather than a software or hardware malfunction: table updated with wrong values, table dropped, table truncated. They are the kinds of errors that, in our honest moments, we realize that everyone makes, but in the heat of an outage, we need to know who did what and just how soon can they be verbally reprimanded by someone important.

Prepared for the Inevitable: Flashback Technology

As an HADBA, though, our energies our best spent preparing for the inevitable user-induced problem. And media recovery should not be our first line of attack; typically, user error is not something that we can recover from because the action is not interpreted as an error by the database. 'Delete * from scott.emp' is not an error; it's a perfectly legitimate DML statement that is duly recorded in the redo stream. So if you restore the datafile and then perform recovery, all you will do is, well, delete * from scott.emp again. Point-in-time recovery can be a solution, but not for the HADBA committed to avoiding full restore of the database-way too much outage. Tablespace-point-in-time-recovery (TSPITR) offered a toned-down version of media recovery for user error, but it still required a full outage on the tablespace, had huge space demands for a temporary clone instance, and didn't work for all objects.

To make up for the frightening lack of options afforded a DBA when faced with user-induced database trauma, Oracle introduces in Oracle Database 10g the concept of Flashback Technology. Flashback Technology refers to a suite of features that allow a multitude of different ways to survive user errors. They have as a unifying concept only the simple idea that user errors occur, and recovering from them should be simple and fast. The Flashback features are

• Flashback Query

• Flashback Table

• Flashback Drop

• Flashback Database

---