RMAN : Recovery of a dropped tablespace

If a tablespace has been dropped, 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.

Step 1. Lets create a tablespace droptest first.
SQL> create tablespace droptest datafile '/u01/app/oracle/oradata/HCL/droptest01.dbf' size 50m;

Tablespace created.

Step 2. Delete this tablespace including contents and datafiles.
SQL> drop tablespace droptest including contents and datafiles;

Tablespace dropped.

Step 3. Now try to recover the dropped tablespace using RMAN
RMAN> recover tablespace droptest;

Starting recover at 18-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/18/2013 20:43:24
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "DROPTEST"

RMAN>
See the error - RMAN-20202: Tablespace not found in the recovery catalog

Step 4. So to recover the dropped tablespace, we have two options:
1) Do a point in time recovery of the whole database until the time the tablespace was dropped.
2) 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 the 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.

Step 5. Lets create the same tablespace again and take a full backup of database including controlfile.

SQL> create tablespace droptest datafile '/u01/app/oracle/oradata/HCL/droptest01.dbf' size 50m;

Tablespace created.

Take full backup.

RMAN> backup database plus archivelog;

Drop the tablespace DROPTEST again and we will recover it as below.

Step 6. Start the database in nomount stage and restore the controlfile from backup.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  631914496 bytes
Fixed Size                  1338364 bytes
Variable Size             394265604 bytes
Database Buffers          230686720 bytes
Redo Buffers                5623808 bytes

Step 7. Next We need to restore controlfile from backup which contains metadata of tablespace DROPTEST.

RMAN> restore controlfile from autobackup;

Starting restore at 18-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: HCL
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/HCL/autobackup/2013_01_18/o1_mf_s_805064205_8hltfpnt_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/HCL/autobackup/2013_01_18/o1_mf_s_805064205_8hltfpnt_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/HCL/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/HCL/control02.ctl
Finished restore at 18-JAN-13

Now restored controlfile contains the metadata of dropped Tablespace DROPTEST.

RMAN> report schema;

------

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    670      SYSTEM               ***     /u01/app/oracle/oradata/HCL/system01.dbf
2    450      SYSAUX               ***     /u01/app/oracle/oradata/HCL/sysaux01.dbf
3    30       UNDOTBS1             ***     /u01/app/oracle/oradata/HCL/undotbs01.dbf
4    5        USERS                ***     /u01/app/oracle/oradata/HCL/users01.dbf
5    0        DROPTEST             ***     /u01/app/oracle/oradata/HCL/droptest01.dbf
6    20       TEMP                 32767   /u01/app/oracle/oradata/HCL/temp01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/HCL/temp01.dbf



The alert log will also show the time when the tablespace was dropped. We can also see that a controlfile autobackup has taken place after the tablespace was dropped

Fri Jan 18 21:01:20 2013
drop tablespace droptest including contents and datafiles
Deleted file /u01/app/oracle/oradata/HCL/droptest01.dbf
Completed: drop tablespace droptest including contents and datafiles

Step 7.Now that we know the time the tablespace was dropped, we can do a point in time recovery of the DATABASE in order to recover the tablespace which has been dropped.

RMAN> run {
set until time "to_date('18-JAN-2013 21:01:00','DD-MON-YYYY HH24:Mi:SS')";
restore database;
recover database;
}
2> 3> 4> 5>
executing command: SET until clause

Starting restore at 18-JAN-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/HCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/HCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/HCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/HCL/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/HCL/droptest01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/HCL/backupset/2013_01_18/o1_mf_nnndf_TAG20130118T205505_8hltbl5x_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/HCL/backupset/2013_01_18/o1_mf_nnndf_TAG20130118T205505_8hltbl5x_.bkp tag=TAG20130118T205505
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:28
Finished restore at 18-JAN-13

Starting recover at 18-JAN-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/HCL/archivelog/2013_01_18/o1_mf_1_2_8hltfmh8_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/HCL/redo03.log
archived log file name=/u01/app/oracle/flash_recovery_area/HCL/archivelog/2013_01_18/o1_mf_1_2_8hltfmh8_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/oradata/HCL/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-JAN-13

RMAN> alter database open resetlogs;

database opened

Step 8 Now check the the restored tablespace.

SQL> select file_name,bytes from dba_data_files where tablespace_name='DROPTEST';

FILE_NAME                                               BYTES
-------------------------------------------------- ----------
/u01/app/oracle/oradata/HCL/droptest01.dbf           52428800