Thursday, February 27, 2014

SET NEWNAME CLAUSE in Oracle 11g Release 2

Hi Guys,

This is another feature in Oracle 11g Release 2 in RMAN.  Till now we know its only for datafile.

Set NEWNAME Flexibility :

Suppose you are restoring datafiles from the backup, either on the same server or a different one such as staging. If the filesystem (or diskgroup) names are identifical, you won’t have to change anything. But that is hardly ever the case. In staging the filesystems may be different, or perhaps you are restoring a production database to an ASM diskgroup different from where it was originally created. In that case you have to let  RMAN know the new name of the datafile. The way to do it is using the SET NEWNAME command. Here is an example, where your restored files are located on /u02 instead of /u01 where they were codeviously.
   set newname for datafile 1 to ‘/u02/oradata/system_01.dbf’;
   set newname for datafile 2 to ‘/u02/oradata/sysaux_01.dbf’;

   restore database;      … 

Here there are just two datafiles, but what if you have hundreds or even thousands? It will not only be a herculean task to enter all that information but it will be error-prone as well. Instead of entering each datafile by name, now you can use a single set newname clause for a tablespace. Here is how you can do it:
 set newname for tablespace examples to '/u02/examples%b.dbf';
 … rest of the commands come here … 

If the tablespace has more than one datafile, they will all be uniquely created. You can use this clause for the entire database as well:
   set newname for database to '/u02/oradata/%b'; 

The term %b specifies the base filename without the path, e.g. /u01/oradata/file1.dbf will be recodesented as file1.dbf in %b. This is very useful for cases where you are moving the files to a different directory. You can also use it for creating image copies where you will create the backup in a different location with the same names as the parent file which will make it easy for identification.
One caveat: Oracle Managed Files don’t have a specific basename; so this can’t be used for those. Here are some more examples of the placeholders.
%f is the absolute file number 
%U is a system generated unique name similar to the %U in backup formats
%I is the Database ID
%N is the tablespace name
Note:  If you have any query , please send a mail to

1 comment:

  1. I guess You will need a switch datafile to copy after the restores and before the recovery.


Share your knowledge it really improves, don't show off...