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. (You might do this, for example, if the disk that originally contained the tablespace is unusable.)
Specifying a location other than the auxiliary destination for some or all auxiliary set datafiles (or not using an auxiliary destination at all). You might do this if there is no single location on disk with enough space for all auxiliary set files.
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 RMAN 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 in TSPITR";
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. The parameter consists of a list of pairs of strings, such that 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/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')

New filenames for the corresponding auxiliary instance files are /bigtmp/trgt/system01.dbf and /bigtmp/trgt/undotbs01.dbf.

The most important thing to remember is that DB_FILE_NAME_CONVERT must 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.

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 none of the renaming methods used provide a new name for a file at the auxiliary instance, TSPITR will fail.

Renaming ASM OMF Datafiles Using DB_FILE_NAME_CONVERT in TSPITR

The DB_FILE_NAME_CONVERT initialization parameter cannot be used to control generation of new names for files at the auxiliary instance which are Oracle Managed Files (OMF) at the target instance. When using Oracle Managed Files at the target instance, it is not possible to generate valid OMF filenames for the auxiliary instance by replacing a substring of the target instance OMF filename. When using ASM Oracle Managed Files, RMAN will change these invalid names to valid filenames in the converted disk group name.

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 "Using an Auxiliary Destination for 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 of Tempfiles During TSPITR

Tempfiles are considered part of the auxiliary set for your database. When creating the auxiliary instance, tempfiles can be renamed using SET NEWNAME FOR TEMPFILE, DB_FILE_NAME_CONVERT or AUXILIARY DESTINATION. When the auxiliary instance is opened, the tempfiles are recreated 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.

Order of Precedence Among File Renaming Methods in TSPITR

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

Note:

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

Specifying Auxiliary Instance Control File Location in TSPITR

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 in TSPITR

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.

Renaming ASM OMF Redo Logfiles with LOG_FILE_NAME_CONVERT in TSPITR

LOG_FILE_NAME_CONVERT cannot be used to control generation of new names for redo log files at the auxiliary instance which are Oracle Managed Files (OMF) at the target instance. When using Oracle Managed Files at the target instance, it is not possible to generate valid OMF filenames for the auxiliary instance by replacing a substring of the target instance OMF filename. When using ASM Oracle Managed Files, RMAN uses the pattern to convert the disk group name only, and generates a valid filename in the converted disk group.

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

Use an auxiliary destination, as described in "Using an Auxiliary Destination for Automated RMAN TSPITR"
Use the DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST or DB_CREATE_ONLINE_LOG_DEST_n initialization parameter in the auxiliary instance parameter file to specify a location

Using Image Copies for Faster RMAN 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 in TSPITR

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;

The primary use of CONFIGURE AUXNAME is as the basis of a technique to make TSPITR faster by eliminating restore times. If you have tablespaces on which you anticipate performing TSPITR, 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, or, for better performance, 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 part of your recovery set, and update image copies of all datafiles often.

If you do not correctly anticipate all tablespaces to include in the recovery set, or if for reasons of overhead you do not want to maintain copies of all possible recovery set tablespaces, you can prepare only a subset of the datafiles using this strategy. The TSPITR process is still the same if only a subset of datafiles are prepared. The process 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 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.

Note:

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

Performing 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);
```
Note:
Because OMF filenames cannot generally be translated using a simple substitution, the technique of using DB_FILE_NAME_CONVERT to generate names for these image copies cannot generally be used if the image copies to be used are stored in OMF.
BUG 4673106 -- this note was added

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 datafiles for 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 in TSPITR

The automatic auxiliary instance looks for initialization 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). RMAN always looks for this default parameter file for the automatic auxiliary instance when performing TSPITR. 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 initialization parameters in a file, and then provide the location of these 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.)

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 control file 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 you override one of the six basic initialization parameters in the auxiliary instance parameter file with an inappropriate value, TSPITR may fail due to problems with the auxiliary instance. 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.