Recovering lost objects using RMAN duplicate database on the same host

Had a requirement today to restore a schema in a database running on windows, without affecting the rest of the database. So incomplete recovery or a flashback database is not an option clearly as the corruption was limited to one schema which has it's own tablespace.


So the options are to either perform a
"Tablespace Point-in-Time Recovery (TSPITR)" 
  or 
"duplicate database to a previous point-in-time" and then compare the objects and import into the production database.


We preferred the option 2 and here's the steps to achieve it:

• create a new instance/service using oradim

          oradim -new -sid dupdb

• create a simple pfile using the basic parameters as below

          control_files='d:\new_path\control01.ctl' ....
          db_name=dupdb
          db_file_name_convert='d:\oradata\origdb','d:\oradata\dupdb'
          log_file_name_convert='d:\oradata\origdb','d:\oradata\dupdb' 
          and the usual cache size settings follow...

• make sure the directories mentioned in the above parameters are created.
• set ORACLE_SID=dupdb

          sqlplus /nolog
          connect / as sysdba
          create spfile='%ORACLE_HOME%/dbs/spfileDUPDB.ora' from                                                                                                                          pfile='d:\new_path\pfile\initdupdb.ora';
          startup nomount pfile='d:\new_path\pfile\initdupdb.ora';
          exit

• Start the rman session as below (if there's no catalog database)

          rman target sys@origdb auxiliary / nocatalog

•  duplicate target database to dupdb until time "to_date('201109151000','YYYYMMDDHH24MI');

           (Skip tablespace option can be used if the db size is large and only selective  tablespaces are required)

• Once the recovery is complete, start the export of the schema using expdp.

Recovery on the Production DB...

• take a RMAN or export backup of the original production database at this stage...
• Drop the schema or drop the objects in the original schema (The below drop & import steps will require a short downtime)

• drop the objects or the schema in the original production db
• import the schema using impdp xxxx
• recompile the objects using utlrp or dbms_utility.compile_schema

And we got the dropped objects back into the DB successfully.