Renaming an Oracle instance.

[Hai Nguyen_1]

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

[Ian Dennison_1 · Accepted solution]

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

[Steven E. Protter]

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

[Christian Gebhardt]

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]

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]

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