In the first in this series of articles on the tools in the Oracle Flashback Technology armoury, I discussed what is available to application developers, the various means by which they can rewind the clock and correct recently-past data errors.  However, if this was a world in which the worst thing that could happen was a small mix-up with data, what a happy world it would be. Unfortunately, this is not Disneyland, and data errors are not the big, bad wolf at the door; it is errors that raze whole database objects that database administrators fear. But fortunately, Oracle Flashback can come to our rescue in those situations too.

First a quick recap: Oracle Flashback Technology is actually a clutch of different Oracle tools that enable developers and administrators reach into the immediate past of their database to recover from errors, without having to resort to point-in-time recovery options. For this reason, they are relatively fast and incur a relatively low logistical overhead (since the database does not need to suffer any downtime to implement them).

FLASHBACK TABLE

Flashback table enables you revert a table to its state at a specified timestamp or SCN. Any rows of data created after the parameterized timestamp disappear and any deleted rows zombie back to existence; in addition, associated attributes such as triggers, indexes and constraints remain unviolated. And all this magic occurs without even the slightest blip of disruption to the rest of the database; it stays up and available.

Flashback table is able to sidestep the unpleasantness of restoring from backups by using information in the undo tablespace to restore the table.

Imagine this scenario: some catastrophe has befallen the EMP table and the developers are unable to unpick the data. All eyes turn to you: Save us, they beseech, wave your wand and erase the past hour. This is where you would find Flashback Table useful:

FLASHBACK TABLE EMP TO SYSTIMESTAMP – INTERVAL '60' MINUTE;

And it’s done. Day saved. Simple.

To use flashback table, however, there are a few prerequisites that need to be in place:

  • You must have the FLASHBACK privilege on the table. Or you must have the FLASHBACK ANY TABLE system privilege.
  • You must have the SELECT, INSERT, DELETE and ALTER privileges on the table.
  • Row movement must be enabled on the table.
  • Finally – and crucially – the information in the undo tablespace must extend back far enough to cover your timestamp or SCN.

The default behaviour of the flashback table functionality is to automatically disable any related triggers before carrying out the operation; it then re-enables them as its last action.  The thinking behind this is sensible: you will want to have complete control over what changes are effected; you do not want triggers firing invisibly in all directions like unstable plutonium. However, you do have the option to override this default setting, as in the following statement:

FLASHBACK TABLE EMP TO SYSTIMESTAMP – INTERVAL '60' MINUTE ENABLE TRIGGERS;

FLASHBACK DROP

Oracle Flashback Drop is functionality that empowers database administrators to resurrect accidentally dropped tables.  In other words, it is pretty much Ctrl-Z for the DROP TABLE operation.

Despite the apparent similarities between Flashback Drop and Flashback Table, these functionalities actually sprout from different parts of the database.  Unlike Flashback Table, which is based on the undo tablespace, Flashback Drop is based on the recycle bin.  However, one thing both operations have in common – and indeed it is a feature that runs through all members of the flashback family – is that they can be deployed without the database needing to be taken offline and with no necessary effect on any other database object.

Since 10g, Oracle databases have come with a recycle bin. And what happens when a table is dropped is that that table is actually renamed and moved, along with any dependent objects – indexes, triggers and such – to a recycle bin. (The names objects are given in the recycle bin look very much like gobbledygook; they are, however, the characters BIN$ concatenated with a guid.)  These objects proceed to sit in the recycle bin until they are purged, at which point they are permanently deleted.  The recycle bin can be purged by the explicit actions of a database administrator or it may be eventually purged by the database as a space-saving mechanism.

It is the recycle bin functionality that Flashback Drop exploits to bring dropped tables back from the dead. When the command is executed, it reaches into the trash, gets the table (and associated objects), dusts it down, changes its name from its BIN$ recycle bin name and gives it its original name back. The syntax is as follows:

FLASHBACK TABLE EMP TO BEFORE DROP;

If you would rather use the table’s recycle bin name (you can find this out by querying the object_name column in RECYCLEBIN, filtering by the original_name column) instead of its pre-drop name, you can do so. However, you should enclose it in double quotes to cater for the special characters.  If, for whatever reason, you decide that you would rather not restore the table to its original pre-drop name, you can flashback and rename the table in one breath:

FLASHBACK TABLE "BIN$Ik84R6rKEj7gRAgAIMR0GQ==$0" TO BEFORE DROP RENAME TO EMP_RECOVERED;

To use Flashback Drop you must have the DROP privilege for that object. You should also bear in mind that this functionality is only available for as long as the table resides in the recycle bin; if it is purged, either by an administrator or by the database, you will no longer be able to recover it. Finally, you can only use Flashback Drop with locally-managed, non-system tablespaces.

CONCLUSION

Flashback Table and Flashback Drop are two of the brighter stars in the flashback constellation, and have saved many an administrator’s hide. However, when your database is faced with an even greater snafu, Flashback has an even bigger gun that it can call on: Flashback Database.  And it is this functionality that I will discuss in the 3rd and final article in this series.