How to change Oracle database name

Many times when you are doing DBA work you need to change Oracle database name. So in this tutorial we will see how can we change our database name using Oracle NID tool.

The steps to change the database name are as follows:

Make sure you shutdown and start up in mount mode your database before. 
SQL shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
Database mounted.
SQL exit

######################### HOST #########################################

[oracle@mysql db1]$ nid TARGET=SYS/secret_password DBNAME=DB SETNAME=YES

DBNEWID: Release 11.2.0.1.0 - Production on Fri Oct 25 19:24:43 2013

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

Connected to database TEST (DBID=2127904120)

Connected to server version 11.2.0

Control Files in database:
    /opt/oracle/oradata/db1/control01.ctl
    /opt/oracle/flash_recovery_area/db1/control02.ctl

Change database name of database TEST to DB? (Y/[N]) = Y

Proceeding with operation
Changing database name from TEST to DB
    Control File /opt/oracle/oradata/db1/control01.ctl - modified
    Control File /opt/oracle/flash_recovery_area/db1/control02.ctl - modified
    Datafile /opt/oracle/oradata/db1/system01.db - wrote new name
    Datafile /opt/oracle/oradata/db1/sysaux01.db - wrote new name
    Datafile /opt/oracle/oradata/db1/undotbs01.db - wrote new name
    Datafile /opt/oracle/oradata/db1/users01.db - wrote new name
    Datafile /opt/oracle/oradata/db1/temp01.db - wrote new name
    Control File /opt/oracle/oradata/db1/control01.ctl - wrote new name
    Control File /opt/oracle/flash_recovery_area/db1/control02.ctl - wrote new name
    Instance shut down

Database name changed to DB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
Recreate the new password file. 
orapwd file=/opt/oracle/product/11.2.0/dbhome_1/dbs/passwd.ora password=sys_secret_password entries=5
Make sure that you drop any password files before or you will get some errors. Rename the init.ora file. 
[oracle@mysql dbs]$ rename init.ora initDB.ora
Edit the initDB.ora parameters file. 
 [oracle@mysql dbs]$ vim initDB.ora
 -- alter the db_name  parameter to match the new database name.


# Change '' to point to the oracle base (the one you specify at
# install time)

db_name='DB'
Now start the database in nomount mode and alter the db_name parameter in the control file. 
 [oracle@mysql dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 25 18:50:22 2013

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

Connected to an idle instance.

SQL startup nomount
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             289408976 bytes
Database Buffers          121634816 bytes
Redo Buffers                4288512 bytes
SQL alter system set db_name=DB scope=spfile;

System altered.
Shutdown the database and start it backup. 
 SQL shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL startup;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             268437456 bytes
Database Buffers          142606336 bytes
Redo Buffers                4288512 bytes
Database mounted.
Database opened.
Check the status and name of you database. 
SQL select status from v$instance;

STATUS
------------

OPEN

SQL select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

DB
At last step alter the oratab file and change the DB name. 
 [oracle@mysql dbs]$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<n|Y:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
db1:/opt/oracle/product/11.2.0/dbhome_1:N

[oracle@mysql dbs]$ vim /etc/oratab

[oracle@mysql dbs]$ . oraenv
ORACLE_SID = [db] ? db1

The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1 is /opt/oracle

Copyright 2020. All Rights Reserved