Oracle - Renaming An Oracle Database

Oracle DBA - Renaming An Oracle Database

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.

Help Us Grow

If you like this post, please share it with your friends.

You are free to copy and redistribute this article in any medium or format, as long as you keep the links in the articles or provide a link back to this page.

Subscribe to our mailing list