Recover Tablespace
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';
Responses