How to rename Oracle database

Intro

However you can find dozens of useful articles in the Internet regarding the topic I was not able to find any 100% accurate HowTo. Finally I have successfully renamed my Oracle database / instance by mixing couple of different tips found in the Internet. The following Case Study describes just the exact steps taken by me during that process. The components used by me include:

• Ubuntu 12.04 Server amd64
• Oracle 11g R2 Express Edition installed according to the following HowTo
• Source database / instance name: XE (the default one just after fresh installation)
• Ultimate database / instance name: NEW
• Server name: oracleserver

So lets do it!

Configuration

1) Log in into the Oracle box as the root user and verify that the Oracle database is running and the Oracle listener is running:

root@oracleserver:~# /etc/init.d/oracle-xe status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 12-APR-2013 07:28:36

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                12-APR-2013 07:11:43

Uptime                    0 days 0 hr. 16 min. 53 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Default Service           XE

Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oracleserver/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=8000))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "XE" has 1 instance(s).

  Instance "XE", status READY, has 1 handler(s) for this service...

Service "XEXDB" has 1 instance(s).

  Instance "XE", status READY, has 1 handler(s) for this service...

The command completed successfully

You should see the XE service having XE instance in READY state. If you can't see that, restart the Oracle database server by running the following command:

root@oracleserver:~# /etc/init.d/oracle-xe restart

Note: the rest of work is done as the oracle user!

2) Connect into the database as sysdba:

oracle@oracleserver:~$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

oracle@oracleserver:~$ export ORACLE_SID=XE

oracle@oracleserver:~$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 12 07:32:22 2013

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

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

3) Shutdown the database and then mount it back:

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area  204591104 bytes

Fixed Size    2225032 bytes

Variable Size  142609528 bytes

Database Buffers   54525952 bytes

Redo Buffers    5230592 bytes

Database mounted.

4) Disconnect from the database and run the nid tool specifying the new database name:

SQL> exit

Disconnected

oracle@oracleserver:~$ $ORACLE_HOME/bin/nid TARGET=sys/[password]@XE DBNAME=NEW

DBNEWID: Release 11.2.0.2.0 - Production on Fri Apr 12 07:39:30 2013

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

Connected to database XE (DBID=2692371493)

Connected to server version 11.2.0

Control Files in database:

    /u01/app/oracle/oradata/XE/control.dbf

Change database ID and database name XE to NEW? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2692371493 to 2293907314

Changing database name from XE to NEW

    Control File /u01/app/oracle/oradata/XE/control.dbf - modified

    Datafile /u01/app/oracle/oradata/XE/system.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/XE/sysaux.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/XE/undotbs1.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/XE/users.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/XE/temp.db - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/XE/control.dbf - dbid changed, wrote new name

    Instance shut down

Database name changed to NEW.

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

Database ID for database NEW changed to 2293907314.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

5) Connect into the database as sysdba, shutdown the database and mount it back (please, ignore any errors that may occur in this step):

oracle@oracleserver:~$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 12 07:50:08 2013

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

Connected to an idle instance.

SQL> SHUTDOWN IMMEDIATE

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area  204591104 bytes

Fixed Size    2225032 bytes

Variable Size  142609528 bytes

Database Buffers   54525952 bytes

Redo Buffers    5230592 bytes

ORA-01103: database name 'NEW' in control file is not 'XE'

6) Modify the DB_NAME value specifying the new database name and shutdown the database:

SQL> ALTER SYSTEM SET DB_NAME=NEW SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE

ORA-01507: database not mounted

ORACLE instance shut down.

7) Disconnect from the database and run the orapwd tool specifying the new database name and the password used when connecting as sysdba:

SQL> exit

Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
oracle@oracleserver:~$ $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwNEW password=[password] force=y nosysdba=n

8) Rename the database spfile file to match the new database name:

oracle@oracleserver:~$ mv $ORACLE_HOME/dbs/spfileXE.ora $ORACLE_HOME/dbs/spfileNEW.ora

9) Create the database init file from the $ORACLE_HOME/dbs/init.ora template and modify its content to match the new database name and paths. All the changes that have been made comparing to the original file have been made bold:

oracle@oracleserver:~$ cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initNEW.ora
oracle@oracleserver:~$ cat $ORACLE_HOME/dbs/initNEW.ora

db_name='NEW'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/product/11.2.0/xe/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/product/11.2.0/xe/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle/product/11.2.0/xe'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

10) Modify the content of $ORACLE_HOME/network/admin/listener.ora, $ORACLE_HOME/network/admin/tnsnames.ora and /etc/oratab files. All the changes that have been made comparing to the original files content have been made bold. Export the new value of ORACLE_SID variable:

oracle@oracleserver:~$ cat $ORACLE_HOME/network/admin/listener.ora 
# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
    (SID_DESC = 
      (SID_NAME = NEW)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)
oracle@oracleserver:~$ cat $ORACLE_HOME/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File:

NEW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NEW)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

oracle@oracleserver:~$ cat /etc/oratab 
NEW:/u01/app/oracle/product/11.2.0/xe:N
oracle@oracleserver:~$ export ORACLE_SID=NEW

11) As a root user restart the Oracle database server and listener:

root@oracleserver:~# /etc/init.d/oracle-xe restart
Shutting down Oracle Database 11g Express Edition instance.
Stopping Oracle Net Listener.

Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.

12) Connect into the database as sysdba, mount the database and open with RESETLOGS:


oracle@oracleserver:~$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 17 07:40:27 2013

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  204591104 bytes
Fixed Size    2225032 bytes
Variable Size  134220920 bytes
Database Buffers   62914560 bytes
Redo Buffers    5230592 bytes
Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

13) Disconnect from the database and verify the Oracle listener status and whether you can log into the new database as sysdba. If so, you're done!

SQL> exit

Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

oracle@oracleserver:~$ $ORACLE_HOME/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 17-APR-2013 08:35:30

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                17-APR-2013 07:53:18

Uptime                    0 days 0 hr. 42 min. 12 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Default Service           XE

Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oracleserver/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=8000))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "NEW" has 2 instance(s).

  Instance "NEW", status UNKNOWN, has 1 handler(s) for this service...

  Instance "NEW", status READY, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "XEXDB" has 1 instance(s).

  Instance "NEW", status READY, has 1 handler(s) for this service...

The command completed successfully

oracle@oracleserver:~$ $ORACLE_HOME/bin/sqlplus sys@NEW as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 17 08:42:59 2013

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

Enter password: 

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production