RMAN Partial Restoration and Recovery Steps

Partial Restoration is the easiest way to check whether the backup of database which is taken on regular basis can be restored during any disaster situation, in another terms we can say partial restoration is done for backup contingency testing.

In most cases where database size is in TBs we shouldn't restore full database just to check whether database backup that we are taking can be restored or not.

So in this case we can perform Partial Restoration by just restoring mandatory tablespace and one of the application tablespace.

Below are the steps that you are required to do to perform Partial Restoration of Oracle database.

OS:- Linux/Aix

Database Version:- 11g or Higher

NOTE:- Make sure you have latest backup of Production which includes control file auto backup, if not then kindly create control file and transfer it to test server.

Step 1: - Create pfile on production

SQL>  create pfile='/backuplocation/initSIDNAME.ora' from spfile;

Step 2: - Check on Production Database which tablespace you want to restore.

SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;

SQL> SELECT TS#,NAME FROM V$TABLESPACE;

       TS# NAME

---------- ------------------------------

         0 SYSTEM

         1 UNDOTBS1

         2 SYSAUX

         3 TEMP

         4 USERS

         5 APP1

         6 APP2

         7 APP3

 

Step 3: - Transfer Database Backup as Well as Pfile File Backup to Test Server.

Step 4: - Start Database on Test Setup to nomount with help of pfile.

 NOTE: - If mount-points are different as that of production, then we need to edit pfile and make changes in pfile for e.g. control file location, logfile location, db_create_file_dest, etc.

$ export ORACLE_SID=SIDNAME

$ sqlplus / as sysdba

SQL> Connected to Ideal instance.

SQL> Startup nomount pfile='/backuplocation/initSIDNAME.ora';

SQL> EXIT;

Step 5: - Restore Control file from autobackup

$ rman target /

 RMAN> restore controlfile from autobackup;

RMAN> exit;

 

Step 6: - Create spfile from Pfile and then start Database with SPFILE.

SQL> create spfile from pfile='/backuplocation/initSIDNAME.ora';

SQL> shutdown immediate;

SQL> Startup mount;

 

Step 7: - Check Control file Location.

SQL> SHOW PARAMETER CONTROLFILE

SQL> SHOW PARAMETER SPFILE

SQL> EXIT;

 

Step 8: - For Partial Restoration We Will Restore Only Mandatory Tablespace For e.g. ("SYSTEM","SYSAUX","UNDOTBS1") And One Regular Tablespace.

RMAN> run

{

allocate channel t1 type ‘sbt_tape’;

allocate channel t2 type ‘sbt_tape’;

allocate channel t3 type ‘sbt_tape’;

restore tablespace UNDOTBS1, SYSTEM, SYSAUX, APP1 from tag ‘’; 

release channel t1;

release channel t2;

release channel t3;

}

NOTE:-  From Steps 9  to 12 I have used SQLPLUS to offline drop datafiles of non-restored tablespace and to recover database. If you want to do this steps through RMAN I have included steps below (see steps after step 12)

Step 9: - Offline Drop datafiles which are not part of above restored tablespace.

SQL> select 'Alter database datafile '|| file_name || ' Offline drop' || ';' from dba_data_files where tablespace_name in (‘APP3’,’APP2’,’USERS’);

 NOTE:- Execute The Output Of Above Given Dynamic Query On TEST SETUP

Step 10: - Take Clean bounce off Database and Open it in Mount-Stage.

SQL> Shutdown immediate;

SQL> Startup mount

 

Step 11: - Recover Database By applying Archives

SQL> Recover database using backup controlfile untill cancel;

 

Step 12: - Open Database in resetlogs (Incomplete recovery).

SQL> alter database open resetlogs;

 

############

OR THROUGH RMAN

############

Step 9: - Recover Database by skipping tablespaces which we have not restored.

run

{

allocate channel t1 type ‘sbt_tape’;

allocate channel t2 type ‘sbt_tape’;

allocate channel t3 type ‘sbt_tape’;

set until sequence 2000;  ------ (Max sequence + 1)

recover database skip forever tablespace APP2, APP3, USERS;

release channel t1;

release channel t2;

release channel t3;

}

Step 10: - Open Database in resetlogs (Incomplete recovery).

RMAN> sql 'alter database open resetlogs';

If you have any doubt on this topic, you can ask in comments. Thanks for reading. :)