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

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

The DBNEWID(NID) Utility is a new utility introduced with Oracle database. The NID Utility in oracle allows you to change only the DBNAME, or only the DBID or both DBNAME and DBID in the same command.Lets discuss each of these option in detail and how to implement

NID Utility in oracle

Change Only the DBID using NID Utility

Here we will be only changing the DBID of the Oracle database.

(1) Backup Database

rman target /
backup database;
exit

(2) shutdown immediate

sqlplus / as sysdba

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

(3) 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

(4) 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.

(5) 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

Here we will be only changing the DBNAME of the Oracle database.

See also  How to clone the database using manual cold backup

(1) Backup Database

rman target /
backup database;
exit

(2) shutdown immediate

sqlplus / as sysdba

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

(3) 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

(4) 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.

(5) Set the DB_NAME initialization parameter in the initialization parameter
file to the new database name

(6) Create password file in oracle using orapwd

(7) 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

Here we will be changing both the DBID and DBNAME of the Oracle database.

(1) Backup Database

rman target /
backup database;
exit

(2) shutdown immediate

sqlplus / as sysdba

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

(3) 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

(4) 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
See also  Move SQL Profiles from One Database to Another in Oracle

(5) After DBNEWID successfully changes the DBID,shutdown immediate

(6) Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.

(7) Create a new password file with orapwd

(8) 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;

I hope you like this content on NID Utility in oracle. I have provided the step by step implementation for each of the path.

Also Reads
Oracle Database Administration Tutorials
https://docs.oracle.com/cd/E11882_01/server.112/e22490/dbnewid.htm#SUTIL014

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top