RelationalDBDesign RelationalDBDesign


Incomplete Recovery   «Prev  Next»

Tablespace point-in-time Recovery with Oracle

  1. Perform a consistent backup of the primary database.
  2. Find out the dependencies and restrictions on the primary database. If there are any existing, try to resolve them.
  3. Copy the backup of the current control file to a different location and edit the parameter file for the cloned database.
  4. Create a copy of the primary database.
  5. Restore all the datafiles to an alternate location for the cloned database.
  6. Recover the cloned database using time-based incomplete recovery.
  7. Open the cloned database.
  8. Export the lost data using the Oracle Export utility from the cloned database.
  9. Import the exported data into the primary database using the Oracle Import utility.
  10. Make sure that lost objects are recovered in the primary database.
  11. Perform a backup of the tablespace just recovered in the primary database.
  12. Delete the files of the cloned database to conserve disk space and memory.

Purpose of RMAN TSPTIR

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.

RMAN TSPITR is most useful for the following situations:

  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.