This can be used to recover the data in individual tables, possibly after a table has been dropped, for example. The tablespace containing the table is restored to an auxiliary instance, after which the data can be manually copied back to the original instance.

Note: Although the tablespace point in time recovery (TSPITR) initially restores the tablespace to an auxiliary instance, it also recovers the tablespace by transporting it back to the original instance.

1. Log onto the system using the ID for starting the Oracle database.
2. Shut down the auxiliary instance: export ORACLE_SID=auxsvc​
sqlplus 'username/password as sysdba'
show parameter db_name
shutdown immediate

3. The control file of the auxilary instance will be replaced. If the instance will need to be restored back to its original state then make a copy of the existing control file before starting the restore.
4. Start up the instance in nomount mode: startup nomount
exit

5. Generate a list of tablespaces to be skipped: sqlplus username/password@tspsid​
set pagesize 50000
select tablespace_name || ',' from dba_tablespaces where contents not in ('TEMPORARY','UNDO') and tablespace_name not in ('SYSTEM','tspname',...) order by 1
where tspsid is the instance containing the tablespace to be restored. Include a list of the tablespaces to be restored in the second in clause. If there are indexes in separate tablespaces, they should also be restored; otherwise the primary key constraints and/or other indexes on the tables in the tablespaces that are restored will need to be dropped in order to access the data in the tables.
6. Get the file IDs and file names of the data files to be restored: select file_id, file_name from dba_data_files where tablespace_name in ('tspname',...);
Change the file name to avoid overwriting any existing files.
7. Determine the number of groups of redo logs and the number of copies of each.
8. Start RMAN, connecting to the target, catalog and auxiliary instances. rman target sys/password@tspsid catalog rmanid/rmanpass@catsid auxiliary sys/auxpass[@auxsid]

9. Resyncronize the catalog if necessary, to include any new archive logs: list archivelog all;
resync catalog;

10. Execute the following RMAN command to restore the tablespace to the auxiliary instance: run {
allocate auxiliary channel 'tape1' device type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_tspsid.opt)';
set until time "to_date('mm/dd/yyyy hh:mm:ss','mm/dd/yyyy hh24:mi:ss')";
set newname for datafile 1 TO '/u##/oradata/auxsid/systspsid01.dbf';
set newname for datafile 2 TO '/u##/oradata/auxsid/undotspsid01.dbf';
set newname for datafile fileid TO '/u##/oradata/auxsid/tspsid filename';
duplicate target database to auxsid​
skip tablespace
list of tablespaces to be skipped​
logfile
group 1 ('/u01/oradata/auxsid/redotspsid01a.log',
'/u02/oradata/auxsid/redotspsid01b.log') size 10M reuse,
group 2 ('/u01/oradata/auxsid/redotspsid02a.log',
'/u02/oradata/auxsid/redotspsid02b.log') size 10M reuse,
group 3 ('/u01/oradata/auxsid/redotspsid03a.log',
'/u02/oradata/auxsid/redotspsid03b.log') size 10M reuse;
}

11. Wait for the restore and recovery to complete, then exit RMAN: Starting restore at ...
channel tape1: starting datafile backupset restore
channel tape1: restore complete
Finished restore at ...
Starting recover at ...
starting media recovery
media recovery complete
Finished recover at ...
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
exit
Recovery Manager complete.

12. If the restore fails after media recovery, it may be possible to continue without having to restore again. For example: sql statement: drop tablespace XDB including contents
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/15/2007 22:24:22
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/15/2007 22:24:22
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace XDB including contents
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
export ORACLE_SID=auxsvc​
sqlplus 'username/password as sysdba'
set pagesize 50000
select name, open_mode from v$database;
column name format a60
select status, name, enabled from v$datafile;

13. If any required indexes were not restored, an error will occur when attempting to access the data in the restored table. Those indexes will need to be dropped. ERROR at line 1:
ORA-00376: file ## cannot be read at this time
ORA-01111: name for data file ## is unknown - rename to correct file
ORA-01110: data file ##: '/u01/app/oracle/product/version/dbs/MISSING000##'
alter table owner.table_name drop constraint constraint_name;

14. To keep the restored data in the auxiliary instance, reset the database to indicate that a RESETLOGS has been done: export ORACLE_SID=auxsvc​
rman target sys/password[@auxsid] catalog rmanid/rmanpass@catsid
reset database;
To return to the original data in the auxiliary instance, shut down the instance: sqlplus 'username/password as sysdba'
show parameter db_name
shutdown immediate
exit
Rename the control files back, then start up the instance in nomount mode: sqlplus 'username/password as sysdba'
startup nomount
exit
Reset the database incarnation to the one that matches the restored control files: rman target sys/password[@auxsid] catalog rmanid/rmanpass@catsid
list incarnation of database auxsid;
reset database to incarnation inckey​
exit