Re: How to create a copy of an Instance?

In article <D4B4C5.H84_at_ranger.daytonoh.ncr.com>, Robert J. Dattilio <b_at_DaytonOH.attgis.COM> says:
>
>I think you will find your answer in the SET parameter of the Create
>controlfile command. This will rename your database. You should also
>consider changing the controlfile parameter and the db_name parameter in your
>init.ora file to represent your new database.
>
>
>==========Michael Nolan, 2/16/95==========
>Steve Butler <sbut-is_at_seatimes.com> writes:
>
>>On 14 Feb 1995, Michael Nolan wrote:
 

>>> What's the easiest way to create a duplicate copy of an instance
>>> on the same machine but with files in different directories? (with a
>>> different SID, obviously)
>
>[Most of recommended procedure deleted] I received a similar recommendation
>from another reader in the Netherlands. (isn't the net wonderful!)
>
>>You will need to edit that new SQL script to reference the new data file
>>locations. You can also change the name of the database by changing it
>>in the CREATE CONTROLFILE line of this script.
 

>>CREATE CONTROLFILE REUSE DATABASE newname NORESETLOGS NOARCHIVELOG
>
>It doesn't appear that you can change the database name when you
>issue the CREATE CONTROLFILE command under 7.0.15. I get a message
>complaining that the database name isn't the same as that in the headers,
>and it won't create the control files. The SQL Reference Manual for 7.0
>states that the database name 'must be the existing database name'.
>(If this has been changed for 7.1, I don't see it documented as such.)
>
>The fix I'm going to try is to shut down the live database again tonight,
>back it up again (just in case), rebuild the controlfiles as suggested but
>under the original database name, then issue an ALTER DATABASE RENAME
>GLOBAL_NAME TO XXXXX; command to change the global name, shut it down,
>restart the live database, modify the control files and restart the cloned
>database, and see if everything works.
>---
>Michael Nolan, Sysop for the DBMS RoundTable on GEnie
>nolan_at_notes.tssi.com, dbms_at_genie.geis.com
>(posted from nolan_at_helios.unl.edu)
>
>
Sorry for jumping in late but ...

A very easy way to copy and create a new instance is

1. Shut Oracle down - do it cleanly...
2. Copy all files to new location all files ! redo logs, control files, and all files supporting all tablespaces. you may change the names of any file you'd like. also copy initSID.ora to initNEWSID.ora. if you are using an extra configSID.ora file via the ifile init.ora parm then copy it to configNEWSID.ora
3. edit the initNEWSID.ora file and change the control_files entry to have the new copied ones.
4. For Unix set env. variable ORACLE_SID to NEWSID
5. sqldba connect internal
6. sqldba startup mount ( this opens the new control file )
7. for all files issue "alter database rename /oldpath/oldfile to /newpath/newfile. you must tell Oracle the fully qualified names of the new files ! now don't miss one ... redo logs and tablespace file name are all inside the controlfile.
8. either shutdown and startup or alter database open

PRESTO ! Your new sid "NEWSID" will be up with an exact copy of the data that "OLDSID" had. Now as far as the DBNAME goes you can't change it because you copied an existing database... big deal ! You still have at least two levels of naming abstaction to make it look like a new DB; the NEWSID and the TNS servicename!

try it ! its easy.  

Regards
GAG Received on Wed Feb 22 1995 - 05:08:41 CET