How to Change Both DB_NAME and DB_UNIQUE_NAME of a Single-Instance Database

  • by

In previous post, we have talked about how to change DB_UNIQUE_NAME of a single-instance database. In this post, we will talk about how to change both DB_NAME and DB_UNIQUE_NAME of a single-instance database. For a RAC database, you should go for: How to Change Both DB_NAME and DB_UNIQUE_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 and DB_UNIQUE_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.

SQL> exit

We change DB_NAME from ORCLCDB to TESTCDB in the parameter file.

[oracle@test ~]$ vi /home/oracle/pfile
...
*.db_name='TESTCDB' # Changed by Ed Chen
...

Here we don't add parameter DB_UNIQUE_NAME to the file, this is because DB_UNIQUE_NAME will follow DB_NAME if we don't specify DB_UNIQUE_NAME at startup time.

Modify /etc/oratab.

[oracle@ora19c1 ~]$ vi /etc/oratab
...
#ORCLCDB:/u01/app/oracle/product/19.3.0/dbhome_1:Y
TESTCDB:/u01/app/oracle/product/19.3.0/dbhome_1:Y

Change ORACLE_SID.

[oracle@ora19c1 ~]$ export ORACLE_SID=TESTCDB
[oracle@ora19c1 ~]$ echo $ORACLE_SID
TESTCDB

Create a new SPFILE for startup.

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

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

File created.

We are going 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

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name                     string
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      TESTCDB
db_unique_name                       string      TESTCDB
global_names                         boolean     FALSE
instance_name                        string      TESTCDB
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      TESTCDB

Also, we noticed that the new SPFILE has been used.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0
                                                 /dbhome_1/dbs/spfileTESTCDB.or
                                                 a

We did it!

Next, we will talked about how to Change DBID of a RAC database.

Leave a Reply

Your email address will not be published. Required fields are marked *