Ask the Expert

Copying an Oracle database

I have an Oracle database on one server and would like to copy it (or just the data) to a database on another server. The second database would be an exact copy of the first to serve as a backup. All the physical and logical definitions (tablespaces, file definitions, etc) will be the same. We have created all the objects on the second server. What would you recommend copying the data to the second server? If you recommend import/export, we have 865 constraints (729 C, 102 P, and 34 U) defined. We are running Oracle 8.0.6.

    Requires Free Membership to View

My favorite way to make the copy is to do it "Hot." You can copy the database hot if the database is in archivelog mode, and if your network can handle it. I will give you the steps to copy using export/import after the steps for "Hot." Here are the steps for the copy the database "Hot" .... 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) Alter all the tablespaces in the database into backup mode spool /tmp/begin_backup.sql
select "ALTER TABLESPACE " || TABLESPACE_NAME|| ' BEGIN BACKUP ; ' from DBA_TABLESPACES ;
spool off
@/tmp/begin_backup.sql
If the database is experiencing a high load, you may want to do the backup/copy of the datafiles one at a time.

3) Copy all the datafiles from the source machine to the target machine (using ftp or rcp)

4) Alter all the tablespaces in the database out of backup mode on the source database:
spool /tmp/end_backup.sql
select "ALTER TABLESPACE " || TABLESPACE_NAME|| ' END BACKUP ; ' from DBA_TABLESPACES ;
spool off
@/tmp/end_backup.sql

5) Force an archivelog switch on the source database: alter system archive log current;

6) Copy all the archivelog that were generated since you started the copy to the target machine.

7) Setup all necessary files for the target database (init.ora, dump directory, listener.ora, tnsnames.ora, etc.

8) 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.

9) 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

10) Change the location of the redo logs

11) Delete all the trace lines at the beginning of the file, up until the "create controlfile" statement. Under the 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

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

13) 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 ;
alter database open resetlogs;

To make the copy using export/import, I would suggest using a named pipe for the export/import.

1) Disable all foreign constraints on the target database (you can leave the primary and unique constraints)

2) Create the named pipe on the target machine
mknod /tmp/exp.dmp p

3) Start a background process that will import database as it is written to the pipe (on the target machine):
imp system/manager file=/tmp/exp.dmp full=y ignore=y log=/tmp/imp.log &

4) Start the export of the source database on the target machine exp system/manager@sourcedb file=/tmp/exp.dmp full=y log=/tmp/exp.log


For news, advice and other information about database administration, click here.

This was first published in October 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: