ORACLE异机表空间基于时间点恢复 (TSPITR) Method(恢复部分表空间)

RMAN Tablespace Point-In-Time Recovery ( TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.Prior to 11.2 version the TSPITR had a restriction of not being able to recover the dropped tablespace.From 11.2 this limitation no longer exists.RMAN TSPITR requires that the tablespace be self-contained and that no SYS -owned objects reside in the tablespace.
To perform TSPITR, RMAN requires the database to be in archivelog mode and existing backup of database that was created before the point in time recovery, and all the archivelogs and online logs created from the time of the backup until the point in time recovery . Also the controlfile recovery (from autobackup) must matches with the database physical schema at the point in time when recovery ends.
RMAN TSPITR is most useful for recovery the following situations: DDL\\DML corrupt the data in only one or a few tablespace, droped table purge or droped tablespace etc..
Automatic or Manual Model
Automatic Instance Creation for RMAN TSPITR :
oracle automatic Restores the control file and data files from the recovery set and the auxiliary set to the auxiliary instance. The RMAN job restores the auxiliary and recovery sets. Then, it recovers the clone database to the specified point in time. RMAN opens the clone database with resetlogs, and it performs an export of the tablespace(s) to be recovered. Finally, it imports an export dump file into target database completing recovery process, Deletes all auxiliary set files. Automatic mode TSPITR Here I is not to demo ,just using the following command

RECOVER TABLESPACE users 
  UNTIL LOGSEQ 2400 THREAD 1
  AUXILIARY DESTINATION '/u01/oradata/auxdest';
复制

Manual Instance Creation for RMAN TSPITR:
The below example is an attempt to show the steps and functioning of TSPITR to recover a tablespace to another ORACLE DB SERVER(oracle software version on tow host must be same).my case db version 11.2.0.3.
scp Rman backup files from source to target db path '/backup/db40'.
# on target db

# change ORACLE_SID 
export ORACLE_SID=pora40
# create a pfile
vi pfile
DB_NAME=pora40
DB_UNIQUE_NAME=pora40
CONTROL_FILES=/oradata/db40/control01.ctl
log_archive_dest_1='location=/oradata/db40/'
DB_FILE_NAME_CONVERT=('/oracle/oradata/','/oradata/db40/')
LOG_FILE_NAME_CONVERT=('/oracle/oradata/','/oradata/db40/')
REMOTE_LOGIN_PASSWORDFILE=exclusive
COMPATIBLE =11.2.0
DB_BLOCK_SIZE=8192
RMAN> startup nomount
RMAN> restore controlfile from '/backup/db40/pora40_ctrl_20140521_3178_bak';
RMAN> alter database mount;
RMAN> catalog start with '/backup/db40';
RMAN> run
{
 SET NEWNAME FOR DATABASE to '/oradata/db40/%b';
 SET NEWNAME FOR TEMPFILE 1 TO '/oradata/db40/temp01.dbf' ;
 SET NEWNAME FOR TEMPFILE 2 TO '/oradata/db40/temp02.dbf' ;
 SET UNTIL TIME 'May 20 2014 14:00:00';
 restore tablespace system,sysaux,undotbs1,users;
 SWITCH DATAFILE 1;
 SWITCH DATAFILE 2;
 SWITCH DATAFILE 3;
 SWITCH DATAFILE 4;
 SWITCH DATAFILE 5;
 SWITCH DATAFILE 12;
 switch tempfile all;
 recover  tablespace system,sysaux,undotbs1,users;
}
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/23/2014 11:12:53
RMAN-06067: RECOVER DATABASE required with a backup or created control file
复制

Note:
Oracle Error: RMAN-06067
Error Description:
RECOVER DATABASE required with a backup or created control file
Error Cause:
The control file has been restored from a backup or was created via ALTER DATABASE CREATE CONTROLFILE.
Action:
Use the RECOVER DATABASE command to perform the recovery.

sys@PORA40>select wm_concat(name) from v$tablespace;
SYSTEM,SYSAUX,UNDOTBS1,USERS,TEMP,XMSB,ZYY,IPTV,MEDICAL,GPPX,JSPX,SCZY,XY_HENAN,
XIANGYI,CMS,PX_PLATFORM
RMAN> run
{
 SET NEWNAME FOR DATABASE to '/oradata/db40/%b';
 SET NEWNAME FOR TEMPFILE 1 TO '/oradata/db40/temp01.dbf' ;
 SET NEWNAME FOR TEMPFILE 2 TO '/oradata/db40/temp02.dbf' ;
 SET UNTIL TIME 'May 20 2014 14:00:00';
 restore tablespace system,sysaux,undotbs1,users;
 SWITCH DATAFILE 1;
 SWITCH DATAFILE 2;
 SWITCH DATAFILE 3;
 SWITCH DATAFILE 4;
 SWITCH DATAFILE 5;
 SWITCH DATAFILE 12;
 switch tempfile all;
 recover database skip tablespace  XMSB,ZYY,IPTV,MEDICAL,GPPX,JSPX,SCZY,XY_HENAN,XIANGYI,CMS,PX_PLATFORM;
}
... output truncated
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Executing: alter database datafile 6 offline
Executing: alter database datafile 7 offline
Executing: alter database datafile 8 offline
Executing: alter database datafile 9 offline
Executing: alter database datafile 10 offline
Executing: alter database datafile 11 offline
Executing: alter database datafile 13 offline
Executing: alter database datafile 14 offline
Executing: alter database datafile 15 offline
Executing: alter database datafile 16 offline
Executing: alter database datafile 17 offline
Executing: alter database datafile 18 offline
Executing: alter database datafile 19 offline
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8865
...
RMAN> alter database open resetlogs;
database opened
RMAN> report schema;
Report of database schema for database with db_unique_name PORA40
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    15360    SYSTEM               ***     /oradata/db40/system01.dbf
2    2040     SYSAUX               ***     /oradata/db40/sysaux01.dbf
3    195      UNDOTBS1             ***     /oradata/db40/undotbs01.dbf
4    6601     USERS                ***     /oradata/db40/users01.dbf
5    94       UNDOTBS1             ***     /oradata/db40/undotbs02.dbf
6    0        XMSB                 ***     /oradata/pora40/datafile/xmsb01.dbf
7    0        XMSB                 ***     /oradata/pora40/datafile/xmsb02.dbf
8    0        ZYY                  ***     /oradata/pora40/datafile/zyy01.dbf
9    0        IPTV                 ***     /oradata/pora40/datafile/iptv01.dbf
10   0        MEDICAL              ***     /oradata/pora40/datafile/medical01.dbf
11   0        MEDICAL              ***     /oradata/pora40/datafile/medical02.dbf
12   3704     USERS                ***     /oradata/db40/users02.dbf
13   0        GPPX                 ***     /oradata/pora40/datafile/gppx.dbf
14   0        JSPX                 ***     /oradata/pora40/datafile/jspx01.dbf
15   0        SCZY                 ***     /oradata/pora40/datafile/sczy01.dbf
16   0        XY_HENAN             ***     /oradata/pora40/datafile/xy_henan01.dbf
17   0        XIANGYI              ***     /oradata/pora40/datafile/xiangyi01.dbf
18   0        CMS                  ***     /oradata/pora40/datafile/cms01.dbf
19   0        PX_PLATFORM          ***     /oradata/pora40/datafile/px_platform01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /oradata/db40/temp01.dbf
2    2        TEMP                 32767       /oradata/db40/temp02.dbf
复制

# check physical datafile of the new instance

$ ll /oradata/db40/
total 28709184
-rw-r--r-- 1 root   root               0 May 22 18:49 0
-rw-r----- 1 oracle oinstall    12664832 May 23 11:51 control01.ctl
-rw-r----- 1 oracle oinstall  2139103232 May 23 11:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 16106135552 May 23 11:49 system01.dbf
-rw-r----- 1 oracle oinstall    20979712 May 23 11:44 temp01.dbf
-rw-r----- 1 oracle oinstall     2105344 May 23 11:47 temp02.dbf
-rw-r----- 1 oracle oinstall   204480512 May 23 11:50 undotbs01.dbf
-rw-r----- 1 oracle oinstall    98574336 May 23 11:49 undotbs02.dbf
-rw-r----- 1 oracle oinstall  6921920512 May 23 11:44 users01.dbf
-rw-r----- 1 oracle oinstall  3884457984 May 23 11:44 users02.dbf
复制

Tip:
You can also use Flashback Database to rewind data, but you must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to your earliest recoverable backup.
References ORACLE DOC