How do I copy an Oracle DB from one server to another?

For the same server and operating system do the following. The DB will have the same name as the original.

1. Shut source database down with the NORMAL or IMMEDIATE option.

2. Copy all datafiles. Select the name from v$datafile; to get their names.

3. Copy all online redo logs. Select the member from v$logfile; to get their names.

4. Copy all control files. Select the name from v$controlfile; to get their names.

5. Copy the parameter file. Find it in $ORACLE_HOME/dbs in Unix and $ORACLE_HOME/database in Windows. The name is initDBNAME.ora where DBNAME is database name.

6. All of the files must be placed in directories that have same name as the source server directories. The names returned from the queries above are fully qualified with directory names.

7. Create all of the directories specified in the parameter file you just copied. It will be BDUMP, CDUMP, UDUMP. Just recreate all of the directories you find specified in your parameter file.

8. Edit the parameter file you copied. Set REMOTE_LOGIN_PASSWORDFILE to NONE if it is set SHARED or EXCLUSIVE.

9. Start the database up.

10. If you use a true temporary tablespace for sorting, you will have to recreate it. The database will still start, but the first sort that writes to it will bomb. Simple syntax is in the manuals.

11. To use remote authentication again, run ORAPWD. Here's the syntax:

     
    orapwd
    Usage: orapwd file=<fname> password=<password> entries=<users>
    

    where:
    file = name of password file (mand)
    password = password for SYS (mand)
    entries = maximum number of distinct DBAs and OPERs (opt).
    There are no spaces around the equal-to (=) character

12. Edit the parameter file to set REMOTE_LOGIN_PASSWORDFILE to SHARED or EXCLUSIVE (depending on what it was set to before).

13. Restart the DB for it to take affect.

If you want to change the DB name or directories, you must run the CREATE CONTROLFILE statement. You'll have to read the manual on that one; it is about 30 steps. Or you can do the following.

1. Create a database with a new name on the target server. Make it any database name you want with any directories you want.

   Since we will be using Export/Import, you will need to know this information:

   • You must precreate the tablespaces if you are going to change directory names for your data files. Oracle looks for directory names during the IMPORT when it tries to create the TABLESPACES.
     
   • If it can't find the directories, they will blow up.
     
   • If you precreate them (anywhere you want), Oracle will know that the tablespaces are there and load them up with data. It won't blow up because it won't attempt to create them.

2. Export the data from the source database. Set ORACLE_SID to source database name.

   exp system/manager file=full_export_dump.dmp log=full_export_log.out full=y
   

   (We'll take defaults with everything else.)

3. Copy full_export_dump.dmp to the target server.

4. Import the data into the target database. Set ORACLE_SID to the target database name.

   imp system/manager file=full_dump.dmp log=full_import_log.out full=y ignore=y
   

   You only need to use ignore=y if your tablespaces are precreated. That means the database will ignore the "already exists" error that will be returned when it finds the tablespace is already there.

My special thanks go out to Oracle DBA guru and my good friend, Chris Foot for his assistance with this question. Chris is the author of OCP Instructor's Guide for Oracle DBA Certification and an Oracle 10g blog.