How to Change Oracle DBNAME and DBID

When you change DBID of the database all previous backups will become UNUSABLE and you must open the database with RESETLOGS option. Once you change the DBID make sure you take database backup immediately.

If you only change DB_NAME then old backups are still USABLE and you NO need to open the database with RESETLOGS options. But you need to update the database name in initialization parameter file and need to re-create password file.

Follow the steps in Changing the DB_NAME and DBID:
1. Take backup of the Database

2. Drop the DB Console, if any.
For DB Control 10.1.x
    $ ORACLE_HOME/bin/emca -x <sid>
For DB Control 10.2.x and 11.x
    $ORACLE_HOME/bin/emca -deconfig dbcontrol db

3. Shutdown and Start the database in MOUNT stage
SQL> STARTUP MOUNT;

4. Use NID utility to change the Database Name and Database ID
$ nid TARGET=sys/password@alias as sysdba DBNAME=NEW_DBNAME
Or
$ nid TARGET=sys/password as sysdba DBNAME=NEW_DBNAME

The nid utility performs the validation of the controlfile and datafiles. Once the validation successful it will prompt you to confirm the database name change operation.

Please see the below output (changing DBNAME from ORCL1 to ORCL5)

$ nid TARGET=sys/xxxxxx@ORCL1 as sysdba  DBNAME=ORCL5

DBNEWID: Release 10.2.0.4.0 - Production on Mon Feb 03 10:01:06 2012

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

Connected to database ORCL1 (DBID=5989889872)

Connected to server version 10.2.0

Control Files in database:
   /u01/oradata/ORCL1/TESTDB01.ctl
    /u02/oradata/ORCL1/TESTDB02.ctl
    /u03/oradata/ORCL1/TESTDB03.ctl

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

Proceeding with operation
Changing database ID from 5989889872 to 7984565578

After successfully changing the database name the utility will shutdown the database.

5. Update DB_NAME in initialization parameter file with NEW Database name and rename the parameter file to match NEW Database Name.

6. Mount the database and open with RESETLOGS
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;

7. Recreate the password file using below syntax
$ orapwd file=<fname> password=<password> entries=<users> force=<y/n> nosysdba=<y/n>

8. Change the database name in tnsnames.ora and listener.ora (in case of static)  and reload the listener
$ lsnrctl reload

9. Recreate the DB console, if using
$ emca -config dbcontrol db -repos recreate

10. Backup the Database

For Windows: You must recreate the service so the correct name and parameter file are used
C:\> oradim -delete -sid ORCL1
C:\> oradim -new -sid ORCL5 -intpwd password -startmode auto -pfile c:\oracle\product\10.2.0\dbhome_1\dbs\spfileORCL5.ora

Follow the steps in Changing ONLY DBNAME:
Follow the steps 1-3 in above section

4. Use NID utility to change the DBNAME

$ nid TARGET=SYS/password@alias as sysdba DBNAME=NEW_DBNAME SETNAME=YES
or
$ nid TARGET=SYS/password as sysdba DBNAME=NEW_DBNAME SETNAME=YES

5. Update DB_NAME in initialization parameter file with NEW Database name and rename the parameter file to match new DBNAME

6. Startup the database in normal mode
SQL> STARTUP;

Please note that you no need to open the database with resetlogs option as you only changed database the DBNAME.

Follow the steps 7-10 in above section

Follow the steps in Changing ONLY DBID:

1. Take backup of the Database

2. Shutdown and Start the database in MOUNT stage
SQL> STARTUP MOUNT;

3. Use NID utility to change the DBID

$ nid TARGET=SYS/password@alias as sysdba
or
$ nid TARGET=SYS/password as sysdba

4. Mount the database and open with RESETLOGS
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;

5. Backup the Database

You can refer metalink note “Note.278100.1 How To Drop, Create And Recreate DB Control In A 10g Database”

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com