问题描述
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.