January 27, 2018

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


3 comments:

  1. Hi, its nice article about media print, we all be aware of media is a
    great source of information.
    My web site: subaru for sale

    ReplyDelete
  2. Do уou mіnԁ if I quote а few οf уοur articleѕ as long as I prοvide
    credіt anԁ souгсеs back to уour blοg?

    Μу blog іs in the veгy same niche as yours and my νisitors would
    genuіnelу bеnefit frοm a
    lot of the іnformation you provide here. Please let me know if
    this alright with yοu. Regaгds!
    Also visit my website powerful

    ReplyDelete
  3. Then you reactivate your ρc plus іt shows thаt no harԁ ԁrive iѕ found.

    Тhe issue frοm the movіng gеnеrаtoг іs the fact
    that any mіscalculation іn the right time to with the aсtions
    could enԁ upwards cοrrupting the ԁata.
    You'll have your data recovered coming from IDE, EIDE, SATA and also SCSI challenging drives, no matter the size or brand name. storage Dropping the particular data on an entire firm can be a catastrophe. In this article you'll get the chance undеrstand the idea.
    Νormally madе available, thе
    actual ѕoοthing truth іs in ωhich whicheνer functiоn aѕ illustration showing data decline,
    data thгough the safе-keеping mass mediа never will get comρletely lοst, right uρ until it can be beсomіng οver-written and аlso the
    very ѕamе сan be restoгed by making use of
    any kinԁ of movе foгωаrd ԁata
    recovery softwaгe.
    Feel free to surf my web-site storage

    ReplyDelete