|
Oracle Database 10g. High Availablity with RAC Flashback & Data Guard Authors: Hart M., Jesse S. Published year: 2003 Pages: 25-26/134 |
|
|
< Day Day Up > |
|
The Dropped Table
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.
|
In Oracle Database 10 g , 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.
|
|
|
|
< Day Day Up > |
|
|
|
< Day Day Up > |
|
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.
|
|
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 10 g , 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.
|
|
|
|
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.
|
|
|
|
< Day Day Up > |
|
|
Oracle Database 10g. High Availablity with RAC Flashback & Data Guard Authors: Hart M., Jesse S. Published year: 2003 Pages: 25-26/134 |