NID utility has been introduced with Oracle 10g to change the database name and id. Without NID changing the DBNAME is only possible by recreating the control files. Changing the DBID was not possible before, as this is the database unique identifier. Giving a database a new DBNAME is common after migration of a database instance using Data Guard or duplicate where DBNAME and DB_UNIQUE_NAME differs and shall be synchronized afterwards. In some situtation an ORA-01103 error occurs and changing the DBNAME to a new value may be needed.
Changing DBNAME
SQL> select dbid, name, db_unique_name from v$database; DBID NAME DB_UNIQUE_NAME ---------- --------- ------------------------------ 1234567890 NIX NIX
Backup the database and open it in mount mode after a clean shutdown.
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT
Invoke the DBNEWID (NID) utility specifying the DBNAME from the command line using a user with SYSDBA privilege.
[oracle@dbserver ~]$ nid TARGET=/ DBNAME=NEXT LOGFILE=next_name.log
The created log file output as following. If you want to be asked before changing the DBNAME, just remove the LOGFILE parameter from the nid commandline and nid will ask you if anything has been specified correctly.
DBNEWID: Release 12.2.0.1.0 - Production on Fri May 4 19:50:12 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to database NIX (DBID=1234567890)
Connected to server version 12.2.0
Control Files in database:
/u01/oradata/NIX/controlfile/o1_mf_ffq92woh_.ctl
/u01/fast_recovery_area/NIX/NIX/controlfile/o1_mf_ffq92wpj_.ctl
Changing database ID from 1853261916 to 1335865972
Changing database name from NIX to NEXT
Control File /u01/oradata/NIX/controlfile/o1_mf_ffq92woh_.ctl - modified
Control File /u01/fast_recovery_area/NIX/NIX/controlfile/o1_mf_ffq92wpj_.ctl - modified
Datafile /u01/oradata/NIX/datafile/o1_mf_system_ffq8zy7q_.db - dbid changed, wrote new name
Datafile /u01/oradata/NIX/datafile/o1_mf_sysaux_ffq911d2_.db - dbid changed, wrote new name
Datafile /u01/oradata/NIX/datafile/o1_mf_undotbs1_ffq91th4_.db - dbid changed, wrote new name
Datafile /u01/oradata/NIX/datafile/o1_mf_users_ffq91vk9_.db - dbid changed, wrote new name
Datafile /u01/oradata/NIX/datafile/o1_mf_temp_ffq935fb_.tm - dbid changed, wrote new name
Control File /u01/oradata/NIX/controlfile/o1_mf_ffq92woh_.ctl - dbid changed, wrote new name
Control File /u01/fast_recovery_area/NIX/NIX/controlfile/o1_mf_ffq92wpj_.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to NEXT.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEXT changed to 9876543210.
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.
This utility won’t change the database name in the spfile, so change the database name (DB_NAME) in pfile manually and recreate the spfile. Additionally some more files need to be updated, as also your oracle environment parameters.
SQL> create pfile from spfile;
[oracle@dbserver ~]$ cp /u01/product/12.2.0/dbhome_1/dbs/initNIX.ora /u01/product/12.2.0/dbhome_1/dbs/initNEXT.ora
[oracle@dbserver ~]$ sed -i 's/NIX/NEXT/g' /u01/product/12.2.0/dbhome_1/dbs/initNEXT.ora
[oracle@dbserver ~]$ sed -i 's/NIX/NEXT/g' ~/.bashrc
[oracle@dbserver ~]$ mv /u01/product/12.2.0/dbhome_1/dbs/orapwNIX /u01/product/12.2.0/dbhome_1/dbs/orapwNEXT
[root@dbserver ~]$ sed -i 's/NIX/NEXT/g' /etc/oratab
[oracle@dbserver ~]$ source ~/.bashrc
SQL> create spfile from pfile;
File created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8621184 bytes
Variable Size 956302208 bytes
Database Buffers 620756992 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> select dbid, name, db_unique_name from v$database;
DBID NAME DB_UNIQUE_NAME
---------- --------- ------------------------------
1234567890 NEXT NEXT
Afterwards create a new initial database backup. The database has been started with reset logs. The database is a new incarnation and therefore old backups are not usable for any restore.
Changing only DBNAME
It is also possible changing only the DBNAME without changing the DBID parameter. Just set the SETNAME commandline parameter. Just follow the instructions as before.
[oracle@dbserver ~]$ nid TARGET=/ DBNAME=FUTHER LOGFILE=next_name.log SETNAME=YES
...
SQL> startup;
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8621184 bytes
Variable Size 956302208 bytes
Database Buffers 620756992 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> select dbid, name, db_unique_name from v$database;
DBID NAME DB_UNIQUE_NAME
---------- --------- ------------------------------
1234567890 NIX NIX
The database can be started in open mode after updating pfile/spfile parameters and the environment as before but without reset logs!
Changing only DBID
It may be nessesary to update the DBID without changing the DBNAME:
[oracle@dbserver ~]$ nid TARGET=/ LOGFILE=next_name.log
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8621184 bytes
Variable Size 956302208 bytes
Database Buffers 620756992 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid, name, db_unique_name from v$database;
DBID NAME DB_UNIQUE_NAME
---------- --------- ------------------------------
4711081512 NIX NIX
The database can be started in open mode as before with reset logs!
See also this great article from Tim Hall for DBNEWID Utility on oracle-base: https://oracle-base.com/articles/9i/dbnewid
