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.
- Log in as sysdba.
- Verify the database name
- alter system switch logfile;
- create pfile from spfile;
- alter database backup controlfile to trace;
- shutdown immediate;
- Edit the tracefile from step 5:
Find the line reading: Set #2. RESETLOGS case
- Remove all lines above this line.
- Change the line containing the database name from
1CREATE CONTROLFILE <strong>REUSE</strong> DATABASE <strong>cmr1</strong> RESETLOGS NOARCHIVELOG
- Rename all directory references from cmr1 to vrep
- Remove the line reading RECOVER DATABASE USING BACKUP CONTROLFILE.
- Remove lines starting with # and all unneccesary comments at the end of the file.
- Rename this file to db_rename.sql
- Change directories to the directory where all the datafiles are.
- Rename the directory from cmr1 to vrep (this should reflect the path from the db_rename.sql file).cd
- Find the control files and rename them to control01.ora.cmr1.bak
- Create an admin directory in your $ORACLE_BASE with all the sub directories:
- mkdir /u01/app/oracle/admin/vrep/udump|bdump|dpdump|pfile
- Rename the pfile created in step 4 to initrep.ora (init<SID>.ora).
- Edit the pifle and change all cmr1 references to vrep.
- 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)
- Log into sql*plus as sysdba and run db_name.sql
- Also make sure to reflect the new database name in the /etc/oratab file
SYS@cmr1 AS SYSDBA>select name from v$database;
CREATE CONTROLFILE <strong>SET</strong> DATABASE <strong>vrep</strong> RESETLOGS NOARCHIVELOG
Note, in my case, the database is running in noarchive log mode. The corresponding line reads ARCHIVELOG otherwise.
SQL*Plus: Release 220.127.116.11.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.
SYS@vtgt AS SYSDBA>select name from v$database;
SYS@vtgt AS SYSDBA>
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.