Set newname RMAN script

In this article, we are going to learn how to create a rman restoration script with a set newname parameter. If you try to restore the database on another server using RMAN and the directory structure is different, you can use the below query for restoration.

RMAN script

Step 1. Check datafile location on the production environment

In my case my all production data files are available below location.

SQL> select name from v$datafile;


NAME
------------------------------------------------------------------
/u01/app/oracle/oradata/gaming/system01.dbf
/u01/app/oracle/oradata/gaming/sysaux01.dbf
/u01/app/oracle/oradata/gaming/undotbs01.dbf
/u01/app/oracle/oradata/gaming/users01.dbf
/u01/app/oracle/oradata/gaming/und01.dbf
/u01/dharma01.dbf

6 rows selected.

SET NEWNAME

Step 2. Generate set newname command for each data file individually.

The set newname command helps us to restore the database in a different location (/u01/datafile/).

set linesize 300
col file_name format a120

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

OUTPUT above query:

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' ;
set newname for datafile 6 to '/u01/datafile/dharma01.dbf' ;

6 rows selected.

Generate rman restoration command with set newname

The below query will help you to create a rman restoration script with a set newname parameter.

set linesize 200 
heading off feedback off 
col file_name format a100
col "Query For RMAN Restoration" for a70

select 'run {' "Query For RMAN Restoration" from dual 
union all
select 'set newname for datafile ' ||FILE#|| ' to '||'''/u01/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:

Query For RMAN Restoration
----------------------------------------------------------------------
run {
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' ;
set newname for datafile 6 to '/u01/datafile/dharma01.dbf' ;
restore database;
switch datafile all;
recover database;
}

• Instagram
• YouTube
• Facebook
• Amazon

Read: How to Restore RMAN Backup with a Different Database Name