Script to Set newname in RMAN Restore on new Server or Database

Script to Set newname in  RMAN Restore on new Server or Database

If you are restoring the database on another server with a different directory structure, you can leverage the RMAN SET NEWNAME command to map the old file names to the new file locations. 

Steps:

1. Check datafile location on the production environment

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/system01.dbf

/u01/app/oracle/oradata/sysaux01.dbf

/u01/app/oracle/oradata/undotbs01.dbf

/u01/app/oracle/oradata/users01.dbf

/u01/app/oracle/oradata/und01.dbf

2. Generate set newname command for each data file individually

The set newname command will be used to restore the database in a different location (/u02/datafile/).

set linesize 300

col file_name format a120

select 'set newname for datafile ' ||FILE#|| ' to '||'''/u02/datafile/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'' ;' file_name from v$datafile;

Output:

FILE_NAME

-----------------------------------------------------------------------set newname for datafile 1 to '/u01/datafile/system01.dbf' ;

set newname for datafile 2 to '/u01/datafile/sysaux01.dbf' ;

set newname for datafile 3 to '/u01/datafile/undotbs01.dbf' ;

set newname for datafile 4 to '/u01/datafile/users01.dbf' ;

set newname for datafile 5 to '/u01/datafile/und01.dbf' ;

3. Generate RMAN restoration command with set newname on new environment.

set linesize 200 

heading off feedback off 

col file_name format a100

col "RMAN Restoration Script" for a70

select 'run {' "RMAN Restoration Script" from dual 

union all

select 'set newname for datafile ' ||FILE#|| ' to '||'''/u02/datafile/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'' ;' file_name from v$datafile

union all

select 'restore database;' from dual 

union all

select 'switch datafile all;' from dual 

union all

select 'recover database;' from dual 

union all

select '} ' from dual ;

Output:

RMAN Restoration Script

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

run {

set newname for datafile 1 to '/u02/datafile/system01.dbf' ;

set newname for datafile 2 to '/u02/datafile/sysaux01.dbf' ;

set newname for datafile 3 to '/u02/datafile/undotbs01.dbf' ;

set newname for datafile 4 to '/u02/datafile/users01.dbf' ;

set newname for datafile 5 to '/u02/datafile/und01.dbf' ;

restore database;

switch datafile all;

recover database;

}

If you like please follow and comment