How to Change DB_UNIQUE_NAME of a RAC Database

  • by

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

Did you notice? We can't specify a different DB_UNIQUE_NAME from DB_NAME during database creation (e.g. 19c), even though we use a response file to create the database in silent mode.

This is because Oracle assumes DB_UNIQUE_NAME is the same as DB_NAME, which is really not necessarily correct in some special cases. At least, we should create standby database with a different DB_UNIQUE_NAME to build data guard with the primary database.

For a RAC database, it's more difficult to change the DB_UNIQUE_NAME than a single-instance database. However, let's do it.

In this post, we take several course of actions to change DB_UNIQUE_NAME of the RAC database.

  1. Create New SPFILE
  2. Our goal in this section is to create a new SPFILE for the new In this new DB_UNIQUE_NAME of the database.

  3. Add RAC Database
  4. Our goal in this section is to add the database back to the cluster for the new DB_UNIQUE_NAME of the database.

  5. Some Minor Defects
  6. The procedure is not perfect, there's some minor defects that you may want to improve by yourself thereafter.

A. Create New SPFILE

1. Create PFILE

Let's check the configuration before creating PFILE.

[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is running on node primary01
Instance ORCLCDB2 is running on node primary02
[oracle@primary01 ~]$ srvctl config database -d orclcdb
Database unique name: ORCLCDB
Database name: ORCLCDB
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATA/ORCLCDB/PARAMETERFILE/spfile.275.1053776653
Password file: +DATA/ORCLCDB/PASSWORD/pwdorclcdb.256.1053773741
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: ORCLCDB1,ORCLCDB2
Configured nodes: primary01,primary02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

No matter the database is running or not, you can create PFILE from SPFILE anytime as long as you know the location of SPFILE.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ORCLCDB/PARAMETERFILE/sp
                                                 file.275.1053776653
SQL> create pfile='?/dbs/init@.ora-new' from spfile;

File created.

Since the database is running, so we don't have to specify the location of SPFILE in the above statement.

2. Modify PFILE

We add a parameter DB_UNIQUE_NAME in PFILE like this.

[oracle@primary01 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora-new
...
*.db_unique_name='TESTCDB' # Added by Ed Chen

And most important thing is to replace all instance names with the new names.

   Old   ->   New
--------------------
ORCLCDB1 -> TESTCDB1
ORCLCDB2 -> TESTCDB2

Of course, you also have to change ORACLE_SID on both nodes afterwards.

3. Create New SPFILE

We use the modified PFILE to create our new SPFILE. Please keep in mind, the instance is still running.

SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='?/dbs/init@.ora-new';

File created.

SQL> exit

Optionally, you can point the location of SPFILE in PFILE on both nodes.

[oracle@primary01 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
spfile='+DATA/ORCLCDB/spfile'
[oracle@primary02 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
spfile='+DATA/ORCLCDB/spfile'

We have created a new SPFILE as we want, we can use it to configure the new RAC database in the next section.

B. Add RAC Database

Now we remove the database, then add it back with a little different configuration.

1. Stop and Remove Database

We have to make sure the database is stop before removing it.

[oracle@primary01 ~]$ srvctl stop database -d orclcdb

If you hesitated to remove the database, you can stop here by responding n to cancel the command. Also, make sure that's the name you want to remove.

[oracle@primary01 ~]$ srvctl remove database -d orclcdb
Remove the database orclcdb? (y/[n]) y

I decided to go forward.

2. Add Database

[oracle@primary01 ~]$ srvctl add database \
> -db TESTCDB \
> -dbname ORCLCDB \
> -oraclehome /u01/app/oracle/product/19.0.0/db_1 \
> -spfile +DATA/ORCLCDB/spfile \
> -pwfile +DATA/ORCLCDB/PASSWORD/pwdorclcdb.256.1053773741 \
> -startoption OPEN \
> -stopoption IMMEDIATE \
> -role PRIMARY \
> -policy AUTOMATIC \
> -diskgroup DATA \
> -dbtype RAC

This time I use TESTCDB for my DB_UNIQUE_NAME, DB_NAME stays unchanged.

3. Add Instance

Then We add two nodes to the database. [oracle@primary01 ~]$ srvctl add instance -db TESTCDB -instance TESTCDB1 -node primary01
[oracle@primary01 ~]$ srvctl add instance -db TESTCDB -instance TESTCDB2 -node primary02

As you can see, we add nodes to the new database.

More about adding a RAC database and why we use upper-cased instance names, you may refer to: How to Create an Empty RAC Database without Using DBCA.

4. Review Configuration

We review the configuration of the RAC database to make sure everything is ready to startup the database.

[oracle@primary01 ~]$ srvctl config database -d testcdb
Database unique name: TESTCDB
Database name: ORCLCDB
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATA/ORCLCDB/spfile
Password file: +DATA/ORCLCDB/PASSWORD/pwdorclcdb.256.1053773741
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: TESTCDB1,TESTCDB2
Configured nodes: primary01,primary02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

5. Start Database

If the above works are well prepared, we can start the database safely.

[oracle@primary01 ~]$ srvctl start database -d testcdb
[oracle@primary01 ~]$ srvctl status database -d testcdb
Instance TESTCDB1 is running on node primary01
Instance TESTCDB2 is running on node primary02

6. Check All Names

Don't forget to change your ORACLE_SID on both nodes, either temporarily or permanently before showing parameters.

Change ORACLE_SID Temporarily

[oracle@primary01 ~]$ export ORACLE_SID=TESTCDB1

Or using oraenv to change ORACLE_SID.

[oracle@primary01 ~]$ . oraenv
ORACLE_SID = [ORCLCDB1] ? TESTCDB1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/db_1
The Oracle base remains unchanged with value /u01/app/oracle

Change ORACLE_SID Permanently

[oracle@primary01 ~]$ vi ~/.bash_profile
...
export ORACLE_SID=TESTCDB1
[oracle@primary01 ~]$ . ~/.bash_profile

The final check, list all names in the database.

[oracle@primary01 ~]$ 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      TESTCDB
global_names                         boolean     FALSE
instance_name                        string      TESTCDB1
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

Looking good.

C. Some Minor Defect

It seems that all goes well until you find out some facts about the location of new created data files. For example, we create a tablespace as followings:

SQL> CREATE TABLESPACE ERPTBS DATAFILE '+DATA/ORCLCDB/ERPTBS_1.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ,'+DATA/ORCLCDB/ERPTBS_2.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Tablespace created.

Let's check their location.

[grid@primary01 ~]$ asmcmd ls -l +DATA/ORCLCDB/ERPTBS*
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 04 11:00:00  N    ERPTBS_1.dbf => +DATA/TESTCDB/DATAFILE/ERPTBS.295.1058267833
DATAFILE  UNPROT  COARSE   DEC 04 11:00:00  N    ERPTBS_2.dbf => +DATA/TESTCDB/DATAFILE/ERPTBS.296.1058267835

Although we put data files in +DATA/ORCLCDB/, the actual data files are in +DATA/TESTCDB/. This is because the current database unique name is now TESTCDB, not ORCLCDB any longer. For moving all database files to +DATA/TESTCDB/, you can refer to the following topics.

  1. How to move spfile
  2. How to move control files
  3. How to move data files
  4. How to move redo logs
  5. How to move temp files

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 RAC database.

Leave a Reply

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