How to Change DB_NAME of a Single-Instance Database

In previous post, we have talked about how to change DBID of a single-instance database. In this post, we will talk about how to change DB_NAME of a single-instance database.

Since DB_NAME is not just a simple database parameter like DB_UNIQUE_NAME, so we need more works to do. If you modify DB_NAME in SPFILE, you'll get error ORA-32016.

For a RAC database, you should go for: How to Change DB Name of a RAC Database.

First of all, let's see the current status.

SQL> select dbid, name, open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
3411734329 ORCLCDB   READ WRITE

In this case, we would like to change DB_NAME from ORCLCDB into TESTCDB.

We restart the database to MOUNT state.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
...
Database mounted.
SQL> exit

Use DBNEWID utility nid to change the database name on all files.

[oracle@test ~]$ nid target=sys/password dbname=TESTCDB

DBNEWID: Release 19.0.0.0.0 - Production on Mon Dec 22 19:18:06 2020

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to database ORCLCDB (DBID=3411734329)

Connected to server version 19.9.0

Control Files in database:
    /u01/app/oracle/oradata/ORCLCDB/control01.ctl
    /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl

Change database ID and database name ORCLCDB to TESTCDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3411734329 to 2800509118
Changing database name from ORCLCDB to TESTCDB
    Control File /u01/app/oracle/oradata/ORCLCDB/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/ORCLCDB/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/temp01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012020-07-14_23-10-41-107-PM.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/ORCLCDB/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl - dbid changed, wrote new name
    Instance shut down

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

If you don't want a new DBID, you can set SETNAME=YES to prevent DBID from changing. For example:

[oracle@test ~]$ nid target=sys/password dbname=TESTCDB setname=YES

Next, we create PFILE for later modification.

[oracle@test ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create pfile='/home/oracle/pfile' from spfile;

File created.

Since we don't want to change DB_UNIQUE_NAME, we should add a DB_UNIQUE_NAME and change DB_NAME in the parameter file.

If you don't add DB_UNIQUE_NAME to the file explicitly, DB_UNIQUE_NAME will follow DB_NAME at startup.

[oracle@test ~]$ vi /home/oracle/pfile
...
*.db_name='TESTCDB' # Changed by Scott
*.db_unique_name='ORCLCDB' # Added by Scott
...

Create a new SPFILE for startup.

SQL> create spfile from pfile='/home/oracle/pfile';

File created.

We are ready to startup the database, but the only different is that we should open the database with RESETLOGS.

SQL> startup mount;
ORACLE instance started.
...
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Please note that, if you have not changed DBID by specifying SETNAME=YES in nid command, then you don't have to open the database with RESETLOGS.

Let's check the final result.

SQL> select dbid, name, open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2800509118 TESTCDB   READ WRITE

We did it.

Next, we will talked about how to change DB_UNIQUE_NAME of a single-instance database.