Oracle9i Recovery Manager User's Guide
Release 1 (9.0.1)

Part Number A90135-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Performing RMAN Tablespace Point-in-Time Recovery

This chapter describes how to use Recovery Manager (RMAN) to perform tablespace point-in-time recovery (TSPITR). This chapter contains these topics:

Introduction to RMAN TSPITR

Recovery Manager (RMAN) automated tablespace point-in-time recovery (TSPITR) enables you to quickly recover one or more tablespaces to a time that is different from that of the rest of the database.

RMAN TSPITR is most useful for recovering the following:

Like a table export, RMAN TSPITR enables you to recover a consistent data set; however, the data set is the entire tablespace rather than one object. As Figure 11-1 illustrates, Recovery Manager does the following:

  1. Restores the specified tablespace backups to a temporary auxiliary instance

  2. Recovers the tablespace

  3. Exports metadata from the auxiliary instance

  4. Points the target database control file to the newly recovered datafiles

  5. Imports metadata into the target database

Figure 11-1 RMAN TSPITR

Text description of sbr81085.gif follows
Text description of the illustration sbr81085.gif

Glossary of TSPITR Terminology

Familiarize yourself with the following terms and abbreviations, which are used throughout this chapter:


Tablespace point-in-time recovery

Auxiliary Instance

The auxiliary instance used to recover the backup tablespaces. The database created by TSPITR never has independent existence: it is only an intermediate work area.

Recovery Set

Tablespaces in the target database requiring TSPITR to be performed on them.

Auxiliary Set

Any other items required for TSPITR, including:

Planning for RMAN TSPITR

Recovery Manager TSPITR requires careful planning. Before proceeding, read this chapter thoroughly.

This section covers the following topics:

Performing TSPITR Without a Recovery Catalog

You can perform RMAN TSPITR either with or without a recovery catalog. If you do not use a recovery catalog, then note these restrictions:

Understanding General Restrictions

When performing RMAN TSPITR, you cannot:

Researching and Resolving Inconsistencies

The primary issue for RMAN TSPITR is the possibility of application-level inconsistencies between tables in recovered and unrecovered tablespaces due to implicit rather than explicit referential dependencies. Note the following issues and have the means to resolve possible inconsistencies before proceeding.

This section contains these topics:

RMAN Only Supports Recovery Sets Containing Whole Tables


This limitation is specific to RMAN TSPITR. 

RMAN TSPITR only supports recovery sets that contain whole tables. For example, if you perform RMAN TSPITR on partitioned tables and spread partitions across multiple tablespaces, then RMAN returns an error message during the export phase. Recovery sets that contain either tables without their constraints or the constraints without the table also result in errors.

RMAN Does Not Support Tablespaces Containing Rollback Segments


This limitation is specific to RMAN TSPITR. 

If you are performing TSPITR with transportable tablespaces, then you can take rollback segments in the recovery set offline--thus preventing changes being made to the recovery set before recovery is complete. RMAN TSPITR does not support recovery of tablespaces containing rollback segments. For more information about TSPITR and rollback segments, refer to Oracle9i User-Managed Backup and Recovery Guide.

TS_PITR_CHECK Does Not Check for Objects Owned by SYS

The TS_PITR_CHECK view provides information on dependencies and restrictions that can prevent TSPITR from proceeding. TS_PITR_CHECK does not provide information, however, about dependencies and restrictions for objects owned by SYS.

If any objects--including rollback segments--owned by SYS are in the recovery set, then there is no guarantee that you can successfully recover these objects. TSPITR utilizes the Export and Import utilities, which do not support objects owned by SYS. To find out which recovery set objects are owned by SYS, issue the following statement:


This query shows only the rollback segments owned by SYS:

  WHERE SEGMENT_NAME IN ('segment_name_1','segment_name_2', 

See Also:

Oracle9i User-Managed Backup and Recovery Guide. for more details about the TS_PITR_CHECK view 

Managing Data Relationships

TSPITR provides views that can detect any data relationships between objects in the recovery set and objects in the rest of the database. TSPITR cannot successfully complete unless these relationships are managed, either by removing or suspending the relationship or by including the related object within the recovery set.

See Also:

Oracle9i User-Managed Backup and Recovery Guide. to learn how to research and resolve dependency issues. 

Preparing the Auxiliary Instance for RMAN TSPITR

Satisfy the following requirements discussed in this section before performing RMAN TSPITR:

Task 1: Create an Oracle Password File for the Auxiliary Instance

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

Task 2: Create a Parameter File for the Auxiliary Instance

Create an init.ora file for the auxiliary instance and set the parameters described in the following table.

Parameter  Specify 


The same name as the target database. 


A value different from any database in the same Oracle home. For simplicity, specify _dbname


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 running CONFIGURE AUXNAME


Patterns to convert filenames for the online redo logs of the auxiliary database. 


A different value from the CONTROL_FILES parameter in the target parameter file. 

Set other parameters as needed, including the parameters that allow you to connect as SYSDBA through Oracle Net.

Following are examples of the init.ora parameter settings for the auxiliary instance.



After setting these parameters, ensure that you do not overwrite the init.ora settings for the production files at the target database.  

See Also:

"Specifying Datafile Filenames During RMAN TSPITR" for details about DB_FILE_NAME_CONVERT, and Oracle9i Net Services Administrator's Guide for more information about Oracle Net 

Task 3: Start the Auxiliary Instance

Before beginning RMAN TSPITR, use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT mode (specifying a parameter file if necessary):

SQL> CONNECT SYS/aux_pwd@aux_str AS SYSDBA
SQL> STARTUP NOMOUNT PFILE='/oracle/aux/dbs/initAUX.ora'

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.

Task 4: Ensure 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 connection to the auxiliary instance.

Task 5: Start the Recovery Manager Command-Line Interface

Use one of the following methods discussed in this section to start the RMAN command-line interface:

Connecting from the Operating System Command Line

To connect to the auxiliary instance, target instance, and optional recovery catalog, supply the following information when starting Recovery Manager:

% rman TARGET SYS/target_pwd@target_str CATALOG rman/cat_pwd@cat_str AUXILIARY  \
> SYS/aux_pwd@aux_str



User with SYSDBA privilege


Owner of the recovery catalog


The password for connecting as SYSDBA specified in the target database's orapwd file


The net service name for the target database


The password for user RMAN specified in the recovery catalog's orapwd file


The net service name for the recovery catalog database


The password for connecting as SYSDBA specified in the auxiliary database's orapwd file.


The net service name for the auxiliary database.

Connecting from the RMAN Prompt

You can start the RMAN command-line interface without a connection to the auxiliary instance, and then use the CONNECT command at the RMAN prompt. This example connects in the default NOCATALOG mode:

% rman
RMAN> CONNECT TARGET SYS/target_pwd@target_str

To connect to a catalog, run the CONNECT CATALOG command:

RMAN> CONNECT CATALOG rman/cat_pwd@cat_str

Performing RMAN TSPITR

After you have completed all planning requirements, perform RMAN TSPITR. Run the following commands, where tablespace_list is the list of tablespace names in the recovery set and recovery_end_time is the point to which you want to recover. The following example assumes that you have configured automatic channels:

RECOVER TABLESPACE tablespace_list UNTIL recovery_end_time;

Note that if no auxiliary device configuration is specified, and if RMAN needs to automatically allocate auxiliary channels, then RMAN uses the target database device configuration. It is not necessary to specify configuration information for auxiliary channels unless they require different parameters from the target channels.

The following example assumes that you do not have automatic channels configured and so must manually allocate auxiliary channels:

# manually allocate at least one auxiliary channel
  RECOVER TABLESPACE tablespace_list UNTIL recovery_end_time;

Note that the format for recovery_end_time should use the same format as the NLS_DATE_FORMAT parameter. Also, the tablespace recovery set should not contain the SYSTEM tablespace or any tablespace with rollback segments.

The following example performs TSPITR on tablespaces tbs_2 and tbs_3 to 8:00 p.m. on January 10, 2000:

RECOVER TABLESPACE tbs_2, tbs_3 UNTIL TIME 'JAN 10 2000 20:00:00';

Recovery Manager automatically performs the following steps during TSPITR:

  1. Restores the datafiles to the auxiliary instance.

  2. Recovers the restored datafiles to the specified time.

  3. Opens the auxiliary database with the RESETLOGS option.

  4. Exports the dictionary metadata about objects in the recovered tablespaces--the DDL to create the objects along with pointers to the physical locations of those in the recovered datafiles--to the target database.

  5. Closes the auxiliary database.

  6. Issues SWITCH commands so that the target control file now points to the datafiles in the recovery set that were just recovered at the auxiliary database.

  7. Imports the dictionary metadata that was exported from the auxiliary database, allowing the recovered objects to be accessed.


    RMAN attempts to find datafile copies instead of restoring the datafiles being recovered. If it finds none, then it performs a restore operation and does not execute a switch. If you have configured names for the datafiles with the CONFIGURE AUXNAME command, and suitable datafile copies exist in those AUXNAME locations, then RMAN optimizes away the restore and performs a switch to the AUXNAME datafile copy. 

Preparing the Target Database for Use After RMAN TSPITR

The tablespaces in the recovery set remain offline until after RMAN TSPITR completes successfully.

To prepare the target database for reuse after TSPITR:

  1. Start RMAN and connect to the target database. For example, run:

    % rman TARGET SYS/target_pwd@target_str
  2. Make backups of tablespaces in the recovery set before bringing these tablespaces online. Note that all previous backups of datafiles in the recovery set are no longer valid. For example, this command backs up tablespace tbs_4:

  3. Bring the recovered tablespaces online. For example, enter:

  4. Connect to the auxiliary instance using SQL*Plus. For example:

    % sqlplus 'SYS/aux_pwd@aux_str AS SYSDBA'
  5. Query V$ views to determine the filenames of auxiliary files to be deleted. Query V$DATAFILE of the auxiliary database to determine which datafiles are online, V$LOGFILE for online logs, and V$CONTROLFILE for control files. For example, run:

  6. Because the auxiliary database is not usable after a successful RMAN TSPITR, abort the auxiliary instance:

  7. Delete the following from the operating system:

    • Auxiliary set datafiles restored to temporary locations during RMAN TSPITR

    • Auxiliary database control files

    • Auxiliary database redo log files

Responding to Unsuccessful RMAN TSPITR

A variety of problems can cause TSPITR to abort. For example, if there is a conflict between the target database and the converted filename, then you have to shut down the auxiliary instance, correct the converted datafile name, issue a STARTUP NOMOUNT, and then run RMAN TSPITR again.

Another possible cause for failure is a lack of sufficient sort space for the Export utility. In this case, you need to edit the recover.bsq file (on UNIX, it is located in $ORACLE_HOME/rdbms/admin). This file contains the following:

# tsiptr_7: do the incomplete recovery and resetlogs.  This member is used once. 
define tspitr_7 
# make the control file point at the restored datafiles, then recover them 
recover clone database tablespace &1&; 
alter clone database open resetlogs; 
# PLUG HERE the creation of a temporary tablespace if export fails due to lack of 
# temporary space. 
# For example in Unix these two lines would do that: 
#sql clone "create tablespace aux_tspitr_tmp 
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K"; 

Remove the '#' symbols from the last two lines of comments and modify the statement to create a temporary tablespace. Retry the TSPITR operation, increasing the size of the tablespace until the export operation succeeds.

If TSPITR is unsuccessful for some reason, then follow the procedure below.

To respond to unsuccessful TSPITR:

  1. If RMAN TSPITR is unsuccessful, then shut down the auxiliary instance:

  2. Identify and correct the error.

  3. Start the auxiliary instance without mounting it. For example, enter:

  4. Perform TSPITR again, following the instructions in "Performing RMAN TSPITR".

Specifying Datafile Filenames During RMAN TSPITR

You may need to tune the performance of RMAN TSPITR. This section contains these topics:

Specify a New Name for Datafiles in Auxiliary Set Tablespaces

Recovery Manager restores and recovers all datafiles belonging to the tablespaces in the recovery set and auxiliary set at the auxiliary instance. Note that the auxiliary set includes the SYSTEM tablespace plus all the tablespaces with rollback segments.

Specify a new name for any datafiles in the auxiliary set tablespace using the SET NEWNAME command. RMAN uses this new name as the temporary location in which to restore and recover the datafile. This new name also overrides the setting in the DB_FILE_NAME_CONVERT parameter in the initialization parameter file. For example, to rename datafile 2 to new_df_name.f enter:

  SET NEWNAME FOR DATAFILE 2 TO '/oracle/dbs/new_df_name.f'; 
  RECOVER ...;

You can specify new filenames for any datafiles in recovery set tablespaces. If you specify a new name, then the new filenames replace the original filenames in the target control file.

When setting new filenames, RMAN does not check for conflicts between datafile names at the auxiliary and target databases. Any conflicts result in an RMAN error during TSPITR.

Set the Auxiliary Name and Use a Datafile Copy for Recovery Manager TSPITR

Using a datafile copy on disk is much faster than restoring a datafile. Hence, you may wish to use an appropriate copy of a datafile in the recovery or auxiliary set instead of restoring and recovering a datafile.

Recovery Manager uses a datafile copy if the following conditions are met:

  1. The datafile copy name is registered in the recovery catalog as the auxiliary name of the corresponding datafile with the following command (where filename is the datafile name or number, and aux_datafile_name is the datafile auxiliary name):

  2. The datafile copy was made before the time specified in the UNTIL clause with the following RMAN command (where 'filename' is the datafile filename):

      COPY DATAFILE 'filename' TO AUXNAME;


The following commands are examples of the conditions required by Recovery Manager:

CONFIGURE AUXNAME FOR DATAFILE '/df_1_1.dbf' TO '/backup/df_1_1.dbf';

RMAN does not use a datafile copy if you use SET NEWNAME for the same datafile.

If RMAN uses a datafile copy and TSPITR completes successfully, then the auxiliary_datafile_name is removed from the recovery catalog, and updated to status DELETED in the control file. The original datafile at the target is replaced by this datafile copy after RMAN TSPITR completes.

Use the Converted Filename in the Auxiliary Control File

If neither a new name nor auxiliary name is set for a datafile in an auxiliary set tablespace, then RMAN can use the converted filename specified in the auxiliary database control file to perform the restore and recovery. Recovery Manager checks for conflicts between datafile names at the auxiliary and target databases. Any conflicts result in an error.

If neither a new name or auxiliary name is set for a datafile in a recovery set tablespace, or if the file at the auxiliary name is unusable, then RMAN uses the original location of the datafile.

Summary: Datafile Naming Methods

The following commands and parameters are used to name datafiles in the auxiliary and recovery sets during TSPITR. The order of precedence in the following table goes top to bottom, so SET NEWNAME takes precedence over CONFIGURE AUXNAME and DB_FILE_NAME_CONVERT.

Order of Precedence  Command/Parameter  Auxiliary Set  Recovery Set 





If filenames are not converted in the auxiliary set, then RMAN signals an error during TSPITR.

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index