RMAN Point in time Recovery (PITR) scenario of a dropped oracle tablespace

advertisements

_____________________________________________________________________________________________________________________

Facts: Tablespace point in time recovery cannot be used in this scenario because the control file will not have any details about the dropped tablespace. If you try “restore tablespace <tablespace_name>” command in RMAN it will throw error “RMAN-06019 – could not translate tablespace name”.

Recovery can be done either one of the following methods.

1. Do a point in time recovery of the whole database until the time when the tablespace was dropped with a full database OUTAGE window
2. 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.
3. 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.

Steps for RMAN Recovery

1. Startup the database in nomount state. startup nomount;
2. 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.

RMAN> restore controlfile from autobackup;

3. After the controlfile recovery, mount the database

RMAN> alter database mount;

4. 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

RMAN> restore the controlfile from ‘/db1/oradata/FRA/prod/autobackup/2010_05_27/o1_mf_n_752265016_6y0nh0bm_.bkp’;

After successful completion of above command, the database will be restored with an old controlfile which is taken before the tablespace drop.

5. 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.

RMAN> run {

2> set until time "to_date('24-JUL-2009 04:28:00','DD-MON-YYYY HH24:Mi:SS')";

3> restore database;

4> recover database;

5> }

6. Open the database with resetlogs option

RMAN> alter database open resetlogs;

Now the database recovery completed with the dropped tablespace.

_____________________________________________________________________________________________________________________

Website Stats