While the RMAN Tablespace Point In Time Recovery (TSPITR) automates the process of creating an auxiliary instance and exporting and importing objects from the auxiliary instance to the target database, it still has many limitations and we encountered bugs which apparently have been only fixed in 11g.

Not only do we need to allocate temporary disk space for the SYSTEM, UNDO and the tablespace that we want to be recovered, it cannot be used to recover from a case of a dropped tablespace. Further, the tablespace needs to be self contained and cannot contain any objects owned by SYS or any undo or rollback segments. In case of partitioned tables spread over a number of tablespaces, all the referenced tablespaces will need to be recovered together.

While running the following command to do a TSPITR, we encountered the errors shown below

run {
recover tablespace test_tspitr
until logseq 7 thread 1
auxiliary destination ‘/u02/oradata/stage/’;
}

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/04/2009 11:28:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found – aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 2
RMAN-06100: no channel to restore a backup or copy of datafile 1

Metalink Note 391785.1 indicates that this error is because the automatically configured Auxiliary SBT channel is released during the TSPITR process and not reused later on in the process. This is also seen in the log and the workaround suggested is to explicitly allocate an auxiliary channel.

released channel: ORA_SBT_TAPE_1
released channel: ORA_AUX_SBT_TAPE_1
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

if we change the command to allocate the channel as shown below, we are getting another error that it is not able to connect to an auxiliary instance. This means we need to startup and connect to an auxiliary instance first before running the TSPITR command. Not very good.

run {
allocate auxiliary channel c1 device type ‘SBT_TAPE’ ;
recover tablespace test_tspitr
until logseq 7 thread 1
auxiliary destination ‘/u02/oradata/stage/’;
}

RMAN-03002: failure of allocate command at 08/04/2009 12:08:48
RMAN-06174: not connected to auxiliary database

This has been documented in Bug 4768353 which is apparently fixed in 11g. The issue is not faced if backups are available on disk and default device type is disk.