Changing database ID (DBID) and database name (DBNAME) using nid utility

posted Sep 16, 2010, 5:33 AM by Sachchida Ojha
The DBNEWID utility(NID) allows the DBID to be altered for the first time and makes changing the DBNAME simpler. Changing the DBID is necessary when you want to use an RMAN catalog to backup a cloned instance. RMAN identifies instances using the DBID, preventing the original and cloned instance being managed by the same catalog. Alteration of the BID in the cloned instance removes this restriction.

Change DB name  (DB1 to DB2) and DBID

Window 1: Connect to the database server

1. Backup the database.

2. $export ORACLE_SID=DB1

3. Mount the database after a clean shutdown.
$sqlplus / as sysdba
SYS@DB1 SQL>shutdown immediate;
SYS@DB1 SQL>startup mount;

Window2:  Open another window (make sure ORACLE_SID is theDB1), and from unix command line, issue:

$nid TARGET=SYS/< passwrod> DBNAME=DB2 LOGFILE=/tmp/db2nid.log

If you get a NID-135 error, then in the sqlplus window (Window 1) issue:
  SYS@DB1 SQL>alter database open; 
(if system.dbf requires recovery, issue 'recover database;' then 'alter database open;')
 SYS@DB1 SQL>shutdown immediate;
 SYS@DB1 SQL>startup mount;

go back to unix window(Window2) and retype nid command

$nid TARGET=SYS/< passwrod> DBNAME=DB2 LOGFILE=/tmp/db2nid.log

Check /tmp/stgratnid.log for errors

it should look like

nid TARGET=sys/password@DB1 DBNAME=DB2
DBNEWID: Release 10.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database DB1 (DBID=1024155118)

Control Files in database:
    C:\ORACLE\ORADATA\DB1\CONTROL01.CTL
    C:\ORACLE\ORADATA\DB1\CONTROL02.CTL
    C:\ORACLE\ORADATA\DB1\CONTROL03.CTL

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

Proceeding with operation
Changing database ID from 1024155118 to 1317279075
Changing database name from DB1 to DB2
    Control File C:\ORACLE\ORADATA\DB1\CONTROL01.CTL - modified
    Control File C:\ORACLE\ORADATA\DB1\CONTROL02.CTL - modified
    Control File C:\ORACLE\ORADATA\DB1\CONTROL03.CTL - modified
    Datafile C:\ORACLE\ORADATA\DB1\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\CWMLITE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\DRSYS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\EXAMPLE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\INDX01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\ODM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\TOOLS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\USERS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\XDB01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\DB1\TEMP01.DBF - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\DB1\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\DB1\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\DB1\CONTROL03.CTL - dbid changed, wrote new name

Database name changed to DB2.
Modify parameter file and generate a new password file before restarting.
Database ID for database DB2 changed to 1317279075.
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.

Window1:



1. logout from SQLPLUS
2. export ORACLE_SID=DB2
3. Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener
3.Edit $HOME/.profile (Change ORACLE_SID=DB1  to ORACLE_SID=DB2)
4.Go to $ORACLE_HOME/dbs
5. Create passwordfile:  orapwd file=orapwDB2 password=new_password entries=10 (optional)
6. Connect to SQLplus as sys user
7.  SYS@DB1 SQL>startup mount
8. SYS@DB1 SQL>alter database open resetlogs;
Rename the SPFILE to match the new DBNAME.
f you are using Windows you must recreate the service so the correct name and parameter file are used:
oradim -delete -sid DB1
oradim -new -sid DB2 -intpwd password -startmode a -pfile c:\oracle\product\10g\database\spfileDB2.ora

9. SYS@DB1 SQL>create spfile from pfile='/u01/MJBDB/app/oracle/admin/DB2/pfile/initDB2.ora';
10. SYS@DB1 SQL>shutdown immediate;
11.  SYS@DB1 SQL>startup

DBNAME Only

Repeat the process as before except use the following command to start the DBNEWID utility:

nid TARGET=sys/password@DB2 DBNAME=DB3 SETNAME=YES

The SETNAME parameter tells the DBNEWID utility to only alter the database name.

When opening the database the RESETLOGS option is not needed so the database can be started using the STARTUP command.


DBID Only

Backup the database.
Mount the database after a clean shutdown:

SHUTDOWN IMMEDIATE
STARTUP MOUNT

Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:

nid TARGET=sys/password@DB3

Shutdown and open the database with RESETLOGS:

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

Backup the database.



Comments