Rename Oracle DataBase

Some times we may need to rename our database. Controlfile is the file which will have the database name. This controlfile is read when database is mounted and instance comes to know  the database name and mounts the database with this name.So if we recreate the controlfile and we keep new database name in controlfile recreation script then the database will have new name.

Follow the below steps to rename the database

 SQL> select name from v$database;

NAME                                                                           
---------                                                                      
ORCL                                                                           

SQL> sho parameter db_name

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_name                              string      orcl                          


1. Take the controlfile backup as ASCII file using below statement

SQL>alter database backup controlfile to trace;

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Contents of controlfile backup as trace with modified values

CREATE CONTROLFILE set DATABASE "ORCLNEW" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'H:\ORCL\ORCL\REDO01.LOG'  SIZE 50M,
  GROUP 2 'H:\ORCL\ORCL\REDO02.LOG'  SIZE 50M,
  GROUP 3 'H:\ORCL\ORCL\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'H:\ORCL\ORCL\SYSTEM01.DBF',
  'H:\ORCL\ORCL\UNDOTBS01.DBF',
  'H:\ORCL\ORCL\SYSAUX01.DBF',
  'H:\ORCL\ORCL\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;

 3.Modify the parameter file and give name to db_name parameter(highlited in red).

orcl.__db_cache_size=134217728

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=58720256

orcl.__streams_pool_size=0

*.audit_file_dest='H:\oracle\product/admin/orcl/adump'

*.background_dump_dest='H:\oracle\product/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='H:\orcl\orcl\control01.ctl','H:\orcl\orcl\control02.ctl','H:\orcl\orcl\control03.ctl'

*.core_dump_dest='H:\oracle\product/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='ORCLNEW'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=h:\orcl\arch'

*.log_archive_format='ARC%S_%R.%T'

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=209715200

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='H:\oracle\product/admin/orcl/udump'


4.Startup database in nomount and execute the script

SQL> startup nomount
ORACLE instance started.

Total System Global Area  209715200 bytes                                      
Fixed Size                  1248116 bytes                                      
Variable Size              67110028 bytes                                      
Database Buffers          134217728 bytes                                      
Redo Buffers                7139328 bytes                                      
SQL> sho parameter db_name

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_name                              string      ORCLNEW                       

SQL> @h:\rename.trc

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME                                                                           
---------                                                                      

ORCLNEW