Partial Tablespace recovery using RMAN

 Prior to 12C: -

Scenario  : - We have to restore test database from PROD database, there was one schema in PROD database which holds users sensitive data, to secure that we have to take export backup of same schema from TEST and then drop the TEST database and restore PROD database as TEST and then drop sensitive data schema and import schema which we had taken from TEST database, but unfortunately that test dump got deleted from O/S after dropping the TEST database. :(

We have one day old RMAN level0 backup but still we cannot restore it because TEST database is of 6 TB, then we decided to do tablespace recovery. Below are the steps that we performed.

Note : Change file location and tablespace name as per your environment.

Restore the control file back that was taken after level0 backup.

Startup mount, and then get to know the max. sequence of archive file information from the v$log_history.
Select max(sequence#) from v$log_history;

Determine the list of tablespaces that needs to be restored. Look for sys objects in all tablespaces.

Build the set newname for datafiles for choosen tablespaces that needs to be restored.
select 'set newname for datafile '||d.file#||' to '||''''||'/RMAN_tmp/TST11G/oradata/'||substr(d.name,instr(d.name,'/',-1)+1)||''''||';'
from v$datafile d, v$tablespace t where t.ts#=d.ts# and t.name in ('USERS',’TST11G_D', ’TST11G_I','SYSTEM','UNDO_TS1','SYSAUX') order by 1;

Now, run the rman restore...
Export ORACLE_SID=TST11G
rman target = / <<! >TST11G_rman_restore.log
run {
     sql 'alter session set optimizer_mode=rule';
     allocate channel a1 type 'sbt_tape';
     allocate channel a2 type 'sbt_tape';
     allocate channel a3 type 'sbt_tape';
     allocate channel a4 type 'sbt_tape';
     allocate channel a5 type 'sbt_tape';
     allocate channel a6 type 'sbt_tape';
     allocate channel a7 type 'sbt_tape';
     allocate channel a8 type 'sbt_tape';
      send 'NB_ORA_SERV=denver, NB_ORA_CLIENT=TST11Gdb-g1';
      set until sequence 21496;
set newname for datafile 13 to '/u01/oradata/sysaux_2.dbf';
set newname for datafile 12 to '/u01/oradata/sysaux_02.dbf';
set newname for datafile 11 to '/u01/oradata/sysaux_01.dbf';
set newname for datafile 959 to '/u01/oradata/sysaux_03.dbf';
set newname for datafile 960 to '/u01/oradata/sysaux_05.dbf';
set newname for datafile 00919 to '/u01/oradata/undo_ts1_14.dbf';
set newname for datafile 00920 to
'/u01/oradata/undo_ts1_15.dbf';
set newname for datafile 00921 to '/u01/oradata/undo_ts1_16.dbf';
set newname for datafile 00475 to '/u01/oradata/undo_ts1_08.dbf';
set newname for datafile 00828  to '/u01/oradata/TST11G_lrg_i_15.dbf';
set newname for datafile 00888  to '/u01/oradata/TST11G_lrg_i_16.dbf';
     restore tablespace system;
     restore tablespace sysaux;
     restore tablespace undo_ts1;
     restore tablespace TST11G_MED_I;
     restore tablespace TST11G_LRG_I;
     restore tablespace TST11G_SML_D;
     restore tablespace TST11G_SML_I;
     restore tablespace TST11G_LRG_D;
     restore tablespace tools;
     restore tablespace audit_d;
     restore tablespace audit_i;
     restore tablespace users;
SWITCH DATAFILE ALL;
}
exit

Offline all other datafiles than restored tablespaces.

select 'alter database datafile '||''''||d.name||''''||' offline;'
from v$datafile d, v$tablespace t where t.ts#=d.ts# and t.name not in
('TOOLS','USERS','AUDIT_I','AUDIT_D','TST11G_MED_D',
'TST11G_MED_I','TST11G_LRG_I','TST11G_SML_D','TST11G_SML_I',
'TST11G_LRG_D','SYSTEM','UNDO_TS1','SYSAUX') order by 1;

Rename redologfile, before opening the database.

select 'alter database rename file '||''''||member||''''||' to '||''''||'/u02/oradata/RESTORE/REDO/'||substr(member,instr(member,'/',-1)+1)||''''||';' member from v$logfile;

Restore the all archive files.

We already have script to restore archive log, that we use to restore archives for goldengate.

run {
     sql 'alter session set optimizer_mode=rule';
     allocate channel a1 type 'sbt_tape';
     allocate channel a2 type 'sbt_tape';
     allocate channel a3 type 'sbt_tape';
     allocate channel a4 type 'sbt_tape';
     send 'NB_ORA_SERV=denver, NB_ORA_CLIENT=tbmktgdb-g1';
     restore archivelog from logseq 21496 until logseq 21508;

    }

 First logseq will be the sequence that needs to start recovery and the second logseq we can get from rman backup log or from v$log_history.

Start recovery.

Recover database using backup controlfile until cancel;

AUTO

Use resetlogs to open the database.

Alter database open resetlogs;

Select name, open_mode from v$database; 

Take the export backup of that schema, and import it newly refreshed database from PROD.