Recovering From a Table DROP With a Backup that Contains the Lost Table

The following steps are recommended by Oracle:

1. 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.

2. 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.

3. 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.

4. Use an Oracle import utility to import the data back into the production database.

5. 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:

SQL> select * from demo1;

ID

----------

1

2

3

Backup the database using at this time. After the backup completes, drop the table to simulate the problem.

SQL> drop table demo1;

Table dropped.

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:

alternate$ exp file=demo1.dmp tables=SYS.DEMO1
Export: Release 11.1.0.6.0 - Production on Thu Nov 6 12:07:09 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                          DEMO1          3 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
Copy the file demo1.dmp from alternate to primary and then import it.
primary$ imp file=demo1.dmp tables=DEMO1
Import: Release 11.1.0.6.0 - Production on Thu Nov 6 12:52:44 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.01.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table                        "DEMO1"          3 rows imported
Import terminated successfully without warnings.

At this point, the dropped table is back in the primary’s database.