Renaming a Database in Oracle 11g R2: A Step-by-Step Guide

Current Environment:

Operating System: Oracle Linux 6.3
RDBMS Version : 11.2.0.4 

Step 1: Perform below prerequisites before proceeding.

1) Your database must be mounted to do this.(It will not allow you to change the database name in open mode.
2)To change or rename the database name, you need to change below locations of database:

• Data Dictionary
• tnsnames.ora,sqlnet.ora, and listener.ora file
• Instance

 3) Listener must be started and you must able to connect your database remotely using TNS service.

Step 2: Take backup of pfile, spfile,tnsnames.ora, listener.ora and sqlnet.ora file

Step 3: Set the environment variables for current database and run the below command.
 

[oracle@dc admin]$ env | grep ORA

ORACLE_SID=DC

ORACLE_BASE=/home/oracle/u01

ORACLE_HOME=/home/oracle/u01/product/11.2.0/dbhome_1

You will get below error message if you run below command in open mode.

SQL> select name,instance_name,open_mode from v$database,v$instance;
 

NAME      INSTANCE_NAME    OPEN_MODE

--------- ---------------- --------------------

DC         DC               READ WRITE

[oracle@dc admin]$ nid target=sys/oracle@DC dbname=GTS setname=YES

DBNEWID: Release 11.2.0.4.0 - Production on Thu Aug 10 12:57:35 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database DC (DBID=1238895280)

NID-00121: Database should not be open

Change of database name failed during validation - database is intact.

DBNEWID - Completed with validation errors.

SQL> alter database close;

Database altered.

[oracle@dc admin]$ nid target=sys/oracle@DC dbname=GTS setname=YES

DBNEWID: Release 11.2.0.4.0 - Production on Thu Aug 10 12:58:49 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database DC (DBID=1238895280)

Connected to server version 11.2.0

Control Files in database:

    /oradisk/DC/control01.ctl

    /home/oracle/u01/fast_recovery_area/DC/control02.ctl

Change database name of database DC to GTS? (Y/[N]) => Y

Proceeding with operation

Changing database name from DC to GTS

    Control File /oradisk/DC/control01.ctl - modified

    Control File /home/oracle/u01/fast_recovery_area/DC/control02.ctl - modified

    Datafile /oradisk/DC/system01.db - wrote new name

    Datafile /oradisk/DC/sysaux01.db - wrote new name

    Datafile /oradisk/DC/undotbs01.db - wrote new name

    Datafile /oradisk/DC/users01.db - wrote new name

    Datafile /oradisk/DC/temp01.db - wrote new name

    Control File /oradisk/DC/control01.ctl - wrote new name

    Control File /home/oracle/u01/fast_recovery_area/DC/control02.ctl - wrote new name

    Instance shut down

Database name changed to GTS.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID - Completed succesfully.

In the below command,

 nid target=sys/oracle@DC dbname=GTS setname=YES

 DC => is the tns service entry in tnsnames.ora file.

 GTS => It is the new name for the current database.

Step 4: Now start the database but you will get below error message, because you have not changed db_name and db_unique_name entries in the pfile.

[oracle@dc admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 12:59:06 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                2253824 bytes

Variable Size             180358144 bytes

Database Buffers          230686720 bytes

Redo Buffers              4247552 bytes

ORA-01103: database name 'GTS' in control file is not 'DC'

SQL> show parameter instance_name

NAME            TYPE           VALUE

--------------- ----------- ------------

instance_name   string          DC

SQL> show parameter db_name

NAME              TYPE      VALUE

----------------- ------ --------

db_name           string     DC

SQL> show parameter db_unique

NAME              TYPE      VALUE

----------------- ------ --------

db_unique_name    string     DC

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dc admin]$ pwd

/home/oracle/u01/product/11.2.0/dbhome_1/dbs

[oracle@dc dbs]$ ls -ltr

-rw-r-----. 1 oracle oinstall    1536 Jun 21 17:17 orapwDC

-rw-r-----. 1 oracle oinstall    2560 Aug  9 16:37 bkp_spfileDC.ora

-rw-r--r--. 1 oracle oinstall    1319 Aug  9 16:38 initDC.ora

-rw-r-----. 1 oracle oinstall    3584 Aug 10 11:15 spfileDC.ora

 

[oracle@dc dbs]$ cp initDC.ora bkp_initDC.ora

Step 5: Modify below two parameters in pfile and start the database in nomount and create spfile from pfile:

db_name='GTS'

db_unique_name='GTS'

 

[oracle@dc dbs]$ vi initDC.ora

[oracle@dc dbs]$ mv spfileDC.ora bkp_spfileDC.ora

[oracle@dc dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 13:02:28 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  221331456 bytes

Fixed Size                        2251856 bytes

Variable Size                 163578800 bytes

Database Buffers           50331648 bytes

Redo Buffers                   5169152 bytes

SQL> show parameter instance_name

NAME           TYPE           VALUE

-------------- ----------- ---------

instance_name   string          DC

SQL> show parameter db_name

NAME       TYPE           VALUE

--------- ----------- ---------

db_name    string          GTS

SQL> show parameter db_uni

NAME             TYPE           VALUE

--------------- ----------- ---------

db_unique_name   string          GTS

Step 6: Now mount the database and open it.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

Step 7: Verify below things wherther everthing is working fine.

• sqlplus connectivity
• tnsping connectivity
• listener status

[oracle@dc admin]$ sqlplus sys/oracle@GTS as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 13:07:22 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/rd.sql

NAME    INSTANCE_NAME    OPEN_MODE

--------- ---------------- --------------------

GTS       DC                  READ WRITE

 

Thanks for reading this post ! Please comment if you like this post !