This discussion is archived
6 Replies Latest reply: Dec 15, 2012 10:32 PM by Shivananda Rao RSS

Moving Oracle from one server to another

Meenakshy singh Newbie
Currently Being Moderated
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;

------ ----------
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.


Edited by: Meenakshy singh on Dec 13, 2012 4:34 PM
  • 1. Re: Moving Oracle from one server to another
    hitgon Expert
    Currently Being Moderated

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

    Please refer this

    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"

  • 3. Re: Moving Oracle from one server to another
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi ,

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

    Oracle DB version :-

    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


    Edited by: Meenakshy singh on Dec 15, 2012 5:55 PM
  • 4. Re: Moving Oracle from one server to another
    Shivananda Rao Guru
    Currently Being Moderated
    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>';
    RMAN>catalog backuppiece '<backup piece name with full path>';
  • 5. Re: Moving Oracle from one server to another
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi Sir,

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

  • 6. Re: Moving Oracle from one server to another
    Shivananda Rao Guru
    Currently Being Moderated
    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.



  • Correct Answers - 10 points
  • Helpful Answers - 5 points