Rename a Database

Here's a cool tip - how to actually rename a database. You can always change SERVICE_NAMES, but if you want to change the internal name of the db, here's a quick tutorial using the utility NID:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount --(must be mounted)

ORACLE instance started.

Total System Global Area 1677721600 bytes

Fixed Size                  2066240 bytes

Variable Size             318769344 bytes

Database Buffers         1342177280 bytes

Redo Buffers               14708736 bytes

Database mounted.

 Now, we’re going to change the database name to SBY02. We assume ORACLE_SID=<OLDSID>:




SQL> $nid target=/ DBNAME=SBY02 setname=YES

DBNEWID: Release 10.2.0.4.0 - Production on Thu Jul 29 15:11:25 2010

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

Connected to database LIVE (DBID=551896809)

Connected to server version 10.2.0

Control Files in database:

    +DG1/sby02/controlfile/control01.ctl

Change database name of database LIVE to SBY02? (Y/[N]) => Y

Proceeding with operation

Changing database name from LIVE to SBY02

    Control File +DG1/sby02/controlfile/control01.ctl - modified

    Datafile +DG1/sby02/datafile/system.5391.725630121 - wrote new name

    Datafile +DG1/sby02/datafile/undotbs1.2314.725630121 - wrote new name

    Datafile +DG1/sby02/datafile/sysaux.2311.725630121 - wrote new name

    Datafile +DG1/sby02/datafile/users.2323.725630125 - wrote new name

      Datafile +DG1/sby02/tempfile/temp.2213.725641409 - wrote new name

    Control File +DG1/sby02/controlfile/control01.ctl - wrote new name

    Instance shut down

Database name changed to SBY02.

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

Succesfully changed database name.

DBNEWID - Completed succesfully.

SQL> disconn

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64

bit Production

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

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1677721600 bytes

Fixed Size                  2066240 bytes

Variable Size             318769344 bytes

Database Buffers         1342177280 bytes

Redo Buffers               14708736 bytes

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

System altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup




The other cool thing about this is that DB_UNIQUE_NAME is changed automatically; only DB_NAME needs changing. I leave it to you to generate a new password file.