NID Utility in Oracle

DBNEWID (NID) Utility in Oracle

NID utility was introduced in Oracle 10g
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 the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.

Parameters of NID utility (same parameters in Oracle 10g & Oracle 11g):
$ nid help=y

Keyword	Description
TARGET	Username/Password
DBNAME	New database name
LOGFILE	Output log
REVERT	Revert failed change (YES/NO)?
SETNAME	Set name only (YES/NO)?
APPEND	Append to output log (YES/NO)?
HELP	Displays help messages (YES/NO)?

Changing DBNAME & DBID
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
1744662402 SFM

Backup the database.
Mount the database after a clean shutdown.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

Invoke the DBNEWID (NID) utility specifying the DBNAME from the command line using a user with SYSDBA privilege.
$ nid TARGET=sys/password DBNAME=new_name LOGFILE=change_dbname.log

The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID in all datafiles and then exits. The database is left mounted but is not yet usable.

This utility won’t change the database name in pfile, so change the database name (DB_NAME) in pfile manually and create password file (if necessary).
Mount the database
SQL> STARTUP MOUNT
Open the database in RESETLOGS mode and resume normal use
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
1748860243 SFM_DEV

Make a new database backup. Because you had reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.


Changing only DBNAME
Invoke the utility on the command line; you must specify both the DBNAME and SETNAME parameters.

$ nid TARGET=SYS/password DBNAME=newname SETNAME=YES
DBNEWID performs validations in the headers of the control files (not the datafiles) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, and exits. After DBNEWID completes successfully, the database is left mounted but is not yet usable.

If operation is successful, start the database after updating the init file.

If operation is failed, to revert changes, run the DBNEWID utility again, specifying the REVERT keyword.
$ nid TARGET=SYS/password REVERT=YES LOGFILE=backout.log

Changing only DBID
Invoke the utility on the command line; do not specify DBNAME.
$ nid TARGET=SYS/password

If operation is successful, mount the database and open with resetlogs.

If operation is failed, to revert changes, run the DBNEWID utility again, specifying the REVERT keyword.
$ nid TARGET=SYS/password REVERT=YES LOGFILE=backout.log

Other Oracle Articles:  RMAN (Recovery Manager)      Recycle bin in Oracle