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 DROPTABLE or TRUNCATETABLE statement

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, RMAN does the following:

Restores the specified tablespace backups

Recovers the specified tablespaces

Exports metadata from the auxiliary instance

Points the target database control file to the newly recovered datafiles

Figure 11-1 RMAN TSPITR

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. For example, if you need to recover tablespace users to a noncurrent time, then users is the tablespace in the recovery set.

Auxiliary Set

Any other files required for TSPITR, including:

Backup control file

SYSTEM tablespace

Datafiles containing rollback or undo segments

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

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 or undo segments at the time of the TSPITR are needed in the auxiliary set. Because RMAN has no historical record of the undo in the control file, RMAN assumes that the current rollback or undo segments were the same segments present at the time to which recovery is performed. If the rollback or undo segments have changed since that time, then TSPITR fails when the recovery tries to write to a nonexistent file.

TSPITR to a time that is too old may not succeed if Oracle has reused the control file records for needed copies and backups.

Assume that you run TSPITR on a tablespace, and then bring the tablespace online at time t. Backups of the tablespace created before time t are no longer usable for recovery with a current control file. Thus, you cannot run TSPITR again on this tablespace to recover it to any time less than or equal to time t, nor can you use the current control file to recover the database to any time less than or equal to t.

The reason for the additional restrictions in NOCATALOG mode is that the current control file has no record of the older incarnation of the recovered tablespace. Thus, recovery with a current control file that involves this tablespace can no longer use a backup taken prior to time t. However, if you restore a control file backed up prior to t, then you can perform incomplete recovery of the whole database to any time less than or equal to t.

Understanding General Restrictions

When performing RMAN TSPITR, you cannot do the following:

Run the target and auxiliary databases on separate computers. 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 instance--the auxiliary instance is a temporary instance used for recovery only.

Assume that you perform TSPITR on a tablespace when connected to a recovery catalog, and then bring the tablespace back online at time t. You can use backups created before time t to recover any tablespace or the whole database up to time t, but not later. Hence, you should immediately back up the tablespace after performing TSPITR.

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.

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

Tables without their constraints or constraints without their tables.

Tables with VARRAY columns.

Tables with nested tables.

Tables with external files.

Snapshot logs and snapshot tables.

Tablespaces containing undo or rollback segments.

Objects owned by SYS (including rollback segments).

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.

Determining Whether Objects Will Be Lost

When RMAN TSPITR is performed on a tablespace, any objects created after the recovery time are lost. To see which objects will be lost, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database. The contents of the view are described in Table 11-1.

Table 11-1 TS_PITR_OBJECTS_TO_BE_DROPPED View

Column Name

Meaning

OWNER

Owner of the object to be dropped.

NAME

The name of the object that will be lost as a result of undergoing TSPITR

CREATION_TIME

Creation timestamp for the object.

TABLESPACE_NAME

Name of the tablespace containing the object.

When querying this view, supply all the elements of the date field, otherwise the default setting is used. Also, use the TO_CHAR and TO_DATE functions. For example, with a recovery set consisting of users and tools, and a recovery point in time of '2001-06-02:07:03:11', issue the following statement:

SELECT OWNER, NAME, TABLESPACE_NAME, TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('USERS','TOOLS')
AND CREATION_TIME > TO_DATE('01-JUN-02:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

Researching and Resolving Dependencies on the Primary Database

Query the TS_PITR_CHECK view to identify relationships between objects that overlap the recovery set boundaries. If this view returns rows when queried, then investigate and correct the problem. Proceed with TSPITR only when TS_PITR_CHECK view returns no rows for the tablespaces not in the recovery set. Record all actions performed during this step so that you can retrace these relationships after completing TSPITR.

Supply a four-line predicate detailing the recovery set tablespace to query the TS_PITR_CHECK view. For example, with a recovery set consisting of tools and users, the SELECT statement against TS_PITR_CHECK would be as follows:

SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN ('USERS','TOOLS')
AND TS2_NAME NOT IN ('USERS','TOOLS')
)
OR (
TS1_NAME NOT IN ('USERS','TOOLS')
AND TS2_NAME IN ('USERS','TOOLS')
);

To run a complete TSPITR check on all the tablespaces in the database (not just the tablespaces in the recovery set), you can run the following query:

Assume a case in which the partitioned table tp has two partitions, p1 and p2, that exist in tablespaces users and tools respectively. Also assume that a partitioned index called tpind is defined on tp, and that the index has two partitions id1 and id2 (that exist in tablespaces id1 and id2 respectively). In this case, you would get the following output when TS_PITR_CHECK is queried against tablespaces users and tools (assuming appropriate formatting):

Overview of Datafile Naming in RMAN TSPITR

Before you begin setting up the auxiliary instance to perform the TSPITR, you must decide on a naming method for the datafiles in the auxiliary set. If the names for files in the auxiliary set are not different from the filenames in use by the target database, then RMAN signals an error during TSPITR and exits.

For example, if you are creating the auxiliary database in the /tmp directory, then the absolute path name of the auxiliary datafiles must be prefixed by /tmp. These auxiliary files are intended only for performing the TSPTIR, so you should delete them immediately afterward. If filenames are not converted in the auxiliary set, then RMAN signals an error during TSPITR and exits.

Optionally, you may also choose to rename the datafiles in the recovery set tablespaces on the target (not the auxiliary) database. For example, if you are performing TSPITR on datafile ?/oradata/trgt/users01.dbf in the users tablespace, you may decide to rename it to /disk2/datafiles/users01.dbf. This operation is equivalent to an ALTERDATABASERENAMEFILE for specified files in the target database.

Table 11-1 describes the commands and parameters 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 SETNEWNAME takes precedence over CONFIGUREAUXNAME and DB_FILE_NAME_CONVERT.

Table 11-2 Datafile Naming Methods

Order of Precedence

Command/Parameter

Can Name Files in Auxiliary Set?

Can Rename Files in Recovery Set?

1

SETNEWNAME

Yes

Yes

2

CONFIGUREAUXNAME

Yes

Yes

3

DB_FILE_NAME_CONVERT

Yes

No

Using SET NEWNAME to Name Files in the Auxiliary and Recovery Sets

You can specify a new name for any datafiles in the auxiliary set with the RMAN command SETNEWNAME. 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, if this parameter happens to be set.

You can also use SETNEWNAME to rename 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.

Using SET NEWNAME to Name Files: Example

For example, assume that the auxiliary set contains the following datafiles:

?/oradata/trgt/system01.dbf of the SYSTEM tablespace

?/oradata/trgt/undotbs01.dbf of the undotbs tablespace

The recovery set contains the following datafiles:

?/oradata/trgt/users01.dbf of the users tablespace

?/oradata/trgt/tools01.dbf of the tools tablespace

You want to create the auxiliary database in the /tmp directory. Also, you decide to rename the datafile in tools to /private1/tools01.dbf, but leave the datafile in the users tablespace with its original name.

In this case, you can run the following command to run TSPITR on tools and users, causing ?/oradata/trgt/tools01.dbf to be renamed to ?/dbs/tools01.dbf on the target database:

RUN
{
# set newnames for auxiliary set datafiles
SET NEWNAME FOR DATAFILE '?/oradata/trgt/system01.dbf' TO '/tmp/system01.dbf';
SET NEWNAME FOR DATAFILE '?/oradata/trgt/undotbs01.dbf' TO '/tmp/undotbs01.dbf';
# rename one recovery set datafile
SET NEWNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '/disk1/tools01.dbf';
RECOVER TABLESPACE tools, users UNTIL SEQUENCE 1034 THREAD 1;
}

Using Datafile Copies in the Auxiliary and Recovery Sets

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.

RMAN TSPITR can use a datafile copy if the following conditions are met:

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

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;

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.

Note:

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

Using CONFIGURE AUXNAME to Name Files: Example

For example, assume that the auxiliary set contains the following datafiles:

?/oradata/trgt/system01.dbf of the SYSTEM tablespace

?/oradata/trgt/undotbs01.dbf of the undotbs tablespace

The recovery set contains the following datafiles:

?/oradata/trgt/users01.dbf of the users tablespace

?/oradata/trgt/tools01.dbf of the tools tablespace

Also, assume that you ran the following commands at a time before the end time of the desired TSPTIR:

In this case, you can run the following command to run TSPITR on tools and users, causing ?/oradata/trgt/tools01.dbf to be renamed to ?/dbs/tools01.dbf on the target database:

RECOVER TABLESPACE tools, users UNTIL SEQUENCE 1034 THREAD 1;

Using Initialization Parameters to Name the Auxiliary Set Files

You can use the DB_FILE_NAME_CONVERT parameter in the auxiliary initialization parameter file to name auxiliary set--but not recovery set--datafiles.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. RMAN 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.

Using Initialization Parameters to Name Files: Example

For example, assume that the auxiliary set contains the following files:

?/oradata/trgt/system01.dbf of the SYSTEM tablespace

?/oradata/trgt/undotbs01.dbf of the undotbs tablespace

The recovery set contains the following files:

?/oradata/trgt/users01.dbf of the users tablespace

?/oradata/trgt/tools01.dbf of the tools tablespace

You want to create the auxiliary database in the /tmp directory. Also, you decide to rename the datafile in tools to /private1/tools01.dbf, but leave the datafile in the users tablespace with its original name.

In this case, you can set the following parameter in the auxiliary initialization parameter file to name the auxiliary set files:

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

Task 2: Create a Parameter File for the Auxiliary Instance

Create an initialization parameter file for the auxiliary instance and set the parameters described in Table 11-3.

Table 11-3 Initialization Parameters in the Auxiliary Instance

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. For example, if the target database name is trgt, then specify _trgt.

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 with SETNEWNAME or CONFIGUREAUXNAME. Obtain the datafile filenames by querying V$DATAFILE.NAME, and ensure that the conversion pattern matches the format of the filename displayed in the view.

Note: Some platforms do not support ending the patterns in a forward or backward slash (/ or \).

Patterns to convert filenames for the online redo logs of the auxiliary database. Obtain the online redo log filenames by querying V$LOGFILE.MEMBER, and ensure that the conversion pattern matches the format of the filename displayed in the view.

Note: Some platforms do not support ending the patterns in a forward or backward slash (\ or /).

CONTROL_FILES

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

REMOTE_LOGIN_PASSWORDFILE

Set to EXCLUSIVE when connecting to the auxiliary instance by means of a password file.

COMPATIBLE

The same value as the parameter in the target database.

DB_BLOCK_SIZE

If this initialization parameter is set in the target database, then it must be set to the same value in the auxiliary instance.

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

Following are examples of the initialization parameter settings for the auxiliary instance:

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

Performing RMAN TSPITR

After completing all planning requirements, recover the recovery set tablespaces, specifying the end point of recovery. You do not have to take the tablespaces offline first because RMAN does it automatically. The following command performs TSPITR on the users tablespace until log sequence 13:

RECOVER TABLESPACE users UNTIL SEQUENCE 13 THREAD 1;

If no auxiliary device configuration is specified, and if RMAN needs to automatically allocate auxiliary channels, then RMAN uses the target database device configuration. You do not need to configure 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:

If you specify UNTILTIME, then the format for the time should use the same format as the NLS_DATE_FORMAT parameter.

RMAN automatically performs the following steps during TSPITR:

Takes the tablespaces to be recovered offline.

Restores the datafiles to the auxiliary instance.

Recovers the restored datafiles to the specified time.

Opens the auxiliary database with the RESETLOGS option.

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.

Shuts down the auxiliary instance.

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.

Imports the dictionary metadata that was exported from the auxiliary instance, 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 CONFIGUREAUXNAME command, and suitable datafile copies exist in those AUXNAME locations, then RMAN optimizes away the restore and performs a switch to the recovered 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:

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

% rman TARGET SYS/oracle@trgt

Make backups of tablespaces in the recovery set before bringing these tablespaces online. For example, makes a new backup of tablespace users:

BACKUP TABLESPACE users;

Note:

If you are running in NOCATALOG mode, then you cannot perform TSPITR on a tablespace and then perform it again on the same tablespace to a time equal to or before the TSPITR time. However, after TSPITR completes and you make a new backup of the tablespace, you can perform TSPITR on the tablespace to any time after the backup.

Bring the recovery set tablespaces online. For example, enter:

SQL "ALTER TABLESPACE users ONLINE";

Connect to the auxiliary instance with SQL*Plus and shut down the auxiliary instance. For example:

sqlplus 'SYS/oracle@aux AS SYSDBA'<<EOF
SHUTDOWN ABORT;
EXIT;
EOF

Delete the following files from the operating system:

Auxiliary set datafiles restored to temporary locations during TSPITR

Auxiliary database control files

Auxiliary database redo log files

Responding to Unsuccessful RMAN TSPITR

A variety of problems can cause TSPITR to fail. 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 STARTUPNOMOUNT, 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 use the following procedure.

To respond to unsuccessful TSPITR:

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