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.
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
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
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.
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 failed, to revert changes, run the DBNEWID utility again, specifying the REVERT keyword.
$ nid TARGET=SYS/password REVERT=YES LOGFILE=backout.log
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.
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
Refrence by:-Sachin DBA
No comments:
Post a Comment