How to Change DBID of a Single-Instance Database

 

For a duplicate database, you may want to change its Database Identifier (DBID) only in order to distinguish this one from another. 

Here we use nid, a DBNEWID utility provided by Oracle to change DBID only.

Let's check DBID before changing.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> select name, dbid from v$database;

NAME            DBID
--------- ----------
TESTCDB   3411734329

We restart the database to MOUNT state.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
...
Database mounted.

Next, we use nid to connect to the database. Without specifying DBNAME, nid will change only DBID. There're more options of nid syntax in Oracle documentation.

[oracle@test ~]$ nid target=sys

Or connect to the database via a TNS name.

[oracle@test ~]$ nid target=sys@testcdb

DBNEWID: Release 19.0.0.0.0 - Production on Mon Dec 21 21:00:40 2020

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to database TESTCDB (DBID=3411734329)

Connected to server version 19.9.0

Control Files in database:
    /u01/app/oracle/oradata/TESTCDB/control01.ctl
    /u01/app/oracle/fast_recovery_area/TESTCDB/control02.ctl

Change database ID of database TESTCDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3411734329 to 3425699846
    Control File /u01/app/oracle/oradata/TESTCDB/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/TESTCDB/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/TESTCDB/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/pdbseed/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/pdbseed/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/pdbseed/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/ORCLPDB1/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/ORCLPDB1/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/ORCLPDB1/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/ORCLPDB1/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/ORCLPDB1/example01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/temp01.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/pdbseed/temp012020-07-14_23-10-41-107-PM.db - dbid changed
    Datafile /u01/app/oracle/oradata/TESTCDB/ORCLPDB1/temp01.db - dbid changed
    Control File /u01/app/oracle/oradata/TESTCDB/control01.ctl - dbid changed
    Control File /u01/app/oracle/fast_recovery_area/TESTCDB/control02.ctl - dbid changed
    Instance shut down

Database ID for database TESTCDB changed to 3425699846.
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.

Since the database identifier has been changed, we have to open the database with resetlogs.

[oracle@test ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
...
Database mounted.
SQL> alter database open resetlogs;

Database altered.

A new DBID now service the database.

SQL> select name, dbid from v$database;

NAME            DBID
--------- ----------
TESTCDB   3425699846

Done!