Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Point-In-Time Recovery Tablespaces (Using RMAN)

Posted by FatDBA on November 4, 2012

Point In Time recovery for Tablespaces also known as TSPITR allows you to recover one of more tablespaces to any past time.

Deciding When to Use TSPITR
Like a table import, RMAN TSPITR enables you to recover a consistent data set; however, the data set recovered includes an entire tablespace rather than one object.

RMAN TSPITR is most useful for situations such as these:

  • Recovering data lost after an erroneous TRUNCATE TABLE statement;
  • Recovering from logical corruption of a table;
  • Recovering 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.

Note that, as with database point-in-time recovery (DBPITR), you cannot perform TSPITR if you do not have your archived redo logs. For databases running in NOARCHIVELOG mode, you cannot perform TSPITR. You can only restore your entire database from a consistent backup.

Limitations of TSPITR
There are a number of situations which you cannot resolve by using TSPITR.

  • You cannot recover dropped tablespaces.
  • You cannot recover a renamed tablespace to a point in time before it was renamed. If you try to perform a TSPITR to an SCN earlier than the rename operation, RMAN cannot find the new tablespace name in the repository as of that earlier SCN (because the tablespace did not have that name at that SCN).
  • You cannot recover tables without their associated constraints, or constraints without the associated tables.
  • Tablespaces that contain objects owned by SYS, including rollback segments..

RMAN > Recover tablespace DIXIT until time
“to_date(‘2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination ’/opt/oracle/temp’;

Once the above statement is executed, RMAN does the following for us:

  • Creates auxiliary instance (including the pfile etc..)
  • Mounts the auxiliary instance
  • Makes the candidate tablespace into OFFLINE
  • Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace
  • Applies archives (completes recovery)
  • Opens the database
  • Performs an export of the objects residing in the tablespace
  • Shutdown aux instance
  • Import the objects into the target database
  • Remove aux instance and cleanup


That’s all and you now have all the objects back in the tablespace lost!.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: