Recovering From a Table DROP Performed After the Last Backup

Note that the above example assumes that a backup containing the table that needs to be restored exists. However, if the dropped table is created after the last backup, it is not simply enough to perform a clone restore. This is because clone restore only recovers the database to the point of the backup. Since the table is not present at backup time, it cannot be export from the cloned copy. Further recover the cloned instance up to the point just before the table is dropped. Once complete, the remaining export and import steps can be performed. The following section shows how to perform such additional recovery.

Set the job option Oracle Database Open Options to Do not Open and run a clone restore. This creates a cloned instance of the backup on the alternate node and only mounts the database at the end.

Copy the archive logs generated after the backup from primary to alternate. The job log for the clone restore shows the SCN until which recovery is done. For example:

SQL> alter database recover automatic from 'Y:\Backup Express\tmp\
1226605514__ORACLE_10g_orcl1' using backup controlfile until
change 30041877;

As per this message, the cloned instance is recovered until SCN 30041877. Copy the archive logs starting from this SCN.

SQL> select name, first_change# 
  from  v$archived_log 
  where first_change# >= 30041877;
NAME                                       FIRST_CHANGE#
------------------------------------------ -------------
H:\ORCL1_ARCLOC1\ARC01173_0606494782.001        30041877
H:\ORCL1_ARCLOC1\ARC01174_0606494782.001        30043962
H:\ORCL1_ARCLOC1\ARC01175_0606494782.001        30044713
H:\ORCL1_ARCLOC1\ARC01176_0606494782.001        30053589
H:\ORCL1_ARCLOC1\ARC01177_0606494782.001        30053891

Assume that these logs are copied to C:\alogs on alternate. Then recover the database until the time just before the table was dropped. The following example recovers and opens the database:

SQL> alter database recover automatic from 'C:\alogs' using backup controlfile until time '2008-11-13:14:42:00';

SQL> alter database open read only;

At this point, the dropped table should be present in the cloned instance. It can be exported and then imported on the primary as in the above scenario.