Incomplete Recovery   «Prev  Next»

Lesson 11 Working with tablespace point-in-time recovery
Objective Explain the concepts for point-in-time recovery.

Working with Oracle Tablespace

Point-in-time Recovery

Tablespace point-in-time recovery (frequently abbreviated as TSPITR) refers to recovery of all datafiles in a tablespace to a specific point in time. For example, let us say a developer accidentally dropped a table with data in it yesterday morning, but only informed you today. During this time, some other project teams performed many activities in the same database. Bringing the database back to the time prior to the removal of the table is obviously not a reasonable choice. In this case, you must apply tablespace point-in-time recovery.
There are two basic steps in a TSPITR. First, you clone the current database and recover it to the necessary point-in-time. Second, you export the objects you need from the cloned database and import them into the original database.


Before you perform a TSPITR, you must have the following:
  1. A backup of all the datafiles belonging to the tablespace that need recovery
  2. A backup of the current control file

This may be created using the ALTER DATABASE BACKUP CONTROLFILE TO <CONTROLFILE_NAME>; statement
  1. Enough disk space and memory to clone the current database

Steps to perform a TSPITR

Never try to perform a tablespace point-in-time recovery by yourself. Always have an experienced DBA present as well as the phone number for Oracle technical support at your fingertips. The current database is also referred to as the primary database in the following SlideShow.
You may also preview all the steps.

1) Table Space Point in time Recovery 1 2) Table Space Point in time Recovery 2 3) Table Space Point in time Recovery 3 4) Table Space Point in time Recovery 4 5) Table Space Point in time Recovery 5 6) Table Space Point in time Recovery 6 7) Table Space Point in time Recovery 7

Program 1 Program 2 Program 3 Program 4 Program 5 Program 6 Program 7
  1. To begin a tablespace point-in-time recovery, perform a consistent backup of the primary database.
  2. Next, copy the backup of the current control file to a different location and edit the parameter file for the cloned database.
  3. Create a copy of the primary database.
  4. Restore all the datafiles to an alternate location for the cloned database.
  5. Perform the time-based incomplete recovery by applying the archived logs and open the database.
  6. Export the recovered data files using the Oracle Export utility from the cloned database.
  7. Import the data using the Oracle Export utility into the primary database.

Performing Tablespace point-in-time Recovery
The next lesson concludes this module.