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 ;
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:
select "ALTER TABLESPACE " || TABLESPACE_NAME|| ' END BACKUP ; ' from DBA_TABLESPACES ;
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"
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.