Search BC Sites

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Copying Databases Between Servers

Don't use the EXPORT/IMPORT utility to copy databases between servers running the same operating system. Execute the following steps to speed the transfer:

1. Execute the ALTER DATABASE BACKUP CONTROLFILE TO TRACE; statement on the source server.
    

2. Bring the database down and copy the trace file, parameter file, all datafiles, control files and redo logs to the new server.
    

3. Make the following changes to the trace file created in step 1:
    

4. If you are changing the database name, change the first line of the create statement to reflect the new name and change the REUSE keyword to SET.
    

5. Change NORESETLOGS to RESETLOGS.
    

6. Change directory names if they have changed on the new database server.
    

7. Delete all comments and lines that have a # in front of them (#s aren't comments in all Oracle tools).
    

8. Connect as INTERNAL and run the SQL statement contained in the trace file. It will start up the database in NOMOUNT stage and recreate the control files. The SQL statement’s final step will be to MOUNT and then OPEN the database.

What happens if you can’t take the database down?  You’ll have to execute a hot backup, which means your database will have to be in ARCHIVELOG mode.  Here are the steps you will have to perform to copy an online database to a new server.

• Execute the hot backup, making sure all of the files are backed up. Note the time after the last tablespace is backed up.
   

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.