DBMentors is a solution oriented group, started by a team of qualified and committed professionals with vast experience in IT industry. The team has in-depth technical and design expertise with highest standards of programming quality.

Pages

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, February 24, 2013

RMAN: Recover A Dropped Tablespace Using TSPITR (11gR2)

RMAN automatic Tablespace Point-In-Time Recovery ( TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

Prior to 11.2 version the tspitr had a restriction of not being able to recover the dropped tablesapace.From 11.2 this limitation no longer exists.We can recover the dropped tablespace using TSPITR.

Assumption
CONTROLFILE AUTOBACKUP has been turned on and Flashback has been enabled for the database

3) Make a note of the current log sequence number.
SQL> select sequence# from v$log where status='CURRENT';
SEQUENCE#
----------
1

4) Take a backup of the database and the archivelogs.
rman target /
RMAN> backup database plus archivelog;

5) Now login to the database and just perform a few log switches and then drop the tablespace.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select sequence# from v$log where status='CURRENT';
SEQUENCE#
----------
5

SQL> drop tablespace ts1 including contents and datafiles;

Tablespace dropped.

6) Now note down the current log sequence number in the database.
SQL> select SEQUENCE# from v$log where status='CURRENT';
SEQUENCE#
----------
5

7) The log sequence number in the database when the tablespace was dropped is 5. So if we recover upto sequence 4 then we will be able to get the tablespace back.