How to Change DB_UNIQUE_NAME of a Single-Instance Database

  • by

In previous post, we have talked about how to change DB_NAME of a single-instance database. In this post, we will talk about how to change DB_UNIQUE_NAME of a single-instance database. For a RAC database, you should go for: How to Change DB_UNIQUE_NAME of a RAC Database.

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

[oracle@test ~]$ echo $ORACLE_SID
ORCLCDB
[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show parameter name

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ORCLCDB

In this case, we want the original DB_UNIQUE_NAME derived from DB_NAME to be changed from ORCLCDB into TESTCDB.

Then we take the database offline.

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

We create a plain-text parameter file from SPFILE.

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

File created.

SQL> exit

Remove all parameters leading by the old DB_UNIQUE_NAME and add or modify the parameter DB_UNIQUE_NAME in PFILE like this.

[oracle@test ~]$ vi /home/oracle/pfile
*.audit_file_dest='/u01/app/oracle/admin/ORCLCDB/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCLCDB/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl'
*.db_block_size=8192
*.db_name='ORCLCDB'
*.db_unique_name='TESTCDB'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=20g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTCDBXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCLCDB'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=362m
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1086m
*.undo_tablespace='UNDOTBS1'

Next, turn to /etc/oratab. We replace SID with the new one in the file for oraenv to lookup later.

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

We also change ORACLE_SID environment variable by using oraenv.

[oracle@test ~]$ . oraenv
ORACLE_SID = [ORCLCDB] ? TESTCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@test ~]$ echo $ORACLE_SID
TESTCDB

Permanently, you can change ORACLE_SID in ~/.bash_profile.

We create a new SPFILE from PFILE for later startup.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> create spfile from pfile='/home/oracle/pfile';

File created.

SQL> startup
ORACLE instance started.
...
Database mounted.
Database opened.

After startup, we check the database unique name.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TESTCDB
SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name                     string
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      ORCLCDB
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

We did it.

Don't forget to modify $ORACLE_HOME/network/admin/tnsnames.ora of every client.

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

Leave a Reply

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