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

Common Terms

Table 20-1 defines some of the 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 correspond to how much automation versus customization you require in your environment. There are three ways to run the utility:

1. 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 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"

2. Automated - RMAN-Managed Auxiliary Instance with User Settings

   You can override some of the defaults of RMAN TSPITR while still using a RMAN-managed auxiliary instance and destination. This variation of the default mode enables you to benefit from some of the 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 a RMAN-Managed Auxiliary Instance"

3. 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 as well as some aspects of the TSPITR process. This mode may be appropriate if, for example, you need to 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 RMAN TSPITR (Mode 2) shares many of these high-level processing steps.

RMAN TSPITR automatically performs the following actions:

1. Provided the tablespaces in the recovery set have not been dropped, checks to see if the tablespaces in the recovery set 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 and any tablespace containment violations must be resolved before TSPITR can proceed. Example 20-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, creates the auxiliary instance, starts it, and connects to it.

3. Takes the tablespaces to be recovered offline in the target database, provided 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 datafiles from the recovery set and the auxiliary set to the auxiliary instance.

   Files are restored either in the:

   • Locations you specify for each file

   • Original location of the file (for recovery set datafiles)

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

6. Recovers the restored datafiles 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 datafiles 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 backup 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 above.

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 20-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 datafiles can have Oracle Managed Files (OMF) in the target and can use 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 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.

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 restore is not necessary. For more information, see "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:

1. 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".

   or

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

2. The filenames to be converted are ASM/OMF filenames.

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.

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. When using RMAN-managed auxiliary instance, you can 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.

Using SET NEWNAME with Recovery Set Image Copies

During TSPITR, RMAN looks in the specified NEWNAME 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 to the NEWNAME location. Any file in the location specified by the NEWNAME is overwritten. The specified NEWNAME becomes the name of the datafile in the target database once TSPITR completes. Example 20-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

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.

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 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 datafiles, 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 datafiles

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 datafile 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 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 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 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 datafile copies from an SCN before the TSPITR target time, the auxiliary set is not restored from backup. Instead, the datafile copies are used in recovery which reduces the restore overhead.

You can also prevent the recovery set from being restored. You need to 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 will not be restored and the tablespace will change location once 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 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 the 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 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 will be restored to an OS specific location. For details, consult your OS-specific documentation. You can use LOG_ARCHIVE_DEST_1 initialization parameter to specify an alternative location where the archived logs will be 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 datafiles, 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 will be created. If you choose the later option, you must use DB_CREATE_ONLINE_LOG_1 together with DB_CREATE_FILE_DEST in order for RMAN TSPITR to use it.

If you do not specify a location for the online redo logs 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

TSPITR will fail trying to create the online redo logs.

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

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

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 different a 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 will run for TSPITR.

Planning Datafile 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 out these commands, if necessary, and add them to the sequence of commands you will run for TSPITR. For more information, see "Renaming TSPITR Recovery Set Datafiles 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 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.

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