Ameer Khan

Objective:- Change DBID in oracle database .

For example, we will be changing existing DBID LOGCDB to a new DBID UATCDB

Step 1. Shutdown database instance

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 2. Start database in mount mode

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

Database mounted.

SQL> select name,open_mode,instance_name from v$database,v$instance; 

NAME      OPEN_MODE            INSTANCE_NAME

——— ——————– —————-

LOGCDB    MOUNTED              LOGCDB

Step 3. Change DBNAME using nid utility

Syntax for changing DBNAME

nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

[logminedb@ccuine1126 dbs]$ nid target=sys/manager@LOGCDB DBNAME=UATCDB

DBNEWID: Release 19.0.0.0.0 – Production on Tue Apr 19 16:47:33 2022

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

Connected to database LOGCDB (DBID=3597672493)

Connected to server version 19.9.0

Control Files in database:

    /db/app/logminedb/oradata/LOGCDB/control01.ctl

    /db/app/logminedb/oradata/LOGCDB/control02.ctl

Change database ID and database name LOGCDB to UATCDB? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 3597672493 to 205605669

Changing database name from LOGCDB to UATCDB

    Control File /db/app/logminedb/oradata/LOGCDB/control01.ctl – modified

    Control File /db/app/logminedb/oradata/LOGCDB/control02.ctl – modified

    Datafile /db/app/logminedb/oradata/LOGCDB/system01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/system01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/sysaux01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/sysaux01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/undotbs01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/undotbs01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/users01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/system01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/sysaux01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/undotbs01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/users01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/temp01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/temp01.db – dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/temp01.db – dbid changed, wrote new name

    Control File /db/app/logminedb/oradata/LOGCDB/control01.ctl – dbid changed, wrote new name

    Control File /db/app/logminedb/oradata/LOGCDB/control02.ctl – dbid changed, wrote new name

    Instance shut down

Database name changed to UATCDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database UATCDB changed to 205605669.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID – Completed succesfully.

Step 4. Change dbname in parameter file

SQL> startup nomount

ORACLE instance started.

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

SQL> alter system set db_name=UATCDB scope=spfile; 

System altered.

SQL>  shutdown immediate;

ORA-01507: database not mounted 

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

 Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

 SQL> show parameter db_name 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_name                              string      UATCDB

SQL> shu immediate

ORA-01507: database not mounted

ORACLE instance shut down.

Step 5. Create a new parameter file from old parameter file with a new db name

[logminedb@ccuine1126 ~]$ cd $ORACLE_HOME/dbs

SQL> create pfile from spfile;

[logminedb@ccuine1126 dbs]$ mv initLOGCDB.ora initUATCDB.ora

[logminedb@ccuine1126 dbs]$ export ORACLE_SID=UATCDB

SQL> startup nomount pfile=’initUATCDB.ora’

ORACLE instance started.

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

SQL> ! ps -ef | grep pmon

logmine+  4671     1  0 17:01 ?        00:00:00 ora_pmon_UATCDB

logmine+  4752  4654  0 17:01 pts/5    00:00:00 /bin/bash -c  ps -ef | grep pmon

logmine+  4754  4752  0 17:01 pts/5    00:00:00 grep pmon

SQL> create spfile from pfile=’initUATCDB.ora’;

SQL> alter database mount; 

Database altered.

SQL> alter database open resetlogs; 

Database altered.

SQL> select name,open_mode,instance_name from v$database,v$instance;

NAME      OPEN_MODE            INSTANCE_NAME

——— ——————– —————-

UATCDB    READ WRITE           UATCDB