Q

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

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close