Database Reference
In-Depth Information
Providing that RMAN backups are available that contain the state of the table at the specified SCN, a table-level
restore and recovery is performed.
■
Note
you can also restore a table to an sCN, a point in time, or a log sequence number.
When RMAN performs a table-level recovery, it automatically creates a temporary auxiliary database, uses Data
Pump to export the table, and then imports the table back into the target database as it was at the specified restore
point. After the restore is finished, the auxiliary database is dropped, and Data Pump dump file is removed.
■
although the
RECOVER TABLE
command is a nice enhancement, I would recommend that, if you have an
accidentally dropped table, you first explore using the Flashback table to Before drop feature to restore the table.
or, if the table was erroneously deleted from, then use the Flashback table feature to restore the table back to a point in
time in the past. If neither of the prior options are viable, then consider using the rMaN recover table feature.
Tip
Flashing Back a Table
Prior to Oracle 10g, if a table was accidentally dropped, you had to do the following to restore the table:
1.
Restore a backup of the database to a test database.
2.
Perform an incomplete recovery up to the point in time at which the table was dropped.
3.
Export the table.
4.
Import the table into the production database.
This process can be time-consuming and resource intensive. It requires extra server resources as well as time and
effort from a DBA.
To simplify recovery of an accidentally dropped table, Oracle introduced the Flashback Table feature. Oracle
offers two different types of Flashback Table operations:
•
FLASHBACK TABLE TO BEFORE DROP
quickly undrops a previously dropped table. This feature
uses a logical container named the recycle bin.
•
FLASHBACK TABLE
flashes back to a recent point in time to undo the effects of undesired DML
statements. You can flash back to an SCN, a timestamp, or a restore point.
Oracle introduced
FLASHBACK TABLE TO BEFORE DROP
to allow you to quickly recover a dropped table. As of
Oracle 10g, when you drop a table, if you don't specify the
PURGE
clause, Oracle doesn't drop the table—instead, the
table is renamed. Any tables you drop (that Oracle renames) are placed in the recycle bin. The recycle bin provides
you with an efficient way to view and manage dropped objects.
■
to use the Flashback table feature, you don't need to implement an Fra, nor do you need Flashback database
to be enabled.
Note
