Change the Database Name

In situations we need to change the database name , for example when we do a clone from production server to a development server , we rename the datbase name at the development site.

Let me give an example to you : My company's prod server is BNFD. At the develoment site I have restore a copy of the database and it is BNFD.But we want to rename it to a development server say new name would be MARS.

Theoritical Steps:

1. Check the database Name.

2. Take a Trace of the control file.

3.Edit the trace and change the database Name. - Create the controlfile script

4.Change the db_name and instance_name in the init.ora file.

5.Move or remove the binary copies from multiplexed Locations.

6. At NOMOUNT state create the control file.

7.Open the Database - And Check the database name to confirm.

SQL> select name from v$database;

NAME
---------
BNFD

SQL> show parameters dump_dest ;

NAME TYPE VALUE
--------- -------- -----------
background_dump_dest string C:\orcl\admin\BNFD\bdump
core_dump_dest string C:\orcl\admin\BNFD\cdump
user_dump_dest string C:\orcl\admin\BNFD\udump


Step 1: Take a Backup trace of Control file
SQL> alter database backup controlfile to trace;
This ll create the trace file of a control file in UDUMP


The instance needs to be shut down. SYSDBA privileges will be needed.
SQL> shutdown immediate ;


STEP :2Modify (and optionally rename) the created trace file:
1.Find the line reading # Set #2. RESETLOGS case
2.Remove all lines above this line.
3.Change the line containing the database name from CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS NOARCHIVELOG
toCREATE CONTROLFILE SET DATABASE "CAT" RESETLOGS NOARCHIVELOG
Note, in my case, the database is running in noarchivelog mode.
The corresponding line reads ARCHIVELOG otherwise.
4.Remove the line reading RECOVER DATABASE USING BACKUP CONTROLFILE.
5.Remove lines starting with #.

It looks then something like:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CAT" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 '/home/oracle/databases/cat/redo1.ora' SIZE 100M,
GROUP 2 '/home/oracle/databases/cat/redo2.ora' SIZE 100M,
GROUP 3 '/home/oracle/databases/cat/redo3.ora' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/databases/cat/system.dbf',
'/home/oracle/databases/cat/undo.dbf',
'/home/oracle/databases/cat/data.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/OraHome1/databases/ora9/temp.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;

in my case I renamed the file to newcontrol123.sql

STEP3: Move the all the previous Control file to otherlocation
Move the controlfiles away so that they can be re-created..
$ mv ctl_1.ora ctl_1.ora.moved
$ mv ctl_2.ora ctl_2.ora.moved
$ mv ctl_3.ora ctl_3.ora.moved

STEP 4:Change the Database name in Init.ora File
The database name must be entered (changed) in the initSID.ora:
initcat.ora
db_name = CAT

Step 5 : Connect as sysdba and RUN the new controlfile

sqlplus "/ as sysdba"

SQL> @/tmp/newcontrol

SQL> startup open resetlogs ;

SQL> select name from v$database;
NAME
---------
CAT