How to restore and recovery the dropped tablespace in Oracle

When you try to restore the tablespace, then its show following error because current control file does not have information of the tablespace. So you need to first restore the old control file.

RMAN-20202: tablespace not found in the recovery catalog

We have two option to restore table space which is dropped:

1. Recover point in time recovery upto drop command fire.
2. Clone database at new location and transfer table from that database by export or import utility

We are following first method to restore tablespace:

1. Take cold backup of completed database as precautions
 
2. Start the database into nomount stage.
SQL> startup nomount;

3. Restore control file backup from autobackup or last backup present if control database is not open direct in mount state otherwise skip this step

RMAN> restore controlfile from autobackup;
RMAN> restore controlfile from 'D:\rmanbackup\ctl_28012016.bkp';

4. Mount the database

SQL> alter database mount;

5. Check dropped tablespace is present :

rman> report schema;

6. Checked alert log file for drop tablespace command and give time before that command for restore operation:
Suppose tablespace USERS is dropped at 18:31:00.

RMAN> run {
set until time "to_date('29-SEP-2015 18:30:00','DD-MON-YYYY HH24:Mi:SS')";
restore database;
recover database;
}

7. Open database with resetlogs

alter database open resetlogs;

8. Check tablespace present in database:

select file_name,bytes from dba_data_files where tablespace_name='USERS';