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
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
- 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
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;
user bob identified by bob;
dba to bob;
user bob default tablespace tbs;
tablespace tbs add datafile
'c:\tbs2.dbf' size 1m autoextend on next
a.tablespace_name, header_file, file_name
dba_segments a, dba_data_files b
tbs 6 c:\tbs2.dbf
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:
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:
ts2_name<>'TBS') or (ts2_name='TBS'
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
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
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
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
and include the URL for the page.
Copyright © 1996 - 2016
All rights reserved by
is the registered trademark of Oracle Corporation.