When was the last time you had to change the name of a database? Long time ago, changing the database name was a big thing. Nowadays not so much anymore! In the case of a database refresh, RMAN takes care of that for you, during the cloning process.

 

But, I am not referring to changing database name for a refresh purpose. What I am referring to in my question today, is actually changing the name of a database, let’s say from HRPROD to HRPRD, on purpose, and not through refresh or clone.

Now you might be asking me, but Diana, why would you do that?

 

I’ll let you know, why I did it, just keep on reading!

 

I was upgrading a database to 19c. The method I was using was to create an empty 19c database, export the required schemas from the older version, and import into the newly created database.

The only issue was that the new database and the old one were on the same server, and having a different database name was out of the question. This is why, I ended up changing the database name, as the last step of the “upgrade”. I will not get into details of the upgrade, that could be the theme for another post!

 

Let’s get back to our topic for today, changing the database name.

 

The utility you would use to accomplish this task, is the DBNEWID utility, which is available to you since 9i. With this utility you can change the DBID and/or the DBNAME. This post will focus on changing the DBNAME.

 

The prerequisite for the name change is to have a good backup of the database, meaning you can recover from the backup if needed. This prerequisite is not mandatory, you can still change the name of the database without a backup, but remember a Good DBA always has a backup plan. Another good practice at this point is, to create a pfile from the spfile. You will need the pfile later on, in Step 3 below.

 

Step 1) Restart the database in mount, but making sure the database was shutdown consistently before:

 

shutdown immediate;
startup mount;
exit

 

Step 2) On the command line invoke the DBNEWID utility, as a user with SYSDBA privileges (best is to use SYS). You will be prompted for SYS’ password. You also need to specify the new name for the database.
In this example, we are changing the name to HRPRD.

 

nid TARGET=SYS DBNAME=HRPRD SETNAME=YES

 

At this point, the utility performs validations in the header of the control files only. If these validations are successful, then you will be prompted for confirmation.
The db name is changed in the control file, the database is shutdown and the utility exits.

 

Step 3) Manually change the DB_NAME parameter in the init.ora file created at the beginning. Startup nomount the database with the updated pfile. Create an spfile from the pfile, and shutdown the database.

 

startup nomount pfile='/home/oracle/initHRPRD.ora';
create spfile from pfile='/home/oracle/initHRPRD.ora';
shutdown immediate;

 

Step 4) Create a new password file with the orapwd utility. If you had other users added to the password file before, you will need to re-add these users after you re-create the password file.

 

Step 5) Set the ORACLE_SID parameter in your environment to the new value:

 

export ORACLE_SID=HRPRD

 

Step 6) Startup the database. Because you only changed the database name, no RESETLOGS is required at startup, and all previous backups are usable.

 

startup;

 

Step 7) Update the /etc/oratab file with the new database name. If you are on Windows the Oracle Service will have to be recreated with the oradim utility.

You are done, the database name is changed.