使用自定义自动辅助实例执行TSPITR
1.重命名或重新放置恢复集的数据文件，因此在TSPITR执行完成后，被恢复表空间所包含的数据文件将不会存储在其原始位置（例如当包含表空间的原始磁盘不可使用时）。
2.对一些或所有的辅助数据文件指定辅助目录之外的目录。如果在单个磁盘上没有足够的空间来处理所有辅助集文件时可以这样做。
3.提前设置镜像副本备份数据文件，来避免从备份中还原而提高TSPITR的速度。
4.对辅助实例使用不同的通道配置
5.为RMAN管理的辅助实例指定不同的初始化参数
使用set newname来重命名TSPITR恢复集中的数据文件
如果不想恢复集中的数据文件被还原和恢复到它们原来的目录中，在RUN块中使用set newname命令来为从备份中还原和恢复的数据文件指定新的存储目录。
例如下面我们将为恢复集中表空间tspitr的数据文件‘/u01/app/oracle/oradata/test/tspitr01.dbf’重命名为
’ /u01/app/oracle/oradata/auxiliary/tspitr01.dbf’
再次执行truncate table tspitr操作:
SQL> truncate table tspitr;
Table truncated.
SQL> select count(*) from tspitr;
COUNT(*)
----------
0
下面再次将表空间tspitr恢复到logseq=32的时间点，我这里因为使用了恢复目录，在第一次执行tspitr后，可以再次恢复到logseq=32这个时间点。
RMAN> run
2> {
3> set newname for datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' to ' /u01/app/oracle/oradata/auxiliary/tspitr01.dbf';
4> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary';
5> }
executing command: SET NEWNAME
Starting recover at 2015-03-16 17:42:10
using channel ORA_DISK_1
Creating automatic instance, with SID='pjzc'
initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_pjzc
sga_target=180M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/oradata/auxiliary
control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pjzc.f
starting up automatic instance TEST
Oracle instance started
Total System Global Area 188743680 bytes
Fixed Size 1272720 bytes
Variable Size 62915696 bytes
Database Buffers 121634816 bytes
Redo Buffers 2920448 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until logseq 32 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script
executing command: SET until clause
Starting restore at 2015-03-16 17:42:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=47 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pjzc.f
Finished restore at 2015-03-16 17:42:15
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
starting full resync of recovery catalog
full resync complete
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until logseq 32 thread 1;
plsql < <<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TSPITR' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 6 to
"/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TSPITR offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2015-03-16 17:42:23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 2015-03-16 17:43:21
datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=874518201 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_system_bjf9d12k_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=874518201 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_undotbs1_bjf9d14c_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 6 online
Starting recover at 2015-03-16 17:43:22
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=30
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874518204
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:02
Finished recover at 2015-03-16 17:43:27
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclepjzc\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=pjzc^'\)\)\(CONNECT_DATA=\(SID=pjzc\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script
Export: Release 10.2.0.5.0 - Production on Mon Mar 16 17:43:51 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.5.0 - Production on Mon Mar 16 17:44:12 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace TSPITR online
sql statement: alter tablespace TSPITR offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
starting full resync of recovery catalog
full resync complete
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pjzc.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_system_bjf9d12k_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_undotbs1_bjf9d14c_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_temp_bjf9gdqh_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/onlinelog/o1_mf_1_bjf9fz9y_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/onlinelog/o1_mf_2_bjf9g25c_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/onlinelog/o1_mf_3_bjf9g4yj_.log deleted
Finished recover at 2015-03-16 17:44:21
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TSPITR OFFLINE
7 rows selected.
将表空间tspitr联机
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TSPITR ONLINE
7 rows selected.
SQL> select count(*) from tspitr;
COUNT(*)
----------
50678
如果set newname命令指定的数据文件名与目标数据库中的有效数据文件名存在冲突，那么RMAN在执行recover命令时没返回错误信息。有效的数据文件不会被覆盖。
注意直到真实执行recover tablespace … until操作为止，RMAN不会检测使用set newname所设置的文件名与当前数据文件名之间的冲突。在真实执行recover tablespace … until操作时会检测这种冲突。