Sunday, March 09, 2008

Rename the database? WHY?

I've seen repeated questions around renaming the XE database. Many of the reasons are not legitimate - you really can only use one XE at a time on a specific system.

However, there are a few legitimate reasons for wanting a different name. And a few easy workarounds that just might do the trick.

If all you want is a connection to the database other than connecting using @XE, here are two fairly easy ways:

1) Create a new alias in TNSNAMES.ORA
2) Create a new database service for XE

Demo time ... I want to connect to XE using 'sqlplus test/test@test'

Before


C:\temp>sqlplus test/test@test

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 4 07:30:42 2008

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:


Solution

1) Create a new alias in TNSNAMES.ORA on the client ... TEST is the alias, XE is the SERVICE for the database

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = FCC003L)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

and the above yields


C:\temp>sqlplus test/test@test

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 4 07:30:36 2008

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production


The above will work quite nicely for any client that connects to the database using a TNS alias. But it still assumes the database service (often mistakenly called the SID) is XE. So let's go to variation 2)



2) Create a new database service for XE

Since Oracle8i, every database registers the services it wishes to provide with the listener. We can have up to 100 services coming out of one database. There are a few internal services, every Advanced Queue gets it's own service, there is one default service that matches the database name/SID. And we can add some ourself.

So let's ADD a service

a) add the service to the database

C:\temp>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 4 07:18:46 2008

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string XE
SQL> alter system set service_names='XE,TEST';

System altered.

SQL> alter system register;

System altered.

SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string XE,TEST
SQL> exit


b) Verify it is known to the listener


C:\temp>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-MAR-2008 07:19
:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date 04-MAR-2008 07:17:41
Uptime 0 days 0 hr. 2 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oracle\app\oracle\product\10.2.0\server\network\adm
in\listener.ora
Listener Log File C:\oracle\app\oracle\product\10.2.0\server\network\log
\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FCC003L)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=
HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, 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 "TEST" 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...
Service "XE_XPT" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully



c) Update the tnsnames.ora


TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = FCC003L)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)



d) Verify


C:\temp>sqlplus test/test@test

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 4 07:30:36 2008

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

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production


Services are the basis for resource management and are critical for future instance consolidation in which we collapse multiple single-instance databases into one instance that can service [sic] many applications.

Another prerequisite to instance consolidation is the use of CURRENT_SCHEMA as described in an earlier blog.

No comments: