How to rename a Database Instance?

It is very simple if you want to rename your database instance name. If your database is running with spfile then just alter INSTANCE_NAME parameter directly to a name you want to change to and bounce the instance and if your database is running with pfile then you would have to clean shutdown the database instance and modify the INSTANCE_NAME parameter value in the pfile and start database back with that pfile.



My database is running with spfile hence performed below steps to achieve our goal to rename the database instance name from proddb to TESTDB.



SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      proddb




SQL> alter system set instance_name='TESTDB' scope=spfile;

System altered.




SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  841162752 bytes
Fixed Size                  1339768 bytes
Variable Size             507514504 bytes
Database Buffers          327155712 bytes
Redo Buffers                5152768 bytes
Database mounted.


SQL> alter database open;

Database altered.



We can you database instance name changed to TESTDB now:
==========================

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      TESTDB