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

Tablespace point-in-time recovery (TSPITR) with the transportable tablespace feature enables you to quickly recover one or more tablespaces (other than the SYSTEM tablespace) to a time that is prior to the rest of the database.

User-managed TSPITR is most useful for recovering the following:

An erroneous DROP TABLESPACE operation
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
A tablespace in a VLDB (very large database) when TSPITR is more efficient than restoring the whole database from a backup and rolling it forward

Refer to "Preparing for User-Managed Tablespace Point-in-Time Recovery: Basic Steps" before deciding to perform TSPITR.

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:

Take the tablespaces requiring TSPITR offline.
Plan the setup of the auxiliary database.
Create the auxiliary database and recover it to the desired point in time.
Drop the tablespaces requiring TSPITR from the primary database.
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