RMAN: SET NEWNAME Command Using SQL

When you must restore the database the same directory structure is not always there. If you have a database containing 1000s of datafiles it can be very tedious to setup the set newname commands for all the datafiles. Using sqlplus we can extract the information we need into a file which can then be easily modifed and executed as an RMAN script to complete the task.

===================================================
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile 'file#' to NEW;' from v$datafile;
-- select 'set newname for datafile 'file#' to /newpath/NEW;' from v$datafile;
spool off
===================================================
There are 2 select statements above with slightly different output.

Select #1 Output:

set newname for datafile 1 to NEW;
set newname for datafile 2 to NEW;
set newname for datafile 3 to NEW;
set newname for datafile 4 to NEW;
set newname for datafile 5 to NEW;

Select #2 Output:

set newname for datafile 1 to /newpath/NEW;
set newname for datafile 2 to /newpath/NEW;
set newname for datafile 3 to /newpath/NEW;
set newname for datafile 4 to /newpath/NEW;
set newname for datafile 5 to /newpath/NEW;

To generate set newname commands to point to an ASM volume execute the sql below
===================================================
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile 'file#' to ''+DG'';' from v$datafile;
spool off
===================================================