Oracle database’s ORACLE_SID can be renamed using a couple of methods. The first method, which is a tradition method, is to create a new control file using the reuse database flag and give it a new incarnation. Once this is done we can change the values in the external files (sqlnet.ora, tnsnames.ora, init.ora (spfile)) and open the database. From Oracle 9i and beyond Oracle database can be renamed using the dbnewid (or) nid utility. In this post, we will go through the steps to rename the database using the nid utility.
- Backup the database (Always a good practice before any changes are made).
- Start the database in mount state (do not open it).
- Invoke the dbnewid utility
nid TARGET=sys/password@<CURRENT_ORACLE_SID> DBNAME=<NEW_ORACLE_SID>
- If the input parameters to the nid command are correct, the utility would ask for a confirmation to change the database name.
Change database ID and database name <CURRENT_ORACLE_SID> to <NEW_ORACLE_SID>? (Y/[N]) =>
- Once you confirm the operation it will modify the database name in all the control files, change the dbid in the datafiles and control files and show you the new DBID.
- After this operation is successful, shutdown the database.
Shutdown Immediate
- Startup the database in mount state to rename the DB_NAME parameter in the parameter file.
Startup mount
Alter System Set DB_NAM=<NEW_ORACLE_SID> Scope=SPFILE;
Shutdown Immediate
- Create a new password file using orapwd command
orapwd file=/u32/oracle/product/DB/1120/dbs/pwd<NEW_ORACLE_SID>.ora password=password entries=10
- Rename the spfile to match the new DB NAME
- Export the new ORACLE_SID (or modify it in the .bash_profile)
- Modify the tnsnames.ora and listener.ora to reflect the new ORACLE_SID and reload the listener.
- Open the database with resetlogs options( Note: all prior backups are rendered useless at this point as the database is opened in resetlog mode)
Startup mount
Alter database open resetlogs;
- Shutdown and take a good backup of the database.
Note: If you are using RMAN to perform backups, unregister the old database before performing this step and re-register it once the process is complete.
