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
- create a simple pfile using the basic parameters as below
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
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)
- duplicate target database to dupdb until time "to_date('201109151000','YYYYMMDDHH24MI');
- Once the recovery is complete, start the export of the schema using expdp.
- 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
