To recover from a case of a dropped tablespace, the Tablespace Point In Time Recovery (TSPITR) method cannot be used.

When you drop a tablespace, the controlfile will then no longer have any records of the tablespace which has been dropped. Attempts to use the RMAN RECOVER TABLESPACE command will return the RMAN error RMAN-06019 – “could not translate tablespace name” as shown below.

SQL> drop tablespace anup including contents and datafiles;

Tablespace dropped.

RMAN> restore tablespace anup;

Starting restore at 03-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=141 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=140 devtype=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 08/03/2009 11:54:11

RMAN-20202: tablespace not found in the recovery catalog

So to recover from a dropped tablespace, we have two options:

• Do a point in time recovery of the whole database until the time the tablespace was dropped.

• Create a clone of the database from a valid backup, export the required tables from the tablespace which has been dropped, recreate the tablespace and then import the tables from the clone.

The first option will require an outage of the entire database and the entire database will be rolled back in tine in order to recover the tablespace. The second option can be peformed online, but we will need to factor in the disk space requirements to create a clone of the database from which the tablespace has been dropped.

How to find object in Tablespace

SELECT * FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = 'tablespace_here';