Recovering From a Table DROP With a Backup that Contains the Lost Table
The following steps are recommended by Oracle:
If possible, keep the database that experienced the user error online and available for use. Back up all data files of the existing database in case an error is made during the remaining steps of this procedure.
Restore a database backup to an alternate location, then perform incomplete recovery of this backup using a restored backup control file, to the point just before the table was dropped.
Export the lost data from the temporary, restored version of the database using an Oracle export utility. In this case, export the accidentally dropped table.
Use an Oracle import utility to import the data back into the production database.
Delete the files of the temporary copy of the database to conserve space.
There are two possible sub-cases to consider:
The dropped table is present in the latest backup.
The dropped table is created after the last backup. See Example Disaster Recovery Scenarios about Oracle Databases.
In the following examples, assume that the production machine is named primary and the name of the dropped table is demo1. The following steps simulate the problem by dropping the table and then restoring it.
The dropped table is present in the latest backup:
Backup the database using at this time. After the backup completes, drop the table to simulate the problem.
Perform an Oracle clone restore on an alternate machine.
When performing an Oracle clone restore, ensure that the target node does not have the same logical mount points as the source database. The restore process and Oracle startup require that the recovered data be available at the same file system locations that were backed up on the source server. The Instant Access restore process automatically creates the appropriate mount points. For example, if the source database has files hosted on mount points /ora
or E:
, ensure these mount points are not present on the target node so that may use them.
On the alternate machine, export the table as follows:
At this point, the dropped table is back in the primary’s database.
Last updated