Like an incorrect update to the table, an inadvertently dropped table can be catastrophic. Unlike an inadvertent DML statement (insert, update, or delete), a drop cannot be explored and fixed manually. Once dropped, the table must be restored from a backup of some sort.

The DBA at Horatio's Woodscrews did this one to himself: he was trying to clean up unused objects in the production database. There were some leftover tables that had been used for testing purposes in a now-abandoned user's schema. The DBA was reviewing the objects and then dropping them to free up the extents. However, for the Woodscrew table, he put the wrong username in the DROP statement by accident, and he knew it immediately: ws_app.woodscrew had just been dropped.

Use Flashback Drop to Restore Dropped Objects In Oracle Database 10g, as part of the Flashback Technologies, Oracle introduced Flashback Drop. Now, when an object is dropped, it is placed in a Recycle Bin, where it is stored until there is space pressure in the tablespace. Until it ages out, the object can be “undropped” in only a few moments. For more information, see Chapter 9.

The Truncated Table

Another deadly user error can be the use of TRUNCATE to remove rows of an incorrect table. With no undo generated, a truncate is permanent in a way that even a Flashback Transaction or LogMiner operation cannot assist with. Once truncated, the data is missing, and nothing can be done except to restore from a backup and then cancel recovery prior to the truncate.

The DBA was still trying to figure out how to restore the Woodscrew table when he was interrupted by a page. Someone in the Sales group, impatient because their cluster was down, had logged into the production database with a borrowed password. The salesperson had been trying to delete rows from a small sales table, but it was taking too long so he used TRUNCATE. But the production table he truncated had more than just rows for his region, and suddenly the woodscrew_inventory table was empty:

select count(*) from WS_APP.WOODSCREW_INVENTORY;
no rows selected.

When There Is an Unrecoverable Operation, Use Flashback Database A TRUNCATE operation can be deadly to a database, as it is not a DML operation that gets a “before image” stored in the undo segments. A Flashback Table won't be of any use. Typically, a truncate done in error requires a point-in-time recovery. In Oracle Database 10g, we can use the Flashback Database, which quickly rewinds the database back in time in a manner that does not require a media restore operation—so no waiting for all the files to come from tape. Our flashback can occur in minutes, instead of hours. See Chapter 9 for more information.

Use Oracle Streams to Replicate Data to Unique Databases on Different Operating Systems If you've researched Oracle Advanced Replication in the past, you may have discovered that it provides a good way to share data among multiple, independent databases. But the performance could slow data processing to some degree. With Oracle Streams, replication has improved its speed dramatically and provides a way to integrate a heterogeneous OS environment into a shared data/high-availability model. For more on Streams, see Chapter 10.

Connecting Online, Identical Databases

The DBA at Horatio's Woodscrews finally found the time to move the production data from the Solaris production database to the Linux servers run by the Test and Development team. The development lead was very excited about the performance they were getting from the Linux boxes, and the application code ported with very little trouble.

In a few weeks, however, the Solaris box was simply overworked. New orders were coming in faster than ever, and the new warehouses were coming online in four new distribution areas. With all the new data, the database began to bog down. The Chief Information Officer approached the DBA and asked a simple question: how can we leverage the Linux servers for our production system? Can we start connecting the Order Entry group to the Linux database, for instance, and just keep the internal groups running against the Solaris system?

This chapter is from Oracle Database 10g High Availability with RAC, Flashback & Data Guard, by Hart and Jesse. (McGraw-Hill/Osborne, 2004, ISBN: 0072254289). Check it out at your favorite bookstore today. Buy this book now.