nid utility - ORACLEAGENT BLOG

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