Instance is a combination of background process and memory. There are five mandatory background processes in Oracle database. In this article today we will learn how to change instance name in Oracle database. First of all, instance and database are two different. An instance is a set of memory structures that manage the database files whereas a database is a set of physical files that stores the data on a disk. Sometimes, as a DBA we may need to change the instance name while doing any DB build or restore. It can be done in two ways:
i)We can change instance name in Oracle database by issuing the below alter command :
Syntax to pre-check : show parameter instance_name; show parameter spfile; select instance_name from v$instance; sho parameter instance_name Syntax to change the instance_name : alter system set instance_name='TEST' scope=spfile;
To reflect in spfile, we have to add ‘instance_name’ in /etc/oratab entry , then rename the spfile and bounce the database :
Bounce database : shut immediate; mv spfileorclcfb.ora spfileTEST.ora . oraenv TEST startup; Verify instance Name : select instance_name from v$instance; show parameter instance_name;
ii) You can create a pfile from spfile and then change the instance_name into the pfile , Then start with a modified pfile and start the database. Later you can create spfile from that and bounce the database to reflect in spfile as well.
So, here are the steps that you can change instance name in Oracle database. Hope that helps!!