Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Oracle 9i Newbie 1
- Thread starter AdminGreg
- Start date
-
1
- #2
jayjaybigs
IS-IT--Management
Here are the steps:
1)Backup the source controlfile using the command
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
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
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
1)Backup the source controlfile using the command
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
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
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
- Thread starter
- #3
jayjaybigs
IS-IT--Management
sign in as sys - this will give you all the priviledges to perform above steps.
and use your server propmpt - usually $
and use your server propmpt - usually $
- Thread starter
- #5
SantaMufasa
Technical User
Greg,
Since you are relatively new to Tek-Tips, I'll point out a method we use to show gratitude for helpful posts, as I'm sure "JayJayBigs's" post was for you (since "everything worked 100%" and it looks like s/he spent at least 20-40 minutes of valuable time producing the post). We click on the link "Thank JayJayBigs for this valuable post"...it rewards JayJay with a Purple Star and it points subsequent solution-searchers to particularly useful posts.
Yes, I could have clicked on "Thank JayJay..." myself, but this way, you get to do the honours.
Cheers,
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
@ 17:47 (18Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:47 (18Jan05) Mountain Time
Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.
Since you are relatively new to Tek-Tips, I'll point out a method we use to show gratitude for helpful posts, as I'm sure "JayJayBigs's" post was for you (since "everything worked 100%" and it looks like s/he spent at least 20-40 minutes of valuable time producing the post). We click on the link "Thank JayJayBigs for this valuable post"...it rewards JayJay with a Purple Star and it points subsequent solution-searchers to particularly useful posts.
Yes, I could have clicked on "Thank JayJay..." myself, but this way, you get to do the honours.
Cheers,
Mufasa(aka Dave of Sandy, Utah, USA)
@ 17:47 (18Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:47 (18Jan05) Mountain Time
Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.
- Status
Similar threads
- Locked
- Question