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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

RMAN tablespace restore

Oracle Database Tips by Burleson Consulting

May 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:


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

create
 table tbl_test1
as select * from
 dba_objects;
table created.
 
alter
 tablespace tbs add datafile 'c:\tbs2.dbf' size 1m autoextend on next
1m;
Tablespace altered.
 
SQL>
select
 segment_name, a.tablespace_name, header_file, file_name
from
 dba_segments a, dba_data_files b
  2  where
 a.header_file=b.file_id
  3  and
 segment_name
in
('tbl_test1','tbl_test2'); 


SEGMENT_NAME      TABLESPACE_NAME     HEADER_FILE   FILE_NAME
--------------- ------------------    -----------   ------------
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:
SQL>
select
 timestamp_to_scn(to_timestamp('05.11.2009 20.00.00','dd.mm.yyyy
hh24:mi:ss')) scn
from
 dual;
 
       SCN
----------
    604515

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
 sys.ts_pitr_check
where
 (ts1_name='TBS' and ts2_name<>'TBS') or (ts2_name='TBS'
and
 ts1_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 -  2016

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