RE: How to move 200 GB db from prod to dev?
Title: 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- From: Sherman, Edward [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to move 200 GB db from prod to dev? 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 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 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 shoul
RE: How to move 200 GB db from prod to dev?
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 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 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).
RE: How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev? A few options. Do you use RMAN for backups? , if so duplicate the database and rename the files as part of that process. Otherwise clone the database by copying the files at the o/s level and change the instance name. Several documents on metalink show how to do this. If you have used an OFA setup then renaming the datafiles should not be that difficult, it will only be the directory named after the sid that willl need changing On a database that size export would take some time but it is another option. John -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: 06 June 2002 15:28To: Multiple recipients of list ORACLE-LSubject: How to move 200 GB db from prod to dev? 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
Re: How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev?
Helmut,
I have just done it right this
morning. Not that big but worked.
Pasos para Clonar Bases de Datos
1-) Take a cold backup of the Origen DB-
Preferiblemente frio
2-) Take a backup of the controlfile of the
origin DB
3-) Copy all the datafiles from the origin
DB to the copy DB. If the backup taken is
hot also copy the redo logs files and archives
4-) Modify the controlfile with the new path of
the datafiles y redo logs files,
remove the
REUSE clause, the RECOVER command,
add the RESETLOGS to the
ALTER DATABASE OPEN command.
The controlfile should be that way.
-> El Archivo debe quedar de esta
manera--- STARTUP NOMOUNT CREATE
CONTROLFILE SET DATABASE "new clone
name" NOARCHIVELOG
MAXLOGFILES 255 MAXLOGMEMBERS
5 MAXDATAFILES
1022 MAXINSTANCES
5 MAXLOGHISTORY
100 LOGFILE
GROUP 1
('/u01/oradata/redo01a.log',
'/u01/oradata/redo01b.log') SIZE
10M, GROUP
2
('/u01/oradata/redo02a.log',
'/u01/oradata/redo02b.log') SIZE
10M, GROUP
3
('/u01/oradata/redo03a.log',
'/u01/oradata/redo03b.log') SIZE 10M
RESETLOGS
DATAFILE '/u03/oradata/data01.dbf'
SIZE 300M,
'/u03/oradata/data02.dbf' SIZE
300M, '/u03/oradata/data03.dbf'
SIZE 300M,
'/u03/oradata/data04.dbf' SIZE 300M;-- Save
this file with .sql extension
6-) Connect to the clone DB with sysdba
privileges
and execute the
script
7-) Mount and open the y abrir la BD con
Resetlogs ALTER DATABASE MOUNT;
ALTER DATABASE OPEN RESETLOGS;
---
Think doesn't omitted something translating from
spanish.
Luck,
Ramon
- Original Message -
From:
Daiminger, Helmut
To: Multiple recipients of list ORACLE-L
Sent: Thursday, June 06, 2002 9:27
AM
Subject: How to move 200 GB db from prod
to dev?
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
RE: How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev? 1) use RMAN to clone the db; or 2) create the new dev db with the required users but without all the data/index tablespaces, and use transportable tablepsace feature to move tablespaces/data files . . . export/import of meta-data is quick - only real time factor is copying the data files - this assumes, of course, that you can make your production tablespaces read-only for the time it takes to copy (or tar) the data files -bill -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 10:28 AMTo: Multiple recipients of list ORACLE-LSubject: How to move 200 GB db from prod to dev? 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
RE: How to move 200 GB db from prod to dev?
Title: RE: How to move 200 GB db from prod to dev? Dump the prod control to trace Copy the db files over to dev edit the dumped controlfile (you can change db name here) replace the copied controlfile with the new eidted one and bring up dev I forget exactly what needs to change in the controlfile file script but db name and I believe reset logs Hannah -Original Message- From: [EMAIL PROTECTED]@SUNGARD On Behalf Of "Daiminger, Helmut" <[EMAIL PROTECTED]> Sent: Thursday, June 06, 2002 10:28 AM To: Multiple recipients of list ORACLE-L Subject: How to move 200 GB db from prod to dev? 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev? Search the Oracle-L archives- this question has been covered several times recently. Also, “Oracle DBA Tips & Techniques” by Sumit Sarin (2000, Oracle Press) has detailed instructions for cloning a database in Chapter 1. Note the error in the script on page 43: “create controlfile REUSE database…” should be “create controlfile SET database…”. Otherwise, the procedure works wonderfully. “Successful? Begun the clone wars have.” –Master Yoda HTH, david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Office (919) 466-6723 Pager [EMAIL PROTECTED] Fax (919) 466-6783 http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -Original Message- From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 10:28 AM To: Multiple recipients of list ORACLE-L Subject: How to move 200 GB db from prod to dev? 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
RE: How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev? Helmut, Why can't you simply copy the files and then re-create new control files? This gives you the option of renaming the database. Not sure what your backup procedures are, but you could always restore a backup to the development box and, again, recreate the control files renaming the database as you go. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 10:28 AMTo: Multiple recipients of list ORACLE-LSubject: How to move 200 GB db from prod to dev? 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
RE: How to move 200 GB db from prod to dev?
Oh yeah, The dumped controfile script with give you all of the files name s too. Then just search and replace in a text editor PCLDB1 and ROLAND, Hope it helps at all, Hannah > -Original Message- > From: Doran, Johanna > Sent: Thursday, June 06, 2002 11:15 AM > To: '[EMAIL PROTECTED]' > Subject: RE: How to move 200 GB db from prod to dev? > > Dump the prod control to trace > Copy the db files over to dev > edit the dumped controlfile (you can change db name here) > replace the copied controlfile with the new eidted one and bring up dev > > I forget exactly what needs to change in the controlfile file script but db name and >I believe reset logs > > Hannah > > > -Original Message- > From: [EMAIL PROTECTED]@SUNGARD On Behalf Of "Daiminger, Helmut" ><[EMAIL PROTECTED]> > Sent: Thursday, June 06, 2002 10:28 AM > To: Multiple recipients of list ORACLE-L > Subject: How to move 200 GB db from prod to dev? > > > 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 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev? 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
