Recover Dropped tablespace using RMAN tablespace point in time recovery

Recover Dropped tablespace using RMAN tablespace point in time recovery

This is new feature for Oracle 11g R2. How to recover dropped Tablespace using RMAN Tablespace point in time recovery

Step 1: Create Tablespace

SQL> conn / as sysdbaConnected.
SQL> create tablespace testtbs datafile 'd:\backup\testtbs01.dbf' size 100m; 
Tablespace created. 
SQL> create user testtbs identified by testtbs default tablespace testtbs; 
User created.
SQL> grant connect,resource to testtbs; 
Grant succeeded. 
SQL> conn testtbs/testtbs;
Connected.
SQL> create table test(empname varchar2(20),city varchar2(20)); 
Table created.
SQL> insert into test values('azar','riyadh'); 
1 row created. 
SQL> insert into test values('jabar','chennai'); 
1 row created.
SQL> commit; 
Commit complete.
SQL> conn testdb/testdb;
Connected. 
(Note : This table allocated for other tablespace , i just insert data for this

 table for check data consistent after doing TBPITR)

SQL> create table d(empname varchar2(20)); 
Table created. 
SQL> insert into d values('kareem'); 
1 row created. 
SQL> insert into d values('syed'); 
1 row created. 
SQL> commit; 
Commit complete.

Step 2: Backup database Plus archivelog

C:\Users\mazar>set oracle_sid=azardb 
C:\Users\mazar>rman target sys/Admin123 
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 1 15:39:34 2011 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. 
connected to target database: AZARDB (DBID=1652383192) 
RMAN> backup database plus archivelog;
Starting backup at 01-JAN-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK

Step 3: Note Current SCN

SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database; 
CURRENT_SCN
-----------
  8448197

Step 4: Drop tablespace

SQL> drop tablespace testtbs including contents and datafiles; 

Tablespace dropped.

Step 5: I just added data for another table allocated for other tablespace due to just  

check for data consistent.

SQL> conn testdb/testdb;
Connected.
SQL> insert into d values('azmi'); 
1 row created. 
SQL> commit; 
Commit complete.

Step 6: Create auxiliary & Recover tablespace using tablespace point in time recovery.

C:\Windows\system32>rman target sys/Admin123 Recovery Manager: Release 11.2.0.1.0 -

 Production on Sat Jan 1 14:45:08 2011 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. 
connected to target database: AZARDB (DBID=1652383192)
 RMAN> run{
2> recover tablespace testtbs
3> until scn 8448197
4> auxiliary destination 'D:\backupnew'; 
5> } 

SQL> select status,tablespace_name from dba_tablespaces where tablespace_name like 'TESTTBS%'; 
STATUS    TABLESPACE_NAME
--------- ------------------------------
OFFLINE   TESTTBS 

11 rows selected. 
Alter tablespace online
SQL> alter tablespace testtbs online; 
Tablespace altered.
 Step 8: Check table

SQL> conn testtbs/testtbs;Connected.
SQL> select * from tab; 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE 
SQL> select * from test; 
EMPNAME              CITY
-------------------- --------------------
azar                 riyadh
jabar                chennai
SQL> 

And also I'm go to check other table for data consistent. 
SQL> conn testdb/testdb;
Connected.
SQL> select * from d; 

EMPNAME
--------------------
azmi
kareem
syed

Now successfully recovered dropped tablespace using RMAN point in time recovery