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.
No comments:
Post a Comment