The Oracle (tm) Users' Co-Operative FAQ

How can I move the database to a different machine ?

Author's name: Norman Dunbar;

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 27/06/2001

Oracle version(s): 8.0.6.1.1

What is the best way to move a database between two servers ? What problems will I incur and how do I avoid them ?

Back to index of questions

There are two cases to be considered here :

  • The servers are different (OS, OS version, vendor, number of bits etc)
  • They are the same

If the servers differ, then the only way to make the transfer is to export on the old server and import into a freshly created database on the new server. At least this will allow you to create the various tablespaces etc on the new database as they should be, perhaps consolidating a number of data files into a single one etc.

Where the servers are the same, the procedure is as follows :

Login to the old database as a dba user, and create a control file trace using the command alter database backup controlfile to trace;.

  • Shutdown the old instance. Do not shutdown abort or immediate as these will require instance recovery.
  • Take a cold backup of the control files, datafiles, redo logs - if you want but not really required, initSID.ora file.
  • Create an environment on the new server to receive the various dumps files etc. Create users, directories, edit oratab, tnsnames.ora and listener.ora files as required.
  • Copy (via tape, ftp etc) the dump files, control files, redo, initSID.ora and the control file trace over to the new environment.
  • Edit the control file trace to :
    • Remove all the comments from the top and bottom of the script.
    • Remove the startup nomount and recover database commands from the script.
    • Change create controlfile reuse database "old_name" noresetlogs ... to create controlfile reuse set database "new_name" resetlogs ....
    • Change the paths and file names for the datafiles and logfiles to match the new locations.
    • Save the file with a meaningful name - controlfile.sql for example.
  • Edit the initSID.ora file to :
    • Change the db_name parameter to match the new name given in the controlfile.sql script created above.
    • Change the paths to the control file(s).
    • Change the paths to user_dump_dest, background_dump_dest and core_dump_dest locations as appropriate, also log_archive_dest if the database is in archive log mode.
  • On the new server, set $ORACLE_HOME and $ORACLE_SID as required.
  • If you changed the filenames in the control file script and/or the control file names in the initSID.ora file, now is a good time to rename the actual datafiles and/or control file to match.
  • Run svrmgrl and connect internal or / as sysdba according to the version of Oracle in use.
  • Startup nomount the instance and run the controlfile.sql script created above.
  • Alter database open resetlogs; will then open the database. If you didn't copy the redo files over, or if you changed their names in the control file script, then new files will be created for you.
  • Alter database rename global_name to SID.domain; is now required otherwise the database will have the same global name as the old one.

 

Points to note

The above methods can also be used to clone a database on the same server.

If you use RMAN for your backups, beware. RMAN reads an instance id number from the control file and uses that to identify an instance in its catalogue. When you clone a database, the number is the same on both instances. You will need to keep the two in a separate catalogues if you wish to use RMAN. (Thanks Howard !)

When cloning to the same server, keep the old instance closed until the new one has been started and opened. Then the old one can be started again. If you don't keep the old one closed, Oracle will complain about some other instance already being in use - the message is a bit obscure! I find the best order is :

  • close old
  • build new
  • close new
  • open old
  • open new

If you use the above method to copy to a differing server, then Oracle will complain about files not being suitable (or found) when you try to open the new database. Again, the message is a bit misleading.

Information received (28/06/2001)

Connor McDonald informs me that:

Later versions of RMAN support a "duplicate" command to allow you to clone a database to avoid having the instance ID problems. There is also an enhancement request for a future version of RMAN to allow an instance ID to be reset.

Thanks Connor.

Information received (04/Oct/2005)

Brandon Allen emailed me with the following comment:

I noticed what I believe is an error in the article published at http://www.jlcomp.demon.co.uk/faq/db_move.html 

It says "Do not shutdown abort or immediate as these will require instance recovery."  

I believe the portion about 'shutdown abort' is correct, but I don't believe that a 'shutdown immediate' will require instance recovery.

Thanks Brandon.

 

Further reading:

Oracle 8 Server Administrator's Guide.

Oracle 8 Server Utilities Guide. (Export & Import)

Back to top

Back to index of questions