How to Change the DBID and the DBNAME by using NID

The NID (New Database ID)is a new utility introduced with Oracle 9.2.  The NID
utility allows you to change only the DBNAME, or only the DBID or both
DBNAME and DBID in the same command.
 
  1. If 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.
 
  2. If you change the DBNAME without changing the DBID then this does not
     require you to open with the RESETLOGS option, so database backups and
     archived logs are not invalidated.  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.

Change Only the DBID
====================
 
  1. Backup the database
  2. SHUTDOWN IMMEDIATE of the database
  3. STARTUP MOUNT
  4. Open one session and run NID with sysdba privileges
     % nid TARGET=SYS/(password)@(Connect string of TEST1BY)
  5. Shutdown IMMEDIATE of the database
  6. Set the DB_NAME initialization parameter in the initialization parameter
     file to the new database name
  7. Create a new password file
  8. Startup of the database with open resetlogs

Example:
========
 
1. C:\>set ORACLE_SID=TEST1BY
   C:\>sqlplus "/as sysdba"
   SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 24 11:16:52 2002
   Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
   Connected to an idle instance.
 
   SQL> startup pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
   ORACLE instance started.
 
   Total System Global Area  135338868 bytes
   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.
   Database opened.
 
2. check the DBID before change
 
   SQL> select dbid,name,open_mode,activation#,created from v$database;
 
         DBID NAME      OPEN_MODE  ACTIVATION# CREATED
   ---------- --------- ---------- ----------- ---------
   1395399949 TEST1BY   READ WRITE  1395404134 10-SEP-02
 
 
3. SQL> shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
 
4. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
   ORACLE instance started.
 
   Total System Global Area  135338868 bytes
   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.
   SQL>exit
 
5. execute NID
 
   C:\>nid target=sys/(password)@(Connect string of TEST1BY)
 
   DBNEWID: Release 9.2.0.1.0 - Production
   Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
 
   Connected to database TEST1BY (DBID=1395399949)
 
   Control Files in database:
     D:\ORACLE\TEST1BYCONTROL01.CTL
 
   Change database ID of database TEST1BY? (Y/[N]) => y
 
   Proceeding with operation
   Changing database ID from 1395399949 to 1397190693
   Control File D:\ORACLE\TEST1BYCONTROL01.CTL - modified
   Datafile D:\ORACLE\TEST1BY\SYSTEM01.DBF - dbid changed
   Datafile D:\ORACLE\TEST1BY\UNDOTBS01.DBF - dbid changed
   Datafile D:\ORACLE\TEST1BY\DRSYS01.DBF - dbid changed
   Datafile D:\ORACLE\TEST1BY\EXAMPLE01.DBF - dbid changed
   Datafile D:\ORACLE\TEST1BY\INDX01.DBF - dbid changed
   Datafile D:\ORACLE\TEST1BY\TOOLS01.DBF - dbid changed
   Datafile D:\ORACLE\TEST1BY\USERS01.DBF - dbid changed
   Datafile D:\ORACLE\TEST1BY\XDB01.DBF - dbid changed
   Control File D:\ORACLE\TEST1BYCONTROL01.CTL - dbid changed
 
   Database ID for database TEST1BY changed to 1397190693.
   All previous backups and archived redo logs for this database are unusable.
   Shut down database and open with RESETLOGS option.
   Succesfully changed database ID.
   DBNEWID - Completed succesfully.
 
6. SQL> shutdown immediate;
   ORA-01109: database not open
   Database dismounted.
   ORACLE instance shut down.
 
7. create the new passwordfile
 
8. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
   ORACLE instance started.
 
   Total System Global Area  135338868 bytes
   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.
 
9. SQL> alter database open resetlogs;
   Database altered.
 
10. check the new DBID
 
    SQL> select dbid,name,open_mode,activation#,created from v$database;
 
          DBID NAME      OPEN_MODE  ACTIVATION# CREATED
    ---------- --------- ---------- ----------- ---------
    1397190693 TEST1BY   READ WRITE  1397188261 10-SEP-02
 
 
The NID change the OLD DBID 1395399949 to the NEW DBID 1397190693

Change Only the DBNAME
======================
 
  1. Backup the database
  2. SHUTDOWN IMMEDIATE of the database
  3. STARTUP MOUNT
  4. Open one session and run NID with sysdba privileges
     % nid TARGET=SYS/(password)@(Connect string of TEST1BY) DBNAME=test_db2 SETNAME=Y
     - the value of DBNAME is the new dbname of the database
     - SETNAME must be set to Y. The default is N and causes the
       DBID to be changed also.
  5. shutdown IMMEDIATE of the database
  6. Set the DB_NAME initialization parameter in the initialization parameter
     file to the new database name
  7. Create a new password file
  8. Startup of the database(without resetlogs)

Change Both DBID and DBNAME
===========================
   1. Backup of the database.
  2. Shutdown IMMEDIATE of the database
  3. STARTUP MOUNT
  4. Open one session and run NID with sysdba privileges
     % nid TARGET=SYS/(password)@(Connect string of TEST1BY) DBNAME=test_db2
     - the value of DBNAME is the new dbname of the database
  5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the database
  6. Set the DB_NAME initialization parameter in the
     initialization parameter file to the new database name.
  7. Create a new password file.
  8. Startup of the database with open resetlogs

Example:
========
 
1. Shutdown the database
 
   SQL> shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
 
2. Mount the database
 
   SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora
   ORACLE instance started.
 
   Total System Global Area  135338868 bytes
   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.
   startup mount
 
3. Run NID utility
 
   C:\>nid target=sys/oracle@(Connect string of TEST1BY) dbname=test_db2 logfile=d:\oracle\nid.log
 
   DBNEWID: Release 9.2.0.1.0 - Production
   Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
 
   Connected to database TEST1BY (DBID=849281895)
 
   Control Files in database:
     D:\ORACLE\TEST1BYCONTROL01.CTL
 
   Changing database ID from 849281895 to 1395399949
   Changing database name from TEST1 to TEST1BY
   Control File D:\ORACLE\TEST1BYCONTROL01.CTL - modified
   Datafile D:\ORACLE\TEST1BY\SYSTEM01.DBF - dbid changed, wrote new name
   Datafile D:\ORACLE\TEST1BY\UNDOTBS01.DBF - dbid changed, wrote new name
   Datafile D:\ORACLE\TEST1BY\DRSYS01.DBF - dbid changed, wrote new name
   Datafile D:\ORACLE\TEST1BY\EXAMPLE01.DBF - dbid changed, wrote new name
   Datafile D:\ORACLE\TEST1BY\INDX01.DBF - dbid changed, wrote new name
   Datafile D:\ORACLE\TEST1BY\TOOLS01.DBF - dbid changed, wrote new name
   Datafile D:\ORACLE\TEST1BY\USERS01.DBF - dbid changed, wrote new name
   Datafile D:\ORACLE\TEST1BY\XDB01.DBF - dbid changed, wrote new name
   Control File D:\ORACLE\TEST1BYCONTROL01.CTL - dbid changed, wrote new name
 
   Database name changed to test_db2 .
   Modify parameter file and generate a new password file before restarting.
   Database ID for database TEST1BY changed to 1395399949.
   All previous backups and archived redo logs for this database are unusable.
   Shut down database and open with RESETLOGS option.
   Succesfully changed database name and ID.
   DBNEWID - Completed succesfully.
 
4. Shutdown immediate of the database
 
5. Modfiy the file init.ora of the database
   change the parameter  DB_NAME=TEST1BY to DB_NAME=TEST_DB2
 
6. create a new password file
 
   Usage: orapwd file=<fname> password=<password> entries=<users>
   where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and OPERs (opt),
  
7. mount the database
 
   SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora
   ORACLE instance started.
 
   Total System Global Area  135338868 bytes
   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.
  Show parameter db_name
 
 
8. Open the database with RESETLOGS
   SQL> alter database open resetlogs;
   Database altered.
 
9. check the dictionary view for the new DBID and DBNAME
   select dbid, name, open_mode, activation#, created from v$database;