Renaming a database as easily as 1..23

October 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.

  1. Log in as sysdba.
  2. Verify the database name
  3. SYS@cmr1 AS SYSDBA>select name from v$database;
    
    NAME
    ---------
    CMR1
  4. alter system switch logfile;
  5. create pfile from spfile;
  6. alter database backup controlfile to trace;
  7. shutdown immediate;
  8. Edit the tracefile from step 5:
    Find the line reading: Set #2. RESETLOGS case
  9. Remove all lines above this line.
  10. Change the line containing the database name from
    CREATE CONTROLFILE REUSE DATABASE cmr1 RESETLOGS NOARCHIVELOG
  11. 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.

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

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: