11
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

• Planning for RMAN TSPITR

• Preparing the Auxiliary Instance for RMAN TSPITR

• Performing RMAN TSPITR

• Preparing the Target Database for Use After RMAN TSPITR

• Responding to Unsuccessful RMAN TSPITR

• Specifying Datafile Filenames During RMAN TSPITR

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:

• An erroneous DROP TABLE or TRUNCATE TABLE operation

• A table that has become logically corrupted

• An incorrect batch job or other DML statement that has affected only a subset of the database

• A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database

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 the illustration sbr81085.gif

Glossary of TSPITR Terminology

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

TSPITR

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:

• Backup control file

• SYSTEM tablespace

• Datafiles containing rollback segments

• Temporary tablespace (optional). A small space is required by Export for sort operations.

  See Also: "Responding to Unsuccessful RMAN TSPITR" for more information on sort space issues

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

• Understanding General Restrictions

• Researching and Resolving Inconsistencies

• Managing Data Relationships

  Note: Many of the limitations and planning steps in this chapter can also be found in Oracle9i User-Managed Backup and Recovery Guide; however, differences in limitations and planning exist. These differences are explicitly noted.

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:

• The rollback segments at the time of the TSPITR are needed in the auxiliary set. Because RMAN has no historical record of the rollback segments in the control file, RMAN assumes that the current rollback segments were the same segments present at the time to which recovery is performed. If the rollback segments have changed since that time, then TSPITR fails when the recovery tries to write to a nonexistent file.

• If RMAN recovers to a point in time that is too old, then Oracle may have reused the control file records of the copies and backups, thus making it impossible to perform TSPITR.

Understanding General Restrictions

When performing RMAN TSPITR, you cannot:

• Run the target and auxiliary databases on separate nodes. However, the target and auxiliary databases can be in a cluster configuration that uses shared disks.

• Recover dropped tablespaces.

• Recover a tablespace that has been dropped and re-created with the same name.

• Remove a datafile that has been added to a tablespace. If the file was added after the point to which RMAN is recovering, then the file will still be part of the tablespace (and will be empty) after RMAN TSPITR is complete.

• Issue DML statements on the auxiliary database--the auxiliary database is for recovery only.

• Use existing backups of the recovery set datafiles for recovery after TSPITR is complete. Instead, take fresh backups of the recovered files. If you attempt to recover using a backup taken prior to performing TSPITR, then recovery fails.

• Recover optimizer statistics for objects that have had statistics calculated on them; recalculate statistics after performing TSPITR.

• Place any of the following objects within the recovery set:
  • Replicated master tables

  • Tables with VARRAY columns

  • Tables with nested tables

  • Tables with external files

  • Snapshot logs

  • Snapshot tables

  • Objects owned by SYS (including rollback segments)

    Caution: Do not perform RMAN TSPITR for the first time on a production system or when you have a time constraint.

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

• RMAN Does Not Support Tablespaces Containing Rollback Segments

• TS_PITR_CHECK Does Not Check for Objects Owned by SYS

RMAN Only Supports Recovery Sets Containing Whole Tables

Note: 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

Note: 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:

SELECT OBJECT_NAME, OBJECT_TYPE
  FROM SYS.DBA_OBJECTS
  WHERE OWNER = 'SYS';

This query shows only the rollback segments owned by SYS:

SELECT SEGMENT_NAME, SEGMENT_TYPE
  FROM SYS.DBA_EXTENTS
  WHERE SEGMENT_NAME IN ('segment_name_1','segment_name_2', 
                         'segment_name_n') 
  AND OWNER = 'SYS'; 

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

• Task 2: Create a Parameter File for the Auxiliary Instance

• Task 3: Start the Auxiliary Instance

• Task 4: Ensure Oracle Net Connectivity to the Auxiliary Instance

• Task 5: Start the Recovery Manager Command-Line Interface

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
DB_NAME	The same name as the target database.
LOCK_NAME_SPACE	A value different from any database in the same Oracle home. For simplicity, specify _dbname.
DB_FILE_NAME_CONVERT	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.
LOG_FILE_NAME_CONVERT	Patterns to convert filenames for the online redo logs of the auxiliary database.
CONTROL_FILES	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.

DB_NAME=prod1
LOCK_NAME_SPACE=_prod1
CONTROL_FILES=/oracle/aux/cf/aux_prod_cf.f
DB_FILE_NAME_CONVERT=("/oracle/prod/datafile","/oracle/aux/datafile")
LOG_FILE_NAME_CONVERT=("/oracle/prod/redo_log","/oracle/aux/redo_log")

Note: 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

• Connecting from the RMAN Prompt

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

where:

SYS	User with SYSDBA privilege
rman	Owner of the recovery catalog
target_pwd	The password for connecting as SYSDBA specified in the target database's orapwd file
target_str	The net service name for the target database
cat_pwd	The password for user RMAN specified in the recovery catalog's orapwd file
cat_str	The net service name for the recovery catalog database
aux_pwd	The password for connecting as SYSDBA specified in the auxiliary database's orapwd file.
aux_str	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 AUXILIARY SYS/aux_pwd@aux_str
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
RUN
{
  ALLOCATE 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.

   Note: 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:

   BACKUP TABLESPACE tbs_4;
   
   

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

   SQL "ALTER TABLESPACE tbs_4 ONLINE";
   
   

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:

   SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
   SQL> SELECT MEMBER FROM V$LOGFILE;
   SQL> SELECT NAME FROM V$CONTROLFILE;
   
   

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

   SQL> SHUTDOWN ABORT
    
   

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:

   SHUTDOWN ABORT;
   
   

2. Identify and correct the error.

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

   STARTUP NOMOUNT PFILE=initAUX.ora;
    
   

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

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

• Use the Converted Filename in the Auxiliary Control File

• Summary: Datafile Naming Methods

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:

RUN
{ 
  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):

   CONFIGURE AUXNAME FOR DATAFILE FILENAME TO aux_datafile_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):

   RUN
   {
     COPY DATAFILE 'filename' TO AUXNAME;
     .
     .
     .
   }
   

Example

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';
COPY DATAFILE '/df_1_1.dbf' TO AUXNAME;

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
1	SET NEWNAME	X	X
2	CONFIGURE AUXNAME	X	X
3	DB_FILE_NAME_CONVERT	X

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