NID Utility in Oracle
DBNEWID (NID) Utility
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 
1744662402 FREE end_of_the_skype_highlighting 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 data files
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
data files 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 1748860243 FREE end_of_the_skype_highlighting 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
