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



6 comments:

  1. worked nicely on Enterprise Edition 12c as well :)

    ReplyDelete
  2. Nice one. Worked smoothly for me. Good Work!

    ReplyDelete
  3. Hey, anybrotip how to make this rename pernament? After server reboot, listener wont run. Thanks

    ReplyDelete
    Replies
    1. I solved it by myself. You have to also rename EXPORT ORACLE_SID=NEW in etc/rc0.d/oracle-xe file :-)

      Delete
  4. These are the only instructions that worked for me; thanks.

    ReplyDelete