11
Performing RMAN Tablespace Point-in-Time Recovery

This chapter describes how to use Recovery Manager (RMAN) to perform tablespace point-in-time recovery (TSPITR). This chapter contains these topics:

• Introduction to RMAN TSPITR
• Planning for RMAN TSPITR
• Choosing a Naming Method for Files in the Auxiliary and Recovery Sets
• Preparing the Auxiliary Instance for RMAN TSPITR
• Performing RMAN TSPITR
• Preparing the Target Database for Use After RMAN TSPITR
• Responding to Unsuccessful RMAN TSPITR

Introduction to RMAN TSPITR

Recovery Manager (RMAN) automated tablespace point-in-time recovery (TSPITR) enables you to quickly recover one or more tablespaces to a time that is different from that of the rest of the database.

RMAN TSPITR is most useful for recovering the following:

• An erroneous DROP TABLE or TRUNCATE TABLE statement
• A table that has become logically corrupted
• An incorrect batch job or other DML statement that has affected only a subset of the database
• A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database

Like a table export, RMAN TSPITR enables you to recover a consistent data set; however, the data set is the entire tablespace rather than one object. As Figure 11-1 illustrates, RMAN does the following:

1. Restores the specified tablespace backups
2. Recovers the specified tablespaces
3. Exports metadata from the auxiliary instance
4. Points the target database control file to the newly recovered datafiles
5. Imports metadata into the target database

Figure 11-1 RMAN TSPITR

Text description of the illustration sbr81085.gif

Glossary of TSPITR Terminology

Familiarize yourself with the following terms and abbreviations, which are used throughout this chapter:

TSPITR

Tablespace point-in-time recovery

Auxiliary Instance

The auxiliary instance used to recover the backup tablespaces. The database created by TSPITR never has independent existence: it is only an intermediate work area.

Recovery Set

Tablespaces in the target database requiring TSPITR to be performed on them. For example, if you need to recover tablespace users to a noncurrent time, then users is the tablespace in the recovery set.

Auxiliary Set

Any other files required for TSPITR, including:

• Backup control file
• SYSTEM tablespace
• Datafiles containing rollback or undo segments
• Temporary tablespace (optional). A small space is required by Export for sort operations.
• Online redo logs of the auxiliary database.

  See Also: "Responding to Unsuccessful RMAN TSPITR" for more information on sort space issues

Planning for RMAN TSPITR

RMAN TSPITR requires careful planning. Before proceeding, read this chapter thoroughly.

This section covers the following topics:

• Performing TSPITR Without a Recovery Catalog
• Understanding General Restrictions
• Managing Data Relationships

  Note: Many of the limitations and planning steps in this chapter can also be found in Oracle9i User-Managed Backup and Recovery Guide; however, differences in limitations and planning exist. These differences are explicitly noted.

Performing TSPITR Without a Recovery Catalog

You can perform RMAN TSPITR either with or without a recovery catalog. If you do not use a recovery catalog, then note these restrictions:

• The rollback or undo segments at the time of the TSPITR are needed in the auxiliary set. Because RMAN has no historical record of the undo in the control file, RMAN assumes that the current rollback or undo segments were the same segments present at the time to which recovery is performed. If the rollback or undo segments have changed since that time, then TSPITR fails when the recovery tries to write to a nonexistent file.
• TSPITR to a time that is too old may not succeed if Oracle has reused the control file records for needed copies and backups.
• Assume that you run TSPITR on a tablespace, and then bring the tablespace online at time t. Backups of the tablespace created before time t are no longer usable for recovery with a current control file. Thus, you cannot run TSPITR again on this tablespace to recover it to any time less than or equal to time t, nor can you use the current control file to recover the database to any time less than or equal to t.

  The reason for the additional restrictions in NOCATALOG mode is that the current control file has no record of the older incarnation of the recovered tablespace. Thus, recovery with a current control file that involves this tablespace can no longer use a backup taken prior to time t. However, if you restore a control file backed up prior to t, then you can perform incomplete recovery of the whole database to any time less than or equal to t.

Understanding General Restrictions

When performing RMAN TSPITR, you cannot do the following:

• Run the target and auxiliary databases on separate computers. However, the target and auxiliary databases can be in a cluster configuration that uses shared disks.
• Recover dropped tablespaces.
• Recover a tablespace that has been dropped and re-created with the same name.
• Remove a datafile that has been added to a tablespace. If the file was added after the point to which RMAN is recovering, then the file will still be part of the tablespace (and will be empty) after RMAN TSPITR is complete.
• Issue DML statements on the auxiliary instance--the auxiliary instance is a temporary instance used for recovery only.
• Assume that you perform TSPITR on a tablespace when connected to a recovery catalog, and then bring the tablespace back online at time t. You can use backups created before time t to recover any tablespace or the whole database up to time t, but not later. Hence, you should immediately back up the tablespace after performing TSPITR.
• Recover optimizer statistics for objects that have had statistics calculated on them; recalculate statistics after performing TSPITR.
• Place any of the following objects within the recovery set:
  • Replicated master tables.
  • Partial tables. For example, if you perform RMAN TSPITR on partitioned tables and spread partitions across multiple tablespaces, then RMAN returns an error message during the export phase.
  • Tables without their constraints or constraints without their tables.
  • Tables with VARRAY columns.
  • Tables with nested tables.
  • Tables with external files.
  • Snapshot logs and snapshot tables.
  • Tablespaces containing undo or rollback segments.
  • Objects owned by SYS (including rollback segments).

Managing Data Relationships

TSPITR provides views that can detect any data relationships between objects in the recovery set and objects in the rest of the database. TSPITR cannot successfully complete unless these relationships are managed, either by removing or suspending the relationship or by including the related object within the recovery set.

Determining Whether Objects Will Be Lost

When RMAN TSPITR is performed on a tablespace, any objects created after the recovery time are lost. To see which objects will be lost, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database. The contents of the view are described in Table 11-1.

Table 11-1 TS_PITR_OBJECTS_TO_BE_DROPPED View  

Column Name	Meaning
OWNER	Owner of the object to be dropped.
NAME	The name of the object that will be lost as a result of undergoing TSPITR
CREATION_TIME	Creation timestamp for the object.
TABLESPACE_NAME	Name of the tablespace containing the object.

When querying this view, supply all the elements of the date field, otherwise the default setting is used. Also, use the TO_CHAR and TO_DATE functions. For example, with a recovery set consisting of users and tools, and a recovery point in time of '2001-06-02:07:03:11', issue the following statement:

SELECT OWNER, NAME, TABLESPACE_NAME, TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS') 
       FROM TS_PITR_OBJECTS_TO_BE_DROPPED 
WHERE TABLESPACE_NAME IN ('USERS','TOOLS') 
AND CREATION_TIME > TO_DATE('01-JUN-02:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

Researching and Resolving Dependencies on the Primary Database

Query the TS_PITR_CHECK view to identify relationships between objects that overlap the recovery set boundaries. If this view returns rows when queried, then investigate and correct the problem. Proceed with TSPITR only when TS_PITR_CHECK view returns no rows for the tablespaces not in the recovery set. Record all actions performed during this step so that you can retrace these relationships after completing TSPITR.

Supply a four-line predicate detailing the recovery set tablespace to query the TS_PITR_CHECK view. For example, with a recovery set consisting of tools and users, the SELECT statement against TS_PITR_CHECK would be as follows:

SELECT * 
FROM SYS.TS_PITR_CHECK 
WHERE (
        TS1_NAME IN ('USERS','TOOLS') 
        AND TS2_NAME NOT IN ('USERS','TOOLS')
      )
OR    (
        TS1_NAME NOT IN ('USERS','TOOLS') 
        AND TS2_NAME IN ('USERS','TOOLS')
      );

To run a complete TSPITR check on all the tablespaces in the database (not just the tablespaces in the recovery set), you can run the following query:

SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
         'SYSTEM' IN (TS1_NAME, TS2_NAME)
         AND TS1_NAME <> TS2_NAME
         AND TS2_NAME <> '-1'
      )
OR    (
         TS1_NAME <> 'SYSTEM' 
         AND TS2_NAME = '-1'
      );

Because of the number and width of the columns in the TS_PITR_CHECK view, you may want to format the columns as follows when running the query:

SET LINESIZE 120
COLUMN OBJ1_OWNER HEADING "own1"
COLUMN OBJ1_OWNER FORMAT a6
COLUMN OBJ1_NAME HEADING "name1"
COLUMN OBJ1_NAME FORMAT a5
COLUMN OBJ1_SUBNAME HEADING "subname1"
COLUMN OBJ1_SUBNAME FORMAT a8
COLUMN OBJ1_TYPE HEADING "obj1type"
COLUMN OBJ1_TYPE FORMAT a8 word_wrapped
COLUMN TS1_NAME HEADING "ts1_name"
COLUMN TS1_NAME FORMAT a6
COLUMN OBJ2_NAME HEADING "name2"
COLUMN OBJ2_NAME FORMAT a5
COLUMN OBJ2_SUBNAME HEADING "subname2"
COLUMN OBJ2_SUBNAME FORMAT a8
COLUMN OBJ2_TYPE HEADING "obj2type"
COLUMN OBJ2_TYPE FORMAT a8 word_wrapped
COLUMN OBJ2_OWNER HEADING "own2"
COLUMN OBJ2_OWNER FORMAT a6
COLUMN TS2_NAME HEADING "ts2_name"
COLUMN TS2_NAME FORMAT a6
COLUMN CONSTRAINT_NAME HEADING "cname"
COLUMN CONSTRAINT_NAME FORMAT a5
COLUMN REASON HEADING "reason"
COLUMN REASON FORMAT a25 word_wrapped

Assume a case in which the partitioned table tp has two partitions, p1 and p2, that exist in tablespaces users and tools respectively. Also assume that a partitioned index called tpind is defined on tp, and that the index has two partitions id1 and id2 (that exist in tablespaces id1 and id2 respectively). In this case, you would get the following output when TS_PITR_CHECK is queried against tablespaces users and tools (assuming appropriate formatting):

own1   name1 subname1 obj1type ts1_name name2 subname2 obj2type own2 ts2_name cname reason
---    ----  -----    ------   -------  ----  ------   -------- ---  -------- ---   ------ 
SYSTEM  TP   P1       TABLE    USER     TPIND IP1      INDEX    PARTITION PARTITION  SYS 
ID1 Partitioned Objects not fully contained in the recovery set
SYSTEM  TP   P2       TABLE    TOOLS    TPIND IP2      INDEX    PARTITION PARTITION  SYS 
ID2 Partitioned Objects not fully contained in the recovery set 

The table SYSTEM.tp has a partitioned index tpind that consists of two partitions, ip1 in tablespace id1 and ip2 in tablespace id2. Either drop tpind or include id1 and id2 in the recovery set.

Choosing a Naming Method for Files in the Auxiliary and Recovery Sets

This section contains these topics:

• Overview of Datafile Naming in RMAN TSPITR
• Using SET NEWNAME to Name Files in the Auxiliary and Recovery Sets
• Using Datafile Copies in the Auxiliary and Recovery Sets
• Using Initialization Parameters to Name the Auxiliary Set Files

Overview of Datafile Naming in RMAN TSPITR

Before you begin setting up the auxiliary instance to perform the TSPITR, you must decide on a naming method for the datafiles in the auxiliary set. If the names for files in the auxiliary set are not different from the filenames in use by the target database, then RMAN signals an error during TSPITR and exits.

For example, if you are creating the auxiliary database in the /tmp directory, then the absolute path name of the auxiliary datafiles must be prefixed by /tmp. These auxiliary files are intended only for performing the TSPTIR, so you should delete them immediately afterward. If filenames are not converted in the auxiliary set, then RMAN signals an error during TSPITR and exits.

Optionally, you may also choose to rename the datafiles in the recovery set tablespaces on the target (not the auxiliary) database. For example, if you are performing TSPITR on datafile ?/oradata/trgt/users01.dbf in the users tablespace, you may decide to rename it to /disk2/datafiles/users01.dbf. This operation is equivalent to an ALTER DATABASE RENAME FILE for specified files in the target database.

Table 11-1 describes the commands and parameters used to name datafiles in the auxiliary and recovery sets during TSPITR. The order of precedence in the following table goes top to bottom, so SET NEWNAME takes precedence over CONFIGURE AUXNAME and DB_FILE_NAME_CONVERT.

Table 11-2 Datafile Naming Methods

Order of Precedence	Command/Parameter	Can Name Files in Auxiliary Set?	Can Rename Files in Recovery Set?
1	SET NEWNAME	Yes	Yes
2	CONFIGURE AUXNAME	Yes	Yes
3	DB_FILE_NAME_CONVERT	Yes	No

Using SET NEWNAME to Name Files in the Auxiliary and Recovery Sets

You can specify a new name for any datafiles in the auxiliary set with the RMAN command SET NEWNAME. RMAN uses this new name as the temporary location in which to restore and recover the datafile. This new name also overrides the setting in the DB_FILE_NAME_CONVERT parameter in the initialization parameter file, if this parameter happens to be set.

You can also use SET NEWNAME to rename datafiles in recovery set tablespaces. If you specify a new name, then the new filenames replace the original filenames in the target control file. When setting new filenames, RMAN does not check for conflicts between datafile names at the auxiliary and target databases. Any conflicts result in an RMAN error during TSPITR.

Using SET NEWNAME to Name Files: Example

For example, assume that the auxiliary set contains the following datafiles:

• ?/oradata/trgt/system01.dbf of the SYSTEM tablespace
• ?/oradata/trgt/undotbs01.dbf of the undotbs tablespace

The recovery set contains the following datafiles:

• ?/oradata/trgt/users01.dbf of the users tablespace
• ?/oradata/trgt/tools01.dbf of the tools tablespace

You want to create the auxiliary database in the /tmp directory. Also, you decide to rename the datafile in tools to /private1/tools01.dbf, but leave the datafile in the users tablespace with its original name.

In this case, you can run the following command to run TSPITR on tools and users, causing ?/oradata/trgt/tools01.dbf to be renamed to ?/dbs/tools01.dbf on the target database:

RUN
{
  # set newnames for auxiliary set datafiles
  SET NEWNAME FOR DATAFILE '?/oradata/trgt/system01.dbf' TO '/tmp/system01.dbf';
  SET NEWNAME FOR DATAFILE '?/oradata/trgt/undotbs01.dbf' TO '/tmp/undotbs01.dbf';
  # rename one recovery set datafile
  SET NEWNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '/disk1/tools01.dbf';
  RECOVER TABLESPACE tools, users UNTIL SEQUENCE 1034 THREAD 1;
}

Using Datafile Copies in the Auxiliary and Recovery Sets

Using a datafile copy on disk is much faster than restoring a datafile. Hence, you may wish to use an appropriate copy of a datafile in the recovery or auxiliary set instead of restoring and recovering a datafile.

RMAN TSPITR can use a datafile copy if the following conditions are met:

1. The datafile copy name is registered in the recovery catalog as the auxiliary name of the corresponding datafile with the following command (where filename is the datafile name or number, and aux_datafile_name is the datafile auxiliary name):

   CONFIGURE AUXNAME FOR DATAFILE FILENAME TO auxiliary_datafile_name;
   
   

2. The datafile copy was made before the time specified in the UNTIL clause with the following RMAN command (where 'filename' is the datafile filename):

   COPY DATAFILE 'filename' TO AUXNAME;
   
   

If RMAN uses a datafile copy and TSPITR completes successfully, then the auxiliary_datafile_name is removed from the recovery catalog, and updated to status DELETED in the control file. The original datafile at the target is replaced by this datafile copy after RMAN TSPITR completes.

Using CONFIGURE AUXNAME to Name Files: Example

For example, assume that the auxiliary set contains the following datafiles:

• ?/oradata/trgt/system01.dbf of the SYSTEM tablespace
• ?/oradata/trgt/undotbs01.dbf of the undotbs tablespace

The recovery set contains the following datafiles:

• ?/oradata/trgt/users01.dbf of the users tablespace
• ?/oradata/trgt/tools01.dbf of the tools tablespace

Also, assume that you ran the following commands at a time before the end time of the desired TSPTIR:

CONFIGURE AUXNAME FOR DATAFILE '?/oradata/trgt/system01.dbf' TO '/copy/system01.dbf';
CONFIGURE AUXNAME FOR DATAFILE '?/oradata/trgt/undotbs01.dbf' TO '/copy/undotbs01.dbf';
CONFIGURE AUXNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '?/dbs/tools01.dbf';

COPY DATAFILE '?/oradata/trgt/system01.dbf' TO AUXNAME;
COPY DATAFILE '?/oradata/trgt/undotbs01.dbf' TO AUXNAME;
COPY DATAFILE '?/oradata/trgt/tools01.dbf' TO AUXNAME;

In this case, you can run the following command to run TSPITR on tools and users, causing ?/oradata/trgt/tools01.dbf to be renamed to ?/dbs/tools01.dbf on the target database:

RECOVER TABLESPACE tools, users UNTIL SEQUENCE 1034 THREAD 1;

Using Initialization Parameters to Name the Auxiliary Set Files

You can use the DB_FILE_NAME_CONVERT parameter in the auxiliary initialization parameter file to name auxiliary set--but not recovery set--datafiles.If neither a new name nor auxiliary name is set for a datafile in an auxiliary set tablespace, then RMAN can use the converted filename specified in the auxiliary database control file to perform the restore and recovery. RMAN checks for conflicts between datafile names at the auxiliary and target databases. Any conflicts result in an error.

If neither a new name or auxiliary name is set for a datafile in a recovery set tablespace, or if the file at the auxiliary name is unusable, then RMAN uses the original location of the datafile.

Using Initialization Parameters to Name Files: Example

For example, assume that the auxiliary set contains the following files:

• ?/oradata/trgt/system01.dbf of the SYSTEM tablespace
• ?/oradata/trgt/undotbs01.dbf of the undotbs tablespace

The recovery set contains the following files:

• ?/oradata/trgt/users01.dbf of the users tablespace
• ?/oradata/trgt/tools01.dbf of the tools tablespace

You want to create the auxiliary database in the /tmp directory. Also, you decide to rename the datafile in tools to /private1/tools01.dbf, but leave the datafile in the users tablespace with its original name.

In this case, you can set the following parameter in the auxiliary initialization parameter file to name the auxiliary set files:

DB_FILE_NAME_CONVERT=('/oradata/trgt','/tmp') # captures all auxiliary set files

In this case, you can run the following command to run TSPITR on tools and users, causing ?/oradata/trgt/tools01.dbf to be renamed to ?/dbs/tools01.dbf on the target database:

RUN
{
  # rename one recovery set datafile
  SET NEWNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '?/dbs/tools01.dbf';
  RECOVER TABLESPACE tools, users UNTIL SEQUENCE 1034 THREAD 1;
}

Preparing the Auxiliary Instance for RMAN TSPITR

Satisfy the following requirements discussed in this section before performing RMAN TSPITR:

• Task 1: Create an Oracle Password File for the Auxiliary Instance
• Task 2: Create a Parameter File for the Auxiliary Instance
• Task 3: Ensure Oracle Net Connectivity to the Auxiliary Instance
• Task 4: Start the Auxiliary Instance
• Task 5: Start the Recovery Manager Command-Line Interface

Task 1: Create an Oracle Password File for the Auxiliary Instance

For instructions on how to create and maintain Oracle password files, refer to the Oracle9i Database Administrator's Guide.

Task 2: Create a Parameter File for the Auxiliary Instance

Create an initialization parameter file for the auxiliary instance and set the parameters described in Table 11-3.

Table 11-3 Initialization Parameters in the Auxiliary Instance

Parameter	Specify
DB_NAME	The same name as the target database.
LOCK_NAME_SPACE	A value different from any database in the same Oracle home. For simplicity, specify _dbname. For example, if the target database name is trgt, then specify _trgt.
DB_FILE_NAME_CONVERT	Patterns to convert filenames for the datafiles of the auxiliary database. You can use this parameter to generate filenames for those files that you did not name with SET NEWNAME or CONFIGURE AUXNAME. Obtain the datafile filenames by querying V$DATAFILE.NAME, and ensure that the conversion pattern matches the format of the filename displayed in the view. Note: Some platforms do not support ending the patterns in a forward or backward slash (/ or \). See Also: "Choosing a Naming Method for Files in the Auxiliary and Recovery Sets"
LOG_FILE_NAME_CONVERT	Patterns to convert filenames for the online redo logs of the auxiliary database. Obtain the online redo log filenames by querying V$LOGFILE.MEMBER, and ensure that the conversion pattern matches the format of the filename displayed in the view. Note: Some platforms do not support ending the patterns in a forward or backward slash (\ or /).
CONTROL_FILES	A different value from the CONTROL_FILES parameter in the target parameter file.
REMOTE_LOGIN_PASSWORDFILE	Set to EXCLUSIVE when connecting to the auxiliary instance by means of a password file.
COMPATIBLE	The same value as the parameter in the target database.
DB_BLOCK_SIZE	If this initialization parameter is set in the target database, then it must be set to the same value in the auxiliary instance.

Set other parameters as needed, including the parameters that allow you to connect as SYSDBA through Oracle Net.

Following are examples of the initialization parameter settings for the auxiliary instance:

DB_NAME=trgt
LOCK_NAME_SPACE=_trgt
CONTROL_FILES=/tmp/control01.ctl
DB_FILE_NAME_CONVERT=('/oracle/oradata/trgt/','/tmp/')
LOG_FILE_NAME_CONVERT=('/oracle/oradata/trgt/redo','/tmp/redo')
REMOTE_LOGIN_PASSWORDFILE=exclusive
COMPATIBLE = 9.0.1
DB_BLOCK_SIZE=8192

Task 3: Ensure Oracle Net Connectivity to the Auxiliary Instance

The auxiliary instance must have a valid net service name. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance.

Task 4: Start the Auxiliary Instance

Before beginning RMAN TSPITR, use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT mode (specifying a parameter file if necessary). For example:

SQL> CONNECT SYS/oracle@aux AS SYSDBA
SQL> STARTUP NOMOUNT PFILE='/tmp/initAUX.ora'

Because the auxiliary instance does not yet have a control file, you can only start the instance in NOMOUNT mode. Do not create a control file or try to mount or open the auxiliary instance for TSPITR.

Task 5: Start the Recovery Manager Command-Line Interface

Use one of the following methods discussed in this section to start the RMAN command-line interface:

• Connecting from the Operating System Command Line
• Connecting from the RMAN Prompt

Connecting from the Operating System Command Line

To connect to the auxiliary instance, target instance, and optional recovery catalog, supply the following information when starting RMAN:

% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb AUXILIARY SYS/oracle@aux

The following table describes the variables used in this RMAN connection.

Variable	Meaning
SYS	User with SYSDBA privilege
oracle	The password for connecting as SYSDBA specified in the target database's orapwd file
trgt	The net service name for the target database
rman	Owner of the recovery catalog
cat	The password for user RMAN specified in the recovery catalog's orapwd file
catdb	The net service name for the recovery catalog database
aux	The net service name for the auxiliary database.

Connecting from the RMAN Prompt

You can start the RMAN command-line interface without a connection to the auxiliary instance, and then use the CONNECT command at the RMAN prompt. This example connects in the default NOCATALOG mode:

% rman
RMAN> CONNECT AUXILIARY SYS/oracle@aux
RMAN> CONNECT TARGET SYS/oracle@trgt

To connect to a catalog, run the CONNECT CATALOG command:

RMAN> CONNECT CATALOG rman/cat@catdb

Performing RMAN TSPITR

After completing all planning requirements, recover the recovery set tablespaces, specifying the end point of recovery. You do not have to take the tablespaces offline first because RMAN does it automatically. The following command performs TSPITR on the users tablespace until log sequence 13:

RECOVER TABLESPACE users UNTIL SEQUENCE 13 THREAD 1;

If no auxiliary device configuration is specified, and if RMAN needs to automatically allocate auxiliary channels, then RMAN uses the target database device configuration. You do not need to configure auxiliary channels unless they require different parameters from the target channels.

The following example assumes that you do not have automatic channels configured and so must manually allocate auxiliary channels:

# manually allocate at least one auxiliary channel
RUN
{
  ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK; 
  RECOVER TABLESPACE users UNTIL SEQUENCE 13 THREAD 1;
}

RMAN automatically performs the following steps during TSPITR:

1. Takes the tablespaces to be recovered offline.
2. Restores the datafiles to the auxiliary instance.
3. Recovers the restored datafiles to the specified time.
4. Opens the auxiliary database with the RESETLOGS option.
5. Exports the dictionary metadata about objects in the recovered tablespaces--the DDL to create the objects along with pointers to the physical locations of those in the recovered datafiles--to the target database.
6. Shuts down the auxiliary instance.
7. Issues SWITCH commands so that the target control file now points to the datafiles in the recovery set that were just recovered at the auxiliary database.
8. Imports the dictionary metadata that was exported from the auxiliary instance, allowing the recovered objects to be accessed.

   Note: RMAN attempts to find datafile copies instead of restoring the datafiles being recovered. If it finds none, then it performs a restore operation and does not execute a switch. If you have configured names for the datafiles with the CONFIGURE AUXNAME command, and suitable datafile copies exist in those AUXNAME locations, then RMAN optimizes away the restore and performs a switch to the recovered AUXNAME datafile copy.

Preparing the Target Database for Use After RMAN TSPITR

The tablespaces in the recovery set remain offline until after RMAN TSPITR completes successfully.

To prepare the target database for reuse after TSPITR:

1. Start RMAN and connect to the target database. For example, run:

   % rman TARGET SYS/oracle@trgt
   
   

2. Make backups of tablespaces in the recovery set before bringing these tablespaces online. For example, makes a new backup of tablespace users:

   BACKUP TABLESPACE users;
   
   

   Note: If you are running in NOCATALOG mode, then you cannot perform TSPITR on a tablespace and then perform it again on the same tablespace to a time equal to or before the TSPITR time. However, after TSPITR completes and you make a new backup of the tablespace, you can perform TSPITR on the tablespace to any time after the backup.

3. Bring the recovery set tablespaces online. For example, enter:

   SQL "ALTER TABLESPACE users ONLINE";
   
   

4. Connect to the auxiliary instance with SQL*Plus and shut down the auxiliary instance. For example:

   sqlplus 'SYS/oracle@aux AS SYSDBA'<<EOF
   SHUTDOWN ABORT;
   EXIT;
   EOF
   
   

5. Delete the following files from the operating system:
   • Auxiliary set datafiles restored to temporary locations during TSPITR
   • Auxiliary database control files
   • Auxiliary database redo log files

Responding to Unsuccessful RMAN TSPITR

A variety of problems can cause TSPITR to fail. For example, if there is a conflict between the target database and the converted filename, then you have to shut down the auxiliary instance, correct the converted datafile name, issue a STARTUP NOMOUNT, and then run RMAN TSPITR again.

Another possible cause for failure is a lack of sufficient sort space for the Export utility. In this case, you need to edit the recover.bsq file (on UNIX, it is located in $ORACLE_HOME/rdbms/admin). This file contains the following:

# 
# tsiptr_7: do the incomplete recovery and resetlogs.  This member is used once. 
# 
define tspitr_7 
<<< 
# make the control file point at the restored datafiles, then recover them 
recover clone database tablespace &1&; 
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"; 
} 
>>> 

Remove the '#' symbols from the last two lines of comments and modify the statement to create a temporary tablespace. Retry the TSPITR operation, increasing the size of the tablespace until the export operation succeeds. If TSPITR is unsuccessful for some reason, then use the following procedure.

To respond to unsuccessful TSPITR:

1. If RMAN TSPITR is unsuccessful, then shut down the auxiliary instance:

   SHUTDOWN ABORT;
   
   

2. Identify and correct the error.
3. Start the auxiliary instance without mounting it. For example, enter:

   STARTUP NOMOUNT PFILE=initAUX.ora;
    
   

4. Perform TSPITR again, following the instructions in "Performing RMAN TSPITR".