NID Utility in Oracle

DBNEWID is a database utility, in $ORACLE_HOME/bin directory, that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. Prior to the introduction of the DBNEWID utility, we used to manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, alteration of the internal database identifier (DBID) of an instance was impossible.

The DBID is an internal, unique identifier for a database. Because Recovery Manager(RMAN)  distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem.

NID utility allows us to change

  • Only DBID of a database
  • Only DBNAME of a database
  • Both DBNAME and DBID of a database

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1. Consequently, you should make a backup of the whole database immediately after changing the DBID.

Changing DBNAME & DBID

SQL> select dbid, name from v$database;

   DBID          NAME
----------    ---------
 247698686    TRAINING

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>  STARTUP MOUNT
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production
Version 19.3.0.0.0

[oracle@training ~]$ nid TARGET=sqlplus / as sysdba DBNAME=testdb LOGFILE=testdb.log
Password:

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'TESTDB' in control file is not 'TRAINING'


[oracle@training ~]$ cd $ORACLE_HOME/dbs
[oracle@training ~]$ vi inittraining1.ora

training1.__data_transfer_cache_size=0
training1.__db_cache_size=1728053248
training1.__inmemory_ext_roarea=0
training1.__inmemory_ext_rwarea=0
training1.__java_pool_size=0
training1.__large_pool_size=16777216
training1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
training1.__pga_aggregate_target=822083584
training1.__sga_target=2432696320
training1.__shared_io_pool_size=117440512
training1.__shared_pool_size=520093696
training1.__streams_pool_size=0
training1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/training1/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TRAINING1/control01,dbf','
/u01/app/oracle/oradata/TRAINING1/control02.dbf'
*.db_16k_cache_size=33554432
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='testdb'
*.db_recovery_file_dest_size=8256m
*.db_recovery_file_dest='/u01/ARC_BKP'
*.db_unique_name='training1'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=training1XDB)'
*.open_cursors=300
*.pga_aggregate_target=771m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2312m
*.undo_tablespace='UNDOTBS1'

[oracle@training dbs]$ . oraenv
ORACLE_SID = [training] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 02:57:59 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile=$ORACLE_HOME/dbs/inittraining.ora
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.
 ‌

SQL> select dbid,name from v$database;
 
      DBID         NAME
----------       ---------
2862448691        TESTDB

SQL> select status,instance_name from v$instance;

STATUS       INSTANCE_NAME
------------ ----------------
OPEN            training


SQL> create spfile from pfile;

File created.

SQL> show parameter spfile;

NAME             TYPE        VALUE
------------- ----------- ------------------------------
spfile         string      /u01/app/oracle/product/19.0.0
                           /dbhome_1/dbs/spfiletraining.ora

Change the only DBNAME

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production Version 19.3.0.0.0
[oracle@training ~]$  nid target=sqlplus / as sysdba  dbname=training setname=yes

DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 21:48:14 2021

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

Password:
Connected to database TESTDB (DBID=2862448691)

Connected to server version 19.3.0

Control Files in database:
    
    /u01/app/oracle/oradata/TRAINING1/control01.dbf
    /u01/app/oracle/oradata/TRAINING1/control02.dbf

Change database name of database TESTDB to TRAINING? (Y/[N]) => y

Proceeding with operation
Changing database name from TESTDB to TRAINING
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf -modified
Control  File/u01/app/oracle/oradata/TRAINING1/control02.dbf  - modified

Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - wrote new name

Datafile /u01/app/oracle/oradata/TRAINING1/sysaux.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf - wrote new name

Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf  - wrote new name

Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp  - wrote new name

Instance shut down

Database name changed to TRAINING.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.

DBNEWID - Completed succesfully.


[oracle@training ~]$ cd $ORACLE_HOME/dbs

[oracle@training dbs]$ vi inittraining1.ora

training1.__data_transfer_cache_size=0
training1.__db_cache_size=1728053248
training1.__inmemory_ext_roarea=0
training1.__inmemory_ext_rwarea=0
training1.__java_pool_size=0
training1.__large_pool_size=16777216
training1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
training1.__pga_aggregate_target=822083584
training1.__sga_target=2432696320
training1.__shared_io_pool_size=117440512
training1.__shared_pool_size=520093696
training1.__streams_pool_size=0
training1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/training1/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TRAINING1/control01,dbf','
/u01/app/oracle/oradata/TRAINING1/control02.dbf'
*.db_16k_cache_size=33554432
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='training'
*.db_recovery_file_dest_size=8256m
*.db_recovery_file_dest='/u01/ARC_BKP'
*.db_unique_name='training1'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=training1XDB)'
*.open_cursors=300
*.pga_aggregate_target=771m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2312m
*.undo_tablespace='UNDOTBS1'


[oracle@training dbs]$ . oraenv
ORACLE_SID = [training1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 21:52:50 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/inittraining1.ora'
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size               8899432 bytes
Variable Size            536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers             7876608 bytes
Database mounted.
Database opened.

SQL> select name from v$database;

NAME
---------
TRAINING

SQL> select status,instance_name from v$instance;

STATUS       INSTANCE_NAME
--------- ----------------
OPEN         training1

Change only DBID

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production Version 19.3.0.0.0

[oracle@training dbs]$ nid target=sqlplus / as sysdba

DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 22:58:56 2021

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

Password:
Connected to database TRAINING (DBID=2862448691)

Connected to server version 19.3.0

Control Files in database:
   
    /u01/app/oracle/oradata/TRAINING1/control01.dbf
    /u01/app/oracle/oradata/TRAINING1/control02.dbf

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

Proceeding with operation
Changing database ID from 2862448691 to 254963316
    
    Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - modified
    Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - modified
    Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - dbid changed

    Datafile /u01/app/oracle/oradata/TRAINING1/sysaux..dbf - dbid changed
    Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf  - dbid changed
    
Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/TRAINING1/data01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp - dbid changed
    
    Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - dbid changed
    Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - dbid changed
    Instance shut down

Database ID for database TRAINING changed to 254963316.
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 successfully.

[oracle@training dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 22:59:46 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME            DBID
---------   ----------
TRAINING     254963316

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to change the db name using nid utility

Introduction to Nid Utility:

NID is an utility which is used to change the database internal name and dbname of a database. The utility is placed in the $ORACLE_HOME/bin directory. Prior to the nid utility we need to manually create a database and give it a new database name by recreating a control file.

Here I am going to change the db name from standby to Canada.

Overview steps:

Step:1 Mount the database with old db name(standby)

Step:2 Run the nid utility (syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME)

Step:3 Once you run the nid utility the name will be changed to new db name.(standby to Canada)

Step:4 Then you have to change the db_name in the parameter file.

Step:5 change the spfile to a new db name.

Step:6 Now open the database with reset logs option.

Step:1 mount the database with old db name.

Step:2 Run the nid utility

Syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

Step:3 change the db_name in the parameter file.

Step:4 Rename the spfile to a new db name.

cp spfileSTANDBY.ora spfileCANADA.ora

Step:5 Open the database with resetlogs option.

Step:6 Register the database information to the listener using the command  “alter system register;”

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8