Changing Database Name in ORACLE: During this article we’ll discuss about a way to Changing Database Name in ORACLE Database.
Question: I need to re-name my database to vary the ORACLE_SID value. I have already changed the instance_name parameter in the parameter file mean init.ora file, but its still now at database said showing same after we select instance_name from v$instance? what’s the right methods to re-name an Oracle instance?
You need to Follow the below given steps to change the oracle database name using nid utility. Here we will change the database name from ORCL to PROD.
Steps:
Mount the Database.
Run the NID utility.
Shutdown the Database.
Change the db_name parameter in the parameter file.
Rename the spfile to new db name.
Create a new password file.
Reset the ORACLE_SID environment variable.
Alter all the network files setting to match the new database name and take bounce the listener.
Open the database with RESETLOGS.
Firstly, Mount the Database:
SQL>
SQL> select name, database_role, open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
ORCL PRIMARY READ WRITE
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 8622776 bytes
Variable Size 448793928 bytes
Database Buffers 75497472 bytes
Redo Buffers 3956736 bytes
Database mounted.
SQL>
SQL>
SQL> select name, database_role, open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
ORCL PRIMARY MOUNTED
SQL>Secondly, Run the NID utility :
[oracle@orakldba ~]$
[oracle@orakldba ~]$ nid target=sys/sys123@ORCL DBNAME=PROD
DBNEWID: Release 12.2.0.1.0 - Production on Wed Aug 19 00:05:44 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1576507291)
Connected to server version 12.2.0
Control Files in database:
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
Change database ID and database name ORCL to PROD? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1576507291 to 460619609
Changing database name from ORCL to PROD
Control File /u01/app/oracle/oradata/ORCL/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl - modified
Datafile /u01/app/oracle/oradata/ORCL/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCL/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCL/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCL/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCL/temp01.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/ORCL/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to PROD.
Modify parameter file and generate a new password file before restarting.
Database ID for database PROD changed to 460619609.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
Now, Shutdown the Database:
SQL>
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>Then, Change the db_name parameter in the parameter file :
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 8622776 bytes
Variable Size 448793928 bytes
Database Buffers 75497472 bytes
Redo Buffers 3956736 bytes
Database mounted.
SQL>
SQL>
SQL> alter system set db_name=PROD scope=spfile;
System altered.
SQL>
SQL>
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 8622776 bytes
Variable Size 448793928 bytes
Database Buffers 75497472 bytes
Redo Buffers 3956736 bytes
Database mounted.
SQL>
SQL>
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string PROD
SQL>
SQL>
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
After that, Rename the spfile to new db name :
[oracle@orakldba ~]$
[oracle@orakldba ~]$ cd $ORACLE_HOME/dbs/
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/dbs
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$ ls -ltr
total 10376
-rw-r--r--. 1 oracle oracle 3079 May 15 2015 init.ora
-rw-r----- 1 oracle oracle 24 Aug 18 00:53 lkORCL
-rw-r----- 1 oracle oracle 10600448 Aug 18 23:19 snapcf_ORCL.f
-rw-r----- 1 oracle oinstall 3584 Aug 18 23:41 orapwORCL
-rw-r----- 1 oracle oracle 24 Aug 19 00:17 lkPROD
-rw-rw---- 1 oracle oracle 1544 Aug 19 00:17 hc_ORCL.dat
-rw-r----- 1 oracle oracle 3584 Aug 19 00:17 spfileORCL.ora
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$ cp spfileORCL.ora spfilePROD.ora
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$ ls -ltr
total 10380
-rw-r--r--. 1 oracle oracle 3079 May 15 2015 init.ora
-rw-r----- 1 oracle oracle 24 Aug 18 00:53 lkORCL
-rw-r----- 1 oracle oracle 10600448 Aug 18 23:19 snapcf_ORCL.f
-rw-r----- 1 oracle oinstall 3584 Aug 18 23:41 orapwORCL
-rw-r----- 1 oracle oracle 24 Aug 19 00:17 lkPROD
-rw-rw---- 1 oracle oracle 1544 Aug 19 00:17 hc_ORCL.dat
-rw-r----- 1 oracle oracle 3584 Aug 19 00:17 spfileORCL.ora
-rw-r----- 1 oracle oinstall 3584 Aug 19 00:23 spfilePROD.ora
Then, Create a new password file :
[oracle@orakldba ~]$
[oracle@orakldba ~]$ cd $ORACLE_HOME/dbs/
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/dbs
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$ orapwd file=orapwPROD password=prod#1234 entries=10
[oracle@orakldba dbs]$
[oracle@orakldba dbs]$ ls -ltr
total 10388
-rw-r--r--. 1 oracle oracle 3079 May 15 2015 init.ora
-rw-r----- 1 oracle oracle 24 Aug 18 00:53 lkORCL
-rw-r----- 1 oracle oracle 10600448 Aug 18 23:19 snapcf_ORCL.f
-rw-r----- 1 oracle oinstall 3584 Aug 18 23:41 orapwORCL
-rw-r----- 1 oracle oracle 24 Aug 19 00:17 lkPROD
-rw-rw---- 1 oracle oracle 1544 Aug 19 00:17 hc_ORCL.dat
-rw-r----- 1 oracle oracle 3584 Aug 19 00:17 spfileORCL.ora
-rw-r----- 1 oracle oinstall 3584 Aug 19 00:23 spfilePROD.ora
-rw-r----- 1 oracle oinstall 6144 Aug 19 00:28 orapwPROD
Therefore, Reset the ORACLE_SID environment variable :
[oracle@orakldba ~]$
[oracle@orakldba ~]$ export ORACLE_SID=PROD
Lastly, Setting to match the new database name and restart the listener :
[oracle@orakldba admin]$
[oracle@orakldba admin]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/network/admin
[oracle@orakldba admin]$
[oracle@orakldba admin]$ vi listener.ora
[oracle@orakldba admin]$
[oracle@orakldba admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = PROD)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orakldba.localdomain)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@orakldba admin]$
[oracle@orakldba admin]$ vi tnsnames.ora
[oracle@orakldba admin]$
[oracle@orakldba admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orakldba.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
[oracle@orakldba ~]$
[oracle@orakldba ~]$ lsnrctl reload
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-AUG-2020 00:50:06
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orakldba.localdomain)(PORT=1521)))
The command completed successfully
[oracle@orakldba ~]$
[oracle@orakldba ~]$
[oracle@orakldba ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-AUG-2020 00:50:16
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orakldba.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 19-AUG-2020 00:04:41
Uptime 0 days 0 hr. 45 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/orakldba/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orakldba.localdomain)(PORT=1521)))
Services Summary...
Service "PROD" has 1 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfullyIn conclusion, Open the database with RESETLOGS :
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 8622776 bytes
Variable Size 448793928 bytes
Database Buffers 75497472 bytes
Redo Buffers 3956736 bytes
Database mounted.
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL>
SQL> alter system register;
System altered.
SQL>
SQL>
SQL> select name, database_role, open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
PROD PRIMARY READ WRITE
SQL>
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query which is related to above content. You can write to mail me through : orakldba@gmail.com.
1 thought on “Changing Database Name in ORACLE using nid utility”