7 Replies Latest reply: Jul 27, 2012 7:42 AM by 9730283 RSS

    Question on DB_FILE_NAME_CONVERT

    9730283
      Version : 11.2.0.2

      We are restoring our RMAN backup to a new server. We want to restore datafiles to a different datafile location than the source .

      Datafile path at source
      /tlmdata/oradata/lmnprod
      New path at target
      /u04/oradata/lmnprod
      From googling I gather that I have to use SET NEWNAME like below for all the 350 file I have.
      run {
      set newname for datafile 1 to '/u04/oradata/lmnprod/lmnprod_system01.dbf' ;
      set newname for datafile 2 to '/u04/oradata/lmnprod/lmnprod_sysaux01.dbf' ;
      set newname for datafile 3 to '/u04/oradata/lmnprod/lmnprod_undotbs101.dbf' ;
      set newname for datafile 4 to '/u04/oradata/lmnprod/lmnprod_audit_ts01.dbf' ;
      set newname for datafile 5 to '/u04/oradata/lmnprod/lmnprod_quest_ts01.dbf' ;
      set newname for datafile 6 to '/u04/oradata/lmnprod/lmnprod_yelxr_ts01.dbf' ;
      .
      .
      .
      .
      .
      set newname for datafile 350 to '/u04/oradata/lmnprod/lmnprod_dashmon_data01.dbf' ;
      #tempfile
      #set newname for tempfile 1 to '/u04/oradata/lmnprod/lmnprod_temp01.dbf';
      restore database; 
      # no recovery as it is cold backup
      switch datafile all; 
      #switch tempfile all; 
      }
      I also see people using DB_FILE_NAME_CONVERT in their init.ora file to achieve the same purpose.

      I have 2 questions.

      *1*. If I set DB_FILE_NAME_CONVERT in my init.ora file , afte mounting the DB using the restored control file , can I just do
      rman> run
      {
           restore database;
           alter database open resetlogs;
      }
      Do I have to do anything else to restore the datafiles to my new path ?

      *2*. If this can be achieved using DB_FILE_NAME_CONVERT (which is simple) then why do people use SET NEWNAME for each datafile ?
        • 1. Re: Question on DB_FILE_NAME_CONVERT
          Sebastian Solbach -Dba Community-Oracle
          Hi,

          with 11.2 there is some pretty neat new syntax if you have tons of files:

          "set newname for database" which will rename all datafiles.

          http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm#BRADV89956

          Regards
          Sebastian


          PS: The DB_FILE_NAME_CONVERT is more for standby databases, if they have different setup. So that new datafiles get created in the correct path.
          • 2. Re: Question on DB_FILE_NAME_CONVERT
            Shivananda Rao
            Hi,

            If you are having very few datafiles that need to be restored to the destination database, then you can go for the "SET NEWNAME " option.

            But since you say that you are having around 350 datafiles, in that case you can opt for using the db_file_name_convert parameter. If all of the 350 datafiles would be restored to the same path "/u04/oradata/lmnprod", I would suggest to set the db_file_name_convert parameter and proceed with the restoration.
            1. If I set DB_FILE_NAME_CONVERT in my init.ora file , afte mounting the DB using the restored control file , can I just do 
            
            rman> run
            {
                 restore database;
                 alter database open resetlogs;
            }
            
            
            
            Do I have to do anything else to restore the datafiles to my new path ?
            You should be able to restore the database and then perfrom the RECOVERY before you open the database with resetlogs.
            • 3. Re: Question on DB_FILE_NAME_CONVERT
              9730283
              +"set newname for database" which will rename all datafiles.+

              Koool....

              Hi Sebastian, Shivananda
              Thank you very much.
              • 4. Re: Question on DB_FILE_NAME_CONVERT
                AJ
                Note that you cannot use db_file_name_convert for normal RMAN restore - only tablespace point in time recovery and RMAN duplicate.

                Regards,
                AJ
                • 5. Re: Question on DB_FILE_NAME_CONVERT
                  9730283
                  Note that you cannot use db_file_name_convert for normal RMAN restore - only tablespace point in time recovery and RMAN duplicate.

                  Thank you very much AJ.

                  My colleagues says, for a normal RMAN restore, he once set db_file_name_convert in the init.ora file. But RMAN ignored it and the restore errored out because RMAN was trying to restore the datafiles to source server's datafile path and it wasn't there in the new server.
                  • 6. Re: Question on DB_FILE_NAME_CONVERT
                    mseberg
                    But it works great for RMAN duplicate

                    Example from INIT
                    DB_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/RECOVER2', '/u01/app/oracle/oradata/RECLONE','/u01/oradata/RECOVER2','/u01/oradata/RECLONE')
                    LOG_FILE_NAME_CONVERT=('/u01/app/oracle/flash_recovery_area/RECOVER2', '/u01/app/oracle/flash_recovery_area/RECLONE')
                    Not using RMAN duplicate would seem the hard way to me.

                    Best Regards

                    mseberg
                    • 7. Re: Question on DB_FILE_NAME_CONVERT
                      9730283
                      Yes. It will work for DUPLICATE. But it won't work a plain restore.