In this article we will see how to completely rename an Oracle database and the four different methods to do it.

Method 1

Complete Renaming an Oracle Database

Database Name: troy

Our aim is to change the database name as Hercules.

Database Rename Steps

1. Login into troy database, Create the controlfile backup as trace file.
   Alter database backup controlfile to trace as /u01/backup/control01.ora;
2. Shutdown the Database.
   Change the init.ora file (like db name, instance name, controlfile path, etc…)
   Note: Taken the complete cold backup. (For safer side)
3. If database running on windows server create the service id using oradim utility.
   Oradim new sid Hercules
   Set oracle_sid=Hercules
4. If database running on Unix based server change the oracle_sid in
   .bash_profile (Db profile)
5. Recreate the password file.
6. Startup the database in nomount stage using editable pfile.
7. Create New Control file
   Using backup control file trace to generate the create control file scripts.
   Change the Database name, Folder name & files path
   Note: Execute the script in sql prompt. Now controlfile created.
   CREATE CONTROLFILE REUSE DATABASE “Hercules” RESETLOGS FORCE LOGGING NOARCHIVELOG
   MAXLOGFILES 50
   MAXLOGMEMBERS 5
   MAXDATAFILES 100
   MAXINSTANCES 1
   MAXLOGHISTORY 453
   LOGFILE
   GROUP 1 ‘/U01/oradata/Hercules/redo01.log’ SIZE 200M,
   GROUP 2 ‘/U01/oradata/Hercules/redo02.log’ SIZE 200M,
   GROUP 3 ‘/U01/oradata/Hercules/redo03.log’ SIZE 200M
   DATAFILE
   ‘/U01/oradata/Hercules/system01.dbf’,
   ‘/U01/oradata/Hercules/undotbs01.dbf’,
   ‘/U01/oradata/Hercules/users01.dbf’,
   CHARACTER SET WE8ISO8859P1;
   Note: ( below one is optional otherwise you only change the database name not change a directory path)
   Change the directory of datafiles & logfiles path.
   create new directory Hercules in ORADATA. Move all the datafiles / logfiles to /U01/oradata/Hercules
   /U01/oradata/Troy/ Rename as /U01/oradata/Hercules/
8. Now open the database.
   Alter database open resetlogs;
9. Change the Tnsentry, Listener, sqlnet.ora files.
10. Taken the complete cold backup

*************************************************************************

Method 2

Change the Oracle Database Name & DBID

Database Name: troy

Our aim is to change the database name as Hercules.

Database & DBID Rename Steps

1. Select dbid, name from v$database
2. Shutdown the Database.
3. Taken the complete cold backup. (Safer side)
4. If database running on windows server, create the service id using oradim utility.
5. If database running on UNIX based machine, change the oracle_sid name in .bash_profile (profile)
6. Create new password file using new database name.
7. Change the init parameter (dbname, instance name)
8. Startup the database in MOUNT stage using new init parameter.
9. Using below statement to change the database name & dbid.
   $ NID TARGET=sys/pwd@troy DBNAME=HERCULES
   Enter the Y in below prompt.
   C:Documents and Settingsraja>nid target=sys/troy@troy dbname=hercules
   DBNEWID: Release 10.2.0.1.0 – Production on Wed Feb 18 08:58:22 2009
   Copyright (c) 1982, 2005, Oracle. All rights reserved.
   Connected to database TROY (DBID=399919941)
   Connected to server version 10.2.0
   Control Files in database:
   E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL01.CTL
   E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL02.CTL
   E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL03.CTL
   Change database ID and database name TROY to HERCULES? (Y/[N]) => y
   Proceeding with operation
   Changing database ID from 399919941 to 1035501295
   Changing database name from TROY to HERCULES
   Control File E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL01.CTL – modified
   Control File E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL02.CTL – modified
   Control File E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL03.CTL – modified
   Datafile E:ORACLEPRODUCT10.2.0ORADATATROYSYSTEM01.DBF – dbid
   changed, wrote new name
   Datafile E:ORACLEPRODUCT10.2.0ORADATATROYUNDOTBS01.DBF – dbid
   changed, wrote new name
   Datafile E:ORACLEPRODUCT10.2.0ORADATATROYSYSAUX01.DBF – dbid
   changed, wrote new name
   Datafile E:ORACLEPRODUCT10.2.0ORADATATROYUSERS01.DBF – dbid
   changed, wrote new name
   Control File E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL01.CTL – dbid
   changed, wrote new name
   Control File E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL02.CTL – dbid
   changed, wrote new name
   Control File E:ORACLEPRODUCT10.2.0ORADATATROYCONTROL03.CTL – dbid
   changed, wrote new name
   Instance shut down
   Database name changed to HERCULES.
   Modify parameter file and generate a new password file before restarting.
   Database ID for database HERCULES changed to 1035501295.
   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.
   Succesfully changed database name and ID.
   DBNEWID – Completed succesfully.
10. No need to Shutdown the database.( automatically disconnected ORA-03113: end-of-file on communication channel)
11. Change the Sid name in listener & tnsnames.ora files. Reload the listener.
12. Startup Mount using edited pfile.
    Startup mount;
    Alter database open resetlogs;
13. Select dbid, name from v$database;—>ensure the db name & dbid.
14. Shutdown the database.
15. Backup the database.

***************************************************************************

Method 3

Change the Oracle Database Name only

Database Name: troy

Our aim is to change the database name as Hercules.

Database Rename Steps

1. Select dbid, name from v$database
2. Shutdown the Database.
3. Taken the complete cold backup. (Safer side)
4. If database running on windows server, create the service id using oradim utility.
5. If database running on UNIX based machine, change the oracle_sid name in .bash_profile (profile)
6. Create new password file using new database name.
7. Change the init parameter (dbname, instance name)
8. Startup the database in MOUNT stage using new init parameter.
9. Using below statement to change the database name only
   $ NID TARGET=sys/pwd@troy DBNAME=HERCULES SETNAME=YES
   Enter the Y in below prompt.
   Change database ID and database name troy to Hercules? (Y/[N]) => Y
10. No need to Shutdown the database.( automatically disconnected ORA-03113: end-of-file on communication channel)
11. Change the Sid name in listener & tnsnames.ora files. Reload the listener.
12. Startup the database using edited pfile.
    Note: No need to open the database using RESETLOGS option.
13. Select dbid, name from v$database – ensure the db name.
14. Shutdown the database
15. Backup the database.

*************************************************************************

Method 4

Change the Oracle DBID only

Database Name: troy

Our aim is to change the database name as Hercules.

DBID Rename Steps

1. Select dbid, name from v$database
2. Shutdown the Database.
3. Taken the complete cold backup. (Safer side)
4. Startup the database in MOUNT stage using new init parameter.
5. Using below statement to change the dbid only
   $ NID TARGET=sys/pwd@troy
   Enter the Y in below prompt.
   Change database ID and database name troy to Hercules? (Y/[N]) => Y
6. No need to Shutdown the database.( automatically disconnected ORA-03113: end-of-file on communication channel)
7. Startup Mount using edited pfile
   • Startup mount;
   • Alter database open resetlogs;
8. Select dbid, name from v$database –>ensure the dbid.
9. Shutdown the database
10. Backup the database.

I Hope this article helped you to understand the Rename of oracle database.Suggestions are welcome.