缺省undo表空间变更后执行TSPITR要注意的问题

执行TSPITR时auxiliary instance里除了有要恢复的表空间外，还必须包含system、sysaux、undo三个表空间，如果当前的undo表空间与恢复点的undo表空间不同时，就需要在recover tablespace命令里指定恢复点的undo表空间名

我们要模拟的TSPITR场景：当前为T2时刻，系统缺省的undo表空间为YYY，现需要把某个表空间TSPITR到T1时刻，T1时刻数据库的缺省的undo tablespace是XXX
针对该场景进行了三次测试，看一下各自的表现：
1、nocatalog模式，不指定undo tablespace
2、catalog模式，不指定undo tablespace
3、nocatalog/catalog模式，指定undo tablespace


//1、 nocatalog模式，不指定undo tablespace

###undotbs是当前的缺省undo表空间
SQL> show parameter undo_tablespace;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      undotbs


###测试数据准备
create table t0626_undo1 tablespace ts0623 as select * from all_users;


select sysdate from dual;
SYSDATE
-----------------
20150626 19:52:37              


drop table t0626_undo1;


###创建undotbs2，备份undotbs2，将undotbs2设置为当前的undo tablespace，重启实例
create undo tablespace undotbs2 datafile '/oradata06/testaaaaa/undotbs2.dbf' size 512m;


backup tablespace undotbs2;


alter system set undo_tablespace='undotbs2' scope=spfile;


startup force


SYS@tstdb1-SQL> show parameter undo_tablespace


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      undotbs2


create table t0626_undo2 tablespace ts0623 as select * from all_users;


select sysdate from dual;
SYSDATE
-----------------
20150626 19:54:38


###不连接catalog的情况下，执行TSPITR
rman target /    
RMAN> recover tablespace ts0623 until time '20150626 19:52:37' auxiliary destination '/oradata06/tspitr';


Starting recover at 20150626 19:56:05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time


List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS
Tablespace UNDOTBS2


Creating automatic instance, with SID='peaF'
using contents of file /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora


initialization parameters used for automatic instance:
db_name=TSTDB1
db_unique_name=peaF_tspitr_TSTDB1
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/oradata06/tspitr
log_archive_dest_1='location=/oradata06/tspitr'
ifile=/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora




starting up automatic instance TSTDB1


Oracle instance started


Total System Global Area     292278272 bytes


Fixed Size                     2220880 bytes
Variable Size                113249456 bytes
Database Buffers             171966464 bytes
Redo Buffers                   4841472 bytes
Automatic instance created


Removing automatic instance
shutting down automatic instance 
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/26/2015 19:56:52
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "UNDOTBS2"

因为T1时刻不存在UNDOTBS2，所以最后报错无法识别undotbs2
                            
//////
// 2、catalog模式，不指定undo tablespace，因为catalog相比controlfile记录了更多的历史信息，看看能否规避这个错误
//////
rman target / catalog rman/773946@tstdb2


RMAN> recover tablespace ts0623 until time '20150626 19:52:37' auxiliary destination '/oradata06/tspitr';


Starting recover at 20150626 20:01:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK


Creating automatic instance, with SID='jgme'
using contents of file /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora


initialization parameters used for automatic instance:
db_name=TSTDB1
db_unique_name=jgme_tspitr_TSTDB1
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/oradata06/tspitr
log_archive_dest_1='location=/oradata06/tspitr'
ifile=/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora




starting up automatic instance TSTDB1


Oracle instance started


Total System Global Area     292278272 bytes


Fixed Size                     2220880 bytes
Variable Size                113249456 bytes
Database Buffers             171966464 bytes
Redo Buffers                   4841472 bytes
Automatic instance created


Removing automatic instance
shutting down automatic instance 
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/26/2015 20:01:52
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "UNDOTBS2"

使用了catalog也报同样的错误，说明catalog也无法跳过T1时刻并不存在的undotbs2，希望能在后续的版本有所改进

//
// 3、nocatalog/catalog模式，指定undo tablespace
//
###T1时刻的undo tablespace是undotbs
RMAN> recover tablespace ts0623 until time '20150626 19:52:37' auxiliary destination '/oradata06/tspitr' undo tablespace undotbs;


Starting recover at 20150626 20:18:02
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=862 device type=DISK


Creating automatic instance, with SID='Bsdd'
using contents of file /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora


initialization parameters used for automatic instance:
db_name=TSTDB1
db_unique_name=Bsdd_tspitr_TSTDB1
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/oradata06/tspitr
log_archive_dest_1='location=/oradata06/tspitr'
ifile=/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora




starting up automatic instance TSTDB1


Oracle instance started


Total System Global Area     292278272 bytes


Fixed Size                     2220880 bytes
Variable Size                113249456 bytes
Database Buffers             171966464 bytes
Redo Buffers                   4841472 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 19:52:37";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script


executing command: SET until clause


Starting restore at 20150626 20:18:44
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=16 device type=DISK


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_ncsnf_TAG20150626T175310_1kVVUSopJ_.bkp
channel ORA_AUX_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_ncsnf_TAG20150626T175310_1kVVUSopJ_.bkp tag=TAG20150626T175310
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/tspitr/TSTDB1/controlfile/o1_mf_1kVdbWe77_.ctl
Finished restore at 20150626 20:18:46


sql statement: alter database mount clone database


sql statement: alter system archive log current


sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 19:52:37";
plsql <<
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TS0623' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  9 to 
 "/oradata06/tspitr/ts0623.dbf.img";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  3, 2, 9;
switch clone datafile all;
}
executing Memory Script


executing command: SET until clause


sql statement: alter tablespace TS0623 offline immediate


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


renamed tempfile 1 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_temp_%u_.tmp in control file


Starting restore at 20150626 20:18:51
using channel ORA_AUX_DISK_1


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /oradata06/tspitr/ts0623.dbf.img
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T175310_1kVVSv03d_.bkp
channel ORA_AUX_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T175310_1kVVSv03d_.bkp tag=TAG20150626T175310
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 20150626 20:19:27


datafile 9 switched to datafile copy
input datafile copy RECID=21 STAMP=883426767 file name=/oradata06/tspitr/ts0623.dbf.img
datafile 3 switched to datafile copy
input datafile copy RECID=22 STAMP=883426767 file name=/oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_1kVdbzPJR_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=23 STAMP=883426768 file name=/oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_1kVdbzUB-_.dbf


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 19:52:37";
# online the datafiles restored or switched
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  9 online";
# recover and open resetlogs
recover clone database tablespace  "TS0623", "UNDOTBS", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script


executing command: SET until clause


sql statement: alter database datafile  3 online


sql statement: alter database datafile  2 online


sql statement: alter database datafile  9 online


Starting recover at 20150626 20:19:28
using channel ORA_AUX_DISK_1


starting media recovery


archived log for thread 1 with sequence 43 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_43_1kVVkpWCs_.arc
archived log for thread 1 with sequence 44 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_44_1kVcENOm7_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_43_1kVVkpWCs_.arc thread=1 sequence=43
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_44_1kVcENOm7_.arc thread=1 sequence=44
creating datafile file number=9 name=/oradata06/tspitr/ts0623.dbf.img


Removing automatic instance
shutting down automatic instance 
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_1kVdbzUB-_.dbf deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_1kVdbzPJR_.dbf deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/controlfile/o1_mf_1kVdbWe77_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/26/2015 20:19:39
RMAN-03015: error occurred in stored script Memory Script
RMAN-20505: create datafile during recovery
ORA-19723: Cannot recreate plugged in read-only datafile 9
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: '/oracle/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00009'
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_44_1kVcENOm7_.arc'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 9: '/oradata06/tspitr/ts0623.dbf.img'


判断上述的错误可能源于Restore所用的database backup生成时间过早，那时候datafile 9还是read only状态，recover无法继续，看来TSPITR的限制还不少


###由于遇到上述错误后ts0623表空间无法正常打开，所以我们新建一个表空间进行测试
---清理上一次实验的结果
drop table t0626_undo1;
drop table t0626_undo2;
drop tablespace ts0623 including contents and datafiles;


---重新将undotbs设定为当前的undo表空间
alter system set undo_tablespace='undotbs' scope=spfile;


startup force;


---创建TS0626作为新的测试表空间，同时drop掉另一个非当前的undo tablespace : undotbs2，生成最新的全库备份
create tablespace ts0626 datafile '/oradata06/testaaaaa/ts0626.dbf' size 128m;


drop tablespace undotbs2 including contents and datafiles;


backup database;


---创建测试表，记录要恢复的时间点
create table t0626_undo1 tablespace ts0626 as select * from all_users;


select sysdate from dual;
SYSDATE
-----------------
20150626 21:02:53              


drop table t0626_undo1;


---创建另一个undotbs3，并备份
create undo tablespace undotbs3 datafile '/oradata06/testaaaaa/undotbs3.dbf' size 512m;


backup tablespace undotbs3;


---重新将undotbs3设为当前的undo表空间，重启instance
alter system set undo_tablespace='undotbs3' scope=spfile;


startup force


SYS@tstdb1-SQL> show parameter undo_tablespace


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      undotbs3


create table t0626_undo2 tablespace ts0626 as select * from all_users;


select sysdate from dual;
SYSDATE
-----------------
20150626 21:04:35


---再次执行TSPITR，这次带上T1时刻使用的undo表空间undotbs
rman target /
run
{
set newname for datafile 9 to '/oradata06/tspitr/9.dbf';
recover tablespace ts0626 until time '20150626 21:02:53' undo tablespace undotbs auxiliary destination '/oradata06/tspitr';
}


executing command: SET NEWNAME


Starting recover at 20150626 21:07:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=332 device type=DISK


Creating automatic instance, with SID='Bjcg'
using contents of file /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora


initialization parameters used for automatic instance:
db_name=TSTDB1
db_unique_name=Bjcg_tspitr_TSTDB1
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/oradata06/tspitr
log_archive_dest_1='location=/oradata06/tspitr'
ifile=/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora




starting up automatic instance TSTDB1


Oracle instance started


Total System Global Area     292278272 bytes


Fixed Size                     2220880 bytes
Variable Size                113249456 bytes
Database Buffers             171966464 bytes
Redo Buffers                   4841472 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 21:02:53";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script


executing command: SET until clause


Starting restore at 20150626 21:08:44
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=16 device type=DISK


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_ncsnf_TAG20150626T210103_1kVf-W7Od_.bkp
channel ORA_AUX_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_ncsnf_TAG20150626T210103_1kVf-W7Od_.bkp tag=TAG20150626T210103
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/tspitr/TSTDB1/controlfile/o1_mf_1kVgOKuHU_.ctl
Finished restore at 20150626 21:08:46


sql statement: alter database mount clone database


sql statement: alter system archive log current


sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 21:02:53";
plsql <<
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TS0626' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  9 to 
 "/oradata06/tspitr/9.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  3, 2, 9;
switch clone datafile all;
}
executing Memory Script


executing command: SET until clause


sql statement: alter tablespace TS0626 offline immediate


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


renamed tempfile 1 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_temp_%u_.tmp in control file


Starting restore at 20150626 21:08:52
using channel ORA_AUX_DISK_1


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /oradata06/tspitr/9.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T210103_1kVfyyKaa_.bkp
channel ORA_AUX_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T210103_1kVfyyKaa_.bkp tag=TAG20150626T210103
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:31
Finished restore at 20150626 21:09:23


datafile 9 switched to datafile copy
input datafile copy RECID=21 STAMP=883429763 file name=/oradata06/tspitr/9.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=22 STAMP=883429763 file name=/oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_1kVgOnvqs_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=23 STAMP=883429763 file name=/oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_1kVgOo1yh_.dbf


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 21:02:53";
# online the datafiles restored or switched
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  9 online";
# recover and open resetlogs
recover clone database tablespace  "TS0626", "UNDOTBS", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script


executing command: SET until clause


sql statement: alter database datafile  3 online


sql statement: alter database datafile  2 online


sql statement: alter database datafile  9 online


Starting recover at 20150626 21:09:24
using channel ORA_AUX_DISK_1


starting media recovery


archived log for thread 1 with sequence 51 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_51_1kVg7nu3W_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_51_1kVg7nu3W_.arc thread=1 sequence=51
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 1 will be lost if RESETLOGS is done
ORA-01110: data file 1: '/oradata06/testaaaaa/system01.dbf'


media recovery complete, elapsed time: 00:00:01
Finished recover at 20150626 21:09:27




Removing automatic instance
shutting down automatic instance 
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_1kVgOo1yh_.dbf deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_1kVgOnvqs_.dbf deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/controlfile/o1_mf_1kVgOKuHU_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/26/2015 21:09:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01245: offline file 1 will be lost if RESETLOGS is done
ORA-01110: data file 1: '/oradata06/testaaaaa/system01.dbf'


上述报错的原因在于undo tablespace ...里没有将SYSTEM包括进去，因为system表空间包含了名为SYSTEM的rollback segment，同时我们也发现上述的输出日志里并没有包含datafile 1 restore的信息，意味着执行TSPITR的aux instance里不存在datafile 1，显然是不会成功的。下面我们将SYSTEM表空间也包括进去，再测试一遍终于成功了
run
{
set newname for datafile 9 to '/oradata06/tspitr/9.dbf';
recover tablespace ts0626 until time '20150626 21:02:53' undo tablespace system,undotbs auxiliary destination '/oradata06/tspitr';
}


executing command: SET NEWNAME


Starting recover at 20150626 21:17:48
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=598 device type=DISK


Creating automatic instance, with SID='wvjn'
using contents of file /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora


initialization parameters used for automatic instance:
db_name=TSTDB1
db_unique_name=wvjn_tspitr_TSTDB1
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/oradata06/tspitr
log_archive_dest_1='location=/oradata06/tspitr'
ifile=/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/params_auxinst.ora




starting up automatic instance TSTDB1


Oracle instance started


Total System Global Area     292278272 bytes


Fixed Size                     2220880 bytes
Variable Size                113249456 bytes
Database Buffers             171966464 bytes
Redo Buffers                   4841472 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 21:02:53";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script


executing command: SET until clause


Starting restore at 20150626 21:18:30
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=16 device type=DISK


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_ncsnf_TAG20150626T210103_1kVf-W7Od_.bkp
channel ORA_AUX_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_ncsnf_TAG20150626T210103_1kVf-W7Od_.bkp tag=TAG20150626T210103
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata06/tspitr/TSTDB1/controlfile/o1_mf_1kVgxFtOB_.ctl
Finished restore at 20150626 21:18:32


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:
{
# set requested point in time
set until  time "20150626 21:02:53";
plsql <<
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TS0626' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  9 to 
 "/oradata06/tspitr/9.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 9;
switch clone datafile all;
}
executing Memory Script


executing command: SET until clause


sql statement: alter tablespace TS0626 offline immediate


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


renamed tempfile 1 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_temp_%u_.tmp in control file


Starting restore at 20150626 21:18:40
using channel ORA_AUX_DISK_1


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /oradata06/tspitr/9.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T210103_1kVfyyKaa_.bkp
channel ORA_AUX_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_26/o1_mf_nnndf_TAG20150626T210103_1kVfyyKaa_.bkp tag=TAG20150626T210103
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:43
Finished restore at 20150626 21:19:23


datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=883430363 file name=/oradata06/tspitr/9.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=24 STAMP=883430363 file name=/oradata06/tspitr/TSTDB1/datafile/o1_mf_system_1kVgxqPpr_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=25 STAMP=883430363 file name=/oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_1kVgxqN35_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=26 STAMP=883430363 file name=/oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_1kVgxqSGB_.dbf


contents of Memory Script:
{
# set requested point in time
set until  time "20150626 21:02:53";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  9 online";
# recover and open resetlogs
recover clone database tablespace  "TS0626", "SYSTEM", "UNDOTBS", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script


executing command: SET until clause


sql statement: alter database datafile  1 online


sql statement: alter database datafile  3 online


sql statement: alter database datafile  2 online


sql statement: alter database datafile  9 online


Starting recover at 20150626 21:19:24
using channel ORA_AUX_DISK_1


starting media recovery


archived log for thread 1 with sequence 51 is already on disk as file /oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_51_1kVg7nu3W_.arc
archived log file name=/oradata06/fra/TSTDB1/archivelog/2015_06_26/o1_mf_1_51_1kVg7nu3W_.arc thread=1 sequence=51
media recovery complete, elapsed time: 00:00:00
Finished recover at 20150626 21:19:26


database opened


contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TS0626 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/oradata06/tspitr''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/oradata06/tspitr''";
}
executing Memory Script


sql statement: alter tablespace  TS0626 read only


sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oradata06/tspitr''


sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oradata06/tspitr''


Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_wvjn":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_wvjn" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_wvjn is:
   EXPDP>   /oradata06/tspitr/tspitr_wvjn_61118.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TS0626:
   EXPDP>   /oradata06/tspitr/9.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_wvjn" successfully completed at 21:20:10
Export completed




contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  TS0626 including contents keep datafiles';
}
executing Memory Script


database closed
database dismounted
Oracle instance shut down


sql statement: drop tablespace  TS0626 including contents keep datafiles


Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_wvjn" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_wvjn":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_wvjn" successfully completed at 21:20:31
Import completed




contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  TS0626 read write';
sql 'alter tablespace  TS0626 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script


sql statement: alter tablespace  TS0626 read write


sql statement: alter tablespace  TS0626 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 /oradata06/tspitr/TSTDB1/datafile/o1_mf_temp_1kVg-loWA_.tmp deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/onlinelog/o1_mf_3_1kVg-hS7T_.log deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/onlinelog/o1_mf_2_1kVg-eISe_.log deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/onlinelog/o1_mf_1_1kVg-bCYk_.log deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/datafile/o1_mf_sysaux_1kVgxqSGB_.dbf deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/datafile/o1_mf_undotbs_1kVgxqN35_.dbf deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/datafile/o1_mf_system_1kVgxqPpr_.dbf deleted
auxiliary instance file /oradata06/tspitr/TSTDB1/controlfile/o1_mf_1kVgxFtOB_.ctl deleted
Finished recover at 20150626 21:20:35
kgepop: no error frame to pop to for error 600


在TSPITR过程中没有输出"List of tablespaces expected to have UNDO segments" 的字样，因为recover命令里已经指定好了undo tablespace名称
---结果验证
SQL> select name,status,file#,plugged_in from v$datafile;


NAME                                                         STATUS       FILE# PLUGGED_IN
------------------------------------------------------------ ------- ---------- ----------
/oradata06/testaaaaa/system01.dbf                            SYSTEM           1          0
/oradata06/testaaaaa/sysaux01.dbf                            ONLINE           2          0
/oradata06/testaaaaa/undotbs01.dbf                           ONLINE           3          0
/oradata06/testaaaaa/users01.dbf                             ONLINE           4          0
/oradata06/testaaaaa/ts0422_1.dbf                            ONLINE           5          0
/oradata06/testaaaaa/xdbts1.dbf                              ONLINE           6          0
/oradata06/testaaaaa/ts0212.dbf                              ONLINE           7          0
/oradata06/testaaaaa/ts0212_1.dbf                            ONLINE           8          0
/oradata06/tspitr/9.dbf                                      OFFLINE          9          0
/oradata06/testaaaaa/undotbs3.dbf                            ONLINE          10          0


10 rows selected.


SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS                        ONLINE
TEMP                           ONLINE
USERS                          ONLINE
XDBTS                          ONLINE
TS0212                         ONLINE
TS0422_1                       ONLINE
TS0626                         OFFLINE
UNDOTBS3                       ONLINE


10 rows selected.


SQL> alter tablespace TS0626 online;


SQL> select count(*) from t0626_undo1;


  COUNT(*)
----------
        19


SQL> select count(*) from t0626_undo2;
select count(*) from t0626_undo2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


---观察到datafile 9经历了TSPITR之后在v$datafile里还是被标记为plugged_in=0，但是PLUGIN_CHANGE#却不为0，有点不解；一个小细节不影响测试结果
SYS@tstdb1-SQL> select file#,checkpoint_change#,name,creation_time,plugged_in,PLUGIN_CHANGE#  from v$datafile;    


           FILE# CHECKPOINT_CHANGE# NAME                                                         CREATION_TIME           PLUGGED_IN   PLUGIN_CHANGE#
---------------- ------------------ ------------------------------------------------------------ ----------------- ---------------- ----------------
               1     12723362870986 /oradata06/testaaaaa/system01.dbf                            20141110 21:15:48                0                0
               2     12723362870986 /oradata06/testaaaaa/sysaux01.dbf                            20141110 21:15:55                0                0
               3     12723362870986 /oradata06/testaaaaa/undotbs01.dbf                           20141110 21:16:08                0                0
               4     12723362870986 /oradata06/testaaaaa/users01.dbf                             20141110 21:16:11                0                0
               5     12723362870986 /oradata06/testaaaaa/ts0422_1.dbf                            20150623 20:52:59                0   12723362391416
               6     12723362870986 /oradata06/testaaaaa/xdbts1.dbf                              20150130 16:10:19                0                0
               7     12723362870986 /oradata06/testaaaaa/ts0212.dbf                              20150212 15:18:18                0                0
               8     12723362870986 /oradata06/testaaaaa/ts0212_1.dbf                            20150212 15:34:50                0                0
               9     12723362870986 /oradata06/tspitr/9.dbf                                      20150626 21:20:32                0   12723362830760  
              10     12723362870986 /oradata06/testaaaaa/undotbs3.dbf                            20150626 21:03:28                0                0


总结：不管是nocatalog还是catalog模式下，要恢复到缺省undo tablespace变化前的状态，必须人为在recover tablespace命令中指定恢复点所使用的undo tablespace还有别忘了SYSTEM一定要加上。总体上来讲TSPITR虽是一个非常不错的功能但在使用过程中的各种限制还是比较多的，对undo tablespace的处理仅是一个方面，期待更自动化的TSPITR

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1714089/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1714089/