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.

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

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.

OR

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;
}

subscribe on youtube

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

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks