Changing DBID and DB name  

Posted by Mawahid

1.       First take backup of full database.

2.       Then login as SYSDBA to check out the DB ID and DB Name

select dbid, name from v$database;

3.       Make sure the following things should addressed.

a)      Open_cursors=500 (increase it to some value, later revert back to original)
b)      Crosscheck all the db files are present at OS level i.e, datafiles,tempfiles etc. Make sure none of the tablespace is offline.

4.       Shutdown the database and startup in mount state.

SQL> shutdown immediate
SQL> startup mount

5.       Use the DBNEWID command to change the DB ID and db name, you need to login as user with privilege SYSDBA

$ nid target=username/password@servicename dbname=newdbname

which changes the DBID to a new DBID (You can't set DBID though as your wish). In this case the follow operations are performed is below.
a)      The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files.
b)      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)
c)       Then changes the DBID and the DBNAME for each datafile, including offline normal and read-only datafiles
d)      Shuts down the database, and then exits.
                If you want to change only dbid then
$ nid target=username/password@servicename
                If you want only db name to rename then
                                $ nid target=username/password@servicename dbname=newdbname SETNAME=y
If NID fails, with some errors and for revert,

$ nid target=username/password@servicename REVERT=Y

6.       Shutdown database again.

SQL> shutdown immediate

7.       Change db_name initialization parameter in the initialization file.

8.       Create a new password file using ORAPWD utility with the new SID.

$ orapwd file=orapwSID password=password entries=

9.       Start database in mount state

SQL> startup mount;

10.   Open database with RESETLOGS option

SQL> alter database open resetlogs;

11.   Verify the database id and name using following statement

select dbid, name from v$database;

12.   Don’t forget to change ORACLE_SID parameter in .bash_profile and database name in /etc/oratab
All the previous backup, archivelogs become unusable.

This entry was posted on Monday, January 31, 2011 at 10:05 AM . You can follow any responses to this entry through the comments feed .


This looks very useful..

January 31, 2011 at 7:35 PM

Post a Comment