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.