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
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%' ;
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.