- Do a point in time recovery of the whole database until the time when the tablespace was dropped with a full database OUTAGE window
- Restore the database into different location from the last valid backup and export the tables from the tablespace. Create the dropped tablespace in main database and import the tables dump. No downtime needed as it can be done online.
- Restore the database into different location from the last valid back and transport the tablespace into actual database using transportable tablespace option. No downtime needed as it can be done online.
- Startup the database in nomount state. startup nomount;
- Recover the controlfile from autobackup. There would be a autobackup directory under db_recovery_file_dest and the backups are stored under this directory. If some major change happens to the database the autobackup will create a new backup file in this directory. So the recovery will be done from the latest .bkp file which means in this scenario in latest .bkp file will not have the dropped tablespace information. The dropped tablespace history will be recorded in the file which is backed up before the tablespace drop.
- After the controlfile recovery, mount the database
- Next restoration of the controlfile from the latest autobackup which is taken before tablespace drop. Find out the latest backup file name from the corresponding autobackup directory using the tablespace drop timestamp recorded in alert.log and the .bkp file timestamp. Suppose we got a file with name as below. /db1/oradata/FRA/prod/autobackup/2010_05_27/o1_mf_n_752265016_6y0nh0bm_.bkp
- Then recover the database using backup controlfile with until clause. Specify the timestamp just before the tablespace drop. You can get the tablespace drop timestamp from alert.log file.
- Open the database with resetlogs option