Tag Archives: find location use in RMAN

Set new name query for using in RMAN Backup Utility

1 Reply

Script to generate the datafiles set new commands for using in RMAN Backup

During RMAN duplicate command you need to set the new name for datafiles

From this following script you can only give location of new it will automatically replace and give you the list of data files and log file with set newname syntax as location you modified in following script:

It is very helpful if you have large environment like having 100 data files present in production environment and you need to created duplicate of it in Test Environment.

Script as follows:

SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT 'SET NEWNAME FOR DATAFILE ' || file# || 
       ' TO ''/new/location/datafile_' || file# || '.dbf'';'
FROM   v$datafile;

Example: Usage of SET NEWNAME 

RUN {
  SET NEWNAME FOR DATAFILE 1 TO '/u02/oradata/DB/datafile_1.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/u02/oradata/DB/datafile_2.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/u02/oradata/DB/datafile_3.dbf';

  DUPLICATE TARGET DATABASE TO cloneDB
    LOGFILE GROUP 1 ('/u02/oradata/DB/redo01.log') SIZE 200M,
            GROUP 2 ('/u02/oradata/DB/redo02.log') SIZE 200M;
}