In previous post, we have talked about how to change DBID of a RAC database. In this post, we will talk about how to change DB_NAME of a RAC 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 single-instance, you should go for: How to Change DB Name of a Single-instance Database.
There're two major parts in changing the database name of a RAC database.
A. Change DB Name on All Files
What we do in this section is for changing the database name on all database files smoothly later. First of all, let's the current status.
[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
As you can see, DB_NAME and DB_UNIQUE_NAME are all ORCLCDB. I'd like to change DB_NAME into TESTCDB
1. Create PFILE
We create a plain-text parameter file for easily modifying parameters. For preventing to overwrite the original parameter file if any, we output all parameters to another file.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ORCLCDB/PARAMETERFILE/sp
file.275.1053776653
SQL> create pfile='?/dbs/[email protected]-new' from spfile;
File created.
SQL> exit;
If you don't know what "?" or "@" symbol means in the above statement, you may refer to: What Symbols are Used in Oracle Database.
2. Stop and Disable the RAC Database
We are going to start the database for later exclusive use in this instance, so we need to stop and disable it from the cluster.
[oracle@primary01 ~]$ srvctl stop database -d orclcdb
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is not running on node primary01
Instance ORCLCDB2 is not running on node primary02
[oracle@primary01 ~]$ srvctl disable database -d orclcdb
3. Startup Mount by PFILE
For exclusively use the database in this instance, we must startup it without the cluster.
[oracle@primary01 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora-new
...
#*.cluster_database=true
With commenting out the parameter, CLUSTER_DATABASE goes back to the default value FALSE.
If you don't disable CLUSTER_DATABASE, the changing will fail with NID-00120: Database should be mounted exclusively.
We startup the database to mount by the parameter file.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup mount pfile='?/dbs/[email protected]';
ORACLE instance started.
...
Database mounted.
4. Change the Database Name
By using nid, a DBNEWID utility, we can change the database name now. Please make sure the environment variables are all correct before doing it.
[oracle@primary01 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0/db_1
[oracle@primary01 ~]$ echo $ORACLE_SID
ORCLCDB1
[oracle@primary01 ~]$ nid target=sys/password dbname=TESTCDB
DBNEWID: Release 19.0.0.0.0 - Production on Thu Dec 3 19:19:34 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCLCDB (DBID=2824835543)
Connected to server version 19.3.0
Control Files in database:
+DATA/ORCLCDB/control01.ctl
+DATA/ORCLCDB/control02.ctl
Change database ID and database name ORCLCDB to TESTCDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2824835543 to 2799630695
Changing database name from ORCLCDB to TESTCDB
Control File +DATA/ORCLCDB/control01.ctl - modified
Control File +DATA/ORCLCDB/control02.ctl - modified
Datafile +DATA/ORCLCDB/system01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/sysaux01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/undotbs01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/pdbseed/system01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/pdbseed/sysaux01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/users01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/pdbseed/undotbs01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/undotbs02.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/ORCLPDB/system01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/ORCLPDB/sysaux01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/ORCLPDB/undotbs01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/ORCLPDB/system01_i2_undo.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/ORCLPDB/users01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/temp01.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/pdbseed/temp012020-10-14_11-20-07-799-am.db - dbid changed, wrote new name
Datafile +DATA/ORCLCDB/ORCLPDB/temp01.db - dbid changed, wrote new name
Control File +DATA/ORCLCDB/control01.ctl - dbid changed, wrote new name
Control File +DATA/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 2799630695.
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
We have changed data files and control files.
Please note the following things:
- Once you issued the command, don't interrupt it.
- Not only the database name, but also DBID has been changed.
- The instance has been shutdown by the utility.
- You have to open the database with RESETLOGS afterwards.
B. Modify Configuration on Cluster
Now we're going back to work with the cluster.
1. Modify PFILE
We are going to do the following things in the parameter file.
- Revert CLUSTER_DATABASE to TRUE by removing the leading comment symbol (#).
- Change DB_NAME into the new database name TESTCDB.
- Add a DB_UNIQUE_NAME parameter which is set as ORCLCDB as usual.
[oracle@primary01 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora-new
...
*.cluster_database=true
*.db_name='TESTCDB' # Changed by Scott
*.db_unique_name='ORCLCDB' # Added by Scott
2. Create a New SPFILE
For locating the SPFILE correctly, we first startup the database by the parameter file to NOMOUNT.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup nomount pfile='?/dbs/[email protected]';
ORACLE instance started.
...
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 ORCLCDB
global_names boolean FALSE
instance_name string ORCLCDB1
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
As we can see, except DB_NAME is the new name, all other names remain unchanged.
In other words, the instance now knows what current DB_UNIQUE_NAME is. Therefore, the newly created SPFILE won't be put to the wrong directory.
SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='?/dbs/[email protected]';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
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'
2. Modify the RAC Configuration
We modify the following two things in the cluster configuration:
- New Database Name: TESTCDB
- New SPFILE Destination: '+DATA/ORCLCDB/spfile'
[oracle@primary01 ~]$ srvctl modify database -d orclcdb -dbname TESTCDB
[oracle@primary01 ~]$ srvctl modify database -d orclcdb -spfile '+DATA/ORCLCDB/spfile'
Let's check the new configuration.
[oracle@primary01 ~]$ srvctl config database -d orclcdb
Database unique name: ORCLCDB
Database name: TESTCDB
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: 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
3. Startup Mount
We need to get the database back to the cluster before starting it.
[oracle@primary01 ~]$ srvctl enable database -d orclcdb
Start the database to mount for later open.
[oracle@primary01 ~]$ srvctl start database -d orclcdb -o mount
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is running on node primary01
Instance ORCLCDB2 is running on node primary02
4. Open Resetlogs
Since we have changed DBID, we need to open the database with RESETLOGS.
Node 1
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> alter database open resetlogs;
Database altered.
SQL> select inst_id, open_mode from gv$database;
INST_ID OPEN_MODE
---------- --------------------
1 READ WRITE
2 MOUNTED
Since using ALTER DATABASE OPEN to open a database affects only current instance, we need to open the database on rest nodes.
Node 2
[oracle@primary02 ~]$ sqlplus / as sysdba
...
SQL> alter database open;
Database altered.
SQL> select inst_id, open_mode from gv$database;
INST_ID OPEN_MODE
---------- --------------------
1 READ WRITE
2 READ WRITE
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.
The new database identifier is as:
SQL> select dbid from v$database;
DBID
----------
2799630695
One last thing, you should remove the parameter file that we created previously to prevent starting the database in a wrong way. For more about the topic, you may refer to: Why SPFILE Does Not Work in RAC.
Next, we will talked about how to change DB_UNIQUE_NAME of a RAC database.
Thanks for your sharing, the doc help me so much
You’re welcome!