Renaming an Oracle Database

| October 6, 2010

Back in the old’n days (pre 9i) renaming an Oracle database meant modifying your control file, manually changing the name within the data dictionairy in multiple places, then finally changing the name in any external configuration files and scripts.  The process has been streamlined a bit since then with the introduction of the Oracle dbnewid utility, nid.  DBNEWID will update your control files as well as all your data files, all in one fell swoop!  This utility can also change your database’s DBID if you like.

DBNEWID Options:

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

Keyword     Description                    (Default)
—————————————————-
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

As you can see above, nid can be used to change the database name, the DBID, or both.  This utility greatly simplifies the name change process and as a bonus you can change your database’s ID without any extra work.

Let’s get started.

1> Backup your database.  No explanation should be necessary here, right?

2> Shutdown the database with immediate or transactional, then mount the database.

3> Take advantage of nid to update the data dictionary.  Be sure to use a database user with SYSDBA privs (SYS perhaps) or you’ll run into issues.

4> Shutdown your database clean again which means a shutdown immediate.

5> Now we need to update the database name in the startup parameter file.  This process differs depending on whether you use an spfile or an init.ora file but it’s easy either way.

If you’re using the ol’ init.ora file you can simply edit the file replacing any references of the old database name with the new.  For the spfile you can do the following:

a> startup mount

b> alter system set db_name=<new DB name> scope=spfile;

c> shutdown immediate

In place of the above I prefer to create an init.ora file from the spfile, edit the init.ora file, then recreate the spfile from the init.ora.  More work?  Well, this gives me a chance to look over all of the startup parameters and do any cleanup that may be necessary all at the same time.  The newly created init.ora also serves as a backup and can be  used for reference purposes if there are other databases on the same server.

6> Rename your startup parameter file.  Whether it be an init.ora or spfile, rename it either way.

7> Time to recreate the password file if you use one.  This is done with another Oracle utility, orapwd.  The first thing to do would be to locate where your existing password file lives as you will need to reference the location for the new file.  On Windows servers the default location will be in %ORACLE_HOME%\database, for *nix look in $ORACLE_HOME/dbs.  The file name should be something like pwd<DBName>.ora.  Now for the syntax of the orapwd utility:

> orapwd FILE=<full path, including file name of your new password file> PASSWORD=<secret> entries=5

If you need more help with the orapwd utility there is plenty available on the ‘net.

8> If you’re on a *nix operating system and you plan on updating the instance name as well, update the oratab file usually located in /etc/oratab.  Don’t forget to rerun oraenv to reset your environment!

If you’re on Windows you’ll need to recreate your Windows Service.  Oracle provides a lovely command line utility for this, oradim.  My experience with oradim is a bit on the side of confusing.  As of Oracle 10g and 11g, even if you follow the syntax exactly provided by Oracle, there are instances where the utility spits out the help file as if you’ve provided an incorrect syntax.  It’s really a pain.  For this reason, if the following does not work for you, play around with it a bit and you’ll figure it out.  It’s really  not that bad, though I wish the bugs were fixed.  Maybe in a future revision.

> oradim -DELETE -SID <old SID>

> oradim -NEW -SID <new SID> -SRVC <new Service name> -SYSPWD <sys password> -STARTMODE auto -SPFILE

Other options can be set as well.  Type oradim at the command prompt to display the syntax help.

9> If you’re updating the instance name as well, update any tnsnames.ora entries as well as any listener.ora entries.  Reload the listener if you’ve made changes.

10> Done!  At this point you should be able to open the database (resetlogs).

Additional Notes:

Some installations store the database’s data files in a directory structure that includes the database name.  This of course further complicates a name change since the above will not move the data files for you.  If this is the case you will need to also move your data files to a new location using “alter database rename file” statements.  The syntax should looks something like this:

alter database rename file ‘/usr/app/oracle/oradata/oldDBName/system01.dbf’ to ‘/usr/app/oracle/oradata/newDBName/system01.dbf’;

You will need to rename all of your data files in this manner, including your temp data files and redo logs.