Change Oracle DB name using NID: How to Change the DBID, DBNAME Using NID Utility (Doc ID 863800.1)
1. In Real Application Cluster, we need to set cluster_database parameter to false and mount an instance on only one node:
sqlplus / as sysdba alter system set cluster_database=false scope=spfile; oracle@db01:~# . oraenv ORACLE_SID = [DUTSDB] ? DUTSPD The Oracle base has been changed from /opt/Oracle/app/oracle to /opt/Oracle/database oracle@db01:~# oracle@db01:~# oracle@db01:~# oracle@db01:~# oracle@db01:~# oracle@db01:~# sqlplus / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> alter system set cluster_database=false scope=spfile; System altered. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DUTSPD SQL> exit oracle@db01:~# srvctl stop database -d dutspd oracle@db01:~# sqlplus / as sysdba Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1.6435E+10 bytes Fixed Size 2242304 bytes Variable Size 8925481216 bytes Database Buffers 7482638336 bytes Redo Buffers 24707072 bytes Database mounted. SQL> SQL>
2. From the 1st node run nid utility. Specify username with sysdba privilege, target database name, and SETNAME parameter to yes.
oracle@db01:~# clear
oracle@db01:~# ps -ef | grep pmon
oracle 9127 5662 0 Jun 05 ? 4:28 asm_pmon_+ASM1
oracle 11287 5662 0 Jun 05 ? 25:09 ora_pmon_DUTSDB
oracle 16694 5662 0 12:13:19 ? 0:00 ora_pmon_DUTSPD
oracle 17630 10739 0 12:15:05 pts/4 0:00 grep pmon
oracle@db01:~# . oraenv
ORACLE_SID = [DUTSDB] ? DUTSPD
The Oracle base remains unchanged with value /opt/Oracle/database
oracle@db01:~#
oracle@db01:~# nid TARGET=SYS DBNAME=DUTSPB
DBNEWID: Release 19.16.0.0.0 - Production on Mon Dec 28 12:17:04 2022
Password:
Connected to database DUTSPD (DBID=2319832658)
Connected to server version 19.16.0
Control Files in database:
+DATA/dutspd/controlfile/current.265.1075567891
+RECO/dutspd/controlfile/current.317.1075567891
Change database ID and database name DUTSPD to DUTSPB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2319832658 to 2596561288
Changing database name from DUTSPD to DUTSPB
Control File +DATA/dutspd/controlfile/current.265.1075567891 - modified
Control File +RECO/dutspd/controlfile/current.317.1075567891 - modified
Datafile +DATA/dutspd/datafile/system.343.107556773 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/sysaux.346.107556773 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/undotbs1.336.107556773 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/users.335.107556773 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/undotbs2.327.107556790 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcsys_data.342.107640472 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcsys_index.348.107640472 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/learch_tbs.337.107640472 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip2.338.107640472 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/cfnweb.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip_qweeby.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip_lbbw.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip_eptca.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcbws.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip_learch_acceptance.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip_editus.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcsys_train_data.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcsys_train_index.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip_bil.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcip_test.db - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcep_test.db - dbid changed, wrote new name
Datafile +DATA/dutspd/tempfile/temp.270.107556789 - dbid changed, wrote new name
Datafile +DATA/dutspd/datafile/arcsys_train_temp.db - dbid changed, wrote new name
Control File +DATA/dutspd/controlfile/current.265.1075567891 - dbid changed, wrote new name
Control File +RECO/dutspd/controlfile/current.317.1075567891 - dbid changed, wrote new name
Instance shut down
Database name changed to DUTSPB.
Modify parameter file and generate a new password file before restarting.
Database ID for database DUTSPB changed to 2596561288.
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.
oracle@db01:~#
3. Change db_name parameter in the initialization parameter file:
oracle@db01:~# . oraenv ORACLE_SID = [DUTSPD] ? DUTSPB The Oracle base remains unchanged with value /opt/Oracle/database oracle@db01:~# sqlplus / as sysdba Connected to an idle instance. SQL> startup nomount; alter system set db_name=DUTSPB scope=spfile; alter system set cluster_database=true scope=spfile; shut immediate; ORACLE instance started. Total System Global Area 1.6435E+10 bytes Fixed Size 2242304 bytes Variable Size 8925481216 bytes Database Buffers 7482638336 bytes Redo Buffers 24707072 bytes SQL> System altered. SQL> System altered. SQL> ORA-01507: database not mounted ORACLE instance shut down. SQL> SQL> oracle@db01:dbs# sqlplus / as sysdba Connected to an idle instance. SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 1.6435E+10 bytes Fixed Size 2242304 bytes Variable Size 8925481216 bytes Database Buffers 7482638336 bytes Redo Buffers 24707072 bytes Database mounted. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. SQL>
4. Remove existing password file entry and create a new one in both nodes:
oracle@db01:db_home_1# cd $ORACLE_HOME oracle@db01:db_home_1# cd dbs oracle@db01:dbs# orapwd file=orapwDUTSPB Enter password for SYS: oracle@db01:dbs# 5. Change the service of the database
oracle@db01:dbs# sqlplus / as sysdba SQL> alter system set db_unique_name=DUTSPB scope=spfile sid='*'; System altered. SQL> oracle@db01:dbs# srvctl add database -d DUTSPB -o /opt/Oracle/database/product/19.0.0/db_home_1 -r primary -s OPEN -p '+DATA/DUTSPB/spfileDUTSPB.ora' -n db01 oracle@db01:dbs# srvctl add instance -d DUTSPB -i DUTSPB1 -n db01 oracle@db01:dbs# srvctl add instance -d DUTSPB -i DUTSPB2 -n db02 oracle@db01:dbs# oracle@db01:dbs# srvctl config database -d DUTSPB Database unique name: DUTSPB Database name: DUTSPB Oracle home: /opt/Oracle/database/product/19.0.0/db_home_1 Oracle user: oracle Spfile: +DATA/DUTSPB/spfileDUTSPB.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: DUTSPB Database instances: DUTSPB1, DUTSPB2 Disk Groups: Mount point paths: Services: Type: RAC Database is administrator managed oracle@db01:dbs# oracle@db01:dbs# . oraenv ORACLE_SID = [+ASM1] ? DUTSPB The Oracle base remains unchanged with value /opt/Oracle/database oracle@db01:dbs# oracle@db01:dbs# sqlplus / as sysdba Connected to an idle instance. SQL> startup mount pfile=/opt/Oracle/database/product/19.0.0/db_home_1/dbs/initDUTSPB.ora SQL> create spfile='+DATA/DUTSPB/ spfileDUTSPB.ora' from pfile='/opt/Oracle/database/product/19.0.0/db_home_1/dbs/initDUTSPB.ora; File created. SQL>exit oracle@db01:dbs# srvctl start database -d DUTSPB Hope this helps you!!! Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful
