How to change Database name of Oracle Databases

Change database name 
============================

As prerequisite we should do below things

• take existing spfile/pfile backup
• take database backup
• list down where db name use 
• After db name change network entries 

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST      READ WRITE

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

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
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@localhost admin]$
[oracle@localhost admin]$ nid TARGET=sys/sys@test DBNAME=test1 SETNAME=YES

DBNEWID: Release 11.2.0.4.0 - Production on Tue Mar 10 22:18:08 2020

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

Connected to database TEST (DBID=2332867595)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/test/control01.ctl
    /u01/app/oracle/fast_recovery_area/test/control02.ctl

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

Proceeding with operation
Changing database name from TEST to TEST1
    Control File /u01/app/oracle/oradata/test/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/test/system01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/sysaux01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/undotbs01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/users01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/example01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/temp01.db - wrote new name
    Control File /u01/app/oracle/oradata/test/control01.ctl - wrote new name
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - wrote new name
    Instance shut down

Database name changed to TEST1.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

[oracle@localhost admin]$

=====================================


[oracle@localhost ~]$ . oraenv
ORACLE_SID = [test1] ? test
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ !sq
sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:20:11 2020

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
ORA-01103: database name 'TEST1' in control file is not 'TEST'


SQL>
[oracle@localhost tmp]$ !sq
sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:22:10 2020

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

Connected to an idle instance.

SQL> startup mount pfile=/tmp/test1.ora
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> create spfile from pfile='/tmp/test1.ora';

File created.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST1     MOUNTED

SQL> alter database open;

Database altered.

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST1     READ WRITE

SQL>