Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

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

Example:
1) Create a tablespace.
SQL> create tablespace ts1 datafile 'C:\app\Inam\oradata\orcl\ts01.dbf' size 10m reuse;

2) Create a user and create a table in the new tablespace
SQL>create user ali identified by ali;
SQL>grant dba to ali;
SQL>conn ali/ali
SQL> create table objects tablespace ts1 as select * from dba_objects ;

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.

RMAN> recover tablespace ts1 until logseq 4 auxiliary destination 'd:\';
+++++++++


8) Here rman users transportable tablespace mechanism to plug the dropped tablespace back in to the database.
SQL> select tablespace_name,status,plugged_in from dba_tablespaces;

TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS OFFLINE NO
EXAMPLE ONLINE YES
TS1 OFFLINE YES

7 rows selected.

9) Online the tablespace.
SQL> alter tablespace ts1 online;

Tablespace altered.

10) Check for the data.
SQL> conn ali/ali
Connected.
SQL> select count(*) from objects;
COUNT(*)
----------
75206

Ref: 1277795.1

No comments: