使用镜像副本提高RMAN TSPITR的性能
在执行TSPITR时，通过让RMAN使用磁盘上已经存在的恢复集和辅助集数据文件镜像副本，而不是从备份中还原它们将会显著提高TSPITR的性能。可以使用configure auxname命令来使用恢复集数据文件或辅助集数据文件的镜像副本或者使用set newname命令使用辅助集数据文件镜像副本，来告诉RMAN可能存在数据文件的镜像副本。
具体的详细细节依赖于使用的命令和文件是否是辅助集或恢复集文件，通常来说，在指定目录中存在合适的镜像副本，那么在执行TSPITR时，RMAN会从目标实例中RMAN档案库中注消镜像副本并将其登记到辅助实例的控制文件中。辅助实例将使用镜像副本来执行按时间点恢复。
使用configure auxname来使用恢复集镜像副本执行TSPITR
在执行TSPIRT时，RMAN在指定的AUXNAME目录查找数据文件，使用比目标时间还早的检查点SCN来查看是否存在数据文件的镜像备份。如果找到了镜像备份，将会被TSPITR使用。否则，数据文件会被还原和恢复到原始目录中，通过auxname指定目录中的任何文件都不会被改变或删除。
SQL> truncate table tspitr;
Table truncated.
SQL> select count(*) from tspitr;
COUNT(*)
----------
0
执行恢复命令
RMAN> run
2> {
3> configure auxname 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> }
auxiliary name for datafile 6 set to: /u01/app/oracle/oradata/auxiliary/tspitr01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
Starting recover at 2015-03-16 21:11:05
using channel ORA_DISK_1
Creating automatic instance, with SID='okpE'
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_okpE
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_okpE.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 21:11:10
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_okpE.f
Finished restore at 2015-03-16 21:11:13
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_OKPE/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2015-03-16 21:11:21
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_OKPE/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/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:55
Finished restore at 2015-03-16 21:12:17
datafile 1 switched to datafile copy
input datafile copy recid=20 stamp=874530737 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_system_bjfomtoh_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=21 stamp=874530737 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_undotbs1_bjfomttc_.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 21:12:17
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=874530741
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-16 21:12:23
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=oracleokpE\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=okpE^'\)\)\(CONNECT_DATA=\(SID=okpE\)\)\) 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 21:12:50 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 21:13: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_okpE.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_system_bjfomtoh_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_undotbs1_bjfomttc_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_temp_bjfop5sn_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/onlinelog/o1_mf_1_bjfooq75_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/onlinelog/o1_mf_2_bjfootjb_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/onlinelog/o1_mf_3_bjfooxd8_.log deleted
Finished recover at 2015-03-16 21:13:17
从上面的auxiliary name for datafile 6 set to: /u01/app/oracle/oradata/auxiliary/tspitr01.dbf
信息可以看到确实为恢复集数据文件使用了镜像副本来执行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 OFFLINE
7 rows selected.
RMAN> sql 'alter tablespace tspitr online';
sql statement: alter tablespace tspitr online
starting full resync of recovery catalog
full resync complete
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
configure auxname的主要作用是一种基本技术通过消除还原时间来提高TSPITR的性能。如果有预计要执行TSPITR操作的表空间，可以在备份程序中维护一组表空间相关数据文件的镜像副本，并定期将镜像副本更新到TSPITR所要恢复到的目标时间之前的时间点：
1.当设置这种策略时，为文件配置一次auxname
2.定期执行 backup as copy datafile n format auxname来维护镜像副本的更新，或者为了更好的性能使用增量更新备份策略。
3.当需要执行TSPITR时，指定自上次更新镜像副本之后的一个目标时间。
在计划使用镜像副本执行TSPIRT时，记住你不可能提前知道表空间将要使用的镜像副本。在要执行TSPITR的表空间和其它表空间之间存在关联时，要将相关的表空间加入到最终的恢复集中并且其它表空间可能存在于辅助集中。应该为每个数据文件配置一个auxname让其成为恢复集中的一部分并经常更新所有数据文件的镜像副本。
如果没有正确的估计恢复集所包含的表空间或者因为开销原因不想对恢复集中所有表空间维护镜像副本，可以使用只维护所有数据文件的一个子集的策略。如果只准备了所有数据文件的一个子集所对应的镜像副本，TSPITR的处理仍然相同。这个处理过程时间会变长，因为RMAN必须将没有镜像副本的恢复集中的数据文件恢复到它们原来的目录中。
当使用configure auxname来重命名恢复集文件时重命名方法的优先级仍然是要遵守的。对于相同文件的set newname命令将会覆盖configure auxname命令行为。
执行set newname和configure auxname来使用辅助集镜像副本执行TSPITR与恢复集数据文件一样，configure auxname命令给辅助集数据文件镜像副本设置一个永久的替代目录，
set newname在RUN块中设置替代目录。然而，RMAN对辅助集数据文件的处理不同于恢复集数据文件。
如果使用set newname来给辅助集数据文件指定新目录并且在这个目录中存在镜像副本且镜像副本的SCN是可以用来执行TSPITR操作，那么镜像副本会被使用。如果在指定的新目录中没有可用的镜像副本，那么RMAN会从备份中还原一个可用的副本（如果存在的镜像副本的SCN号在TSPITR的目标时间之后，那么会使用还原的文件覆盖存在数据文件）。
如果使用configure auxname来给辅助集数据文件指定新目录并且这个目录中存在镜像副本且镜像副本的SCN可以用来执行TSPITR操作，那么镜像副本会被使用。如果在指定的新目录中没有可用的镜像副本，那么RMAN会从备份中还原数据文件。
不管镜像副本是在执行TSPITR之前被创建，还是在执行TSPITR时从备份中还原生成，与所有辅助集文件一样，在TSPITR成功执行完后会被删除或者当TSPITR失败后保留用来进行诊断。