Q
Problem solve Get help with specific problems with your technologies, process and projects.

Copying an Oracle database from one server to another

I want to copy an Oracle database from one server machine to another. It is going to be an exact replica of the...

existing database with the same tables, tablespaces and data. To do so, I shut down the current database and copied all the Oracle database files (control files, init.ora, data files, log files) to the target machine. I would like to know what I need to do now to start the Oracle database on the new machine? Was my procedure correct in copying the database files?

Your procedure is correct for copying the database. I am assuming in my instructions below that you are going to rename the database from SRCDB to TGTDB. Here are the steps that you still need to follow:

1) Backup the source controlfile using the command:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

A "CREATE CONTROLFILE" script will be written to your user dump directory; use ls -trl to find the file.

2) Copy the create controlfile script to the target machine (name it create.sql). I store all the scripts that I used to create the database in the $ORACLE_BASE/admin/$ORACLE_SID/create directory.

3) Modify the create.sql file. Change the create controlfile statement from:

CREATE CONTROLFILE REUSE DATABASE "SRCDB" NORESETLOGS ARCHIVELOG

to:

CREATE CONTROLFILE SET DATABASE "TGTDB" RESETLOGS ARCHIVELOG

4) Change the location of the redo logs.

5) Delete all the trace lines at the beginning of the file, up until the "create controlfile" statement. Under the "DATAFILE" statement, change the location of the datafiles. I prefer to delete all the lines, then use this command in vi to add the datafile locations back:

:r !ls /*/oradata/tgtdb/*.dbf

6) Delete the "recover database", "alter database open", and trace files from the create.sql script.

7) Start svrmgrl or sqlplus, connect internal, and execute the "create.sql" scripts, recover the database:

svrmgrl "command=connect internal"
@create
recover database using backup controlfile ;

At this point you will be prompted for one redolog. Use the alert log from the SRCDB to determine this. For example, it will ask for arch000000000187, look in the alert log for the line that says:

Thread 1 advanced to log sequence 187
Current log# 3 seq# 187 mem# 0: /u02/oradata/TGTDB/redo03a.log

In this case you know to use redo03a.log that you copied over:

alter database open resetlogs;

This completes the process. I like to verify that I didn't miss any datafiles by executing the following command in sqlplus on the target:

select
    FILE_NAME, TABLESPACE_NAME, STATUS
from
    dba_data_files
where
    FILE_NAME  like '%MISS%' ;

This was last published in March 2002

Dig Deeper on Oracle database export, import and migration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close