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
.
.
.
rcp -rp /zbackup/array3/oracle8/dbs73/PROD/*
george:/extdisk/oracle8/dbs83/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:
-- RECOVER DATABASE
-- ALTER SYSTEM ARCHIVE LOG ALL
Change
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
* * * * * Freedom of Information Act Notice * * * * *
The information in this email is subject to the record protection mandated
by 5 United States Code 552 (b) (4) and relevant judicial opinions.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sherman, Edward
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
