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