How to change the name of a Oracle Database

DBNEWID is a database utility to change the DBNAME and DBID of an Oracle Database. You could change either the DBID or DBNAME or both.

DBNAME is the database name that you specify while creating the database and DBID is a unique number that is assigned to the database when it is created.

The following steps demonstrates how to change the DBNAME and DBID of an Oracle Database using the DBNEWID utility.

IMPORTANT: Before you start the procedure make sure you have a full backup of your database.

Step 0: Before you start, find out the current DBNAME and DBID of your database:

SQL> SELECT DBID,NAME from V$DATABASE;
      DBID NAME
---------- ---------
2426088413 MYORDB

Step 1: The first step is to start an instance and mount a database without opening it.

sqlplus / as sysdba

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             553648592 bytes
Database Buffers          511705088 bytes
Redo Buffers                4603904 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production

Step 2. Run the DBNEWID utility by specifying the connection string and a new name for the database. The user specified in the connection string should have SYSDBA privilege.

 
nid target=sys/MYPASS@MYORDB dbname=MYNEWDB setname=YES
DBNEWID: Release 11.2.0.1.0 - Production on Tue May 30 12:50:01 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database MYORDB (DBID=2436089413)

Connected to server version 11.2.0

Control Files in database:
    C:ORADATAMYORDBCONTROL01.CTL
    C:ORADATAMYORDBCONTROL02.CTL
  
Change database name of database MYORDB to MYNEWDB? (Y/[N]) => Y

Proceeding with operation
Changing database name from MYORDB to MYNEWDB
    Control File C:ORADATAMYORDBCONTROL01.CTL - modified
    Datafile C:ORADATAMYORDBSYSTEM01.DB - wrote new name
    Datafile C:ORADATAMYORDBSYSAUX01.DB - wrote new name
    Datafile C:ORADATAMYORDBUNDOTBS01.DB - wrote new name
    Datafile C:ORADATAMYORDBUSERS01.DB - wrote new name
    Datafile C:ORADATAMYORDBMYORDBDAT01.DB - wrote new name
    Datafile C:ORADATAMYORDBMYORDBIDX01.DB - wrote new name
    Datafile C:ORADATAMYORDBTEMP01.DB - wrote new name
    Control File C:ORADATAMYORDBCONTROL01.CTL - wrote new name
    Control File C:ORADATAMYORDBCONTROL02.CTL - wrote new name

    Instance shut down

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

Step 3: Shut down the database.

SQL> shutdown immediate

Step 4: Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.

sqlplus / as sysdba

SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      MYORDB


SQL> alter system set db_name=MYNEWDB scope=spfile;
System altered.

Step 5: Create a new password file.

orapwd file=orapwMYNEWDB password=MYPASS

Step 6: Start up the database

SQL> startup mount

Step 7: Verify name change

SQL> SELECT DBID,NAME from V$DATABASE;
      DBID NAME
---------- ---------
2426088413 MYNEWDB

Post a comment

Name

Your Comment

Email (We dont publish it)

Comments

Nothing yet..be the first to share wisdom.