| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to move 200 GB db from prod to dev?
Helmut,
This works perfectly for cloning a DB where the instance names are
different,
but the directory structure is exactly the same, which looks to be the case
from
your original post for the /u02 file system. We do this in order to clone
on the same
server, but should work just fine on a different box as well. Add the
following to the
init.ora file for the cloned DB. All we do is copy the files, then mount
the DB followed
by opening it...works like a charm!
lock_name_space = ROLAND
db_name = PCLDB1
instance_name = ROLAND
service_names = ROLAND
#convert file names and handle lock file db_file_name_convert = '/PCLDB1/','/ROLAND/' log_file_name_convert = '/PCLDB1/','/ROLAND/'
-----Original Message-----
Sent: Thursday, June 06, 2002 1:53 PM
To: Multiple recipients of list ORACLE-L
I have a similar situation and here's how I deal with it.
(I'm assuming your datafiles have the same name but the pathname is
different)
Production: SID = PROD => e.g.
/zbackup/array3/oracle8/dbs73/PROD/wds01.data.dbf
Development: SID = WDSU => e.g. /extdisk/oracle8/dbs73/PROD/wds01.data.dbf
In SQL*PLUS:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Locate the trace file and copy it to the development machine.
Move data with a script similar to this:
rcp -rp /zbackup/oracle1/dbs73/PROD/* george:/extdisk/oracle1/dbs73/PROD rcp -rp /zbackup/oracle2/dbs73/PROD/* george:/extdisk/oracle2/dbs73/PROD
.
.
.
Yes it's annoying but you only have to write the script once!
Now you can edit that trace file while you wait for your data to finish moving:
In my trace file I change the line:
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
to
CREATE CONTROLFILE SET DATABASE "WDSU" RESETLOGS NOARCHIVELOG This changes the instance name (I'm not using log archiveing on the dev database).
Now I have to edit all those pathnames.
I have stuff like
LOGFILE
GROUP 1 (
'/oracle4/dbs73/PROD/redoPROD07a.log',
'/oracle1/dbs73/PROD/redoPROD07b.log'
Need to be changed to:
'/extdisk/oracle4/dbs73/PROD/redoPROD07a.log', '/extdisk/oracle1/dbs73/PROD/redoPROD07b.log'
Also, things like
'/array3/oracle8/dbs73/PROD/wds01data.1.dbf',
Needs to be changed to
'/extdisk/oracle8/dbs73/PROD/wds01data.1.dbf',
But its not really a problem if you can use the vi editor:
Hit ESC key
then :1,$s/\/oracle/\/extdisk\/oracle/g <ENTER>
This changes all the /oracle to /extdisk/oracle
This also has the side effect that all my /array3/oracle8 has changed to /array3/extdisk/oracle8.
Need to get rid of the /array3
In vi,
Hit ESC Key
then :1,$s/\/array3//g
Now all my paths are correct for the development database.
At the bottom of the controlfile tracefile I comment out the lines:
ALTER DATABASE OPEN; to
ALTER DATABASE OPEN RESETLOGS; Delete lines in the trace file from the first line until you get to the line that says:
STARTUP NOMOUNT Also delete or comment out any lines with a # character as the first character.
Delete my old control files and run the trace file from SQL*PLUS to create the new controlfiles and open the instance.
The renaming of the paths was done in two steps with vi but you can write a shell script using sed to do the substitution for you if you like.
Maybe annoying but you only have to write the script once!
In your example you need to change PCLDB1 to ROLAND
You bring up the trace file with the CREATE CONTROLFILE script in vi and just do:
Hit ESC
:1,$s/PCLDB1/ROLAND/g <ENTER>
and PCLDB1 will be substituted for ROLAND everywhere in the file.
If you want to write a script for this and are not familiar with sed then maybe the UNIX sysadmin or a UNIX developer can help you with this.
After you get the scripts written then its really easy to move you data and get the instance running. It's only annoying the first time!
Good luck,
Ed
-----Original Message-----
Sent: Thursday, June 06, 2002 10:28 AM
To: Multiple recipients of list ORACLE-L
Hi!
We are supposed to clone our production database onto a new development box
(both boxes are Sun Solaris). The db is about 200 GB in size.
What would be the best way to achieve this? Simply copying over the files won't work, since the instance names are different:
Production: SID=PCLDB1 => e.g. /u02/oradata/PCLDB1/system01.dbf Development: SID=ROLAND => e.g. /u02/oradata/ROLAND/system01.dbf
So would export/import the entire db be the only way? (But writing out dump file that big should be a little disk space problem...)
Renaming all the datafiles (approx. 100) would be kind of annoying...
Any ideas?
This is 8.1.7 on Sun Solaris.
Thanks,
Helmut
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Huntley INET: rhuntley_at_mindleaders.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Jun 06 2002 - 15:36:00 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |