6 Replies Latest reply: Dec 16, 2012 12:32 AM by Shivananda Rao RSS

    Moving Oracle from one server to another

    Meenakshy singh
      Hi Gurus,

      I have a RMAN backup done using following script :-

      +run {+
      allocate channel d1 type disk format='F:\SMARTEMC_BACKUP\%d_%s_%p_%t';
      crosscheck archivelog all;
      backup database include current controlfile;
      backup archivelog all;
      delete noprompt archivelog until time 'sysdate-7';
      release channel d1;
      +}+

      Other issue that i have is my production database is started using pfile. I am not sure of pfile location and pfile that databse used while starting up as I am new to this company.
      I tried searching for pfile but end up with lots of pfile on system as the system host 3-4 databases.

      SQL> select isspecified,count(*) from v$spparameter group by isspecified;

      ISSPEC   COUNT(*)
      ------ ----------
      FALSE         250

      SQL > sho parameter pfile

      NAME                                 TYPE        VALUE
      ------------------------------------ -----------
      spfile                               string

      Now I have to recreate the database on other server .So please let me knwo the steps that i need to follow for recreation of database on other server.
      Also the directory structure on other server is different.

      Thanks
      Meena

      Edited by: Meenakshy singh on Dec 13, 2012 4:34 PM
        • 1. Re: Moving Oracle from one server to another
          hitgon
          http://mmmustafa123.blogspot.in/2011/11/restore-rman-full-backup-to-different_26.html
          http://www.oracledistilled.com/oracle-database/restore-database-to-another-host-using-rman/

          Edited by: hitgon on Dec 13, 2012 2:25 PM
          • 2. Re: Moving Oracle from one server to another
            Shivananda Rao
            Hello Meena,

            Please refer this http://shivanandarao.wordpress.com/2012/04/19/duplicating-primary-database-to-a-new-host-without-connecting-to-the-primary-database-in-oracle-10g11g/

            Also, as your database is started using pfile, you can look out for the PFILE at the default location %ORACLE_HOME%\database. You can make a search on the server with the file format as "init<your-database-SID>.ora"


            Regards,
            Shivananda
            • 3. Re: Moving Oracle from one server to another
              Meenakshy singh
              Hi ,

              I am facing some issue while restore database from RMAN backup on other server

              Oracle DB version :- 9.0.1.1.1

              Tried to restore from RMAN backup copy.
              1)Took RMAN backup using script attached on production server.
              2)Moved the backup piece to secondary server.
              3) create instance with ORADIM
              3)Copied DB pfile and edited accordingly and started database on secondary server.
              4)All location of datafile,control file same on both servers.
              5)restore control file from backup piece.
              6)Mounted the database.
              7) tried to RESTORE DATABASE, but restore failed, after first listing correctly all the datafiles to be restored, with ORA-19505, it's looking for the second to last backup
              8) performed LIST BACKUP. it shows all backup up to the second to last, but not the most recent one
              9)performed CROSSCHECK BACKUP OF DATABASE, it shows that all backups are expired, does not show the most recent one


              The error it shows

              Starting restore at 15-DEC-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 E:\ORACLE\ORADATA\SMARTEMC\SYSTEM01.DBF
              restoring datafile 00002 to E:\ORACLE\ORADATA\SMARTEMC\UNDOTBS01.DBF
              restoring datafile 00003 to E:\ORACLE\ORADATA\SMARTEMC\CWMLITE01.DBF
              restoring datafile 00004 to E:\ORACLE\ORADATA\SMARTEMC\DRSYS01.DBF
              restoring datafile 00005 to E:\ORACLE\ORADATA\SMARTEMC\EXAMPLE01.DBF
              restoring datafile 00006 to E:\ORACLE\ORADATA\SMARTEMC\INDX01.DBF
              restoring datafile 00007 to E:\ORACLE\ORADATA\SMARTEMC\TOOLS01.DBF
              restoring datafile 00008 to E:\ORACLE\ORADATA\SMARTEMC\USERS01.DBF
              restoring datafile 00009 to E:\ORACLE\ORADATA\SMARTEMC\USERS02.DBF
              restoring datafile 00010 to E:\ORACLE\ORADATA\SMARTEMC\USERS03.DBF
              restoring datafile 00011 to E:\ORACLE\ORADATA\SMARTEMC\USERS04.DBF
              restoring datafile 00012 to E:\ORACLE\ORADATA\SMARTEMC\INDX02.DBF
              restoring datafile 00013 to E:\ORACLE\ORADATA\SMARTEMC\HK_BACKUP1.DBF
              restoring datafile 00015 to E:\ORACLE\ORADATA\SMARTEMC\INDX03.DBF
              RMAN-00571: ===========================================================
              RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
              RMAN-00571: ===========================================================
              RMAN-00579: the following error occurred at 12/15/2012 15:12:08
              RMAN-03002: failure during compilation of command
              RMAN-03013: command type: restore
              RMAN-03007: retryable error occurred during execution of command: IRESTORE
              RMAN-12004: unhandled exception during command execution on channel ORA_DISK_1
              RMAN-10035: exception raised in RPC: ORA-19505: failed to identify file "F:\SMARTEMC_BACKUP\SMARTEMC_2197_1_801849604"
              ORA-27041: unable to open file
              OSD-04002: unable to open file
              O/S-Error: (OS 2) The system cannot find the file specified.
              RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE



              Thanks
              Meena

              Edited by: Meenakshy singh on Dec 15, 2012 5:55 PM
              • 4. Re: Moving Oracle from one server to another
                Shivananda Rao
                Hi Meena,

                RMAN is looking for the backuppiece SMARTEMC_2197_1_801849604 at location "F:\SMARTEMC_BACKUP\" and it is not able to find it.
                Are you sure that the backups were copied to the location "F:\SMARTEMC_BACKUP\" on the new server ?

                May be the controlfile you restored does not know about the exact location where you have placed the backups on the new server. Once the new instance is mounted, catalog the backup location or the backuppieces you have placed.
                RMAN>catalog start with '<location where you have placed the backup pieces on the new server>';
                or
                RMAN>catalog backuppiece '<backup piece name with full path>';
                Regards,
                Shivananda
                • 5. Re: Moving Oracle from one server to another
                  Meenakshy singh
                  Hi Sir,

                  Catalog is the perfect option but mine is Oracle 9i and it doesn't support catalog command.

                  Thanks
                  Meena
                  • 6. Re: Moving Oracle from one server to another
                    Shivananda Rao
                    In that case you can try creating a similar directory structure on the auxiliary server as structure where you took the backup of the primary database.


                    Regards,
                    Shivananda