Databases Reference
In-Depth Information
■
the entire database is rolled back during flashback database; you can't perform flashback on individual tables
or tablespaces.
Note
Undropping a Table
This has happened to the best of us—you dropped a very important table. What can you do? In versions prior to
Oracle Database 10g Release 1, there wasn't any choice other than to restore the backup of the corresponding
tablespace to another database, recover the tablespace, export the table, and import the table to the production
database. These tasks are time-consuming and risky, and the table is unavailable throughout them.
Starting with Oracle Database 10g, the process is less threatening. When the table is dropped, it's not really erased
from the database; rather, it is renamed and placed in a logical container called the recycle bin, similar to the Recycle
Bin found in Windows. After you realize the mistake, you can reinstate the dropped table using only one simple
command. Who says you can't revive the dead?
Flashing Back a Table
The Oracle9i Database introduced a feature called flashback query. When the data in the database changes,
the past images of the changed data are stored in special segments called undo segments. The reason for storing this
data is simple—if the changed data is not committed yet, the database must reconstruct the prechange data to present
a read-consistent view to the other users selecting the same data item. When the change is committed, the need for
the past image is gone, but it's not discarded. The space is reused if necessary. The reason for keeping it around is
simple, too.
The read-consistency requirement does not stop after the data changes are committed. For instance,
a long-running query needs a read-consistent view when the query started, which could be well in the past. If the
query refers to the data that might have been changed and committed in the recent past, after the query has started,
the query must get the past image, not the image now, even though it is committed. If the query finds that the past
data is no longer available, it throws the dreaded
ORA-1555 Snapshot Too Old
error.
Anyway, what does the
ORA-1555
error have to do with flashback operation? Plenty. Since the past image of the data
is available in the undo segment for a while, why should a long-running query be the only one to have fun? Any query
can benefit from that past image, too. That thought gave rise to flashback queries in Oracle9i Database where you could
query data as of a time in the past. With Oracle Database 10g, that functionality was made richer with flashback version
queries, where you can pull the changes made to the row data from the undo segments, as long as they are available in
the undo segments, of course. And when you pull the older versions of the table, you can effectively reinstate the entire
table to a point in time in the past using these past images. This is known as flashing back the table.
Recovering a Table
Suppose you have dropped a table. Now it is gone from the recycle bin, so you can't get it out from there using the
undrop process mentioned earlier in this section. The only option is to get it from the backup. However, database
recovery recovers the entire database from backup and will overwrite
all
data as of that point in time in the past. When
all you want is to recover a single table from the backup, you can't just surgically extract the table from the backup.
Instead, you have to create a temporary instance and recover the tablespace containing the table to the very point just
prior to the time the table was dropped. However, you need to restore and recover some other mandatory tablespaces,
such as system, sysaux, and one or more undo tablespaces. Once they are restored, you will need to recover that
temporary database to the required time, extract the table using Data Pump, and import it into the main database.
All this is a lot of work.
