By Pas Apicella and Frances Zhao
Published March 2011
There are two implementations in Oracle WebLogic Server to support Oracle Real Application Clusters (RAC): the multi data source solution, and the new implementation in Oracle WebLogic 11g Release 1 (10.3.4) called Oracle WebLogic Active GridLink for RAC. This how-to compares these approaches.
The WebLogic Server JDBC subsystem has supported Oracle RAC since WLS version 9.0, originally developed for Oracle9i RAC. This support is based on a particular type of data source configuration, called a multi data source. A multi data source is a data source abstraction over one or more individual data sources. It serves JDBC connections from each of the member data sources according to a specified policy. A RAC multi data source configuration requires that each member data source obtain connections to a particular RAC instance.
The multi data source solution has multiple limitations. First the configuration is complex. An Oracle RAC multi data source requires n+1 JDBC modules, where n is the number of nodes in the cluster. For Oracle RAC service configurations, a separate multi data source is required for each defined service. In addition, the configuration itself is static and requires administrative intervention to add or remove data sources when changes are made to the RAC cluster topology.
Connection polling is the mechanism used to determine the viability of individual JDBC connections and to detect changes in the RAC cluster topology for multi data source. Although effective, performing SQL operations on individual connections comes at the expense of additional runtime overhead, and potentially delayed detection of RAC node failures.
The round-robin load balancing employed by the multi data source implementation distributes work evenly across all member data sources. Finer grained control is desirable for situations where Oracle RAC instances exhibit different performance/response time characteristics. The XA affinity is provided by each MDS. When several MDSs are enlisted in a global transaction, it is possible that connections could be obtained from different RAC instances. This results in branches of the same global transaction being processed by separate RAC instances. Although supported in more recent Oracle RAC versions, it is less than optimal from a performance perspective.
In Oracle WebLogic Server 10.3.4, a single data source implementation has been introduced to support an Oracle RAC cluster. It responds to FAN events to provide Fast Connection Failover (FCF), Runtime Connection Load-Balancing (RCLB), and RAC instance graceful shutdown. XA affinity is supported at the global transaction Id level. The new feature is called WebLogic Active GridLink for RAC; which is implemented as the GridLink data source within WebLogic Server.
The Universal Connection Pool Java library has been integrated with WebLogic Server to utilize WebLogic Server work manager and timer manager implementations for internal task scheduling and timer event processing for improved resource utilization and manageability. The RAC integration capabilities of UCP have been utilized by the Oracle RAC data source implementation to provide the FCF, RCLB and affinity features.
To simplify and consolidate its support for Oracle RAC, WebLogic Server has provided a single data source that is enhanced to support the capabilities of Oracle RAC. It provides a single connection pool/data source within Oracle WebLogic Server that supports the consumption of database services in an unrestricted manner. This is the key foundation for providing deeper integration with Oracle RAC.
This single data source implementation in Oracle WebLogic Server supports the full and unrestricted use of database services as the connection target for a data source. The active management of the connections in the pool is based on static settings configured on the connection pool itself (min/max capacity, timeouts, etc.) and real time information the connection pool receives from the RAC ONS subsystem that advises the “client” of any state changes within the RAC cluster.
A GridLink data source uses Fast Connection Failover to:
To provide better throughput and more efficient use of resources, the Oracle Database provides a runtime load balancing service to distribute connections across the RAC instance based on performance goals set by a DBA. The load balancing advisory service issues FAN events that advise clients on the current state of the cluster including advice on where to direct connections. GridLink data sources provide load balancing in XA and non-XA environments. GridLink data sources use runtime connection load balancing to distribute connections to Oracle RAC instances based on Oracle FAN events issued by the database. This simplifies data source configuration and improves performance as the database drives load balancing of connections through the GridLink data source, independent of the database topology.
Runtime Connection Load Balancing allows WebLogic Server to:
XA affinity is a performance feature that ensures that all database operations performed on a RAC cluster within the context of a global transaction are directed to the same RAC instance. Affinity will be established based on the global transaction id, instead of by individual data source, to ensure that connections obtained from different data sources that are configured for the same RAC cluster are all associated with the same RAC instance.
The affinity capabilities provided by UCP will be leveraged to assign connections based on GTRID even when different data sources are accessed on the same, and separate, WebLogic Server instances. The Last Logging Resource two-phase commit optimization will be supported by the RAC data source and will also participate in XA affinity. The first connection request for an XA transaction is load balanced using RCLB and is assigned an Affinity context. All subsequent connection requests are routed to the same Oracle RAC instance using the Affinity context of the first connection.
In the how-to that follows, we will show you how easy to configure a GridLink Data Source and use it in your application to leverage great features of Fast Connection Failover, Runtime Connection Load Balancing and XA Affinity.
Before we can create a JDBC GridLink Data Source we need to verify from SQL*PLus and various other commands that we have a valid Oracle RAC 11g Release 2 SCAN setup in place.
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 as shown below.
pas_srv = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = apctcsol1)(PORT = 1521)) (LOAD_BALANCE = YES) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pas_srv) ) )
Test using SQL*Plus as shown below to verify you can connect.
[oradb1@auw2k3 admin]$ sqlplus scott/tiger@pas_srv SQL*Plus: Release 220.127.116.11.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 18.104.22.168.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL>
Issue a query as follows to verify the instance you are connected to in the cluster.
Repeat step #2 and #3 a few times to verify that you are connecting to different instances within your cluster. You can see from the output below that we have connected to two different instances on different nodes: auw2k3 and auw2k4.
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
Confirm the SCAN listener is running via the three 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
Create the JDBC GridLink Data Source
Log into WebLogic console using a URL formatted like that below.
JNDI Name: jdbc/gridlinkDS
SQL> show parameter remote_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_listener string apctcsol1:1521 SQL>
[oragrid@auw2k3 grid]$ onsctl debug
HTTP/1.1 200 OK
== auw2k3:6200 17995 11/01/31 21:31:05 ==
======== ONS ========
IP ADDRESS PORT TIME SEQUENCE FLAGS
--------------------------------------- ----- --------
10.187.80.187 6200 4d4607c1 00000002 00000008
TYPE BIND ADDRESS PORT SOCKET
Local 127.0.0.1 6100 5
Remote any 6200 7
Remote any 6200 -
INSTANCE NAME TIME SEQUENCE FLAGS DEFER
---------------------------------------- -------- --------
dbInstance_auw2k4_6200 4d4607c1 0000000e 00000002 0
Connection Topology: (2)
IP PORT VERS TIME
10.187.80.187 6200 4 4d4607c1=
10.187.80.185 6200 4 4d4607c1
ID CONNECTION ADDRESS PORT FLAGS SENDQ REF WSAQ
-------- --------------------------------------- -----
0 10.187.80.185 6200 010405 00000 001
ID CONNECTION ADDRESS PORT FLAGS SENDQ REF SUB W
-------- --------------------------------------- -----
1 internal 0 01008a 00000 001 002
3 127.0.0.1 6100 01001a 00000 001 001
5 127.0.0.1 6100 01001a 00000 001 001
6 127.0.0.1 6100 01001a 00000 001 001
7 127.0.0.1 6100 01001a 00000 001 000
request 127.0.0.1 6100 03201a 00000 001 000
Worker Ticket: 2058/2058, Last: 11/01/31 21:30:47
Received: Total 16 (Internal 2), in Receive Q: 0
Processed: Total 16, in Process Q: 0
Message: 1, Link: 1, Ack: 1, Match: 1
<?xml version='1.0' encoding='UTF-8'?>
<?test-table-name>SQL SELECT 1 FROM DUAL<?/test-table-name>
The following demo can be used to verify your JDBC GridLink Data Source from a Web application.
[oradb1@auw2k3 admin]$ sqlplus / as sysdba
SQL*Plus: Release 22.214.171.124.0 Production on Tue Feb 1 09:34:50 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown abort; ORACLE instance shut down.
SQL> startup; ORACLE instance started. Total System Global Area 790941696 bytes Fixed Size 1347084 bytes Variable Size 587203060 bytes Database Buffers 197132288 bytes Redo Buffers 5259264 bytes Database mounted. Database opened. SQL> <
For a graphical view use the following steps: