Change database name using DBNEWID

This blog explains about one of the simplest and reliable method to change the database name and database id.

The database version used for this is 19c and below is the DBID for both container and pluggable databases.

SQL> select name,dbid from v$database;
NAME                                 DBID
------------------------------ ----------
ORCL19C                        1633330436
SQL> select con_id,name,dbid from v$pdbs;
CON_ID NAME                                 DBID
---------- ------------------------------ ----------
         2 PDB$SEED                       2523086050
         3 ORCLPDB                        4003124817

nid is executable that is used for changing the database name and dbid. To look at the options for this, just type nid.

[oracle@nzel91 ~]$ nid
DBNEWID: Release 19.0.0.0.0 - Production on Tue Oct 1 13:48:07 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Keyword     Description                    (Default)
---------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

Below are the step by step process for this.

1. Shutdown the database and startup in mount mode.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 1426059984 bytes
Fixed Size                  9134800 bytes
Variable Size             352321536 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7639040 bytes
Database mounted.

1. In my example, I am changing the database name from orcl19c to new19c.

and I am redirecting the output to a log and not displaying it on the screen.

[oracle@nzel91 ~]$ nid target=sys/xxxxxxx DBNAME=new19c LOGFILE=/home/oracle/db_change.log

[oracle@nzel91 ~]$ cat db_change.log
DBNEWID: Release 19.0.0.0.0 - Production on Tue Oct 1 13:57:19 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to database ORCL19C (DBID=1633330436)
Connected to server version 19.3.0
Control Files in database:
    /u01/app/oracle/oradata/ORCL19C/control01.ctl
    /u01/app/oracle/fast_recovery_area/ORCL19C/control02.ctl
Changing database ID from 1633330436 to 614011775
Changing database name from ORCL19C to NEW19C
    Control File /u01/app/oracle/oradata/ORCL19C/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/ORCL19C/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/ORCL19C/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/pdbseed/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/pdbseed/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/pdbseed/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/orclpdb/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/orclpdb/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/orclpdb/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/orclpdb/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/orclpdb/new01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/orclpdb/test01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/temp01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/pdbseed/temp012023-12-07_11-21-05-876-AM.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCL19C/orclpdb/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/ORCL19C/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/fast_recovery_area/ORCL19C/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to NEW19C.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEW19C changed to 614011775.
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.

The changes that need to be done after the above step are more critical than just running the nid command.

1. Change the db_name parameter in the spfile.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1426059984 bytes
Fixed Size                  9134800 bytes
Variable Size             352321536 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7639040 bytes
SQL> alter system set db_name=new19c scope=spfile;
System altered
SQL> shutdown immediate //shutdown abort can be used as well
ORA-01507: database not mounted
ORACLE instance shut down.

1. Rename the spfile and also re-create the password file.

[oracle@nzel91 dbs]$ mv spfileorcl19c.ora spfilenew19c.ora
[oracle@nzel91 dbs]$ orapwd file=/u01/app/oracle/19c/dbs/orapwnew19c password=kiwi1234! entries=10

1. Mount the database again and open with resetlogs

SQL> alter database open resetlogs;
Database altered.

1. As an additional step, I also renamed the db_unique_name and restarted the database again.

SQL> alter system set db_unique_name=new19c scope=spfile;
System altered.
/* database restarted again*/
SQL>show parameter unique
NAME                     TYPE                    VALUE
---------------------- ----------- -----------------------------
db_unique_name                       string      NEW19C

1. We see that the dbid changed only for the container database and not for the pluggable database.

SQL> select name,dbid from v$database;
NAME            DBID
--------- ----------
NEW19C     614011775
NAME                               CON_ID       DBID
------------------------------ ---------- ----------
PDB$SEED                                2 2523086050
ORCLPDB                                 3 4003124817

There is a PDB option with the nid command that by default is NONE and the other value that can be presented with this parameter is ALL. We cannot choose to change only on PDB's dbid if you have more than one. Its either NONE or ALL PDB's dbid changes.

After Changing the name and dbid ensure below points are taken care.

1. All previous backups are invalidated as we have opened the database with resetlogs ( the archivelog sequence is reset to 1)

2. New backups must be taken immediately after this is done.

3. Ensure the application connectivity is not hindered by

   1. Changing the tns entries

   2. Change service_names if you have to

   3. Change the db_unique_name( this would still point to old db_name)

   4. reload the listeners

   5. Check the application server to ensure that the connect string is pointing to the new database name change( the service_name changes automatically to the new name)

If you are planning to change the database name alone, you don't have to open the database with resetlogs. The procedure is the same as above. The init parameters and the spfile names along with password file also needs to be changed.

Hope this was useful!!