How to recover of a dropped tablespace?

This scenario may be unusual, but if this situation will come, then you may feel lots of trouble. But if you have valid backups OR dataguard environments, then you are 100% safe to restore the database.

In this case, 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.

e.g, to drop a tablespace,

SQL> drop tablespace SAMPLE  including contents and datafiles;

Tablespace dropped.

When you will try to recover below error will come:

RMAN> restore tablespace SAMPLE;

....

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

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

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

RMAN-03002: failure of restore command at 04/07/2017 13:17:45

RMAN-20202: tablespace not found in the recovery catalog

So to recover from a 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 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.

Let us examine the first option using the example shown below:

In this example, CONTROLFILE AUTOBACKUP has been turned on and Flashback has been enabled for the database.

With Flashback enabled, the db_recovery_file_dest will have a sub-directory ‘autobackup’ as shown below for each day.

When we drop the tablespace we are changing the structure of the database and since controlfile autobackup has been turned on, we see another backup file has been created in the autobackup location in the flash recovery area on disk.

SQL> drop tablespace EXAMPLE including contents and datafiles;

Tablespace dropped.

We then shutdown the database, startup in nomount mode and attempt to restore the controlfile from autobackup.

The most recent controlfile autobackup has been restored, but since this has been taken after the tablespace was dropped, the tablespace which has been dropped (ARUL) is not referenced in the control file that we just restored. If we try to restore and recover the database, the dropped tablespace will not be restored.

SQL> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> report schema;

-- here you will not fine any EXAMPLE tablespace.

We will need to restore a backup of the controlfile which contains records for the tablespace ARUL. We use the RESTORE CONTROLFILE FROM command to restore a specific controlfile autobackup.

RMAN>  restore controlfile from '/FRA/oracle/testdb/TESTDB/autobackup/20017_07_04/o1_mf_s_893930026_87f0fbo2_.bkp';

  

RMAN> report schema;

.....

-- here you able to see your dropped tablespace. 

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.

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 {

2> set until time "to_date('04-JUL-2017 13:45:00','DD-MON-YYYY HH24:Mi:SS')";

3> restore database;

4> recover database;

5> }

RMAN> alter database open resetlogs;

database opened

We can now see that the tablespace which has been dropped has been recovered

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

From Oracle 11g on wards you can do Tablespace point-in-time recovery.

Click here to read about Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)