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

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.

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-6 illustrates the basic technique.

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 database. 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 database, as described in "Customizing Initialization Parameters for the Automatic Auxiliary Database in TSPITR"

• You create your own auxiliary database, as described in "Performing RMAN TSPITR Using Your Own Auxiliary Database"

The DB_FILE_NAME_CONVERT initialization parameter in the auxiliary database specifies how to derive names for files in the auxiliary database 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 database 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 database in /bigtmp, you add the following line to the auxiliary database parameter file:

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

New file names for the corresponding auxiliary database files are /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 database parameter file. If the auxiliary database was manually created, then add DB_FILE_NAME_CONVERT to the auxiliary database 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 database, 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 the renaming methods do not provide a new name for a file at the auxiliary database, then TSPITR fails.

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 after TSPITR completes. Example 21-7 illustrates this technique.

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:

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

If you use an initialization parameter file, then you can specify your own location for the control file of your auxiliary database. 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 database control files are stored in an operating system-specific location.

No matter where you store your auxiliary database control file, it is removed at the end of the TSPITR operation. Because control files are relatively small, it is rare that RMAN encounters a problem creating an auxiliary control file. If there is not enough space to create the control file, however, then TSPITR fails.

To perform recovery on the auxiliary and recovery sets after restoring them at the auxiliary database, 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.

If you specify the LOG_FILE_NAME_CONVERT initialization parameter in your auxiliary database 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 database 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 database 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

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

Use a text editor to create an initialization parameter file for the auxiliary database 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 database uses.

The following example shows possible initialization parameter settings for an auxiliary database 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

The auxiliary database must have a valid net service name. Before proceeding, use SQL*Plus to ensure that you can establish a SYSBACKUP or SYSDBA connection to the auxiliary database.

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

Start the auxiliary database 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 database 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 database for TSPITR.

Start RMAN and connect to the target database and the manually created auxiliary database.

rman target dba AUXILIARY auxusr@aux

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