Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







RMAN tablespace restore

Oracle Database Tips by Donald BurlesonMay 17, 2016

Question: What do I do to restore an individual tablespace in RMAN.  I have a table and it's indexes in a separate tablespace and I want to restore just one tablespace using RMAN.  How do I restore a tablespace.

Answer:  The RMAN utility allows for a tablespace point-in-time recovery (TSPITR).   An RMAN Backup and Tablespace Point-in-Time Recovery (TSPITR) is simple.  To perform an incomplete recovery on a tablespace which consists of one or more datafiles, RMAN uses the Tablespace Point-in-Time Recovery (TSPITR) feature.  This feature recovers erroneously updated data that was due to users error or the batch process that stopped after updating some data on different datafiles of a tablespace while the database is running. 

To use this type of recovery, we need to have an auxiliary instance.  It should be an instance created by RMAN automatically or a user-defined instance.  To recover a tablespace to any point in time, RMAN performs the following steps:

If there is not any user-managed auxiliary instance, RMAN creates an automatic auxiliary instance assigning to it a random SID, starts it up and connects to it.
As TSPITR is performed while the database is open, RMAN takes the tablespace that is recovering offline.

  • It then restores the control file from backup of the target database to the auxiliary instance according to the time/SCN value specified in the UNTIL clause of the command.
  • Next it restores datafiles of the system and undo tablespaces with the datafiles of the recovered tablespace from backup to the auxiliary destination, and then recovers it to the specified point in time and opens the auxiliary instance with resetlogs option.
  • Next, it exports metadata of the recovered tablespace from the auxiliary instance to a dump file and shuts down the auxiliary instance. 
  • If there were any changes to datafiles names, then RMAN issues switch commands to update the control file name with the recovered datafiles information.
  • Next it imports metadata of objects in the recovered tablespace to the target database.
  • Then it removes the auxiliary instance and deletes all files associated with it.

Before starting to perform TSPITR, we should check the relationship between objects of the recovered tablespace and objects of the different tablespaces which will not be recovered.  We can also check on which objects will be lost after the recovery.  To check on the relationship and the objects that will be lost after the recovery, two views are used: ts_pitr_check and ts_pitr_objects_to_be_dropped views.  An example of how to use both views will be shown in the next scenario.

To recover the restored tablespace, RMAN needs an auxiliary instance.  It is possible to leave it to RMAN to create it automatically, or we can create our own auxiliary instance.  In this section, both ways will be examined.

In the following scenario, we will see how our DBA, Bob, has recovered two dropped tables of one tablespace consisting of two datafiles.  To perform this scenario, we need to create a tablespace with two datafiles and two tables in each datafile.  Now start the scenario.
Bob has created a tablespace and two tables in each datafile and backs up the database as follows:

 tablespace tbs datafile 'c:\tbs1.dbf' size 1m autoextend on next 1m;
Tablespace created.
 user bob identified by bob;
User created.
 dba to bob;
Grant succeeded.
user bob default tablespace tbs;
user altered.

 table tbl_test1
as select * from
table created.
 tablespace tbs add datafile 'c:\tbs2.dbf' size 1m autoextend on next
Tablespace altered.
 segment_name, a.tablespace_name, header_file, file_name
 dba_segments a, dba_data_files b
  2  where
  3  and

--------------- ------------------    -----------   ------------
tbl_test1       tbs                        5 c:\tbs1.dbf
tbl_test2       tbs                        6 c:\tbs2.dbf
RMAN> backup database;

At 20.02, he gets a call from another DBA who says that suddenly  two tables were dropped from his schema a minute ago.  Bob decides to perform an incomplete recovery of the tablespace where these tables resided.  As a first step, he tries to find out the scn value of the database before the tables were dropped as follows:
 timestamp_to_scn(to_timestamp('05.11.2009 20.00.00','
hh24:mi:ss')) scn

As noted above, before performing any TSPITR, two kinds of checks should be performed.  The first check is the check of the relationship between objects of the recovered and non-recovered tablespaces.  The second check is to identify the objects which will be lost after recovery.  Therefore, Bob performs the first check as follows:

select * from
 (ts1_name='TBS' and ts2_name<>'TBS') or (ts2_name='TBS'
no rows selected




Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training

  Oracle consulting and training


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster