執行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

預設undo表空間變更後執行TSPITR要注意的問題

相關文章