10
Recovery Manager Tablespace Point-in-Time Recovery

Attention:

Due to the complex nature of tablespace point-in-time recovery, Oracle recommends that you contact and work with Worldwide Customer Support Services before using the procedures described here.

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

Introduction to Recovery Manager Tablespace Point-in-Time Recovery

Recovery Manager (RMAN) automated Tablespace Point-In-Time Recovery (TSPITR) enables you to quickly recover one or more tablespaces to a point-in-time that is different from that of the rest of the database. TSPITR is most useful in the following situations:

To recover from an erroneous drop or truncate table operation.
To recover a table that has become logically corrupted.
To recover from an incorrect batch job or other DML statement that has affected only a subset of the database.
In cases where there are multiple logical databases in separate tablespaces of one physical database, and where one logical database must be recovered to a point different from that of the rest of the physical database.
For VLDBs (very large databases) even if a full database point-in-time recovery would suffice, you might choose to do tablespace point-in-time recovery rather than restore the whole database from a backup and perform a complete database roll-forward (see "Planning for Recovery Manager Tablespace Point-in-Time Recovery" before making any decisions).

Similar to a table export, RMAN TSPITR enables you to recover a consistent data set; however, the data set is the entire tablespace rather than just one object.

Prior to Oracle8, point-in-time recovery could only be used on a subset of a database by:

Creating a copy of the database
Rolling the copied database forward to the desired point in time
Exporting the desired objects from the copied database
Dropping the relevant objects from the production database
Importing the objects into the production database

However, there was a performance overhead associated with exporting and importing large objects.

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

TSPITR

Tablespace Point-in-Time Recovery

Clone Database

The copied database used for recovery in Oracle 8 TSPITR is called a "clone database", and has various substantive differences from a regular database.

Recovery Set

Tablespaces that require point-in-time recovery to be performed on them.

Auxiliary Set

Any other items required for TSPITR, including:

backup control file
system tablespaces
datafiles containing rollback segments
temporary tablespace (optional)

A small amount of space is required by export for sort operations. If a copy of the temporary tablespace is not included in the auxiliary set, then you must provide sort space either by creating a new temporary tablespace after the clone has been started up, or by setting autoextend to ON on the system tablespace files.

Planning for Recovery Manager Tablespace Point-in-Time Recovery

Recovery Manager TSPITR is a complicated procedure and requires careful planning. Before proceeding you should read all of this chapter thoroughly.

warning:

You should not perform RMAN TSPITR for the first time on a production system, or during circumstances where there is a time constraint.

Attention:

Many of the limitations and planning steps in this chapter can also be found in Chapter 13, "Performing Tablespace Point-in-Time Recovery"; however, differences in limitations and planning exist. These differences are explicitly called to your attention in this chapter.

Limitations

This section describes the limitations associated with performing RMAN TSPITR. As you begin the planning stage, familiarize yourself with these limitations.

The primary issue you should consider when deciding whether or not to perform RMAN TSPITR is the possibility of application-level inconsistencies between tables in recovered and unrecovered tablespaces (due to implicit rather than explicit referential dependencies). You must understand these dependencies, and also have the means to resolve any possible inconsistencies before proceeding.

Recovery Manager Only Supports Recovery Sets Containing Whole Tables

Note:

This limitation is specific to RMAN TSPITR.

Recovery Manager TSPITR only supports recovery sets that contain whole tables. If, for example, you are performing RMAN TSPITR on partitioned tables, and have partitions spread across more than one table, RMAN will return an error message during the export phase of TSPITR. Recovery sets that contain tables without their constraints, or only the constraints without the table, will 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 manual TSPITR, you can take rollback segments in the recovery set offline--thus preventing changes being made to the recovery set before recovery is complete. However, 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 for TSPITR".

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. However, TS_PITR_CHECK does not provide information about dependencies and restrictions for objects owned by SYS.

If there are any objects, including undo segments, owned by SYS in the recovery set, there is no guarantee that you can successfully recover these objects (because TSPITR utilizes the Export and Import utilities, which do not operate on 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 ('<tablespacename1>','<tablespacename',' 
   <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".

TS_PITR_CHECK Does Not Detect Snapshot Tables

The TS_PITR_CHECK view does not detect snapshot tables (it does detect snapshot logs); they are exported as stand-alone tables. Thus, if a snapshot is dropped at time 3, and a backup from time 1 is used to roll forward to time 2, after TSPITR is complete the snapshot table will have been created as a stand-alone table, but without its associated snapshot view.

Partitioned Tables and TS_PITR_CHECK

If any of the tablespaces supplied to the predicate contain the first segment of a partitioned table, then the result set of the TS_PITR_CHECK view is inverted. If tablespaces supplied to the predicate do not include the first segment of a partitioned table, then one row is returned for the partition in question. If the tablespaces supplied to the predicate contain the first segment of a partitioned table, the results are inverted (for example, one row is returned for every tablespace containing partitions of that partitioned table, but not the tablespace that was supplied to the predicate). Returned rows indicate that there is a conflict that you must resolve by exchanging the partitions with stand-alone tables.

Bitmap Indexes

You must drop and re-create bitmap indexes after you complete TSPITR. If you don't, they will be unusable. If any bitmap indexes exist on the tables, imports will fail even if the bitmap indexes have been dropped from the primary database. An incorrect index segment will also be created, despite the failure, and you will have to drop and re-build the index.

Non-Partitioned Global Indexes

The TS_PITR_CHECK view does not detect non-partitioned global indexes of partitioned tables that are outside the recovery set. This is apparent when the view is queried manually and also during the export and import phase of TSPITR. After TSPITR completes, the old index still exists on the recovered table, even though no errors are returned. You must drop and re-create the index.

Note:

Because the index is still valid, queries that use the index will return incorrect rows.

General Restrictions

In addition to the preceding limitations, RMAN TSPITR has the following restrictions:

You cannot use RMAN TSPITR to recover dropped tablespaces.
You cannot use RMAN TSPITR to recover a tablespace that has been dropped and re-created with the same name.
You cannot use RMAN TSPITR to remove a datafile that has been added to the wrong tablespace. If the file was added after the point to which RMAN TSPITR is being performed, then the file will still be part of the tablespace (and will be empty) after RMAN TSPITR is complete.
You cannot use DML statements on the clone database--the clone database is for recovery purposes only.
After RMAN TSPITR is complete, you cannot use existing backups of the recovery set datafiles for recovery; instead, you must take fresh backups of the recovered files. If you attempt to recover using a backup taken prior to performing TSPITR, recovery will fail (with the error message ORA 1247, 00000, "database recovery through TSPITR of tablespace %s").
RMAN TSPITR does not recover optimizer statistics for objects that have had statistics calculated on them; statistics must be re-calculated after performing TSPITR.
The following object types are not allowed within the RMAN TSPITR recovery set:
- replicated master tables
- tables with varray columns
- tables with nested tables
- tables with external Bfiles
- snapshot logs
- snapshot tables
- objects owned by SYS (including rollback segments)

Data Consistency and Recovery Manager TSPITR

TSPITR provides views that can detect any data relationships between objects that are in the tablespaces being recovered 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.

Recovery Manager TSPITR Planning Requirements

You must satisfy the following requirements before performing RMAN TSPITR.

Create an Oracle Password File for the Clone Instance

For information about creating and maintaining Oracle password files, see "Password File Administration" in the Oracle8 Administrator's Guide.

Set init.ora Parameters for Clone Instance

You must set the following parameters in the init.ora file used by the clone instance.

CONTROL_FILES
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT

These settings will apply to the control file name, converted datafile names, and converted logfile names at the clone database.

Example of init.ora Parameter Settings for Clone Instance:

Following are examples of the init.ora parameter settings you must make for the clone instance.

control_files=/oracle/clone/cf/clon_prod_cf.f
db_file_name_convert=("/oracle/prod/datafile","/oracle/clone/datafile")
log_file_name_convert=("/oracle/prod/redo_log","/oracle/clone/redo_log")

See Also: For details about DB_FILE_NAME_CONVERT, see "Tuning Considerations".

Attention:

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

Create a Clone Database

Before beginning RMAN TSPITR, you must have a clone database up (startup NOMOUNT) and running.

Start the Recovery Manager Command Line Interface

Use either of the following two methods to start the RMAN command line interface.

Connect To the Clone Instance, Target Instance and Recovery Catalog

To connect to the clone instance, target instance and recovery catalog, you must supply the following information when starting up Recovery Manager:

rman target sys/<tsyspwd>@<target_str> 
     rcvcat rman /<rmanpwd>@<rcvcat_str>
     clone sys/<csyspwd>@<clone_str>

Where:

<tsyspwd>

The "connect sys" password specified in the target database's orapwd file

<target_str>

The TNS alias for the target database

<rmanpwd>

The "connect rman" password specified in the recovery catalog's orapwd file

<rcvcat_str>

The TNS alias for the recovery catalog database

<csyspwd>

The "connect sys" password specified in the clone database's orapwd file.

<clone_str>

The TNS alias for the clone database.

Start Up the Recovery Manager Without the Connection to the Clone Instance

You can start the Recovery Manager command line interface without a connection to the clone instance, and then use the connect clone Recovery Manager command to make the clone connection, as follows:

RMAN> connect clone sys/<syspwd>@<clone_str>;

Performing Recovery Manager Tablespace Point-In-Time Recovery

After you have completed all planning requirements you can perform RMAN TSPITR.

To perform RMAN TSPITR, issue the following commands (where <tbslist> is the list of tablespace names in the recovery set):

'allocate clone channel'
'recover tablespace <tbslist> until'

At least one clone channel must be allocated with the allocate clone channel command.

Reminder:

The tablespace recovery set should not contain the system tablespace or any tablespace with rollback segments.

The following example statement runs RMAN TSPITR:

RMAN>  run  {
       allocate clone channel dev1 type 'SBT_TAPE';
       recover tablespace tbs_2, tbs_3 until time 'Jan 10 1998 20:00:00'; 
       }

Back Up Tablespaces After Recovery Manager TSPITR Is Complete

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

You must make backups of tablespaces in the recovery set before placing these tablespaces online; all previous backups of datafiles in the recovery set are no longer valid.

RMAN TSPITR is not repeatable after completing successfully. At this point, all tablespaces and datafiles in the recovery set are assigned a new SCN. However, when you make a backup after RMAN TSPITR, you can perform another RMAN TSPITR to an until_time after this backup set creation time.

The clone database is not usable after a successful RMAN TSPITR; therefore, you should release the memory using the SHUTDOWN ABORT statement.

Should RMAN TSPITR be unsuccessful, SHUTDOWN ABORT the clone instance and restart (using NOMOUNT) after identifying and correcting the error. For example, if there is a conflict between the target database and the converted filename, the user should shutdown the clone instance, correct the converted datafile name, restart (using NOMOUNT), and run RMAN TSPITR again.

Also, after a successful RMAN TSPITR you can remove the following:

clone auxiliary set datafiles restored to temporary locations during RMAN TSPITR
clone database control files
clone database redo log files

Tuning Considerations

This section describes issues that can affect the performance of RMAN TSPITR.

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 clone instance (Remember that the auxiliary set includes the system tablespace plus all the tablespaces with rollback segments). You can specify a new name for any datafiles in the auxiliary set tablespace using the set newname Recovery Manager command. Recovery Manager will use 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 init.ora parameter. You can specify a new name for any datafiles in recovery set tablespaces.

If you specify a new name for datafiles in the recovery set tablespace, the datafile(s) will replace the original datafile in the control file at the target database--so the new filename replaces the existing filename.

Recovery Manager does not check for conflicts between datafile names at the clone and target databases. Any conflicts will result in an RMAN error.

Set the Clone 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 will use a datafile copy if the following 2 conditions are met:

The datafile copy name is registered in the recovery catalog as the clone name of the corresponding datafile via the following command (where <datafile> is the datafile name or number, and <clone_datafilename> is the datafile clone name):

     RMAN> set clonename for datafile <datafile> to <clone_datafilename>


The datafile copy was made before the recovery "until_time" using the following RMAN command (where <datafile> is the datafile name):
     RMAN> run  {
           copy datafile <datafile> to clonename;
           ...}

Examples

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

     RMAN> set clonename for datafile '/oracle/prod/datafile_1_1.dbf'
               to '/oracle/prod_copy/datafile_1_1.dbf';

     RMAN> run  {
                allocate channel dev1 type disk;
                copy datafile '/oracle/prod/datafile_1_1.dbf'
                to clonename;
                }

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 clone_datafilename will be marked "deleted" in the recovery catalog. The original datafile at the target will be replaced by this datafile copy after RMAN TSPITR is complete.

Use the Converted Datafile Name

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

If neither a new name nor clone name is set for a datafile in a recovery set tablespace, or the file at the clone name is unusable, Recovery Manager uses the original location of the datafile.

<tsyspwd>	The "connect sys" password specified in the target database's orapwd file
<target_str>	The TNS alias for the target database
<rmanpwd>	The "connect rman" password specified in the recovery catalog's orapwd file
<rcvcat_str>	The TNS alias for the recovery catalog database
<csyspwd>	The "connect sys" password specified in the clone database's orapwd file.
<clone_str>	The TNS alias for the clone database.

10 Recovery Manager Tablespace Point-in-Time Recovery Attention: Due to the complex nature of tablespace point-in-time recovery, Oracle recommends that you contact and work with Worldwide Customer Support Services before using the procedures described here.