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
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。