A
Performing Tablespace Point-in-Time Recovery with Recovery Manager

This chapter describes how to use Recovery Manager (RMAN) to perform tablespace point-in-time recovery (TSPITR), and includes the following topics:

• Introduction to RMAN TSPITR

• Planning for TSPITR

• Preparing the Auxiliary Instance for TSPITR

• Performing TSPITR

• Preparing the Target Database for Use After TSPITR

• Responding to Unsuccessful TSPITR

• Tuning TSPITR Performance

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:

• 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 database to a point different from the rest of the physical database when multiple logical databases 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 16-4 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 16-4 RMAN TSPITR

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 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 (for more information on sort space issues, see "Responding to Unsuccessful TSPITR").

Planning for 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 Chapter B, "Performing Operating System Tablespace Point-in-Time Recovery"; however, differences in limitations and planning exist. These differences are explicitly called to your attention in this chapter.

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, note these restrictions:

• Because RMAN has no historical record of the rollback segments in TSPITR, RMAN assumes that the current rollback segments were the same segments present at the time to which recovery is performed.

• If RMAN recovers to a remote time, Oracle may have reused the 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. The target and auxiliary databases can be in a cluster configuration, however, using 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, recovery fails.

• Recover optimizer statistics for objects that have had statistics calculated on them; re-calculate 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)

    WARNING: Do not perform RMAN TSPITR for the first time on a production system or when there is 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:

• RMAN Only Supports Recovery Sets Containing Whole Tables

• Recovery Manager Does Not Support Tables 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 tables, 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.

Recovery Manager Does Not Support Tables Containing Rollback Segments

Note: This limitation is specific to RMAN TSPITR.

If you are performing O/S TSPITR, 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, see "Step 3: Prepare the Primary Database".

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 tablespace_name IN ('tablespace_name_1','tablespace_name_2', 
      'tablespace_name_n') 
AND owner = 'SYS'; 

See Also: For more details about the TS_PITR_CHECK view, see "Step 2: Research and Resolve Dependencies on the Primary Database".

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 will not 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: For more information see "Step 2: Research and Resolve Dependencies on the Primary Database".

Preparing the Auxiliary Instance for TSPITR

Satisfy the following requirements before performing RMAN TSPITR:

• Create an Oracle Password File for the Auxiliary Instance

• Create a Parameter File for the Auxiliary Instance

• Start the Auxiliary Instance

• Ensure Net8 Connectivity to the Auxiliary Instance

• Start the Recovery Manager Command Line Interface

Create an Oracle Password File for the Auxiliary Instance

For information about creating and maintaining Oracle password files, see the Oracle8i Administrator's Guide.

Create a Parameter File for the Auxiliary Instance

Create an init.ora file for the auxiliary instance and set the following required parameters:

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 using set 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 Net8.

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")

See Also: For details about DB_FILE_NAME_CONVERT, see "Tuning TSPITR Performance". For more information about Net8, see the Net8 Administrator's Guide.

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

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

Ensure Net8 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.

Start the Recovery Manager Command Line Interface

Use one of the following methods to start the RMAN command line interface:

• Connect from the O/S Command Line

• Connect from the RMAN Prompt

Connect from the O/S 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 privileges
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.

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

% rman
RMAN> connect auxiliary sys/aux_pwd@aux_str
RMAN> connect target target sys/target_pwd@target_str
RMAN> connect catalog rman/cat_pwd@cat_str

Performing TSPITR

After you have completed all planning requirements, perform RMAN TSPITR. Issue the following commands within run, 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:

allocate auxiliary channel . . . 
recover tablespace tablespace_list until recovery_end_time;

You must allocate at least one auxiliary channel with the allocate auxiliary channel command.

Note: The tablespace recovery set should not contain the SYSTEM tablespace or any tablespace with rollback segments.

The following example statement performs RMAN TSPITR on tablespaces TBS_2 and TBS_3 to 8 p.m. on January 10, 1999:

run {
     allocate auxiliary channel dev1 type 'sbt_tape';
     recover tablespace tbs_2, tbs_3 until time 'Jan 10 1999 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, it performs a restore operation and does not execute a switch. If you have configured names for the datafiles with the set auxname command, and suitable datafile copies exist in those auxname locations, RMAN optimizes away the restore and performs a switch to the auxname datafile copy.

Preparing the Target Database for Use After TSPITR

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

To prepare the target database for re-use after TSPITR:

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

   run {
        allocate channel ch1 type disk;
        backup tablespace tbs_4;
   }
       
   
            

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

   sql "ALTER TABLESPACE TBS_4 ONLINE";
       
   
            

3. Because the auxiliary database is not usable after a successful RMAN TSPITR, release the memory by shutting down the database:

   shutdown abort;
       
    
            

4. Delete the following:
   • Auxiliary set datafiles restored to temporary locations during RMAN TSPITR

   • Auxiliary database control files

   • Auxiliary database redo log files

Responding to Unsuccessful 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, you will 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 will need to edit the recover.txt file (in UNIX, it is located in $ORACLE_HOME/admin). This file contains the following:

# 
# tsiptr_7: do the incomplete recovery and resetlogs.  This member is used once. 
# 
define tspitr_7 
<<< 
# make the controlfile point at the restored datafiles, then recover them 
recover clone database tablespace &1&; 
sql clone "alter 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, follow the procedure below.

To respond to unsuccessful TSPITR:

1. Should RMAN TSPITR be unsuccessful, 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 as in "Performing TSPITR".

Tuning TSPITR Performance

This section describes steps you can take to tune the performance of RMAN TSPITR:

• 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 Recovery Manager command. RMAN uses this new name as the temporary location in which to restore and recover the datafile. This new name will also override the setting in the DB_FILE_NAME_CONVERT parameter in the init.ora 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';

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

When setting new filenames, RMAN does not check for conflicts between datafile names at the auxiliary and target databases. Any conflicts will 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 via the following command (where filename is the datafile name or number, and auxiliary_datafile_name is the datafile auxiliary name):

       set auxname for datafile filename to auxiliary_datafile_name;
   
   

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

   run {
        copy datafile 'filename' to auxname;
        ...
   }
   

Examples

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

set auxname for datafile '/oracle/prod/datafile_1_1.dbf' 
  to '/oracle/prod_copy/datafile_1_1.dbf';

run {
     allocate channel ch1 type disk;
     copy datafile '/oracle/prod/datafile_1_1.dbf'
     to auxname;
}

Recovery Manager will not use a datafile copy if you use set newname for the same datafile.

If Recovery Manager uses a datafile copy and TSPITR completes successfully, the auxiliary_datafile_name will be marked deleted in the recovery catalog. The original datafile at the target will be 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, Recovery Manager 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 the file at the auxiliary name is unusable, Recovery Manager 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 table goes top to bottom, so set newname takes precedence over set auxname and DB_FILE_NAME_CONVERT:

	Command/Parameter	Auxiliary Set	Recovery Set
1	set newname	X	X
2	set auxname	X	X
3	DB_FILE_NAME_CONVERT	X

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

---