Renaming a database as easily as 1..23

In this example I renamed a database from cmr1 to vrep. I also include steps to make sure that the datafiles, control files, and audit files reside in directories that contain the SID name.

  1. Log in as sysdba.
  2. Verify the database name
  3. alter system switch logfile;
  4. create pfile from spfile;
  5. alter database backup controlfile to trace;
  6. shutdown immediate;
  7. Edit the tracefile from step 5:
    Find the line reading: Set #2. RESETLOGS case
  8. Remove all lines above this line.
  9. Change the line containing the database name from
  10. to

    Note, in my case, the database is running in noarchive log mode. The corresponding line reads ARCHIVELOG otherwise.

  11. Rename all directory references from cmr1 to vrep
  13. Remove lines starting with # and all unneccesary comments at the end of the file.
  14. Rename this file to db_rename.sql
  15. Change directories to the directory where all the datafiles are.
  16. Rename the directory from cmr1 to vrep (this should reflect the path from the db_rename.sql file).cd
  17. Find the control files and rename them to control01.ora.cmr1.bak
  18. Create an admin directory in your $ORACLE_BASE with all the sub directories:
  19. mkdir /u01/app/oracle/admin/vrep/udump|bdump|dpdump|pfile
  20. Rename the pfile created in step 4 to initrep.ora (init<SID>.ora).
  21. Edit the pifle and change all cmr1 references to vrep.
  22. Set the environment to register the new database SID. (Typically this would be in the .bash_profile. You should just have to set ORACLE_SID=vrep)
  23. Log into sql*plus as sysdba and run db_name.sql
  24. Also make sure to reflect the new database name in the /etc/oratab file


Typically, if things go wrong, there may be a typo involved. Check the db_rename.sql and the init<SID>.ora file. Make sure all the paths are valid and that the directories referenced acutally exists, and that they have the correct permissions.

If the db_rename.sql fails, you may not be able to shut down the database. In that case you may want to find the right OS processes and kill them manually.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.