Oracle JDBC 11g Thin Driver 访问SCAN

郑全

 

In this Document
Goal
Solution
Verify SCAN Setup
Steps
More Information
References

---

Applies to:

JDBC - Version: 11.1.0.7 to 11.2.0.2.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Goal

With the introduction of 11g r2 RDBMS RAC, clusters can be created using SCAN (Single Client Access Name) as described in this white paper below:

http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf

In this how to, we use a SCAN JDBC URL to verify that we can load balance connections to the RAC nodes using a SCAN address.

Solution

Verify SCAN Setup

1. Log into one of you RAC nodes and verify the tnsnames.ora entry for your service as shown below. If you have a valid service created, there will exist an entry in your $ORACLE_HOME/network/admin/tnsnames.ora file.

pas_srv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apctcsol1)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pas_srv)
)
)

 

2. Test using SQL*Plus as shown below to verify you can connect.

[oradb1@auw2k3 admin]$ sqlplus scott/tiger@pas_srv

SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 1 09:23:03 2011

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


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

SQL>

 

3. Run some SQL as follows to check the instance you are connected to.

SQL> col "Instance" format a25
SQL> col "Hostname" format a25
SQL> col "Service" format a25
SQL> select sys_context('userenv', 'instance_name') as "Instance",
2 sys_context('userenv', 'server_host') as "Hostname",
3 sys_context('userenv', 'service_name') as "Service"
4 from dual;

Instance Hostname Service
------------------------- ------------------------- -------------------------
A12 auw2k4 pas_srv

SQL>

 

4. Repeat #2 and #3 to verify you are load balancing nodes from SQL*Plus.

SQL> col "Instance" format a25
SQL> col "Hostname" format a25
SQL> col "Service" format a25
SQL>
SQL> select sys_context('userenv', 'instance_name') as "Instance",
2 sys_context('userenv', 'server_host') as "Hostname",
3 sys_context('userenv', 'service_name') as "Service"
4 from dual;

Instance Hostname Service
------------------------- ------------------------- -------------------------
A11 auw2k3 pas_srv

 

5. Determine the SCAN host/port by connecting to any of the RAC instances in the cluster as shown below.

SQL> show parameter remote_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string apctcsol1:1521
SQL>

 

6. Check the SCAN listener is running as shown by the 2 commands below.

[oradb1@auw2k3 admin]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

[oradb1@auw2k3 admin]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node auw2k3

[oradb1@auw2k3 ~]$ srvctl config scan
SCAN name: apctcsol1, Network: 1/10.187.80.0/255.255.254.0/eth1
SCAN VIP name: scan1, IP: /apctcsol1.au.oracle.com/10.187.80.129

 

郑全

 

Steps

The following demo can be run to verify your SCAN JDBC URL is load balancing between the RAC nodes.

1. Download the 11.2.0.2 JDBC driver "ojdbc6.jar" from the following location

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

 

SCAN functionality was first added in the 11.2.0.1 JDBC driver.
JDBC versions 11.1 and lower do not support SCAN connectivity.
Please refer to Document: 1150835.1 Which JDBC Driver Versions Support SCAN?

 

2. Ensure you have JDK 1.6 in your path

C:\temp\notes>java -version
java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b07)
Java HotSpot(TM) 64-Bit Server VM (build 17.0-b17, mixed mode)

 

3. Download the source code below onto your file system in the same directory where you have "ojdbc6.jar"
   LoadBalanceTestSCAN.java
4. File system would look as follows.

C:\temp\notes>dir
Volume in drive C is OS
Volume Serial Number is 7C37-0C64

Directory of C:\temp\notes

01/02/2011 01:12 PM <DIR> .
01/02/2011 01:12 PM <DIR> ..
01/02/2011 01:12 PM 3,271 LoadBalanceTestSCAN.java
14/10/2010 09:22 AM 2,152,051 ojdbc6.jar
3 File(s) 2,158,900 bytes
2 Dir(s) 249,171,558,400 bytes free

 

5. Edit the code below to specify your SCAN JDBC URL and database username and password.

public final String userId = "scott";
public final String password = "tiger";

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

 

Note: The format of the JDBC URL is as follows.

jdbc:oracle:thin:@{scan-listener-hostname}:{port}/{service-name}

You could also use a URL as follows.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apctcsol1.au.oracle.com)(PORT=1521))(LOAD_BALANCE=YES)(CONNECT_DATA=
(SERVER=DEDICATED)(SERVICE_NAME=pas_srv)))

 

6. 6. Compile as follows.

C:\temp\notes>javac -cp ojdbc6.jar;. LoadBalanceTestSCAN.java

 

7. Run as follows.

C:\temp\notes>java -cp ojdbc6.jar;. LoadBalanceTestSCAN
Test Started at Tue Feb 01 13:12:46 EST 2011
Obtaining 5 connections
using URL : jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv


=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============
Connection #0 : instance[A11], host[auw2k3], service[pas_srv]
Connection #1 : instance[A12], host[auw2k4], service[pas_srv]
Connection #2 : instance[A11], host[auw2k3], service[pas_srv]
Connection #3 : instance[A12], host[auw2k4], service[pas_srv]
Connection #4 : instance[A12], host[auw2k4], service[pas_srv]
Closing Connections
Test Ended at Tue Feb 01 13:12:47 EST 2011

 

 

If you find the JDBC Thin does not load balance among the available scan vips/listeners. Then you could be hitting the following unpublished bug 8812294 - TB: PERFORMANCE: JDBC THIN NOT LOAD-BALANCING AMONG SCAN IP'S

 

More Information

• SCAN Whitepaper
• Oracle JDBC Driver

 

References

NOTE:1150835.1 - Which JDBC Driver Versions Support SCAN?