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 commandALTER 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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation