20 Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)

This chapter explains how to perform RMAN tablespace point-in-time recovery. This chapter contains the following sections:

Overview of RMAN TSPITR

This section explains the basic concepts and tasks involved in RMAN tablespace point-in-time recovery (TSPITR).

Purpose of RMAN TSPTIR

Recovery Manager (RMAN) automatic TSPITR enables you to quickly recover one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

RMAN TSPITR is most useful for the following situations:

  • You want to recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database. For example, you maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or DML statement corrupts the data in only one of the tablespaces.

  • You want to recover data lost after DDL operations that change the structure of tables. You cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation.

  • You want to recover a table after it has been dropped with the PURGE option.

  • You want to recover from the logical corruption of a table.

You can also use Flashback Database to rewind data, but you must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to your earliest recoverable backup.

Basic Concepts of RMAN TSPITR

You perform TSPITR by using the RMAN RECOVER TABLESPACE command. The target instance contains the tablespace to be recovered to the target time. The target time is the point in time or SCN of the tablespace after TSPITR completes.

The auxiliary instance is a database instance used in the recovery process to perform the work of recovery. 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 recovery set includes the datafiles that are in the tablespaces that you intend to recover. The auxiliary set includes the datafiles required for TSPITR of the recovery set that are not themselves part of the recovery set. The auxiliary set typically includes:

  • The SYSTEM and SYSAUX tablespaces

  • Datafiles containing rollback or undo segments from the target database instance

  • Temporary tablespaces

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

The simplest form of TSPITR, which is described in "Performing Fully Automated RMAN TSPITR", involves specifying the tablespaces of the recovery set and the target time. RMAN automatically performs the following actions:

Queries SYS.TS_PITR_CHECK for the tablespaces in the recovery set, as shown in Example 20-1. If the query returns rows, then RMAN does not proceed with the TSPITR.

Creates the auxiliary instance, starts it, and connects to it (if there is no existing auxiliary instance).

Takes the tablespaces to be recovered offline in the target database.

Restores a backup control file from a point in time before the target time to the auxiliary instance.

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).

Recovers the restored datafiles in the auxiliary instance to the specified time.

Opens the auxiliary database with the RESETLOGS option.

Exports the dictionary metadata about objects in the recovered tablespaces to the target database.

Shuts down the auxiliary instance.

Issues SWITCH commands on the target database instance if new names were given to the datafiles in the recovery set. The target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.

Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.

Deletes all auxiliary set files.

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

Basic Steps of RMAN TSPITR

Before performing TSPITR, review "Prerequisites and Consequences of TSPITR" to determine whether TSPITR is a viable option. If you decide to perform TSPITR, then you can then proceed to the preparatory stage described "Planning and Preparing for TSPITR".

When you are ready to perform the actual TSPITR, the basic steps depend on which technique you use. The following sections describe your options:

  • Performing Fully Automated RMAN TSPITR

    You specify an auxiliary destination and allow RMAN to manage all aspects of the TSPITR. This is the simplest technique and is recommended unless you specifically need more control over the operation.

  • Performing Customized RMAN TSPITR with an RMAN-Managed Auxiliary Instance

    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. For example, you could specify the location of auxiliary set or recovery set files, or specify initialization parameters or channel configurations for the auxiliary instance created and managed by RMAN.

  • Performing RMAN TSPITR Using Your Own Auxiliary Instance

    In this technique, 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.

Prerequisites and Consequences of TSPITR

A number of database problems cannot be resolved with TSPITR. The following list explains TSPITR prerequisites in terms of when you cannot perform TSPITR:

  • You cannot perform TSPITR if you do not have archived redo logs or if the database runs in NOARCHIVELOG mode.

  • 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, then 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.

  • If the constraints for the tables in tablespace tbs1 are contained in tablespace tbs2, then you cannot recover tbs1 without recovering tbs2 as well.

  • You cannot use TSPITR to recover any of the following objects:

    • 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

Consequences of TSPITR

After TSPITR completes, RMAN recovers the datafiles in the recovery set to the target time. Note the following consequences of TSPITR:

  • If a datafile was added after the point to which RMAN is recovering, then 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 completes.

  • If you run TSPITR on a tablespace and bring the tablespace online at time t, then 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 recovered tablespace as soon as TSPITR is complete.

Special Considerations When Not Using a Recovery Catalog

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

  • 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.)

  • Assume that you run TSPITR on a tablespace, and then bring the tablespace online at time t. 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 cannot 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

This section assumes that you have read "Prerequisites and Consequences of TSPITR". You must carry out the following steps when preparing for 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 in "Prerequisites and Consequences of TSPITR", after 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, 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, then you can perform repeated TSPITR operations to different target times because the catalog contains tablespace history information. If RMAN uses only a control file, however, then it does not have the tablespace history. In this case, RMAN knows only the current set of tablespaces. The tablespace on which TSPITR was performed has a creation time of the time it was brought online.

Assume a situation in which you are not using a recovery catalog. You run TSPITR on a tablespace and then bring the tablespace online at 5 p.m. on Friday. Backups of the tablespace created before 5 p.m. 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 5 p.m. Friday, nor can you use the current control file to recover the database to any time earlier than 5 p.m. Friday. Your only option is point-in-time recovery of the entire database using a restored control file.

To investigate past states of your data to identify the target time for TSPITR, you can use Oracle Flashback Query, Oracle Transaction Query and Oracle Flashback Version Query to find the point in time when unwanted database changes occurred.

See Also:

Oracle Database Advanced Application Developer's Guide for more information on Flashback Query, Flashback Transaction Query, and Flashback Version Query

Determining the Recovery Set

Initially, your recovery set includes the datafiles for the tablespaces you intend to recover. However, if objects in the tablespaces you need have relationships (such as constraints) to objects in other tablespaces, then you will have to account for this relationship before you can perform TSPITR. You have the following 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

You can use the TS_PITR_CHECK view to 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 query in Example 20-1 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:

Example 20-1 Querying TS_PITR_CHECK for a Subset of Tablespaces

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 query in Example 20-2.

Example 20-2 Querying TS_PITR_CHECK for All Tablespaces

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 output shown in Example 20-3 when you run the query in Example 20-1.

Example 20-3 Output for Query of TS_PITR_CHECK

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 

Example 20-3 shows that 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 you perform RMAN TSPITR on a tablespace, objects created after the target recovery time are lost. You can preserve such objects after they are identified by exporting them before TSPITR with the Data Pump Export utility and re-importing them afterward with Data Pump Import.

To determine which objects will be lost in TSPITR, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database. Table 20-1describes the contents of the view.

Table 20-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, 2007, 7:03:11 AM, issue the statement shown in Example 20-4.

Example 20-4 Querying TS_PITR_OBJECTS_TO_BE_DROPPED

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-06: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. Of course, you can 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 Fully Automated RMAN TSPITR

When you perform fully automated TSPITR, RMAN manages the entire process.

RMAN bases as much of the configuration for TSPITR as possible on the 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.

Use the AUXILIARY DESTINATION parameter to set the auxiliary destination for RMAN to use for the auxiliary set datafiles and other files for the auxiliary instance. The auxiliary destination must be a location on disk with enough space to hold auxiliary set datafiles. Even if you use other techniques 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. TSPITR will not fail if you inadvertently do not provide names for all auxiliary set datafiles.

To perform fully automated RMAN TSPITR:

  1. Review the information in "Prerequisites and Consequences of TSPITR".

    Perform the tasks in "Planning and Preparing for TSPITR".

    Start an RMAN session on the target database and, if applicable, connect to a recovery catalog.

    Note:

    Do not connect to an auxiliary instance when starting the RMAN client for automated TSPITR. If RMAN is connected to an auxiliary instance when you run RECOVER TABLESPACE, then RMAN assumes that you are trying to manage your own auxiliary instance, as described in "Performing RMAN TSPITR Using Your Own Auxiliary Instance", and will try to use the connected auxiliary for TSPITR.

    Configure any channels required for the TSPITR on the target instance.

    The auxiliary instance uses the same channel configuration as the target instance when performing the TSPITR.

    Run the RECOVER TABLESPACE command, specifying both the UNTIL clause and the AUXILIARY DESTINATION parameter.

    Example 20-5 returns the users and tools tablespaces to the end of log sequence number 1300, and stores the auxiliary instance files in the /disk1/auxdest directory.

    Example 20-5 Performing TSPITR on Two Tablespaces

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

    The next step depends on the results of the RECOVER command:

    • If no error occurs during TSPITR, then proceed to step 6.

      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. All auxiliary set datafiles and other auxiliary instance files are cleaned up from the auxiliary destination.

  2. In an error occurs during TSPITR, then proceed 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. After you resolve the problem, you can try TSPITR again.

    Note:

    The files in the auxiliary destination are Oracle Managed Files. The target database does not catalog them, so a second TSPITR will not use them. However, if you specify SET NEWNAME instead of AUXILIARY DESTINATION for the auxiliary set, and if the failure occurred before the online logs are reset, then RMAN can reuse the existing datafiles.

    If TSPITR completes successfully, then back up the recovered tablespaces before bringing them online.

    For example, enter the following command:

    BACKUP TABLESPACE users, tools;
    

    After you perform TSPITR on a tablespace, you cannot use backups of that tablespace from before the TSPITR was completed and the tablespace brought back online. If you use the recovered tablespaces without taking a backup, then you are running your database without a usable backup of these tablespaces.

    Bring the tablespaces back online.

    For example, enter the following command:

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

    Your recovered tablespaces are now ready for use.

Performing Customized RMAN TSPITR with an RMAN-Managed Auxiliary Instance

You can customize the following aspects of RMAN TSPITR while still mostly following the procedure described in "Performing Fully Automated RMAN TSPITR":

Renaming Oracle Managed Files in TSPITR

If you customize TSPITR when datafiles or online redo log files are in an Oracle Managed Files (OMF) format, then you can use the following techniques to rename them. The naming techniques are listed in order from most recommended to least recommended:

Use an auxiliary destination, as described in "Performing Fully Automated RMAN TSPITR".

Specify a location with one or more of the Oracle Managed Files initialization parameters for the auxiliary instance: DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST, and DB_CREATE_ONLINE_LOG_DEST_n. Do not set the DB_FILE_NAME_CONVERT or LOG_FILE_NAME_CONVERT initialization parameters.

Set the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters for the auxiliary instance. For Oracle Managed Files in ASM, RMAN uses the pattern to convert the ASM disk group name only and generates a valid OMF filename in the converted disk group.

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 enables you to specify a new destination. You can also use CONFIGURE AUXNAME to rename recovery set datafiles, but the effects of are different. Refer to the discussion of "Using Image Copies for Faster RMAN TSPITR Performance" for details about CONFIGURE AUXNAME.

To specify new recovery set filenames, create a RUN block and use SET NEWNAME commands within it. Be sure to assign names that do not conflict with each other or with the names of your current datafiles. Example 20-6 illustrates the basic technique.

Example 20-6 Renaming Recovery Set Files

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 each specified datafile to the new location during TSPITR, 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. RMAN overwrites any existing image copy backup of a datafile found at the new specified location.

RMAN does not detect conflicts between names set with SET NEWNAME and current datafile names on the target database until the actual recovery. If RMAN detects a conflict, then TSPITR fails and RMAN reports an error. The valid datafile is not overwritten.

Naming TSPITR Auxiliary Set Datafiles

Unlike recovery set datafiles, which are usually stored in their original locations, auxiliary set datafiles must not overwrite the corresponding original files in the target database. If you do not specify an auxiliary set file location that is different from its original location, then TSPITR fails. The failure occurs when RMAN attempts to overwrite the corresponding file in the original database and discovers the file in use.

The simplest way to provide locations for auxiliary set datafiles is to specify an auxiliary destination for TSPITR. However, RMAN supports the following alternatives for controlling the location of auxiliary set datafiles, which are listed in order of precedence shown in Table 20-2.

Table 20-2 Order of Precedence for Naming Files

Order Technique Section

1

SET NEWNAME

"Using SET NEWNAME to Name Auxiliary Set Datafiles"

2

CONFIGURE AUXNAME

"Using SET NEWNAME and CONFIGURE AUXNAME with Auxiliary Set Image Copies"

3

DB_FILE_NAME_CONVERT

"Using DB_FILE_NAME_CONVERT to Name Auxiliary Set Datafiles"

4

AUXILIARY DESTINATION argument to RECOVER TABLESPACE

 

Settings higher on the list override settings lower on the list in situations where both have been applied. For example, you might run RECOVER TABLESPACE... AUXILIARY DESTINATION on a target database when some auxiliary set datafiles have auxiliary names configured with CONFIGURE AUXNAME.

Even if you intend to use either of the preceding techniques to provide locations for specific files, Oracle recommends that you provide an AUXILIARY DESTINATION argument to RECOVER TABLESPACE. If you overlook renaming some auxiliary set datafiles, then TSPITR will still succeed. Any files not otherwise renamed will be placed in the auxiliary destination.

Note:

You can view any current CONFIGURE AUXNAME settings by running the SHOW AUXNAME command, which is described in Oracle Database Backup and Recovery Reference.

Using SET NEWNAME to Name Auxiliary Set Datafiles

To specify a new name for an auxiliary set datafile, you can enclose RECOVER TABLESPACE in a RUN command and use a SET NEWNAME command within the RUN block to rename the file. Example 20-7 illustrates the basic technique.

Example 20-7 Renaming Auxiliary Set Files

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

The result depends on whether /disk1/auxdest/system01.dbf exists when RECOVER TABLESPACE is executed. If ?/oradata/system01.dbf exists at the specified location and was created at an SCN before the UNTIL time for TSPITR, then the behavior is as described in "Using SET NEWNAME and CONFIGURE AUXNAME with Auxiliary Set Image Copies". Otherwise, RMAN restores the auxiliary set datafile to the NEWNAME instead of the default location. If your intention is only to control where the auxiliary set datafiles are stored, then ensure that no file is stored at the location specified by SET NEWNAME before performing TSPITR.

Using DB_FILE_NAME_CONVERT to Name Auxiliary Set Datafiles

Assume that 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. In this case, you can include a DB_FILE_NAME_CONVERT initialization parameter in the initialization parameter file used by the auxiliary instance. You can only use this technique in the following circumstances:

The DB_FILE_NAME_CONVERT initialization 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 database instance. The parameter consists of a list of pairs of strings. For any filename that contains the first string of a pair as a substring, the name of the corresponding file in the auxiliary instance is generated by substituting the second string of the pair into the original filename.

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

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

  • ?/oradata/trgt/sysaux01.dbf of the SYSAUX tablespace

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

To place the corresponding files of 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')

New filenames for the corresponding auxiliary instance files would be /bigtmp/trgt/system01.dbf, /bigtmp/trgt/sysaux01.dbf, and /bigtmp/trgt/undotbs01.dbf.

The most important point to remember is that DB_FILE_NAME_CONVERT must be present in the auxiliary instance parameter file. If the auxiliary instance was manually created, then add DB_FILE_NAME_CONVERT to the auxiliary instance parameter file.

Note that you can still rename individual auxiliary set datafiles with 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 none of the renaming methods used provide a new name for a file at the auxiliary instance, then TSPITR will fail.

Renaming OMF Datafiles Using DB_FILE_NAME_CONVERT in TSPITR

Oracle Managed Files (OMF) can exist in ASM or non-ASM storage. When the DB_FILE_NAME_CONVERT initialization parameter is set, TSPITR performs name conversion differently depending on whether OMF storage is ASM. For OMF files in ASM, the database converts only disk group names, for example, +DISK1 to +DISK2. For OMF files not stored in ASM, the database stores the files in the DB_FILE_NAME_CONVERT location and replaces the substring. It is not possible to generate valid non-ASM OMF filenames for the auxiliary instance by replacing a substring of the target instance OMF filename. Thus, you cannot use DB_FILE_NAME_CONVERT to control generation of new names in this situation.

To avoid this issue, use one of the other supported options for generating new names for OMF files (including files stored in ASM):

  • Use an auxiliary destination, as described in "Performing Fully Automated RMAN TSPITR".

  • Use the DB_CREATE_FILE_DEST initialization parameter in the auxiliary instance parameter file to specify a location for all auxiliary instance files for which no new name is specified using SET NEWNAME or CONFIGURE AUXNAME.

  • For ASM files, you can use SET NEWNAME to redirect individual files to a specific disk group accessible from the auxiliary instance (and allow the database to generate the filename within the disk group). For example:

    RUN
    {
      SET NEWNAME FOR DATAFILE 1 TO "+DISK2";
      SET NEWNAME FOR DATAFILE 2 TO "+DISK3";
      RECOVER TABLESPACE users, tools 
        UNTIL LOGSEQ 1300 THREAD 1
        AUXILIARY DESTINATION '/disk1/auxdest';
    }
    
Renaming Tempfiles During TSPITR

Tempfiles are considered part of the auxiliary set for your database. When the auxiliary instance is instantiated, RMAN re-creates the temporary tablespaces of the target database and generates their names by means of the regular rules for the auxiliary datafile names.

You can rename tempfiles with the SET NEWNAME FOR TEMPFILE, DB_FILE_NAME_CONVERT, or AUXILIARY DESTINATION commands. When RMAN opens the auxiliary instance, RMAN re-creates the tempfiles according to the applicable renaming rule. When the auxiliary instance is cleaned up, the tempfiles are deleted along with the rest of the auxiliary instance files.

Using Image Copies for Faster RMAN TSPITR Performance

You can enhance TSPITR performance by redirecting RMAN to use existing image copies of the recovery set and auxiliary set datafiles. In this case, RMAN does not need to restore the datafiles from backup. You can use the following techniques to tell RMAN about the possible existence of an image copy of a datafile:

  • Use CONFIGURE AUXNAME command with image copies of recovery set datafiles or auxiliary set datafiles

  • Use SET NEWNAME command with image copies of auxiliary set datafiles

In general, if a suitable image copy is available in the specified location, then 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. The details vary depending on the command used and whether the file is an auxiliary set or recovery set file.

Using CONFIGURE AUXNAME with Recovery Set Image Copies

During TSPITR, RMAN looks in the specified AUXNAME location for the datafile. RMAN checks whether an image copy backup of the datafile exists with a datafile checkpoint SCN early enough that it can be recovered to the target time. If RMAN finds a usable image copy, then RMAN uses it in TSPITR. Otherwise, RMAN restores the datafile and recovers it in its original location. Any file in the location specified by the AUXNAME is not changed or deleted. Example 20-8 illustrates this technique.

Example 20-8 Using CONFIGURE AUXNAME

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

The primary use of CONFIGURE AUXNAME is to make TSPITR faster by eliminating restore times. If you have tablespaces on which you anticipate performing TSPITR, then you can include in your backup routine the maintenance of a set of image copies of the affected datafiles, and update these periodically to the earliest point to which you expect to perform TSPITR. The expected usage model is:

Configure the AUXNAME for the files once, when setting up this strategy.

Perform BACKUP AS COPY DATAFILE n FORMAT auxname regularly to maintain the updated image copy. For better performance, use an incrementally updated backup strategy to keep the image copies up to date without performing full backups of the datafiles.

When TSPITR is needed, specify a target time after the last update of the image copy.

Remember that you may not know which tablespaces will require image copies in advance. As discussed in "Determining the Recovery Set", relationships between the tablespaces to be recovered and other tablespaces may require that you add tablespaces to your final recovery set. Other tablespaces may wind up in the auxiliary set as well. You should configure an AUXNAME for each datafile that is likely to be part of your recovery set, and update image copies of all datafiles often.

It is possible for you not to correctly anticipate all tablespaces to include in the recovery set. Alternatively, for reasons of overhead you may not want to maintain copies of all possible recovery set tablespaces. In either case, you can prepare only a subset of the datafiles: the TSPITR process is still the same. The process simply takes longer because RMAN must recover recovery set datafiles for which there are no image copies in their original locations.

Note that the 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 set as part of the TSPITR command overrides the effect of the persistent 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.

Using 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, while SET NEWNAME sets an alternative location for the duration of a RUN command. Nevertheless, RMAN handles values for auxiliary set datafiles differently from recovery set datafiles. In short, auxiliary set files always use the AUXNAME or NEWNAME, but recovery set files only use the AUXNAME or NEWNAME if a valid copy exists.

Assume that you use SET NEWNAME or CONFIGURE AUXNAME to specify a new location for an auxiliary set datafile. Also assume that there is an image copy at that location with an SCN that can be used in TSPITR. In this case, RMAN uses the image copy. If there is no usable image copy at that location, however, then RMAN restores 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.)

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

Performing TSPITR with CONFIGURE AUXNAME and Image Copies: Scenario

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

  • Configure an AUXNAME for each datafile in your database by using a command of the following form:

    CONFIGURE AUXNAME FOR DATAFILE n TO auxname_n;
    
  • Take an image copy of the database every Sunday by using a command of the following form:

              BACKUP AS COPY DATAFILE n FORMAT auxname_n
    

    If the image copies are all in the same location on disk, and if they are named similarly to the original datafiles, then you can avoid performing backups of every datafile. Instead, you can use the FORMAT or DB_FILE_NAME_CONVERT options of the BACKUP command and use BACKUP AS COPY DATABASE. For example, if the configured auxiliary names are a translation of the location maindisk to auxdisk, then you could use the following command:

    BACKUP AS COPY 
      DATABASE 
      DB_FILE_NAME_CONVERT (maindisk, auxdisk);
    

    Note:

    Because Oracle-managed filenames cannot generally be translated using a simple substitution, you cannot typically use the technique of using DB_FILE_NAME_CONVERT to generate names for image copies stored in OMF.

You are then prepared for TSPITR without restoring from backup. For example, if an erroneous batch job, started on November 15, 2007, at 19:00:00, incorrectly updates 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 2007, 19:00:00';

Because AUXNAME locations 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 datafiles for tablespace parts will not be located in the original datafile locations. Instead, they will be in the AUXNAME locations. If you want only to use only the AUXNAME locations for the auxiliary set, then run CONFIGURE AUXNAME ... CLEAR for the files in the recovery set before starting TSPITR. In this case, RMAN will have to restore the datafiles.

Customizing Initialization Parameters for the Automatic Auxiliary Instance in TSPITR

The automatic auxiliary instance looks for initialization parameters in a file that is operating system-dependent. RMAN always looks for this default parameter file for the automatic auxiliary instance when performing TSPITR. If the file is not found, then RMAN does not generate an error. Instead, RMAN defines the basic initialization parameters in Table 20-3 for the automatic auxiliary instance.

Table 20-3 Default Initialization Parameters

Initialization Parameter Value

DB_NAME

Same as DB_NAME of the source database.

COMPATIBLE

Same as the compatible setting of the target database.

DB_UNIQUE_NAME

Generated unique value based on DB_NAME.

DB_BLOCK_SIZE

Same as the DB_BLOCK_SIZE of the target database.

DB_CREATE_FILE_DEST

Auxiliary destination (only if the AUXILIARY DESTINATION argument is set). RMAN creates Oracle-managed control files and online logs in this location.

CONTROL_FILES

A generated filename in the auxiliary destination (only if the AUXILIARY DESTINATION argument is set). RMAN creates control files in this location.

By default, RMAN creates one control file for the auxiliary instance in an operating system-specific location. In UNIX, the default location is ?/dbs/cntrl_@.dbf, where ? stands for ORACLE_HOME and @ stands for ORACLE_SID. For an automatic auxiliary instance, RMAN randomly generates the ORACLE_SID.

See Also: Oracle Database Reference for more on the use of the CONTROL_FILES initialization parameter

LARGE_POOL_SIZE

1M

SHARED_POOL_SIZE

110M

PROCESSES

50


It is rarely necessary to alter the parameter file, especially if you provide an AUXILIARY DESTINATION argument to RECOVER TABLESPACE. If you override one of the initialization parameters in Table 20-3 with an inappropriate value, then TSPITR may fail due to problems with the auxiliary instance. Nevertheless, you can add other parameters besides these basic parameters if needed. For example, you can use DB_FILE_NAME_CONVERT to specify the names of the datafiles in the auxiliary set.

Another way to specify parameters for the automatic auxiliary instance is to place the initialization parameters in a file. You can then provide the location of this file with the SET AUXILIARY INSTANCE PARAMETER FILE 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.

Specifying the Auxiliary Instance Control File Location in TSPITR

If you use an initialization parameter file, then you can specify your own location for the control file of your auxiliary instance. Set the CONTROL_FILES initialization parameter to specify a location for the control files.

If you do not explicitly specify a control file location, and if you use the AUXILIARY DESTINATION parameter, then RMAN locates the control file in the auxiliary destination. If you do not use AUXILIARY DESTINATION, then the auxiliary instance control files are stored in an operating system-specific location.

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. If there is not enough space to create the control file, however, then TSPITR will fail.

Specifying the Auxiliary Instance Online Log Location in TSPITR

If you specify the LOG_FILE_NAME_CONVERT initialization parameter in your auxiliary instance parameter file, then this parameter determines 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.

If you do not specify a location for the online redo logs using LOG_FILE_NAME_CONVERT or AUXILIARY DESTINATION, then TSPITR will fail trying to create the online redo logs. Even if DB_CREATE_FILE_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.

Performing RMAN TSPITR Using Your Own Auxiliary Instance

Typically, you should allow RMAN to manage the creation and destruction of the auxiliary instance used during RMAN TSPITR. Nevertheless, creating and using your own auxiliary instance is also supported.

One reason you might want to create your own instance is to exercise control of channels used in TSPITR. The 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 restore. You may need different channel settings and may not want to use CONFIGURE to change the settings on the target database. In this case, 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

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

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

Use a text editor to create an initialization parameter file for the auxiliary instance on the target database host. For this example, assume your parameter file is placed at /tmp/initAux.ora. Set the parameters described in Table 20-4.

Note:

For TSPITR, the target and auxiliary database instances must be on the same host.

Table 20-4 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.

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.

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 redo log filenames, and ensure that the conversion pattern matches the format of the filename shown 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 /).

See Also: "Specifying the Auxiliary Instance Online Log Location in TSPITR" for restrictions on possible values for LOG_FILE_NAME_CONVERT with OMF filenames

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.

The following example shows possible initialization parameter settings for an auxiliary instance for TSPITR:

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 =11.0.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 Database 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 SYSDBA connection to the auxiliary instance.

Preparing RMAN Commands for TSPITR with Your Own Auxiliary Instance

If you are running your own auxiliary instance, then it is possible for the sequence of commands required for TSPITR to be long. This situation can occur when you allocate a complex channel configuration for restoring from backup and you are not using DB_FILE_NAME_CONVERT to determine file naming.

You may wish to store the series of commands for TSPITR in an RMAN command file. Review the command file carefully to catch any errors. To read the command file into RMAN, use the @ command (or the CMDFILE command line argument when starting RMAN).

The following example runs the command file named /tmp/tspitr.rman:

@/tmp/tspitr.rman;

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

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. If you decide to allocate your own channel configuration, however, then you can include 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 for TSPITR.

See Also:

"Performing TSPITR with Your Own Auxiliary Instance: Scenario" to learn 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 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 for TSPITR.

Executing TSPITR with Your Own Auxiliary Instance

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

Step 1: Start the Auxiliary Instance in NOMOUNT Mode

Before beginning RMAN TSPITR, start SQL*Plus and connect to the auxiliary instance with SYSOPER privileges.

Start the auxiliary instance in NOMOUNT mode, specifying a parameter file if necessary. For example, enter the following SQL*Plus command:

SQL> STARTUP NOMOUNT PFILE='/tmp/initAux.ora'

Remember that if you specify PFILE, then the path for the PFILE will be a client-side path on the host from which you run SQL*Plus.

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 and connect to the target database and the manually created auxiliary instance.

Step 3: Execute the RECOVER TABLESPACE Command

In the simplest case, execute the RECOVER TABLESPACE... UNTIL command at the RMAN prompt:

RECOVER TABLESPACE ts1, ts2... UNTIL TIME 'time';

If you want to use the ALLOCATE AUXILIARY CHANNEL or SET NEWNAME commands, then include these commands before the RECOVER TABLESPACE command within a RUN command. The following example illustrates this technique:

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

Performing TSPITR with Your Own Auxiliary Instance: Scenario

This scenario shows the execution of a RECOVER TABLESPACE... UNTIL operation. This scenario illustrates the following features of RMAN TSPITR:

  • Managing your own auxiliary instance

  • Configuring channels for restore of backups from disk and SBT devices

  • Using recoverable image copies for some auxiliary set datafiles using SET NEWNAME

  • Specifying new names for recovery set datafiles using SET NEWNAME

Assume that you follow these steps:

Prepare the auxiliary instance as described in "Preparing Your Own Auxiliary Instance for RMAN TSPITR". Specify a 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.dbf'
DB_FILE_NAME_CONVERT=('?/oradata/prod', '/bigtmp')
LOG_FILE_NAME_CONVERT=('?/oradata/prod', '/bigtmp')
COMPATIBLE=11.0.0
BLOCK_SIZE=8192
REMOTE_LOGIN_PASSWORD=exclusive

Create service name pitprod for the auxiliary instance, and check for connectivity.

Using SQL*Plus, connect to the auxiliary instance with SYSOPER privileges. Start the instance in NOMOUNT mode:

SQL> STARTUP NOMOUNT PFILE=/bigtmp/init_tspitr_prod.ora

Start RMAN and connect to the target and auxiliary database instances.

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

RUN 
{
# Specify NEWNAMES for recovery set datafiles
  SET NEWNAME FOR DATAFILE '?/oradata/prod/clients01.dbf' 
                        TO '?/oradata/prod/clients01_rec.dbf';
  SET NEWNAME FOR DATAFILE '?/oradata/prod/clients02.dbf'
                        TO '?/oradata/prod/clients02_rec.dbf';
  SET NEWNAME FOR DATAFILE '?/oradata/prod/clients03.dbf'
                        TO '?/oradata/prod/clients03_rec.dbf';
  SET NEWNAME FOR DATAFILE '?/oradata/prod/clients04.dbf'
                        TO '?/oradata/prod/clients04_rec.dbf';

# 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.dbf'
                        TO '/backups/prod/system01_monday_noon.dbf';
  SET NEWNAME FOR DATAFILE '?/oradata/prod/system02.dbf'
                        TO '/backups/prod/system02_monday_noon.dbf';
  SET NEWNAME FOR DATAFILE '?/oradata/prod/sysaux01.dbf'
                        TO '/backups/prod/sysaux01_monday_noon.dbf';
  SET NEWNAME FOR DATAFILE '?/oradata/prod/undo01.dbf'
                        TO '/backups/prod/undo01_monday_noon.dbf';

# Specified the types of channels to use
  ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE sbt;

# Recovered the clients tablespace to 24 hours ago:
  RECOVER TABLESPACE clients UNTIL TIME 'sysdate-1';
}

Consider storing this command sequence in a command file and executing the command file.

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, then 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.

  • Name conflicts can occur 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, then you need to increase the amount of sort space available.

Troubleshooting Filename Conflicts

Suppose that SET NEWNAME, CONFIGURE AUXNAME, and DB_FILE_NAME_CONVERT cause multiple files in the auxiliary or recovery sets to have the same name. In this case, RMAN reports an error during TSPITR. To correct the problem, use different values for these parameters to eliminate the duplicate name.

Troubleshooting Identification of Tablespaces with Undo Segments

During TSPITR, RMAN needs information about which tablespaces had undo segments at the TSPITR target time. This information is usually available in the recovery catalog, if one is used.

Assume a case in which there is no recovery catalog or the information is not found in the recovery catalog. In this case, RMAN assumes that the set of tablespaces with undo segments at the target time is the same as the set of tablespaces with undo segments at the present time. If this assumption is not correct, then TSPITR will fail with an error. In this case, use the UNDO TABLESPACE clause to provide a list of tablespaces with undo segments at the target time.

Troubleshooting the Restart of a Manual Auxiliary Instance After TSPITR Failure

Assume that you are managing your own auxiliary instance when there is a failure in TSPITR. Before you can try TSPITR again, you must shut down the auxiliary instance, correct the problem which interfered with TSPITR, and then start the auxiliary instance NOMOUNT before trying TSPITR again.