In previous post, we have talked about how to change both DB_NAME and DB_UNIQUE_NAME of a single-instance database. In this post, we will talk about how to change DBID of a RAC database. For a single-instance database, you should go for: How to Change DBID of a Single-instance Database.
There're two major parts in changing the database identifier (DBID) of a RAC database.
A. Change DBID on All Files
What we do in this section is for changing DBID 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 Identifier
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
[oracle@primary01 ~]$ nid target=sys/password
DBNEWID: Release 19.0.0.0.0 - Production on Tue Dec 2 13:52:54 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 of database ORCLCDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2824835543 to 2830895031
Control File +DATA/ORCLCDB/control01.ctl - modified
Control File +DATA/ORCLCDB/control02.ctl - modified
Datafile +DATA/ORCLCDB/system01.db - dbid changed
Datafile +DATA/ORCLCDB/sysaux01.db - dbid changed
Datafile +DATA/ORCLCDB/undotbs01.db - dbid changed
Datafile +DATA/ORCLCDB/pdbseed/system01.db - dbid changed
Datafile +DATA/ORCLCDB/pdbseed/sysaux01.db - dbid changed
Datafile +DATA/ORCLCDB/users01.db - dbid changed
Datafile +DATA/ORCLCDB/pdbseed/undotbs01.db - dbid changed
Datafile +DATA/ORCLCDB/undotbs02.db - dbid changed
Datafile +DATA/ORCLCDB/ORCLPDB/system01.db - dbid changed
Datafile +DATA/ORCLCDB/ORCLPDB/sysaux01.db - dbid changed
Datafile +DATA/ORCLCDB/ORCLPDB/undotbs01.db - dbid changed
Datafile +DATA/ORCLCDB/ORCLPDB/system01_i2_undo.db - dbid changed
Datafile +DATA/ORCLCDB/ORCLPDB/users01.db - dbid changed
Datafile +DATA/ORCLCDB/temp01.db - dbid changed
Datafile +DATA/ORCLCDB/pdbseed/temp012020-10-14_11-20-07-799-am.db - dbid changed
Datafile +DATA/ORCLCDB/ORCLPDB/temp01.db - dbid changed
Control File +DATA/ORCLCDB/control01.ctl - dbid changed
Control File +DATA/ORCLCDB/control02.ctl - dbid changed
Instance shut down
Database ID for database ORCLCDB changed to 2830895031.
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 ID.
DBNEWID - Completed succesfully.
We have changed data files and control files.
B. Modify Configuration on Cluster
Now we're going back to work with the cluster. Since we didn't change either DB_NAME or DB_UNIQUE_NAME, current SPFILE is still available and useful to startup the database.
1. 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
2. Open Resetlogs
Since we have changed DBID, we need to open the database with RESETLOGS.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> alter database open resetlogs;
Database altered.
The new database identification is as:
SQL> select name, dbid from v$database;
NAME DBID
--------- ----------
ORCLCDB 2830895031
Done!
Next, we will talked about how to change DB_NAME of a RAC database.