regaring instance

[shailendra]

I want to change my instance name
there is a parameter in init.ora instance_name
I have change but there is no effect of that change
while fetching the instance name from the query it is giving me the provios one
even I shutdown database soo many times but there is no
effect on it
pls. help me to find out the correct way
Thanks in advance

[pando]

to change instance name

1. shutdown your database
2. export ORACLE_SID=new_instance_name
3. start your database
4. check v$instance it should now indicates a new instance name

[shailendra]

Thanks for reply

But will you plz. tell me how do I export Oracle_SID

thanks in Advance

[pando]

err it's just I wrote, in command line export ORACLE_SID... are u in NT?

[gram2000]

I am guessing that you are attempting to rename your instance.
If this is true, you have to recreate your controlfile. Here are the steps to do that;

-. from server manager execute the following command :
alter database backup controlfile to trace;
-. go to your user dump destination
-. search through the list of recent file (date time stamp)
-. there should be a file there that has information about your database
-. make a copy of that file
-. if your database was called "DEV" and you wish to call it "TEST" you would
see the following:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DEV1" NORESETLOGS ARCHIVELOG
-. Change it to :
STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE TEST NORESETLOGS ARCHIVELOG
-. shutdown the instance
-. Make changes to your oratab, listener, tnsnames files
-. set your oracle sid to test (export ORACLE_SID=TEST (on unix 0 or
SET ORACLE_SID=TEST (on microsoft virus))
-. start server manager and run the script.
-. When your database comes up, your instance will now be called TEST.

Hope it helps

[pando]

gram2000 that is to change database name not instance name, you can change your instance name just by changing oracle_sid and bounce the database

[sln81]

I think, in some other thread also 'changing the instance name' was discussed. About the INSTANCE_NAME parameter, it is a new parameter added in oracle 8i. It is not to change the instance name. It is for the automatic registration of the instance with the default listener.

[shailendra]

Thanks to all of you

but still I am in problem as you said that
set Oracle_SId =(new instance name)
I have given this command on command prompt
but it has no use the instance name is still same it is not taking what I have specified as well as about instance parameter that is availiable in 8.1.6 or 8.1.7
So what I do now?
Pls. help me

[hrishy]

could you please tell us about you operating system wheather it is windows unix linux solaris ?

[pando]

have you shutdown the database before change ORACLE_SID...?!?!