Changing an Oracle database name in Oracle11g

most of the times we wrongly created the database to avoid this confusion we just rename the database here i 'm showing how to change the oracle database name using DBNEWID utility?

S-1:

First, we have to ensure details of the database name and Id using V$database View


SQL> select DBID,Name from V$database;

      DBID     NAME
----------    ---------
2804950687      XE

 

S-2:

Then shut down our current database running




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

S-3:

Start our database in mount stage



  SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             226496200 bytes
Database Buffers          301989888 bytes
Redo Buffers                5791744 bytes
Database mounted.


S-4:

Run the DBNEW Utility by specifying the connection string and a new name for the database




C:\Windows\system32>nid TARGET=sys/oracle@XE DBNAME=Dhoni SETNAME=YES

DBNEWID: Release 11.2.0.2.0 - Production on Thu Sep 29 11:45:06 2016

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

Connected to database XE (DBID=2804950687)

Connected to server version 11.2.0

Control Files in database:
    C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF

Change database name of database XE to DHONI? (Y/[N]) => Y

Proceeding with operation
Changing database name from XE to DHONI
    Control File     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF - modified
    Datafile     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DB - wrote new name
    Datafile     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DB - wrote new name
    Datafile     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DB - wrote new name
    Datafile    C:\ORACLEXE \APP\ORACLE\ORADATA\XE\USERS.DB - wrote new name
    Datafile    C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DB - wrote new name
    Control File     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF - wrote new name
    Instance shut down

Database name changed to DHONI.
Modify parameter file and generate a new password file before restarting.
Successfully changed database name.
DBNEWID - Completed successfully.

S-5:

Again shut down our database


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

S-5:

I tried to start the machine in mount stage it through an error of ORA-01103 database name 'DHONI' in control file is not 'XE' Which means This error is common when we try to duplicate a database and the controlfile still holds the old name.



SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             226496200 bytes
Database Buffers          301989888 bytes
Redo Buffers                5791744 bytes
ORA-01103: database name 'DHONI' in control file is not 'XE'

S-7:

Stop The database




SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.


S-8:

Start our database in nomount stage and change the DB_NAME parameter in spfile




 SQL> startup nomount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             226496200 bytes
Database Buffers          301989888 bytes
Redo Buffers                5791744 bytes

SQL> alter system set db_name=Dhoni scope=spfile;

System altered.


  

S-9:

Shut our database and Create the password file our new DBname




  SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.

    
C:\>orapwd file=orapwDHONI password=ORACLE

S-10:

We have just changed the database name only ,Here SETNAME Parameter tells DBNEW Utility to only alter database name and not the database id so we don't need use RESETLOGS




  SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             226496200 bytes
Database Buffers          301989888 bytes
Redo Buffers                5791744 bytes
Database mounted.
Database opened.



S-10:

Verify the database name change



  SQL> select DBID,Name from V$database;

      DBID       NAME
----------     ---------
2804950687      DHONI


Source: Oracle,Google