Renaming a database as easily as 1..23October 27, 2011 at 2:24 pm | Posted in Oracle Developement | Leave a comment
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
CREATE CONTROLFILE REUSE DATABASE cmr1 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; NAME --------- CMR1
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.
SQL*Plus: Release 184.108.40.206.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>
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.