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

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 series of images.

You perform a complete recovery when you bring 1) a database, 2) a tablespace, or 3) a data file up-to-date with the most current point in time possible. It is important to emphasize that complete recovery is not synonymous with recovering the complete database. Rather, completeness here alludes to the completeness of the entire database or part of it (tablespace or data file) with reference to the time element. If you update the database tablespace or data file completely by applying all changes from the archived redo logs to the backup files, you are performing a complete backup. In other words, complete recovery will ensure that you have not lost any transactions. Note that when using RMAN, you may also use incremental backups as well, in addition to archived redo logs, during the recovery process.
When you perform media recovery, it isn’t always the case that you can or should bring the database up-to-date to the latest possible point in time. Sometimes you may not want to recover the database to the current point in time. Following a loss of a disk or some other problem, the complete recovery of a database will make the database current by bringing all of its contents up to the present. A point-in-time recovery, also known as incomplete recovery, brings the database to a specified time in the past. A point-in-time recovery implies that changes made to the database after the specified point may be missing. On the face of it, a point-in-time recovery may seem strange. After all, why would you recover your database only to a past period in time and not bring it up-to-date?
There may be situations where a point-in-time recovery is your best bet, as in the following examples:

In all of these situations, you can use either 1) point-in-time recovery or 2) Oracle's flashback technology to get the database back to a previous point in time. Prior to the introduction of the flashback technology, a (DBPITR) database point-in-time recovery and a tablespace point-in-time recovery (TSPITR) were the automatic solutions when confronted by situations such as an erroneous data entry or wrong updates. Flashback technology offers you the capability to perform point-in-time recovery much quicker than the traditional point-in-time recovery techniques that rely on media recovery. The flashback database feature is the alternative to traditional database point-in-time recovery, while the flashback table feature lets you avoid having to perform a media recovery in most cases.

Recovery Manager (RMAN) automatic TSPITR enables you to quickly recover one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

1. You want to recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database. For example, you maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or DML statement corrupts the data in only one of the tablespaces.
2. You want to recover data lost after DDL operations that change the structure of tables. You cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation.
3. You want to recover a table after it has been dropped with the PURGE option.
4. You want to recover from the logical corruption of a table.

tablespace point-in-time recovery (TSPITR): The recovery of one or more non-SYSTEM tablespaces to a noncurrent time. You use RMAN to perform TSPITR. You can also use Flashback Database to rewind data, but you must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to your earliest recoverable backup.
The next lesson concludes this module.