7 Replies Latest reply: Nov 11, 2012 11:02 PM by 007 RSS

    Set newname for datafiles

    007
      Hi,

      I am using oracle 10g (10.2.0.1) in RHEL 5 server. I am trying to restore a RMAN full backup in another server with different directory structure. I have cataloged the backup using the below command

      RMAN> catalog start with '/u01/backupset/o1_mf_nnndf_FULLBKP_88zqky0p_.bkp';

      using target database control file instead of recovery catalog
      searching for all files that match the pattern /u01/backupset/o1_mf_nnndf_FULLBKP_88zqky0p_.bkp
      no files found to be unknown to the database


      Then issued the set new name command

      run{
      2> set newname for datafile 1 to '/u01/app/oracle/oradata/DEVDB/datafile/system1.dbf';}

      executing command: SET NEWNAME

      RMAN>

      Now when i issue the restore datafile 1 command i get restored in different location

      restore datafile 1;

      Starting restore at 11-NOV-12
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: sid=155 devtype=DISK

      channel ORA_DISK_1: starting datafile backupset restore
      channel ORA_DISK_1: specifying datafile(s) to restore from backup set
      restoring datafile 00001 to */u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_89zlhw35_.dbf*
      channel ORA_DISK_1: reading from backup piece /u01/backupset/o1_mf_nnndf_FULLBKP_88zqky0p_.bkp
      channel ORA_DISK_1: restored backup piece 1
      piece handle=/u01/backupset/o1_mf_nnndf_FULLBKP_88zqky0p_.bkp tag=FULLBKP
      channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
      Finished restore at 11-NOV-12

      But i have issued the */u01/app/oracle/oradata/DEVDB/datafile/system1.dbf* in set newname command.

      My datafile locations in the source database server is */data/u01/app/oracle/oradata/ORADB/datafile*

      Is this a bug in 10.2.0.1 ?? or am i missing out anything??

      Thanks in advance

      Regards,

      007
        • 1. Re: Set newname for datafiles
          mseberg
          Hello;


          You need to include :

          SWITCH DATAFILE ALL;
          run{
          2> set newname for datafile 1 to '/u01/app/oracle/oradata/DEVDB/datafile/system1.dbf';
          
          RESTORE DATABASE;
          SWITCH DATAFILE ALL;
          RECOVER DATABASE;
          
          }
          
          ALTER DATABASE OPEN RESETLOGS; 
          Best Regards
          
          mseberg
          • 2. Re: Set newname for datafiles
            007
            Hi mseberg,

            I used the below commands as per your instruction. It is showing that it is restoring in */u01/app/oracle/oradata/DEVDB/datafile* location but at the end the datafile gets restored in /u01/app/oracle/oradata/ORADB/datafile location. Any other idea??

            RMAN> run{
            2> set newname for datafile 1 to '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_80jq0kfw_.dbf';
            3> restore datafile 1;
            4> SWITCH DATAFILE ALL;}

            executing command: SET NEWNAME

            Starting restore at 11-NOV-12
            using channel ORA_DISK_1

            channel ORA_DISK_1: starting datafile backupset restore
            channel ORA_DISK_1: specifying datafile(s) to restore from backup set
            restoring datafile 00001 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_80jq0kfw_.dbf
            channel ORA_DISK_1: reading from backup piece /u01/backupset/o1_mf_nnndf_FULLBKP_88zqky0p_.bkp
            channel ORA_DISK_1: restored backup piece 1
            piece handle=/u01/backupset/o1_mf_nnndf_FULLBKP_88zqky0p_.bkp tag=FULLBKP
            channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
            Finished restore at 11-NOV-12

            datafile 1 switched to datafile copy
            input datafile copy recid=10 stamp=799108115 filename=/u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_89zp9d9r_.dbf

            Regards,
            007
            • 3. Re: Set newname for datafiles
              mseberg
              Hello again;

              No error then?

              Might be this bug :

              Bug 9550354 - SWITCH DATAFILE TO DATAFILECOPY TAG <tag> fails if <tag> not for single datafile [ID 9550354.8]

              The work around there is worth a try.

              OR

              This could cause the issue :

              The DB_FILE_NAME_CONVERT clause cannot be used to control generation of new names. So I read this as if DB_FILE_NAME_CONVERT is set, it could cause an issue.

              If you are using OMF this is worth a look :

              http://levipereira.wordpress.com/2011/10/25/tip-hint-db_create_file_dest-behavior-when-using-asm/

              If "SET NEWNAME" is specified, RMAN will use that name for restore.
              If the original file exists, RMAN will use the original filename for restore.
              If the DB_CREATE_FILE_DEST is set, RMAN will use the diskgroup name specified.
              If no DB_CREATE_FILE_DEST is set and the original file does not exist, then RMAN will create another name for that file in the original disk group.

              So you may have unset a parameter before the restore :
               alter system set DB_CREATE_FILE_DEST='' scope=memory;'
               
              Best Regards

              mseberg

              Edited by: mseberg on Nov 11, 2012 11:27 AM
              • 4. Re: Set newname for datafiles
                Shivananda Rao
                RMAN> run{
                2> set newname for datafile 1 to '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_80jq0kfw_.dbf';
                3> restore datafile 1;
                4> SWITCH DATAFILE ALL;}
                Hello,

                It looks like you are having OMF (Oracle Managed Files). Can you please post the outcome of below:
                show parameter db_create_file_dest
                I suspect the above parameter is set to "/u01/app/oracle/oradata/ORADB". Also, for an OMF file, you cannot assign a name. The name is uniquely taken up by Oracle.
                You can just try as below but before that please post the outcome of the "show parameter db_create_file_dest".
                RMAN> run{
                set newname for datafile 1 to new;
                restore database;
                switch datafile all;
                recover database;
                }
                • 5. Re: Set newname for datafiles
                  Hemant K Chitale
                  With OMF, the restore command shows that it is restoring to the old location but it may well be restoring to the new location (see next paragraph below). You should not need to name the full qualified path to the new datafile if you want OMF.

                  For example, when restoring datafiles from one ASM server to another, I find that the restore command shows the old ASM path but the files actually go to the new ASM path (defined based on DB_UNIQUE_NAME).


                  Hemant K Chitale
                  • 6. Re: Set newname for datafiles
                    007
                    Hi Shivananda,

                    SQL> show parameter db_create%

                    NAME TYPE VALUE
                    ------------------------------------ ----------- ------------------------------
                    db_create_file_dest string /u01/app/oracle/oradata
                    db_create_online_log_dest_1 string
                    db_create_online_log_dest_2 string
                    db_create_online_log_dest_3 string
                    db_create_online_log_dest_4 string
                    db_create_online_log_dest_5 string

                    Regards,
                    007
                    • 7. Re: Set newname for datafiles
                      Shivananda Rao
                      Hello,

                      As said earlier, since it is the backup of the ORADB database, Oracle restores it as "/u01/app/oracle/oradata/DEVDB/datafile/".

                      Once the restoration and recovery process is done, you can change the name of the database from ORADB to DEVDB using NID utility.

                      You can take a look at this http://shivanandarao.wordpress.com/2012/04/19/duplicating-primary-database-to-a-new-host-without-connecting-to-the-primary-database-in-oracle-10g11g/