10
RMAN Tablespace Point-in-Time Recovery (TSPITR)

Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

This chapter explains when you can and cannot use TSPITR, what RMAN actually does to your database during TSPITR, how to prepare a database for TSPITR, how to run TSPITR, and options for controlling the TSPITR process.

This chapter contains the following sections:

• Understanding RMAN TSPITR
• Planning and Preparing for TSPITR
• Performing Basic RMAN TSPITR
• Performing Customized RMAN TSPITR with an RMAN-Managed Auxiliary Instance
• Performing RMAN TSPITR Using Your Own Auxiliary Instance
• Troubleshooting RMAN TSPITR

Understanding RMAN TSPITR

In order to use TSPITR effectively, you need to understand what problems it can solve for you, what the major elements used in TSPITR are, what RMAN does during TSPITR, and limitations on when and how it can be applied.

RMAN TSPITR Concepts

Figure 10-1 illustrates the context within which TSPITR takes place, and a general outline of the process.

Figure 10-1 Tablespace Point-in-Time Recovery (TSPITR) Architecture

Text description of the illustration bradv032.gif

The figure contains the following entities:

• The target instance, containing the tablespace to be recovered
• The Recovery Manager client
• The control file and (optional) recovery catalog, used for the RMAN repository records of backup activity
• Archived redo logs and backup sets from the target database, which are the source of the reconstructed tablespace.
• The auxiliary instance, an Oracle database instance used in the recovery process to perform the actual work of recovery.

There are four other important terms related to TSPITR, which will be used in the rest of this discussion:

• The target time, the point in time or SCN that the tablespace will be left at after TSPITR
• The recovery set, which consists of the datafiles containing the tablespaces to be recovered;
• The auxiliary set, which includes datafiles required for TSPITR of the recovery set which are not themselves part of the recovery set. The auxiliary set typically includes:
  • A copy of the SYSTEM tablespace
  • Datafiles containing rollback or undo segments from the target instance
  • In some cases, a temporary tablespace, used during the export of database objects from the auxiliary instance

  The auxiliary instance has other files associated with it, such as a control file, parameter file, and online logs , but they are not part of the auxiliary set.

• The auxiliary destination, an optional location on disk which can be used to store any of the auxiliary set datafiles, control files and online logs of the auxiliary instance during TSPITR. Files stored here can be deleted after TSPITR is complete.

All of these terms will be referenced throughout the remainder of this chapter.

How TSPITR Works With an RMAN-Managed Auxiliary Instance

To perform TSPITR of the recovery set using RMAN and an automated auxiliary instance, you carry out the preparations for TSPITR described in "Planning and Preparing for TSPITR", and then issue the RECOVER TABLESPACE command, specifying, at a minimum, the tablespaces of the recovery set and the target time for the point-in-time recovery, and, if desired, an auxiliary destination as well.

RMAN then carries out the following steps:

1. If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
2. Takes the tablespaces to be recovered offline in the target database
3. Restores a backup controlfile from a point in time before the target time to the auxiliary instance
4. Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance. Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE)
5. Recovers the restored datafiles in the auxiliary instance to the specified time
6. Opens the auxiliary database with the RESETLOGS option
7. Exports the dictionary metadata about objects in the recovered tablespaces to the target database
8. Shuts down the auxiliary instance
9. Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
10. Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
11. Deletes all auxiliary set files.

At that point the TSPITR process is complete. The recovery set datafiles are returned to their contents at the specified point in time, and belong to the target database.

Deciding When to Use TSPITR

Like a table import, RMAN TSPITR enables you to recover a consistent data set; however, the data set recovered includes an entire tablespace rather than one object.

RMAN TSPITR is most useful for situations such as these:

• Recovering data lost after an erroneous TRUNCATE TABLE statement;
• Recovering from logical corruption of a table;
• Undoing the effects of an incorrect batch job or other DML statement that has affected only a subset of the database;
• Recovering 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.

Note that, as with database point-in-time recovery (DBPITR), you cannot perform TSPITR if you do not have your archived redo logs. For databases running in NOARCHIVELOG mode, you cannot perform TSPITR.

Limitations of TSPITR

There are a number of situations which you cannot resolve by using TSPITR.

• You cannot recover dropped tablespaces.
• You cannot recover a renamed tablespace to a point in time before it was renamed. If you try to perform a TSPITR to an SCN earlier than the rename operation, RMAN cannot find the new tablespace name in the repository as of that earlier SCN (because the tablespace did not have that name at that SCN).

  In this situation, you must recover the entire database to a point in time before the tablespace was renamed. The tablespace will be found under the name it had at that earlier time.

• You cannot recover tables without their associated constraints, or constraints without the associated tables.
• You cannot use TSPITR to recover any of the following:
  • Replicated master tables
  • Partial tables (for example, if you perform RMAN TSPITR on partitioned tables and spread partitions across multiple tablespaces, then you must recover all tablespaces which include partitions of the table.)
  • Tables with VARRAY columns, nested tables, or external files
  • Snapshot logs and snapshot tables
  • Tablespaces containing undo or rollback segments
  • Tablespaces that contain objects owned by SYS, including rollback segments

TSPITR has some other limitations:

• If a datafile was added after the point to which RMAN is recovering, an empty datafile by the same name will be included in the tablespace after RMAN TSPITR.
• TSPITR will not recover query optimizer statistics for recovered objects. You must gather new statistics after the TSPITR.
• 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. 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. Therefore, you must back up the tablespace as soon as TSPITR is complete.

Limitations of TSPITR Without a Recovery Catalog

If you do not use a recovery catalog when performing TSPITR, then note the following special restrictions:

• The undo segments at the time of the TSPITR must be part of 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 undo segments have changed since that time, then TSPITR will fail.
• TSPITR to a time that is too old may not succeed if Oracle has reused the control file records for needed backups. (In planning your database, set the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter to a value large enough to ensure that control file records needed for TSPITR are kept.)
• When not using a recovery catalog, 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. You can, however, perform incomplete recovery of the whole database to any time less than or equal to t, if you can restore a backup control file from before time t.

Planning and Preparing for TSPITR

There are several steps to be carried out in preparing for TSPITR:

• Choosing the Right Target Time for TSPITR
• Determining the Recovery Set: Analyzing Data Relationships
• Identifying and Preserving Objects That Will Be Lost After TSPITR

Choosing the Right Target Time for TSPITR

It is extremely important that you choose the right target time or SCN for your TSPITR. As noted already, once you bring a tablespace online after TSPITR, you cannot use any backup from a time earlier than the moment you brought the tablespace online. In practice, this means that you cannot make a second attempt at TSPITR if you choose the wrong target time the first time, unless you are using a recovery catalog. (If you have a recovery catalog, however, you can perform repeated TSPITRs to different target times.)

For example, assume that you run TSPITR on a tablespace, and then bring the tablespace online at 5PM on Friday. Backups of the tablespace created before 5PM Friday are no longer usable for recovery with a current control file. You cannot run TSPITR again on this tablespace with a target time earlier than 5PM Friday, nor can you use the current control file to recover the database to any time earlier than 5PM Friday. Your only option will be point-in-time recovery of your entire database using a restored control file.

To investigate past states of your data to identify the target time for TSPITR, you can use features of Oracle such as Oracle Flashback Query, Oracle Transaction Query and Oracle Flashback Version Query to find the point in time when unwanted database changes occurred. See "Oracle Flashback Query: Recovering at the Row Level" for more details on Flashback Query, and Oracle Database Application Developer's Guide - Fundamentals for more information on Flashback Transaction Query and Flashback Version Query.

Determining the Recovery Set: Analyzing Data Relationships

Your recovery set starts out including the datafiles for the tablespaces you wish to recover. If, however, objects in the tablespaces you need have relationships (such as constraints) to objects in other tablespaces, you will have to account for this relationship before you can perform TSPITR. You have three choices when faced with such a relationship:

• Add the tablespace including the related objects to your recovery set
• Remove the relationship
• Suspend the relationship for the duration of TSPITR

Identifying and Resolving Dependencies on the Primary Database

The TS_PITR_CHECK view lets you identify relationships between objects that span 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 re-create any suspended or removed relationships after completing TSPITR.

The following query illustrates how to use the TS_PITR_CHECK view. For an example with an initial 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. To perform TSPITR, you must either drop tpind or include id1 and id2 in the recovery set.

See Also: Oracle Database Reference for more information about the TS_PITR_CHECK view

Identifying and Preserving Objects That Will Be Lost After TSPITR

When RMAN TSPITR is performed on a tablespace, any objects created after the target recovery time are lost. You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.

To see which objects will be lost in TSPITR, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database. The contents of the view are described in Table 10-1.

Table 10-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.

Filter the view for objects whose CREATION_TIME is after the target time for TSPITR. For example, with a recovery set consisting of users and tools, and a recovery point in time of November 2, 2002, 7:03:11 AM, 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('02-NOV-02:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

(The TO_CHAR and TO_DATE functions are used to avoid issues with different national date formats. You can, of course, use local date formats in your own work.)

See Also: Oracle Database Reference for more information about the TS_PITR_OBJECTS_TO_BE_DROPPED view

Performing Basic RMAN TSPITR

Having selected your tablespaces to recover and your target time, you are now ready to perform RMAN TSPITR. You have a few different options available to you:

• Fully automated TSPITR--in which you specify an auxiliary destination and let RMAN manage all aspects of the TSPITR. This is the simplest way to perform TSPITR, and is recommended unless you specifically need more control over the location of recovery set files after TSPITR or auxiliary set files during TSPITR, or control over the channel configurations or some other aspect of your auxiliary instance.
• Customized TSPITR with an automatic auxiliary instance--in which you base your TSPITR on the behavior of fully automated TSPITR, possibly still using an auxiliary destination, but customize one or more aspects of the behavior, such as the location of auxiliary set or recovery set files, or specifying initialization parameters or channel configurations for the auxiliary instance created and managed by RMAN.
• TSPITR with your own auxiliary instance--in which you take responsibility for setting up, starting, stopping and cleaning up the auxiliary instance used in TSPITR, and possibly also manage the TSPITR process using some of the methods available in customized TSPITR with an automatic auxiliary instance.

Fully Automated RMAN TSPITR

When performing fully automated TSPITR, letting RMAN manage the entire process, there are only two requirements beyond the preparations in "Planning and Preparing for TSPITR":

• You must specify the auxiliary destination for RMAN to use for the auxiliary set datafiles and other files for the auxiliary instance.
• You must configure any channels required for the TSPITR on the target instance. (The auxiliary instance will use the same channel configuration as the target instance when performing the TSPITR.)

RMAN bases as much of the configuration for TSPITR as possible on your target database. During TSPITR, the recovery set datafiles are written in their current locations on the target database. The same channel configurations in effect on the target database are used on the auxiliary instance when restoring files from backup. Auxiliary set datafiles and other auxiliary instance files, however, are stored in the auxiliary destination.

Using an Auxiliary Destination

Oracle Corporation recommends that you use an auxiliary destination with your auxiliary instance. Even if you use other methods to rename some or all of the auxiliary set datafiles, specifying an AUXILIARY DESTINATION parameter provides a default location for auxiliary set datafiles for which names are not specified. This way, TSPITR will not fail if you inadvertently do not provide names for all auxiliary set datafiles.

To specify an auxiliary destination, find a location on disk where there is enough space to hold your auxiliary set datafiles. Then, use the AUXILIARY DESTINATION parameter in your RECOVER TABLESPACE command to specify the auxiliary destination location, as shown in the next section.

Performing Fully Automated RMAN TSPITR

To actually peform automated RMAN TSPITR, start the RMAN client, connecting to the target database and, if applicable, a recovery catalog. This example shows connecting in NOCATALOG mode, using operating system authentication:

% rman TARGET /

Note: Do not connect to an auxiliary instance when starting the RMAN client for automated TSPITR. If there is no connected auxiliary instance, RMAN constructs the automatic auxiliary instance for you when carrying out the RECOVER TABLESPACE command. (If there is a connected auxiliary instance, RMAN will assume that you are trying to manage your own auxiliary instance, and try to use the connected auxiliary for TSPITR.)

If you have configured channels that RMAN can use to restore from backup on the primary instance, then you are ready to perform TSPITR now, by running the RECOVER TABLESPACE... UNTIL... command.

This example returns the users and tools tablespaces to the end of log sequence number 1300, and stores the auxiliary instance files (including auxiliary set datafiles) in the destination /disk1/auxdest:

RMAN> RECOVER TABLESPACE users, tools 
     UNTIL LOGSEQ 1300 THREAD 1
      AUXILIARY DESTINATION '/disk1/auxdest';

Assuming the TSPITR process completes without error, the tablespaces are taken offline by RMAN, restored from backup and recovered to the desired point in time on the auxiliary instance, and then re-imported to the target database. The tablespaces are left offline at the end of the process. All auxiliary set datafiles and other auxiliary instance files are cleaned up from the auxiliary destination.

Tasks to Perform After Successful TSPITR

If TSPITR completes successfully, you must back up the recovered tablespaces, and then you can bring them online.

Backing Up Recovered Tablespaces After TSPITR

It is very important that you backup recovered tablespaces immediately after TSPITR is completed.

After you perform TSPITR on a tablespace, you cannot use backups of that tablespace from before the TSPITR was completed and the tablespace put back on line. If you start using the recovered tablespaces without taking a backup, you are running your database without a usable backup of those tablespaces. For this example, the users and tools tablespaces must be backed up, as follows:

RMAN> BACKUP TABLESPACE users, tools;

You can then safely bring the tablespaces online, as follows:

RMAN> SQL "ALTER TABLESPACE users, tools ONLINE";

Your recovered tablespaces are now ready for use.

Handling Errors in Automated TSPITR

In the event of an error during automated TSPITR, you should refer to "Troubleshooting RMAN TSPITR". The auxiliary set datafiles and other auxiliary instance files will be left in place in the auxililary destination as an aid to troubleshooting. The state of the recovery set files is determined by the type of failure. Once you resolve the problem, you can try your TSPITR operation again.

Performing Customized RMAN TSPITR with an RMAN-Managed Auxiliary Instance

There are several aspects of RMAN TSPITR which you can customize while still mostly following the basic procedure described in "Fully Automated RMAN TSPITR":

• Renaming or relocating your recovery set datafiles, so that the datafiles making up the recovered tablespaces are not stored in the original locations after TSPITR
• Specifying a location other than the auxiliary destination for some or all auxiliary set datafiles (or not using an auxiliary destination at all)
• Setting up image copy backups of your datafiles in advance, to speed up TSPITR by avoiding restores from backup
• Using a different channel configuration for the auxiliary instance
• Specifying different initialization parameters for your RMAN-managed auxiliary instance

Renaming TSPITR Recovery Set Datafiles with SET NEWNAME

You may not want the recovery set datafiles restored and recovered in their original locations. The SET NEWNAME command, used in a RUN block, lets you specify a new destination for the restore from backup and recovery of a datafile.

Note: CONFIGURE AUXNAME can be used to rename recovery set datafiles as well, but the effects of doing so are quite different and the two commands cannot be used interchangeably. (They do interact, in that if you use SET NEWNAME to rename a file, this takes precedence over any renaming performed with CONFIGURE AUXNAME.) Refer to the discussion of "Using Image Copies for Faster TSPITR Performance" for details.

Create a RUN block and use SET NEWNAME commands within it to specify new recovery set filenames, as shown here:

RUN {
...
   SET NEWNAME FOR DATAFILE 'ORACLE_HOME/oradata/trgt/users01.dbf'
      TO '/newfs/users01.dbf';
...other setup commands...
   RECOVER TABLESPACE users, tools UNTIL SEQUENCE 1300 THREAD 1;
}

RMAN restores the specified datafile from backup to the new location during TSPITR and recovers it in the new location, and updates the control file so that the newly recovered datafile replaces the old one in the control file. Any existing image copy backup of a datafile found at the new specified location is overwritten.

If the name specified with SET NEWNAME conflicts with the name of a valid datafile in the target database, then RMAN reports an error while executing the RECOVER command. The valid datafile is not overwritten.

Note that RMAN does not detect conflicts between names set with SET NEWNAME and current datafile names on the target database until the actual RECOVER TABLESPACE... UNTIL operation. At that point, the conflict is detected, TSPITR fails and RMAN reports an error. If you rename your recovery set datafiles, be sure to assign them names that do not conflict with each other, or with the names of your current datafiles.

Renaming TSPITR Auxiliary Set Datafiles

Unlike the recovery set datafiles, which can be and usually are stored in their original locations, the auxiliary set datafiles must not overwrite the corresponding original files in the target database. If you do not specify a location for an auxiliary set file that is different from its original location, then TSPITR will fail when RMAN attempts to overwrite the corresponding file in the original database and discover that the file is in use.

The simplest way to provide locations for your auxiliary set datafiles is to specify an auxiliary destination for TSPITR. However, RMAN supports two other methods of controlling the location of your auxiliary set datafiles: specifying new names for individual files with SET NEWNAME, and using DB_FILE_NAME_CONVERT to provide rules for converting datafile names in the target database to datafile names for the auxiliary database.

Even if you intend to use either of these methods to provide locations for specific files, it is still suggested that you provide an AUXILIARY DESTINATION argument to RECOVER TABLESPACE. This will ensure that, if you overlook renaming some auxiliary set datafiles, your TSPITR will still succeed. Any files not otherwise renamed will be placed in the auxiliary destination.

Renaming TSPITR Auxiliary Set Datafiles with SET NEWNAME

To use the SET NEWNAME command to specify a new name for an auxiliary set datafile, enclose your RECOVER TABLESPACE command in a RUN block, and use a SET NEWNAME command within the RUN block to rename the file. For example:

RMAN> RUN
{
  SET NEWNAME FOR DATAFILE '?/oradata/prod/system01.f'
    TO '/disk1/auxdest/system01.f'
  RECOVER TABLESPACE users, tools 
     UNTIL LOGSEQ 1300 THREAD 1
     AUXILIARY DESTINATION '/disk1/auxdest';
}

The resulting behavior depends upon whether there is a file at /disk1/auxdest/system01.f when the RECOVER TABLESPACE command is executed. If there is an image copy backup of the file ?/oradata/system01.f at the specified location, created at an SCN prior to the target time for TSPITR, then the behavior is as described in "SET NEWNAME and CONFIGURE AUXNAME With Auxiliary Set Image Copies". Otherwise, the auxiliary set datafile will be restored to the NEWNAME specified instead of the default location. If your intention is only to control where the auxiliary set datafiles are stored, you should make sure that there is no file stored at the location specified by SET NEWNAME before performing your TSPITR.

Using DB_FILE_NAME_CONVERT to Name Auxiliary Set Datafiles

If you do not want to use an auxiliary destination for all of your auxiliary set datafiles, but you also do not want to name every file individually, you can include a DB_FILE_NAME_CONVERT initialization parameter in the initialization parameter file used by your auxiliary instance. You can only use this method in two circumstances:

• If you are creating your own initialization parameter file for RMAN's automatically managed auxiliary instance, as described in "Customizing Initialization Parameters for the Automatic Auxiliary Instance";
• If you are creating your own auxiliary instance, as described in "Performing RMAN TSPITR Using Your Own Auxiliary Instance".

Refer to the appropriate discussion for your circumstance, to see how to add a parameter to your initialization parameter file.

The DB_FILE_NAME_CONVERT parameter in the auxiliary instance specifies how to derive names for files in the auxiliary instance from the original names of the corresponding files in the target instance.

For example, assume that the target instance contains the following files:

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

and you need to locate the corresponding files in the auxiliary instance in '/bigtmp', then you would add the following line to the auxiliary instance parameter file:

DB_FILE_NAME_CONVERT=('?/oradata/trgt', '/bigtmp')

The most important thing to remember is that DB_FILE_NAME_CONVERT needs to be present in the auxiliary instance parameter file.

If the auxiliary instance was manually created, add DB_FILE_NAME_CONVERT to the auxiliary instance parameter file (wherever it resides).

Note that you can still rename individual auxiliary set datafiles using SET NEWNAME or CONFIGURE AUXNAME. Also, files that do not match the patterns provided in DB_FILE_NAME_CONVERT will not be renamed. You may wish to use the AUXILIARY DESTINATION parameter of RECOVER TABLESPACE to ensure that all auxiliary set datafiles are sent to some destination. If a file is not renamed at all, TSPITR will fail.

Order of Precedence Among File Renaming Methods

The different methods of renaming files follow an order of precedence, as follows:

• SET NEWNAME
• CONFIGURE AUXNAME
• DB_FILE_NAME_CONVERT
• AUXILIARY DESTINATION argument to RECOVER TABLESPACE

Settings higher on the list override settings lower on the list, in situations where both have been applied (by, for example, running RECOVER TABLESPACE... AUXILIARY DESTINATION on a target database where some auxiliary set datafiles also have auxnames configured with CONFIGURE AUXNAME).

Specifying Auxiliary Instance Control File Location

You can specify your own location for the control file of your auxiliary instance, if you use a client-side initialization parameter file. Set the CONTROL_FILES initialization parameter to specify any location you wish for the control files.

If you do not explicitly specify a location for the control file, RMAN will locate it in the auxiliary destination if you use the AUXILIARY DESTINATION parameter when performing TSPITR. If you do not use an AUXILIARY DESTINATION parameter, the auxiliary instance control files are stored in an operating system-specific location. (on Unix, ORACLE_HOME/rdbms/admin/params_auxinit.ora).

No matter where you store your auxiliary instance control file, it is removed at the end of a successful TSPITR operation. Because control files are relatively small, it is rare that RMAN will encounter a problem creating an auxiliary control file, but if there is not enough space to create the control file, TSPITR will fail.

Specifying Auxiliary Instance Online Log Location

If you specify the LOG_FILE_NAME_CONVERT initialization parameter in your auxiliary instance parameter file, this parameter will determine the online redo log location. Otherwise, if RMAN is using an auxiliary destination and managing the auxiliary instance for you, it creates the online redo log in the auxiliary destination.

Note: If you do not specify a location for the online redo logs using LOG_FILE_NAME_CONVERT or AUXILIARY DESTINATION, your TSPITR will fail trying to create the online redo logs. Even if DB_FILE_CREATE_DEST or LOG_FILE_CREATE_DEST are specified in the initialization parameter file, in TSPITR they do not control the creation of the online redo logs of the auxiliary instance.

Using Image Copies for Faster TSPITR Performance

TSPITR performance can be greatly enhanced by redirecting RMAN to use existing image copies of the recovery set and auxiliary set datafiles on disk in TSPITR, rather than restoring them from backup. You can use the CONFIGURE AUXNAME command with image copies of recovery set datafiles or auxiliary set datafiles, or the SET NEWNAME command with image copies of auxiliary set datafiles, to tell RMAN about the possible existence of an image copy of a datafile.

While exact details vary depending on the command used and whether the file is an auxiliary set or recovery set file, in general, if a suitable image copy is available in the specified location, then during TSPITR, RMAN uncatalogs the image copy from the RMAN repository of the target instance, and catalogs it in the control file of the auxiliary instance. The auxiliary instance then performs point-in-time recovery using the image copy.

Details of using image copies with each type of file are explained in the following sections.

Using CONFIGURE AUXNAME With Recovery Set Image Copies

During TSPITR, RMAN looks in the specified AUXNAME location for the datafile, to see whether the file there is an image copy backup of the datafile, with a checkpoint SCN early enough that it can be recovered to the target time for TSPITR. If such an image copy is found, it is used in TSPITR. Otherwise, the datafile is restored and recovered in its original location, and any file in the location specified by the AUXNAME is not changed or deleted.

RMAN> CONFIGURE AUXNAME FOR DATAFILE 'ORACLE_HOME/oradata/trgt/users01.dbf'
      TO '/newfs/users1.dbf';
...other RMAN commands, if any...
RMAN> RECOVER TABLESPACE users, tools UNTIL SEQUENCE 1300 THREAD 1;

CONFIGURE AUXNAME is meant to be used as the basis of a strategy to make TSPITR faster by eliminating restore times. If you have tablespaces on which you anticipate performing TSPITR, you can maintain a set of image copies of the affected datafiles, updated periodically to the earliest point to which you expect to perform TSPITR. The expected usage model is:

• Configure the AUXNAME for the file once
• Perform "BACKUP AS COPY DATAFILE n FORMAT auxname" regularly to maintain the updated image copy, or use an incrementally updated backups strategy as described in Oracle Database Backup and Recovery Basics to keep the image copies up to date without performing full backups of the datafiles
• When TSPITR is needed, specify a target time since the last update of the image copy.

In planning for TSPITR with image copies, remember that you may not know know which tablespaces will require image copies in advance. As discussed in "Determining the Recovery Set: Analyzing Data Relationships", relationships between the tablespaces you wish to TSPITR and other tablespaces may require that you add tablespaces to your final recovery set, and still other tablespaces may wind up in the auxiliary set. You should configure an AUXNAME for each datafile that is likely to be required, and update image copies of all datafiles often. However, TSPITR will still work if only a subset of datafiles are prepared in advance using this strategy. The process will just take longer, and recover recovery set datafiles for which there are no image copies in their original locations.

Note thatthe order of precedence of naming methods is still respected when you use CONFIGURE AUXNAME to rename a recovery set file. A SET NEWNAME for a recovery set file will override the effect of the CONFIGURE AUXNAME command for the same file. Behavior in this instance will be as described in "Renaming TSPITR Recovery Set Datafiles with SET NEWNAME". SET NEWNAME used with a recovery set file never refers to an image copy file.

SET NEWNAME and CONFIGURE AUXNAME With Auxiliary Set Image Copies

As with recovery set datafiles, CONFIGURE AUXNAME sets a persistent alternative location for an auxiliary set datafile image copy, and SET NEWNAME sets an alternative location for the duration of a RUN block. However, RMAN handles values for auxiliary set datafiles differently from recovery set datafiles.

If SET NEWNAME is used to specify a new location for an auxiliary set datafile, and there is an image copy at that location with an SCN such that it can be used in TSPITR, then the image copy will be used. If there is no usable image copy at that location, however, RMAN will restore a usable copy from backup. (If an image copy is present but the SCN is after the target time for TSPITR, then the datafile is overwritten by the restored file.)

If CONFIGURE AUXNAME is used to specify a new location for an auxiliary set datafile, and there is an image copy at that location with an SCN such that it can be used in TSPITR, then the image copy will be used. If there is no usable copy at the specified location, the file is restored to this location from bcakup.

As with all auxiliary set files, the file is deleted after successful TSPITR, or left for use in troubleshooting if TSPITR fails, regardless of whether it was an image copy created before TSPITR or restored by RMAN from backup during TSPITR.

TSPITR With CONFIGURE AUXNAME and Image Copies: Scenario

You have enough disk space to save image copies of your entire database for use in TSPITR. In preparation for the possibility that you need perform TSPITR, you perform the following tasks:

• Configure an AUXNAME for each datafile in your database using:

  CONFIGURE AUXNAME FOR DATAFILE n TO auxname_n;
  
  

• Every week on Sunday night you take an image copy of the database which is placed in the files referenced by the configured AUXNAMEs:

                BACKUP AS COPY DATAFILE n FORMAT auxname_n
  
  

  Note that if the image copies are all in the same location on disk and named similarly to the original datafiles, it is possible to use FORMAT or DB_FILE_NAME_CONVERT options of the BACKUP command and use BACKUP AS COPY DATABASE instead of performing individual backups of every datafile. For example if the configured auxnames are a simple translation of the location 'maindisk' to 'auxdisk', you could use the following backup command:

  BACKUP AS COPY DATABASE DB_FILE_NAME_CONVERT=(maindisk, auxdisk);
  
  

You are then prepared for TSPITR without restoring from backup. If, for example, an erroneous batch job started at November 15 2003, 19:00:00 updates incorrectly the tables in the tablespace PARTS. You could use the following command to perform TSPITR on tablespace PARTS:

RECOVER TABLESPACE parts UNTIL TIME 'November 15 2003, 19:00:00';

Because AUXNAMES are configured and refer to datafile copies from an SCN before the TSPITR target time, the auxiliary set and recovery set datafiles are not restored from backup. Instead the datafile copies are directly used in recovery, eliminating the restore overhead.

Note that at the end of the TSPITR, the tablespace PARTS will not be located in the original datafile locations, but in the auxname locations. If only the auxnames for the auxiliary set should be used (so that the recovery set is left in its original locations), then CONFIGURE AUXNAME ... CLEAR should be used before TSPITR is started. In such a case, though, note that the datafiles will have to be restored.

Customizing Initialization Parameters for the Automatic Auxiliary Instance

The automatic auxiliary instance looks for parameters in a file that is operating system dependent (for Unix this location is ?/rdbms/admin/params_auxint.ora, where '?' stands for ORACLE_HOME, and the file is located on the node running the RMAN client, not necessarily the same node as the one running the database instances) This default parameter file for the automatic auxiliary instance is always searched when TSPITR is performed. If the file is not found RMAN does not generate an error.

Another way to specify parameters for the automatic auxiliary instance is to place the initializations parameter in a file, and then provide the location of these file with the SET AUXILIARY INSTANCE PARAMETER command before executing TSPITR. (Note that the path specified when using SET AUXILIARY INSTANCE PARAMETER is a path on the system running the RMAN client, not the target or auxiliary instances.)

RMAN defines the following basic parameters for the automatic auxiliary instance:

• DB_NAME - Same as db_name of the target database
• DB_UNIQUE_NAME - Generated, based on the DB_NAME, to be unique
• DB_BLOCK_SIZE - Same as the DB_BLOCK_SIZE of the target database
• COMPATIBLE - Same as the compatible setting of the target database

If AUXILIARY DESTINATION is used, RMAN also defines:

• DB_CREATE_FILE_DEST - Set to the auxiliary destination
• CONTROL_FILES - Generated filename in the auxiliary destination

When an auxiliary destination is specified, RMAN uses these two parameters in creating the auxiliary instance online logs and control files in the auxiliary destination.

If AUXILIARY DESTINATION is not used, then you must use LOG_FILE_NAME_CONVERT in an auxiliary instance parameter file to specify the online log file names. Otherwise, TSPITR fails when attempting to create the online logs for the automatic instance.

If AUXILIARY DESTINATION is not used and you do not use CONTROL_FILES in an auxiliary instance parameter file, the auxiliary instance will create one controlfile with an operating system-dependent name in an operating system dependent location. (In Unix, it defaults to ?/dbs/cntrl_@.dbf, where '?' stands for ORACLE_HOME and '@' stands for ORACLE_SID. For an automatic auxiliary instance, ORACLE_SID is randomly generated by RMAN).

It is rarely necessary, however, to alter the parameter file, especially if you provide an AUXILIARY DESTINATION argument to RECOVER TABLESPACE. If one of the six basic initialization parameters is overridden in the auxiliary instance parameter file, it might cause TSPITR to fail. However, other parameters besides these basic parameters can be added if needed. For example you can use DB_FILE_NAME_CONVERT to specify the names of the datafiles in the auxiliary set.

Performing RMAN TSPITR Using Your Own Auxiliary Instance

Oracle Corporation recommends that you allow RMAN to manage the creation and destruction of the auxiliary instance used during RMAN TSPITR. However, creating and using your own auxiliary instance is also supported. One reason you might want to do this is to exercise control of channels used in TSPITR. RMAN's automatic auxiliary instance uses the configured channels of the target database as the basis for the channels to configure on the auxiliary instance and use during backup. If you need different channel settings, and you do not want to use CONFIGURE to change the settings on the target database, you can operate your own auxiliary instance.

Preparing Your Own Auxiliary Instance for RMAN TSPITR

Creating an Oracle instance suitable for use as an auxiliary instance requires that you carry out all of the following steps:

• Step 1: Create an Oracle Password File for the Auxiliary Instance
• Step 2: Create an Initialization Parameter File for the Auxiliary Instance
• Step 3: Check Oracle Net Connectivity to the Auxiliary Instance
• Step 1: Start the Auxiliary Instance in NOMOUNT Mode

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

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

Step 2: Create an Initialization Parameter File for the Auxiliary Instance

Create a client-side initialization parameter file for the auxiliary instance on the machine where you will be running SQL*Plus to contorl the auxiliary instance. For this example, we will assume your parameter file is placed at /tmp/initAux.ora. Set the parameters described in the following table, making sure that paths in parameters like DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT and CONTROL_FILES are all server-side paths, not client-side.

Table 10-2 Initialization Parameters in the Auxiliary Instance

Parameter	Mandatory?	Value
DB_NAME	YES	The same name as the target database.
DB_UNIQUE_NAME	YES	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.
LOG_FILE_NAME_CONVERT	YES	Patterns to generate filenames for the online redo logs of the auxiliary database based on the online redo log names of the target database. Query V$LOGFILE.MEMBER, to obtain target instance online log names, and ensure that the conversion pattern matches the format of the filename displayed in the view. This parameter is the only way to name the online redo logs for the auxiliary instance. Without it, TSPITR will fail when trying to open the auxiliary instance because the online logs cannot be created. Note: Some platforms do not support ending patterns in a forward or backward slash (\ or /).
REMOTE_LOGIN_PASSWORDFILE	YES	Set to EXCLUSIVE when connecting to the auxiliary instance by means of a password file. Otherwise, set to NONE.
COMPATIBLE	YES	The same value as the parameter in the target database.
DB_BLOCK_SIZE	YES	If this initialization parameter is set in the target database, then it must be set to the same value in the auxiliary instance.
DB_FILE_NAME_CONVERT	NO	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. You can also specify this parameter on the RECOVER command itself. Note: Some platforms do not support ending patterns in a forward or backward slash (\ or /). See Also: "Using DB_FILE_NAME_CONVERT to Name Auxiliary Set Datafiles"
CONTROL_FILES	NO	Filenames that do not conflict with the control file names of the target instance (or any other existing file).

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
DB_UNIQUE_NAME=_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 =10.1.0
DB_BLOCK_SIZE=8192

Note: After setting these initialization parameters, ensure that you do not overwrite the initialization settings for the production files at the target database.

See Also: Oracle Net Services Administrator's Guide for more information about Oracle Net

Step 3: Check 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.

Preparing RMAN Commands for TSPITR with Your Own Auxiliary Instance

If you are running your own auxiliary instance, then you may find that the sequence of commands required for TSPITR is quite long, if you allocate a complex channel configuration for restoring from backup, or if you are not using DB_FILE_NAME_CONVERT to control file naming.

You may wish to store the sequence of commands for TSPITR in a command file, a text file under the host operating system. This command file can be read into RMAN using the @ command (or the CMDFILE command line argument when starting RMAN) to execute the series of commands in the command file.

See "Using RMAN with Command Files" for more details.

Planning Channels for TSPITR with Your Own Auxiliary Instance

When you run your own auxiliary instance, the default behavior is to use the automatic channel configuration of the target instance. However, if you decide to allocate your own channel configuration, you can do so by including the ALLOCATE AUXILIARY CHANNEL commands in a RUN block along with the RECOVER TABLESPACE command for TSPITR. Plan out these commands, if necessary, and add them to the sequence of commands you will run to perform your TSPITR.

See the example in "Executing TSPITR With Your Own Auxiliary Instance: Scenario" for details of how to include channel allocation in your TSPITR script.

Planning Datafile Names with Your Own Auxiliary Instance: SET NEWNAME

You may wish to use SET NEWNAME commands, either to refer to existing image copies of auxiliary set files to improve TSPITR performance, or to assign new names to the recovery set files for after TSPITR. Plan out these commands, if necessary, and add them to the sequence of commands you will run to perform your TSPITR.

Executing TSPITR with Your Own Auxiliary Instance

With the preparations complete and your TSPITR commands completely planned, you are now ready to carry out your TSPITR. The following steps are required:

• Step 1: Start the Auxiliary Instance in NOMOUNT Mode
• Step 2: Connect the RMAN Client to Target and Auxiliary Instances
• Step 3: Execute the RECOVER TABLESPACE Command

Step 1: Start the Auxiliary Instance in NOMOUNT Mode

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'

Remember that the path for the PFILE will be a client-side path, on the machine from which you run SQL*Plus, not a server-side path.

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.

Step 2: Connect the RMAN Client to Target and Auxiliary Instances

Start RMAN connecting to the target and the manually created auxiliary instance:

% rman target / auxiliary sysuser/syspwd@auxiliary_service_name

Step 3: Execute the RECOVER TABLESPACE Command

Now you are ready to run your TSPITR commands. In the simplest case, just execute the RECOVER TABLESPACE... UNTIL command at the RMAN prompt:

RMAN> RECOVER TABLESPACE ts1, ts2... UNTIL TIME 'time'

If you want to use ALLOCATE CHANNEL or SET NEWNAME then create a RUN block which includes those commands before the RECOVER TABLESPACE command.

RUN {
   ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
   ALLOCATE CHANNEL c2 DEVICE TYPE SBT;
  # and so on...
   RECOVER TABLESPACE ts1, ts2 UNTIL TIME 'time';
}

Using a Command File for TSPITR

Entering a lengthy series of commands in a RUN block can be error-prone. To avoid making mistakes entering the sequence of commands, create a command file (called, for example, /tmp/tspitr.rman) to store the whole sequence of commands for your TSPITR. Review it carefully to catch any errors. Then run the command file at the RMAN prompt, using this command:

RMAN> @/tmp/tspitr.rman ;

The results will be the same as in the previous example.

Executing TSPITR With Your Own Auxiliary Instance: Scenario

The following example shows the execution of a RECOVER TABLESPACE... UNTIL operation using the following features of RMAN TSPITR:

• Managing your own auxiliary instance
• Configuring channels for restore of backups from disk and sbt
• Using recoverable image copies for some auxiliary set datafiles using SET NEWNAME
• Specifying new names for recovery set datafiles using SET NEWNAME

The process used is as follows:

1. Prepare the auxiliary instance as described in "Preparing Your Own Auxiliary Instance for RMAN TSPITR". Specify "tspitr" as the password for the auxiliary instance in the password file, and set up the auxiliary instance parameter file /bigtmp/init_tspitr_prod.ora with the following settings:

   db_name=PROD
   db_unique_name=tspitr_PROD
   control_files=/bigtmp/tspitr_cntrl.f'
   db_file_name_convert=('?/oradata/prod', '/bigtmp')
   log_file_name_convert=('?/oradata/prod', '/bigtmp')
   compatible=10.1.0
   block_size=8192
   remote_login_password=exclusive
   
   

2. Create service name pitprod for the auxiliary instance, and check for connectivity.
3. Start the auxiliary instance in NOMOUNT state, as shown:

   $ sqlplus
   SQL> connect sys/tspitr@pit_prod as sysdba
     SQL> startup nomount pfile=/bigtmp/init_tspitr_prod.ora
   
   

4. Start up RMAN, connecting to the auxiliary instance:

   % rman target / auxiliary sys/tspitr@pit_prod 
   
   

5. Enter the following commands, in a RUN block, to set up and execute the TSPITR:

   run {
   # Specify NEWNAMES for recovery set datafiles
       SET NEWNAME FOR DATAFILE '?/oradata/prod/clients01.f' 
                                     TO '?/oradata/prod/clients01_rec.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/clients02.f'
                                     TO '?/oradata/prod/clients02_rec.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/clients03.f'
                                     TO '?/oradata/prod/clients03_rec.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/clients04.f'
                                     TO '?/oradata/prod/clients04_rec.f';
   
   # Specified newnames for some of the auxiliary set 
   # datafiles that have a valid image copy to avoid restores:
               SET NEWNAME FOR DATAFILE '?/oradata/prod/system01.f'
                                     TO '/backups/prod/system01_monday_noon.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/system02.f'
                                     TO '/backups/prod/system02_monday_noon.f';
               SET NEWNAME FOR DATAFILE '?/oradata/prod/undo01.f'
                                     TO '/backups/prod/undo01_monday_noon.f';
   
   # Specified the disk and SBT channels to use
               allocate auxiliary channel c1 device type disk;
               allocate auxiliary channel c2 device type disk;
               allocate auxiliary channel t1 device type sbt;
               allocate auxiliary channel t2 device type sbt;
   
   # Recovered the clients tablespace to 24 hours ago:
       RECOVER TABLESPACE clients UNTIL TIME 'sysdate-1';
      }
   
   

If the TSPITR operation is successful, then the results are:

• The recovery set datafiles are registered in the target database control file under the names specified with SET NEWNAME, with their contents as of the time specified time for the TSPITR.
• The auxiliary files are removed by RMAN, including the control files, online logs and auxiliary set datafiles of the auxiliary instance
• The auxiliary instance is shut down

If the TSPITR operation fails, the auxiliary files are left on disk for troubleshooting purposes. If RMAN created the auxiliary instance, it is shut down; otherwise it is left in whatever state it was in when the TSPITR operation failed.

Troubleshooting RMAN TSPITR

A variety of problems can cause TSPITR to fail before the process is complete.

• There can be name conflicts between files already in the target database, filenames assigned by the SET NEWNAME or CONFIGURE AUXNAME commands, and filenames generated by the effect of the DB_FILE_NAME_CONVERT parameter.
• When RMAN exports the metadata about recovered objects from the auxiliary instance, it uses space in the temporary tablespace for sorting. If there is insufficient space in the temporary tablespace for the sorting operation, you need to increase the amount of sort space available.

Troubleshooting TSPITR Example: Filename Conflicts

If your uses of SET NEWNAME, CONFIGURE AUXNAME and DB_FILE_NAME_CONVERT cause multiple files in the auxiliary or recovery sets to have the same name, RMAN will report an error during TSPITR. To correct the problem, use different values for these parameters to eliminate the duplicate name.

Troubleshooting TSPITR Example: Insufficient Sort Space during Export

In this case, you need to edit the recover.bsq file, wherever it resides on your host platform. For instance, on UNIX, it is located in $ORACLE_HOME/rdbms/admin. This file contains the following:

# 
# tspitr_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.

Troubleshooting: Restarting Manual Auxiliary Instance After TSPITR Failure

If you are managing your own auxiliary instance and there is a failure in TSPITR, then before you can try TSPITR again, you must shut down the auxiliary instance, correct the problem which interfered with TSPITR, and then bring the auxiliary instance back to NOMOUNT before trying TSPITR again.