How to Rename Database Using Controlfile

How do I change a database name in Controlfile?

Hi Guys, in this article we are going to learn how to rename a database using controlfile. Actually, we have two ways to rename the oracle database listed below.

1. Rename Database using controlfile.
2. Rename Database using NID utility.

So, here I'm going to us first one how to rename database using controlfile step by step.

Rename Database Using Controlfile

Check the Database name before starting the activity.

SQL> select name from v$database;

NAME
---------
GAMING

In my case the database name is "GAMING", so let's change the name to "FRTGAME".

Step 1. Take controlfile backup using the below command.

SQL> alter database backup controlfile to trace as '/u01/control_bkp.log';

Database altered.

Step 2. Create pfile from spfile and change the "db_name=frtgame" in pfile.

Create pfile from spfile.

SQL> create pfile='/u01/initFRTGAME.ora' from spfile;

File created.

Open the pfile and change the db_name parameter value as your new database name.

How can I change database name in Oracle?

Before Change, my pfile look like this.

gaming.__db_cache_size=805306368
gaming.__java_pool_size=16777216
gaming.__large_pool_size=16777216
gaming.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
gaming.__pga_aggregate_target=402653184
gaming.__sga_target=1207959552
gaming.__shared_io_pool_size=0
gaming.__shared_pool_size=335544320
gaming.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/gaming/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/gaming/control01.ctl','/u01/app/oracle/fast_recovery_area/gaming/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='gaming'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=gamingXDB)'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=300
*.pga_aggregate_target=401604608
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1204813824
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'

After changing my pfile looks like this.

gaming.__db_cache_size=805306368
gaming.__java_pool_size=16777216
gaming.__large_pool_size=16777216
gaming.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
gaming.__pga_aggregate_target=402653184
gaming.__sga_target=1207959552
gaming.__shared_io_pool_size=0
gaming.__shared_pool_size=335544320
gaming.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/gaming/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/gaming/control01.ctl','/u01/app/oracle/fast_recovery_area/gaming/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='frtgame'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=gamingXDB)'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=300
*.pga_aggregate_target=401604608
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1204813824
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'

Step 3. Shut down the database and start with pfile till the nomount stage.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount pfile=/u01/initFRTGAME.ora;
ORACLE instance started.

Total System Global Area 1202556928 bytes
Fixed Size                  2227824 bytes
Variable Size             385876368 bytes
Database Buffers          805306368 bytes
Redo Buffers                9146368 bytes
SQL>
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
db_name                              string      frtgame

Step 4. Open controlfile backup file from step 1 copy create controlfile text and make some changes like below.

Before Changes:

CREATE CONTROLFILE REUSE DATABASE "GAMING" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/gaming/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/gaming/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/gaming/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/gaming/system01.dbf',
  '/u01/app/oracle/oradata/gaming/sysaux01.dbf',
  '/u01/app/oracle/oradata/gaming/undotbs01.dbf',
  '/u01/app/oracle/oradata/gaming/users01.dbf',
  '/u01/app/oracle/oradata/gaming/und01.dbf',
  '/u01/dharma01.dbf'
CHARACTER SET WE8MSWIN1252
;

How do I change a Dbname?

After Change: We just replace REUSE DATABASE "GAMING" NORESETLOGS NOARCHIVELOG to set DATABASE “FRTGAME” RESETLOGS before executing the below query remove your existing controlfiles otherwise you will face the error "ORA-27038: created file already exists"

CREATE CONTROLFILE set DATABASE "FRTGAME" RESETLOGS 
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/gaming/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/gaming/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/gaming/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/gaming/system01.dbf',
  '/u01/app/oracle/oradata/gaming/sysaux01.dbf',
  '/u01/app/oracle/oradata/gaming/undotbs01.dbf',
  '/u01/app/oracle/oradata/gaming/users01.dbf',
  '/u01/app/oracle/oradata/gaming/und01.dbf',
  '/u01/dharma01.dbf'
CHARACTER SET WE8MSWIN1252
;

Control file created.

Now your new controlfile has been successfully created.

Step 5. Open your database with the resetlogs keyword.

SQL> alter database open resetlogs;

Database altered.

Check Database name.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
FRTGAME   READ WRITE

Now it's time to create spfile from pfile.

SQL> create spfile from pfile='/u01/initFRTGAME.ora';

File created.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1202556928 bytes
Fixed Size                  2227824 bytes
Variable Size             385876368 bytes
Database Buffers          805306368 bytes
Redo Buffers                9146368 bytes
Database mounted.
Database opened.

I hope you found this article useful, if yes please write your fillings in the comment box.