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

Common Terms

Table 21-1 defines some common entities that are used by RMAN TSPITR.

Modes of RMAN TSPITR

You start RMAN TSPITR with the RMAN RECOVER TABLESPACE command. You have several options for running RMAN TSPITR. The difference between the various modes of operation corresponds to how much automation versus customization you require in your environment. There are three ways to run the utility:

• Fully Automated (the default)

  In this mode, RMAN manages the entire TSPITR process including the auxiliary instance. You specify the tablespaces of the recovery set, an auxiliary destination, the target time, and you allow RMAN to manage all other aspects of TSPITR.

  The default mode is recommended unless you specifically need more control over the location of recovery set files after TSPITR, auxiliary set files during TSPITR, channel settings and parameters or some other aspect of your auxiliary instance. For more information, see "Performing Fully Automated RMAN TSPITR".

• Automated: RMAN-Managed Auxiliary Instance with User Settings

  You can override some defaults of RMAN TSPITR while still using an RMAN-managed auxiliary instance and destination. This variation of the default mode enables you to benefit from some built-in management that RMAN TSITR provides while being able to specify:

  • Location of auxiliary set or recovery set files

  • Initialization parameters

  For more information, see "Overriding Defaults for RMAN TSPITR with an RMAN-Managed Auxiliary Instance".

• Non-Automated: TSPITR and User-Managed Auxiliary Instance

  This mode of RMAN TSPITR requires you to set up and manage all aspects of the auxiliary instance and some aspects of the TSPITR process. This mode may be appropriate if, for example, you must allocate a different number of channels or change the channel parameters for your user-managed auxiliary instance.

  For more information, see "Performing RMAN TSPITR Using Your Own Auxiliary Instance".

How RMAN TSPITR Works With an RMAN-Managed Auxiliary Instance

Having selected tablespaces from the recovery set, an auxiliary destination and a target time, you are now ready to perform Fully Automated RMAN TSPITR (default). The automated mode of RMAN TSPITR shares many of these high-level processing steps.

RMAN TSPITR automatically performs the following actions:

1. If the tablespaces in the recovery set have not been dropped, checks to see if they are self-contained by executing the DBMS_TTS.TRANSPORT_SET_CHECK for the recovery set tablespaces and then checking that the view TRANSPORT_SET_VIOLATIONS is empty. If the query returns rows, RMAN stops TSPITR processing. You must resolve any tablespace containment violations before TSPITR can proceed. Example 21-1 shows you how to set up and run the query before invoking RMAN TSPITR.

2. Checks to see if a connection to a user-managed auxiliary instance was provided. If it is, then RMAN TSPITR uses it. If not, RMAN TSPITR creates the auxiliary instance, starts it, and connects to it.

3. Takes the tablespaces to be recovered offline in the target database, if the tablespaces in the recovery set have not been dropped.

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

5. Restores the data files from the recovery set and the auxiliary set to the auxiliary instance.

   Files are restored either in the:

   • Locations that you specify for each file

   • Original location of the file (for recovery set data files)

   • Auxiliary destination (if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE and an RMAN-managed auxiliary instance)

6. Recovers the restored data files in the auxiliary instance to the specified time.

7. Opens the auxiliary database with the RESETLOGS option.

8. Makes the recovery set tablespaces read-only in the auxiliary instance.

9. Exports the recovery set tablespaces from the auxiliary instance using the Data Pump utility to produce a transportable tablespace dump file.

10. Shuts down the auxiliary instance.

11. Drops the recovery set tablespaces from the target.

12. Data Pump utility reads the transportable tablespace dump file and plugs the recovery set tablespaces into the target.

13. Makes the tablespaces that were put in the target database read/write and immediately takes them offline.

14. Deletes all auxiliary set files.

At this point, RMAN TSPITR has finished. The recovery set data files are returned to their contents at the specified point in time, and belong to the target database.

The recovery set tablespaces are left offline for you to back up and then bring back online. These last steps follow Oracle's recommendation and best practice of backing up recovered tablespaces as soon as TSPITR completes.

Identify and Resolve Dependencies on the Primary Database

RMAN TSPITR requires that the tablespace be self-contained and that no SYS -owned objects reside in the tablespace. You can use the DBMS_TTS.TRANSPORT_SET_CHECK procedure to locate objects outside the tablespace and identify relationships between objects that span the recovery set boundaries. If the TRANSPORT_SET_VIOLATIONS view returns rows, you must investigate and correct the problem according to the choices mentioned earlier in this step.

Record all actions performed during this step so that you can re-create any suspended or removed relationships after completing TSPITR. Proceed with TSPITR only when the TRANSPORT_SET_VIOLATIONS view is empty for the tablespaces in the recovery set.

The query in Example 21-1 illustrates how to use the DBMS_TTS.TRANSPORT_SET_CHECK procedure for an initial recovery set consisting of tablespaces tools and users. It queries the transportable tablespace violations table to manage any dependencies. No rows are returned from this query when all dependencies are managed.

BEGIN
   EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USERS,TOOLS', TRUE,TRUE);
END;
/
SELECT * FROM  TRANSPORT_SET_VIOLATIONS;

Considerations When Renaming OMF Auxiliary Set Files in TSPITR

Auxiliary set data files can have Oracle Managed Files (OMF) in the target and can use Automatic Storage Management (ASM) or non-ASM storage. TSPITR performs name conversion differently when the DB_FILE_NAME_CONVERT initialization parameter is set and the OMF files are in ASM or non-ASM storage.

LOG_FILE_NAME_CONVERT='+onlinelogs','+tmpasm'

DB_FILE_NAME_CONVERT='+DATAFILE/prod','+DATAFILE/tspitr'

WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only 

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';
}

Using SET NEWNAME to Name Auxiliary Set Data Files

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

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 DATAFILECOPY is used and the restore operation is not necessary. For more information, see "Using SET NEWNAME and CONFIGURE AUXNAME with Auxiliary Set Image Copies". Otherwise, RMAN restores the auxiliary set data file to the NEWNAME instead of the default location. If your intention is to control where the auxiliary set data files 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 Data Files

Assume that you do not want to use an auxiliary destination for all of your auxiliary set data files, 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 use this technique only in the following circumstances:

One of the following situations exists:

• - You create your own initialization parameter file for an automatically managed auxiliary instance, as described in "Customizing Initialization Parameters for the Automatic Auxiliary Instance in TSPITR"

• - You create your own auxiliary instance, as described in "Performing RMAN TSPITR Using Your Own Auxiliary Instance"

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 file name 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 file name.

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, you would add the following line to the auxiliary instance parameter file:

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

New file names 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.

You can still rename individual auxiliary set data files with the SET NEWNAME or CONFIGURE AUXNAME command. Also, files that do not match the patterns provided in DB_FILE_NAME_CONVERT are not renamed. When using RMAN-managed auxiliary instance, you can use the AUXILIARY DESTINATION parameter of RECOVER TABLESPACE command to ensure that all auxiliary set data files 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 fails.

Using SET NEWNAME with Recovery Set Image Copies

During TSPITR, RMAN looks in the specified NEWNAME location for the data file. RMAN checks whether an image copy backup of the data file exists with a data file 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 data file to the NEWNAME location. Any file in the location specified by the NEWNAME is overwritten. The specified NEWNAME becomes the name of the data file in the target database once TSPITR completes. Example 21-8 illustrates this technique.

RUN
{
SET NEWNAME 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;
}

Using SET NEWNAME and CONFIGURE AUXNAME with Auxiliary Set Image Copies

The CONFIGURE AUXNAME command sets a persistent alternative location for an auxiliary set data file image copy, whereas the SET NEWNAME command sets an alternative location for the duration of a RUN command.

Assume that you use SET NEWNAME or CONFIGURE AUXNAME to specify a new location for an auxiliary set data file. 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 data file is overwritten by the restored file.)

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

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

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

2. 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 data files.

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

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 data file in the auxiliary set by using a command of the following form:

  CONFIGURE AUXNAME FOR DATAFILE n TO auxname_n;
  

• Take an image copy of the auxiliary set 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 data files, then you can avoid performing backups of every data file. 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 use the following command:

  BACKUP AS COPY 
    DATABASE 
    DB_FILE_NAME_CONVERT (maindisk, auxdisk);
  

  Note:

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

After these steps, you are prepared for TSPITR without restoring the auxiliary set 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 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 data file copies from an SCN before the TSPITR target time, the auxiliary set is not restored from backup. Instead, the data file copies are used in recovery, which reduces the restore overhead.

You can also prevent the recovery set from being restored. You must take frequent image copies of the tablespaces and use SET NEWNAME to specify the location of these copies. This method ensures that the recovery set is not restored, and the tablespace changes location after TSPITR successfully completes.

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 clause, then RMAN locates the control file in the auxiliary destination. If you do not use the AUXILIARY DESTINATION clause, 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 the TSPITR operation. Because control files are relatively small, RMAN seldom encounters a problem creating an auxiliary control file. If there is not enough space to create the control file, however, then TSPITR fails.

Specifying the Auxiliary Instance Archived Logs in TSPITR

To perform recovery on the auxiliary and recovery sets after restoring them at the auxiliary instance, RMAN may need to restore archived logs. When an auxiliary destination is being used, the archived logs are restored to that location. In the absence of an auxiliary destination and any other initialization parameters, the archived logs are restored to an operating system specific location. For details, consult your operating system specific documentation. You can use the LOG_ARCHIVE_DEST_1 initialization parameter to specify an alternative location where the archived logs are restored.

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 and the parameter successfully converts the names of the online redo logs of the target, then this parameter determines the online redo log location. The same restrictions that apply to OMF data files, apply to OMF online redo logs. For more information, see "Considerations When Renaming OMF Auxiliary Set Files in TSPITR". If RMAN is managing the auxiliary instance and an auxiliary destination is specified, RMAN creates the online redo log in the auxiliary destination.

Alternatively, you can use DB_CREATE_FILE_DEST or DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_1 to specify the location where the auxiliary instance redo logs are created. If you choose the latter option, you must use DB_CREATE_ONLINE_LOG_1 with DB_CREATE_FILE_DEST.

TSPITR fails to create the online redo logs if you do not specify a location for them by using one of the following:

• LOG_FILE_NAME_CONVERT

• DB_CREATE_FILE_DEST

• DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_1

• AUXILIARY DESTINATION

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 that your parameter file is placed at /tmp/initAux.ora. Set the parameters described in Table 21-5.

Set other parameters as needed, including the parameters to specify how much memory the auxiliary instance uses.

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

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.

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 channels with a different configuration (changing the number of channels or channel parameters), 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 run for TSPITR.

Planning Data File Names with Your Own Auxiliary Instance: SET NEWNAME

You may want 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 these commands, if necessary, and add them to the sequence of commands that you run for TSPITR. For more information, see "Renaming TSPITR Recovery Set Data Files with SET NEWNAME".

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

$rman target dba/dbapwd AUXILARY aux/auxpwd@aux

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';
}