1.you can use "alter system set instance_name" to change the value of instance name.
2. you need to do the following to activate the standby database (NOTE:-this is only for activating the standby database and not for switchover of database)
1. startup nomount
2. alter database mount standby database.
3. alter database activate standby database.
4. alter database open.
only "alter system set instance_name='TEST' scope=spfile" will work since the parameter INSTANCE_NAME is static. After restarting the database/instance the statement "show parameter instance_name" will show the new instance name.
But when selecting the view V$INSTANCE the value of the column INSTANCE_NAME has still the old value. Any idea why this value remains unchanged in the view V$INSTANCE?
1 person found this helpful
The reason for the same is that you did not change the name of spfile to the new instance name.
change the spfile name to new instance name and restart the dataabse using new name.
check the instance name in v$instance view.
But the password file must be copied, too.
And before starting the database/instance, the ORACLE_SID must be set to the new instance_name.
Yes the password file should be renamed to the new name and while starting the database you use ORACLE_SID=new name.