| 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.
Prerequisites
Before you perform a TSPITR, you must have the following:
- A backup of all the datafiles belonging to the tablespace that need recovery
- A backup of the current control file
This may be created using the
ALTER DATABASE BACKUP CONTROLFILE TO <CONTROLFILE_NAME>; statement
- 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.
- To begin a tablespace point-in-time recovery, perform a consistent backup of the primary database.
- Next, copy the backup of the current control file to a different location and edit the parameter file for the cloned database.
- Create a copy of the primary database.
- Restore all the datafiles to an alternate location for the cloned database.
- Perform the time-based incomplete recovery by applying the archived logs and open the database.
- Export the recovered data files using the Oracle Export utility from the cloned database.
- Import the data using the Oracle Export utility into the primary database.