Author's name: Norman Dunbar;
Author's Email: Oracle@bountifulsolutions.co.uk
Date written: 27/06/2001
Oracle version(s): 18.104.22.168.1
What is the best way to move a database between two servers ? What problems will I incur and how do I avoid them ?
There are two cases to be considered here :
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;.
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 :
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.
Connor McDonald informs me that:
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
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.
Oracle 8 Server Administrator's Guide.
Oracle 8 Server Utilities Guide. (Export & Import)