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

SYS@cmr1 AS SYSDBA>select name from v$database;

NAME
---------
CMR1

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

   CREATE CONTROLFILE REUSE DATABASE cmr1 RESETLOGS NOARCHIVELOG

to

CREATE CONTROLFILE SET DATABASE vrep RESETLOGS NOARCHIVELOG

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

10. Rename all directory references from cmr1 to vrep
11. Remove the line reading RECOVER DATABASE USING BACKUP CONTROLFILE.
12. Remove lines starting with # and all unneccesary comments at the end of the file.
13. Rename this file to db_rename.sql
14. Change directories to the directory where all the datafiles are.
15. Rename the directory from cmr1 to vrep (this should reflect the path from the db_rename.sql file).cd
16. Find the control files and rename them to control01.ora.cmr1.bak
17. Create an admin directory in your $ORACLE_BASE with all the sub directories:
18. mkdir /u01/app/oracle/admin/vrep/udump|bdump|dpdump|pfile
19. Rename the pfile created in step 4 to initrep.ora (init<SID>.ora).
20. Edit the pifle and change all cmr1 references to vrep.
21. 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)
22. Log into sql*plus as sysdba and run db_name.sql
23. Also make sure to reflect the new database name in the /etc/oratab file

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 27 14:12:44 2011

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

Connected to an idle instance.

SYS@vtgt AS SYSDBA>@db_rename
ORACLE instance started.

Total System Global Area 539848704 bytes
Fixed Size 1337748 bytes
Variable Size 411043436 bytes
Database Buffers 121634816 bytes
Redo Buffers 5832704 bytes

Control file created.

Database altered.

Tablespace altered.

SYS@vtgt AS SYSDBA>select name from v$database;

NAME
---------
VTGT

SYS@vtgt AS SYSDBA>

Troubleshooting:

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.