Recovering drop table using RMAN

Hi,
The best method to recover the drop table is as given below:

Steps for Option 3 from MY Yodlee inteview questions:

1. To recover from a dropped or truncated table, a dummy database (copy of primary) will be restored and recovered to point in time so the table can be exported.

2. Once the table export is complete, the table can be imported into the primary database. This dummy database can be a subset of the primary database. However,the ‘dummy’ database must include the SYSTEM, UNDO (or ROLLBACK), and the tablespace(s) where the dropped/truncated table resides.

The simpliest method to create this ‘dummy’ database is to use the RMAN duplicate command.

RMAN Duplicate Command

CONNECT TARGET SYS/oracle@trgt
CONNECT AUXILIARY SYS/oracle@dupdb

DUPLICATE TARGET DATABASE TO dupdb
NOFILENAMECHECK UNTIL TIME ‘SYSDATE-7′;

Assuming the following

•The target database trgt and duplicate database dupdb are on different hosts but have exactly the same directory structure.

•You want to name the duplicate database files the same as the target files.

•You are not using a recovery catalog.

•You are using automatic channels for disk and sbt, which are already configured.

•You want to recover the duplicate database to one week ago in order to view the data in prod1 as it appeared at that time (and you have the required backups and logs to recover the duplicate to that point in time).



Hope it helps.

Best regards,

Rafi.