Changing an Oracle Database Name in Oracle 10g

As per the requirement for our development team, we needed to perform a fresh installation of Oracle 10g and Creation of a database on Solaris 10, and import the production data. During the database creation, we had to use the same scrip what we used for Production Database Creation. After the database creation of database for development environment we realized that the Database name is also same as Production Database. So, we had used the oracle DBNEWID utility to change the Database Name to avoid the confusion between Prod DB and Dev DB.

Below is the snapshot the task.

Make sure that you have a whole database backup.

Note: In our case, we had not taken the backup as the database was created recently and we had an export dump of a production DB.

Ensure to perform clean shut down of a database and startup in mount stage (but not open).

SQL> SHUDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

$ nid TARGET=SYS/XXXX@unc DBNAME=uncdev SETNAME=YES

DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.

DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007

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

Connected to database UNIC (DBID=3680129277)

Connected to server version 10.2.0

Control Files in atabase:

/s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl

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

Proceeding with operation
Changing database ID from 3680129277 to 975738954
Changing database name from UNIC to UNCDEV Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - modified Datafile /s/oracle10g/product/10.2.0/oradata/system01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/undotbs01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/sysaux01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/users01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/temp01.dbf - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - dbid changed, wrote new name

Instance shut down
Database name changed to UNCDEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database UNCDEV changed to 975738954.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID - Completed successfully.

Connect to SQL* Plus and shutdown the database

SQL> SHUDOWN IMMEDIATE;

Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.

SQL> STARTUP MOUNT;

SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;

SQL> SHUDOWN IMMEDIATE;

Create a new password file

On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdUNCDEV.ora password=xxxxxxx entries=10

On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/uncdev/dbs/ pwdUNCDEV.ora password=xxxxxxx entries=10

We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and not the database ID, it is not necessary to use the RESETLOGS option when you open the database

SQL> STARTUP;

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string UNCDEV

Backup the whole database again

Conclusion: The above series of steps are only for changing the database name, if you wanna change Database ID, or Database ID and Database Name both, please refer the below URLs.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm
http://www.oracle-base.com/articles/9i/DBNEWID.php

Regards,
Sabdar Syed.