Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Renaming an Oracle instance.

SOLVED
Go to solution
Hai Nguyen_1
Honored Contributor

Renaming an Oracle instance.

Folks.

I would like to know if it is easy to rename an oracle instance. If so, please give me a detailed procedure to do it.

If there is any easier, more reliable method, I would like to know as well.

My Oracle version is 8.1.6 running on an rp5450, 11.0. Thanks.

Hai
6 REPLIES
Ian Dennison_1
Honored Contributor
Solution

Re: Renaming an Oracle instance.

Short answer: Ask a DBA.

Long Answer:

Create a copy of the definition of the database by performing the command 'alter database backup controlfile to trace;' in svrmgrl.

Modify trace file produced, remove extraneous lines at top and bottom of file. Change topmost line to read 'create database set name '. Change all occurences of old SID to new SID. Rename file to end with .sql.

Stop database, umount all file systems, change mount points to new SID, change names of users to new SID, change Oracle Parameter files to new SID, remount all file systems.

Log in as Oracle Userid, in svrmgrl, run sql script created previously.

There is a recent thread in the forums that holds Oracle Scripts; look there for the create controlfile script.

Share and Enjoy! Ian


Building a dumber user
Steven E. Protter
Exalted Contributor

Re: Renaming an Oracle instance.

You need to shut down the database.

then run this command:

find /oracle_home -exec grep -l 'instance_name' {} \; > /tmp/filelist

The oracle home must be your current oracle home. the instance_name must be replaced by your actual instance name.

Now you have a datafile of all the configuration changes you need to make with the exception of /etc/oratab

You can do it manually, or you can use a little script I wrote to make the change in an automated fashion.

Now the trick with this script is putting the old and new instance name in the sed section. You may need to modify your location of perl as well.

This works, its called database cloning.

If you admit to Oracle support you did it this way, they might not give you support. Hint: we do this all the time and have never had trouble except when we used to to convert a 32 bit 8.1.7.0 database to a 64 bit 8.1.7.0 database. To do that, you need a few oracle patches and might as well just install 8.1.7.4.0

P
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Christian Gebhardt
Honored Contributor

Re: Renaming an Oracle instance.

Hi

Don't forget to change the config files listener.ora, tnsnames.ora on the serverside and tnsnames.ora on all clients

Chris
Hai Nguyen_1
Honored Contributor

Re: Renaming an Oracle instance.

Ian, Steven,

Thanks for your help. I have not yet tried your solutions. But I believe they should work. I prefer Steven's approach. It seems to be simpler.

Hai
Jean-Luc Oudart
Honored Contributor

Re: Renaming an Oracle instance.

Hi you can clone the database on itself.
cf. the following thread
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x2a7eef70e827d711abdc0090277a778c,00.html


This is straightforward and I don't think there is any support issue with this method

Rgds,
Jean-Luc

PS : as mentioned above you will have to amend your config files
fiat lux
Yogeeraj_1
Honored Contributor

Re: Renaming an Oracle instance.

 
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)