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!
|
|
|
|
| |
|
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.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
