How to Change DBID of a RAC Database

  • by

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.

  1. Change DB Name on All Files
  2. Modify Configuration on Cluster

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/init@.ora-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/init@.ora-new';
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.

Leave a Reply

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