How to change the DBNAME /DBID using DBNEWID(nid) utility for Oracle database

Last updated on March 30th, 2019 at 09:21 am

The DBNEWID(NID) Utility is a new utility introduced with Oracle database. The NID utility allows you to change only the DBNAME, or only the DBID or both DBNAME and DBID in the same command.

Change Only the DBID

  1. Backup Database
rman target /
backup database;
exit
  1. shutdown immediate
sqlplus / as sysdba

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
  1. startup mount
sqlplus / as sysdba

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 4764747646 bytes
Fixed Size                  2253584 bytes
Variable Size             457873600 bytes
Database Buffers          343860800 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> exit
  1. Open one session and run NID utility(DBNEWID utility) with sysdba privileges  to change the DB ID.
nid TARGET=SYS/password@exptest

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Password:
Connected to database EXPTEST (DBID=587585)
 
Connected to server version 11.2.0
 
Control Files in database:
    +DATA/EXPTEST/control01.ctl
    +FLASH/EXPTEST/control02.ctl
   
 
Change database ID ? (Y/[N]) => Y
 
Proceeding with operation
Changing database ID from 587585 to 587585534
 

Database ID for database EXPTEST changed to 587585534.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database  ID.
DBNEWID - Completed successfully.
  1. Startup of the database with open resetlogs
sqlplus / as sysdba

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 4764747646 bytes
Fixed Size                  2253584 bytes
Variable Size             457873600 bytes
Database Buffers          343860800 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> alter database open resetlogs;

 

Change Only the DBNAME

  1. Backup Database
rman target /
backup database;
exit
  1. shutdown immediate
sqlplus / as sysdba

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
  1. startup mount
sqlplus / as sysdba

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 4764747646 bytes
Fixed Size                  2253584 bytes
Variable Size             457873600 bytes
Database Buffers          343860800 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> exit
  1. Open one session and run NID with sysdba privileges
nid TARGET=SYS/password@test_db DBNAME=exptest_db2 SETNAME=Y
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Password:
Connected to database EXPTEST (DBID=587585)
 
Connected to server version 11.2.0
 
Control Files in database:
    +DATA/EXPTEST/control01.ctl
    +FLASH/EXPTEST/control02.ctl
   
 
Change database name ? (Y/[N]) => Y
 
Proceeding with operation
Database name changed to EXPTEST_DB2.
 
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.
DBNEWID - Completed succesfully.
  • the value of DBNAME is the new db-name of the database
  • SETNAME must be set to Y. The default is N and causes the DBID to be changed also.
  1. Set the DB_NAME initialization parameter in the initialization parameter
    file to the new database name
  2. Create a new password file using orapwd
  3. Startup of the database(with resetlogs)
sqlplus / as sysdba

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 4764747646 bytes
Fixed Size                  2253584 bytes
Variable Size             457873600 bytes
Database Buffers          343860800 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> alter database open resetlogs;

Change Both DBID and DBNAME

  1. Backup Database
rman target /
backup database;
exit
  1. shutdown immediate
sqlplus / as sysdba

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
  1. startup mount
sqlplus / as sysdba

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 4764747646 bytes
Fixed Size                  2253584 bytes
Variable Size             457873600 bytes
Database Buffers          343860800 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> exit
  1. Open one session and run NID with sysdba privileges
nid TARGET=SYS/password@test_db DBNAME=exptest_db2

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

Password:
Connected to database EXPTEST (DBID=587585)

Connected to server version 11.2.0

Control Files in database:
+DATA/EXPTEST/control01.ctl
+FLASH/EXPTEST/control02.ctl


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

Proceeding with operation
Database name changed to EXPTEST_DB2.
Modify parameter file and generate a new password file before restarting.
Database ID for database EXPTEST_DB2 changed to 587585534.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID - Completed successfully.
  • the value of DBNAME is the new dbname of the database
  1. After DBNEWID successfully changes the DBID,shutdown immediate
  2. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.
  3. Create a new password file with orapwd
  4. Startup of the database with open resetlogs
sqlplus / as sysdba

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 4764747646 bytes
Fixed Size                  2253584 bytes
Variable Size             457873600 bytes
Database Buffers          343860800 bytes
Redo Buffers                7471104 bytes
Database mounted.
SQL> alter database open resetlogs;

Leave a Reply