Introduction to User-Managed Tablespace Point-in-Time Recovery

TSPITR Terminology

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

TSPITR

Tablespace point-in-time recovery

Primary Database

The database containing the tablespace or tablespaces that you want to recover to a prior point in time.

Auxiliary Database

A copy of the current database that is restored from a backup. It includes restored backups on the auxiliary host of the following files:

• Datafiles belonging to the SYSTEM tablespace

• Datafiles in the set of tablespaces to be recovered

• Datafiles belonging to an undo tablespace or tablespace that contains rollback segments

All backups must be from a point in time prior to the desired recovery time.

Recovery Set

All the tablespaces on the primary database that require point-in-time recovery to be performed on them.

Recovery Set Self-Containment Check

All objects that are part of the recovery set must be self-contained: there can be no dependencies on objects outside the recovery set. For example, if a table is part of the recovery set and its indexes are in a separate tablespace, then the recovery set must include the tablespace containing the index. Alternatively, the index can be dropped. You can check the recovery set tablespaces for self-containment with the procedure DBMS_TTS.TRANSPORT_SET_CHECK.

Auxiliary Set

Any other files required for restoring the auxiliary database, including:

• Backup control file

• Datafiles from the SYSTEM tablespace

• Datafiles in an undo tablespace or datafiles containing rollback segments

Transportable Tablespace

A rapid method of transporting tablespaces across databases by unplugging them from a source database and plugging them into a target database. The databases can even be on different platforms, for example, Solaris and Windows 2000. The unplugging and plugging is done with the Export and Import utilities. Note that there is no actual export and import of the table data, but simply an export and import of internal metadata. During the procedure, the datafiles of the transported tablespaces are made part of the target database.

TSPITR Methods

In releases prior to Oracle9i, you had the following two methods for performing user-managed TSPITR:

• Traditional user-managed TSPITR, which required you to create a special type of database called a clone database

• User-managed TSPITR with the transportable tablespace feature

As of Oracle Database Release 10g, TSPITR should be performed by using the transportable tablespace feature. This procedure is relatively easy to use and is less error prone than the traditional method, which is currently deprecated (although not yet unsupported).

TSPITR is performed by dropping the tablespaces to be recovered from the primary database, restoring a copy of the database called an auxiliary database and recovering it to the desired point in time, then transporting the relevant tablespaces from the auxiliary database to the current version of the primary database.

For ease of use, it is highly recommended that you place the auxiliary and primary databases on different hosts. Nevertheless, you can also perform TSPITR when the databases are located on the same host.

The basic procedure for performing user-managed TSPITR is as follows:

1. Take the tablespaces requiring TSPITR offline.

2. Plan the setup of the auxiliary database.

3. Create the auxiliary database and recover it to the desired point in time.

4. Drop the tablespaces requiring TSPITR from the primary database.

5. Use the transportable tablespace feature to transport the set of tablespaces from the auxiliary database to the primary database.

   See Also:

   Oracle Database Administrator's Guide for a complete account of how to use the transportable tablespace feature