Using SCAN - Single Client Access Name to Connect to 11g R2 RAC from JDeveloper 11g

The ability to have a single / simple connect string for a RAC cluster seemed like something worth trying which SCAN allows us to have as part of 11g R2. Trying to understand how SCAN works and it's setup was not what I had time for so I took an existing setup and verified I could connect from JDeveloper 11g without any issues.

The tnsnames.ora alias was defined as follows. As you can see there is nothing to suggest we are connecting to RAC here, but we are.

RAC11G2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apctcsol1.au.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = J11G2)
)
)

Being able to connect from SQL*PLus was what I first tried and that worked fine.

d:\temp>sqlplus scott/tiger@RAC11G2

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 8 09:15:59 2010

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SCOTT@RAC11G2>

So from JDeveloper here it shows it can connect fine as well as expected.




















So from a JDBC client we would be connecting as follows ensuring we use a URL which indicates the use of service name as follows.

private static final String url =
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
"(HOST=apctcsol1.au.oracle.com)(PORT=1521))" +
"(CONNECT_DATA=(SERVICE_NAME=J11G2)))";

We could also use a connect string as follows:

private static final String url = "jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/J11G2";

Here are some of the commands I ran to verify the SCAN setup and ports on one of the remote RAC instances. RAC Instances register to SCAN listeners as remote listeners.

Check we have SCAN listener configured

[oracle@auw2k3 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

Check status of SCAN listeners

[oracle@auw2k3 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node auw2k4
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node auw2k3
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node auw2k3

[oracle@auw2k3 ~]$ ps -aef | grep -i SCAN
oragrid 20168 1 0 Feb05 ? 00:00:12 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oragrid 20179 1 0 Feb05 ? 00:00:11 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit

Finally verify that the service I need to connect to existed on the SCAN listener

[oracle@auw2k3 ~]$ lsnrctl services LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2010 10:05:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary...
Service "J10G" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521))
Service "J10GXDB" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k3)(PORT=55880))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k4)(PORT=31825))
Service "J10G_TAF" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521))
Service "J10G_XPT" has 2 instance(s).
Instance "J10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521))
Instance "J10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521))
Service "J11G2" has 2 instance(s).
Instance "J11G21", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521)))
Instance "J11G22", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521)))
Service "J11G2XDB" has 2 instance(s).
Instance "J11G21", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k3)(PORT=63414))
Instance "J11G22", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=auw2k4)(PORT=62891))
Service "sv1" has 2 instance(s).
Instance "J11G21", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k3-vip)(PORT=1521)))
Instance "J11G22", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=auw2k4-vip)(PORT=1521)))
The command completed successfully
[oracle@auw2k3 ~]$