Change Database Name and DBID Using NID

NID is a utility to change the database name and database ID. There are many scenario where we need to restore the database backup and we required to change the name of the database. Recreating a controlfile is another option, but nid makes it much more simpler.

You can find this utility in $ORACLE_HOME/bin/nid

The syntax is :

nid TARGET=sys@"connection_entry" DBNAME="NewDBName"

Example:

nid TARGET=sys@ASHISHDB DBNAME=ASHIUAT

===================================================

Below is the example to change the database name:

C:\Users\ashish>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 5 19:38:51 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

---------

ASHISHDB

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\ashish>nid TARGET=sys@ASHISHDB DBNAME=ASHIUAT

DBNEWID: Release 11.1.0.6.0 - Production on Fri Oct 5 19:39:06 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Password:

Connected to database ASHISHDB (DBID=356825129)

Connected to server version 11.1.0

Control Files in database:

    E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL

    E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL

    E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL

Change database ID and database name ASHISHDB to ASHIUAT? (Y/[N]) => Y

Note: If you do not want to change DBID press N.

Proceeding with operation

Changing database ID from 356825129 to 2425101660

Changing database name from ASHISHDB to ASHIUAT

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL - modified

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL - modified

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL - modified

    Datafile E:\DATA\DATAFILE\ASHISHDB\SYSTEM.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\USR01.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\SYSAUX.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\UNDOTBS1.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\DATA01.DBF - dbid changed, wrote new name

    Datafile E:\DATA\DATAFILE\ASHISHDB\TEMP01.DBF - dbid changed, wrote new name

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL - dbid changed, wrote new name

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL - dbid changed, wrote new name

    Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL - dbid changed, wrote new name

    Instance shut down

Database name changed to ASHIUAT.

Modify parameter file and generate a new password file before restarting.

Database ID for database ASHIUAT changed to 2425101660.

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 and ID.

DBNEWID - Completed succesfully.

Now modify the pfile according to new database name:

db_name=ASHIUAT

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1073131520 bytes

Fixed Size                  2121800 bytes

Variable Size             658509752 bytes

Database Buffers          406847488 bytes

Redo Buffers                5652480 bytes

Database mounted.

SQL> select name from v$database;

NAME

---------

ASHIUAT

SQL> alter database open resetlogs;

Database altered.

Thats all. Here are the steps to change the database name and database ID. Dont forget to modify the listener.ora and tnsnames.ora.