2012-04-23

Renaming Your Oracle Instance, Part 1


For DBA's, there's nothing better or more useful than having your own database to play in. How else are you going to learn, practice, and try new features?

So with that in mind, I recently set up an Oracle Linux 6 virtual machine running on Oracle VirtualBox, downloaded Oracle Database 11g R2, and installed it with DBCA. Not a very difficult process, and I now had an up and running instance with the default name of ORCL.

And that presented a problem, in that even though this is going to be playground of sorts for me, I do intend to have it on the network. The default, generic SID of orcl is likely going to be a problem. I needed to rename my database. I'll be renaming it to jgdb.

While I could have just started over, I thought it would be an worthwhile exercise to try renaming the database and modifying it's various components to reflect the new SID, as it's not generally the kind of this you're doing every day at work. The following is the process I went though, starting with the DBNEWID utility.



To get started, I used the documentation on DBNEWID for 11.2 found here.

1) If you using a binary SPFILE (the default when your database has been created by DBCA, as mine was), you'll want to back it up to a text init.ora file.  This is needed to start the instance after DBNEWID does its magic.

SQL> connect / as sysdba
Connected.

SQL> create pfile='/home/oracle/jgdb_init.ora' from spfile;
File created.



2) Run DBNEWID from the shell.  (I'm on Linux, running the bash shell.)


$ nid TARGET=SYS DBNAME=jgdb SETNAME=YES

DBNEWID: Release 11.2.0.1.0 - Production on Thu Apr 19 10:08:32 2012

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

Password: 
Connected to database ORCL (DBID=1308307467)

Connected to server version 11.2.0

Control Files in database:
    /home/oracle/app/oracle/oradata/orcl/control01.ctl
    /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database name of database ORCL to JGDB? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to JGDB
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - modified
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
    Datafile /home/oracle/app/oracle/oradata/orcl/system01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/sysaux01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/undotbs01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/users01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/example01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/temp01.db - wrote new name
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - wrote new name
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - wrote new name
    Instance shut down

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



3)  As you can see above, DBNEWID shut down the instance.  So now we edit the text init.ora file to reflect the new database name.  Below, is jgdb_init.ora that we exported earlier.  What I have done is boldface everywhere that I replaced 'orcl' with 'jgdb'.

What you may notice, is that I have NOT changed the various file system paths in which 'orcl' appears.  Now's not the time, nor is it strictly necessary.

Also, I did change the name of the local listener.  This change was not actually required.  However, my goal here is not just a database rename.  I feel strongly that, if possible, everything that referenced the old SID should be modified for the sake of consistency.

jgdb.__db_cache_size=209715200
jgdb.__java_pool_size=4194304
jgdb.__large_pool_size=4194304
jgdb.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
jgdb.__pga_aggregate_target=293601280
jgdb.__sga_target=545259520
jgdb.__shared_io_pool_size=0
jgdb.__shared_pool_size=314572800
jgdb.__streams_pool_size=4194304
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl'
    '/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='gotoracle.com'
*.db_name='jgdb'
*.db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jgdbXDB)'
*.local_listener='LISTENER_JGDB'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1




4)  Having changed the name of the local listener, we now need to modify the tnsnames.ora file to reflect the change.

# tnsnames.ora Network Configuration File:
#    /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_JGDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


JGDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jgdb.gotoracle.com)
    )
  )



5)  At this point we should be able to start the database using our PFILE that was exported and modified earlier.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 20 11:33:25 2012

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

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup pfile='/home/oracle/jgdb_init.ora'
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size      2217952 bytes
Variable Size    620759072 bytes
Database Buffers   209715200 bytes
Redo Buffers      2412544 bytes
Database mounted.
Database opened.



Great! We're up and running. But we are not completely done. In my next post, we will be relocating the control file(s), redo logs, and data files from $ORACLE_BASE/oradata/orcl to $ORACLE_BASE/oradata/jgdb for consistency.