How To Copy An Oracle Database To Another Machine

How to Copy an Oracle Database to Another Machine Summary: I need to migrate the existing databases from 8.1.6 to 9i. The migration will go accross two different machines. We're not upgrading an existing server, we're going from scratch. What's the course of action to follow? Full Article: Disclaimer: Contents are not reviewed for correctness and are not endorsed or recommended by ITtoolbox or any vendor. Popular Q&A contents include summarized information from ITtoolbox Oracle DB discussion unless otherwise noted. Adapted from response by Elkarama on Fri, 3 May 2002 Here are the answers (From Metalink) Pre-requisites: The copy is between 2 machines, both have to be on the same OS and have to have exactly the same database version installed. 1. Make sure the database you want to copy was closed with a SHUTDOWN IMMEDIATE, SHUTDOWN NORMAL or SHUTDOWN TRANSACTIONAL. 2. Copy init.ora and control files to create instance and be able to go in mount mode. Check the init.ora for the locations where the controlfiles have to be, if those locations are not valid on the machine put the control files on different places and adjust the init.ora accordingly. 3.a. Copy the datafiles (all of them). b. Copy the redo-logfiles (all of them). 4.a. (Unix only) Set the environment variables: ORACLE_SID - set to the database name you wish to create ORACLE_HOME - set to full pathname of the Oracle system home directory PATH - needs to include $ORACLE_HOME/bin b. (NT/2000 only) Do 'set ORACLE_SID=' Use oradim to create the service for the instance. For more information on oradim please refer to (the part that refers to creating a new instance): Creating a new 7.3, 8.0, 8.1 Instance/Database 5. Use servermanager (check the name to use for your version of oracle) or sqlplus (version 9i and above) to startup the database in mount mode. Do CONNECT INTERNAL/ then STARTUP MOUNT Then do a rename of the copied datafiles if they are not in the same path as on the other machine. For all the files that are in the result of the query:newfullpath>\'; SELECT NAME FROM V$DATAFILE; do ALTER DATABASE RENAME FILE '\' to < 6. Query the datadictionary for the old location of the redolog files using: SELECT MEMBER FROM V$LOGFILE; If the new place is not the same as the old do: ALTER DATABASE RENAME FILE '\' to & lt;newfullpath>\'; 7. Now open the database: ALTER DATBASE OPEN;