How-To: Use Oracle WebLogic Server with a JDBC GridLink Data Source
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.
Multi Data Source
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.
Active GridLink for Oracle RAC
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.
FastConnection Failover
A GridLink data source uses Fast Connection Failover to:
- Provide rapid failure detection
- Abort and remove invalid connections from the connection pool
- Perform graceful shutdown for planned and unplanned Oracle RAC node outages
- Adapt to changes in topology, such as addingor removing a node
- Distribute runtime work requests to all active Oracle RAC instances, including those rejoining a cluster
Runtime Connection Load Balancing
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:
- Adjust the distribution of work based on back end node capacities such as CPU, availability, and response time
- React to changes in Oracle RAC topology
- Manage pooled connections for high performance and scalability
XA Affinity
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.
Verify Your Oracle RAC Cluster
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 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> - 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.
http://<host>:<port>/console - Under the Domain Structure tree, expand the Services node.
- Click on Data Sources.
- Next to the New button press the ^ button to reveal the three menu options and select Gridlink Data Source.
- Enter the following details.
Name: jdbc/gridlinkDS
JNDI Name: jdbc/gridlinkDS - Ensure the Is this XA driver? check box remains unchecked as shown below.
- Click Next.
- Click Next accepting the defaults on the Transaction Options page.
- Select the check box Enter complete JDBC URL. Given we are using SCAN this is the simplest way to connect.
- Click Next.
- Enter a Complete JDBC URL as follows. Be sure to use an JDBC URL with a leading forward slash to indicate your using a SERVICE. SCAN will always be using a SERVICE.
Format:
jdbc:oracle:thin:@{scan-listener-host}:{scan-listener-port}/{service-name}
Example:
jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srvOne way to determine the SCAN host/port is by connecting to any of the RAC instances in the cluster as shown below.
SQL> show parameter remote_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_listenerstring apctcsol1:1521 SQL> - Enter in your username and password for the database connections as shown below.
- Click Next.
- Click the button Test All Listeners to ensure a valid setup as shown below.
- Click Next.
- For the ONS client configuration add each RAC node and the remote ONS port as shown below.
auw2k3.au.oracle.com:6200
auw2k4.au.oracle.com:6200It's vital at this step for you to confirm that the remote ONS ports are correct. One way to do this is to get the output of onsctl debug from the CRS_HOME as shown below.
[oragrid@auw2k3 grid]$ onsctl debug HTTP/1.1 200 OK Content-Length: 2608 Content-Type: text/html Response: == auw2k3:6200 17995 11/01/31 21:31:05 == Home: /u01/app/11.2.0/grid ======== ONS ======== IP ADDRESS PORT TIME SEQUENCE FLAGS --------------------------------------- ----- -------- 10.187.80.187 6200 4d4607c1 00000002 00000008 Listener: TYPE BIND ADDRESS PORT SOCKET -------- --------------------------------------- Local 127.0.0.1 6100 5 Remote any 6200 7 Remote any 6200 - Servers: (1) INSTANCE NAME TIME SEQUENCE FLAGS DEFER ---------------------------------------- -------- -------- dbInstance_auw2k4_6200 4d4607c1 0000000e 00000002 0 10.187.80.185 6200 Connection Topology: (2) IP PORT VERS TIME --------------------------------------- 10.187.80.187 6200 4 4d4607c1= ** 10.187.80.185 6200 10.187.80.185 6200 4 4d4607c1 ** 10.187.80.187 6200 Server connections: ID CONNECTION ADDRESS PORT FLAGS SENDQ REF WSAQ -------- --------------------------------------- ----- 0 10.187.80.185 6200 010405 00000 001 Client connections: 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 THREAD FLAGS -------- -------- b5d1db90 00000012 b5d15b90 00000012 b5d0db90 00000012 Resources: Notifications: Received: Total 16 (Internal 2), in Receive Q: 0 Processed: Total 16, in Process Q: 0 Pool Counts: Message: 1, Link: 1, Ack: 1, Match: 1 - Verify the setup as shown below and click Next. We won't be using a Wallet in this how-to.
- Click the Test All ONS Nodes as shown below and verify it is able to communicate with the remote ONS ports on the RAC nodes.
- Click Next.
- Target the JDBC Gridlink Data Source to your managed server as shown below. Here we use a managed server called apple.
- Click Finish.
- Verify the JDBC GridLink Data Source is created as shown below.
- Finally we will ensure the GridLink Data Source has an initial capacity of five connections, which we do by clicking on the newly created data source.
- Click on the Connection Pool tab.
- Set the Initial Capacity to 5 and Maximum Capacity to 50.
- Click Save. Your newly created data source would look as follows:
<?xml version='1.0' encoding='UTF-8'?> <?jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source" xmlns:sec="http://xmlns.oracle.com/weblogic/security" xmlns:wls="http://xmlns.oracle.com/weblogic/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/weblogic/jdbc-data-source http://xmlns.oracle.com/weblogic/jdbc-data-source/1.0/jdbc-data-source.xsd"> <?name>jdbc/gridlinkDS<?/name> <?jdbc-driver-params> <?url>jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv<?/url> <?driver-name>oracle.jdbc.OracleDriver<?/driver-name> <?properties> <?property> <?name>user<?/name> <?value>scott<?/value> <?/property> <?/properties> <?password-encrypted>{AES}V1hP4cnmf/riMy6IQmYzFa57tMup1zorD1EYftoN4PQ=<?/password-encrypted> <?/jdbc-driver-params> <?jdbc-connection-pool-params> <?initial-capacity>5<?/initial-capacity> <?max-capacity>50<?/max-capacity> <?capacity-increment>1<?/capacity-increment> <?test-table-name>SQL SELECT 1 FROM DUAL<?/test-table-name> <?statement-cache-size>10<?/statement-cache-size> <?statement-cache-type>LRU<?/statement-cache-type> <?/jdbc-connection-pool-params> <?jdbc-data-source-params> <?jndi-name>jdbc/gridlinkDS<?/jndi-name> <?global-transactions-protocol>OnePhaseCommit<?/global-transactions-protocol> <?/jdbc-data-source-params> <?jdbc-oracle-params> <?fan-enabled>true<?/fan-enabled> <?ons-node-list>auw2k3.au.oracle.com:6200,auw2k4.au.oracle.com:6200<?/ons-node-list> <?ons-wallet-file><?/ons-wallet-file> <?/jdbc-oracle-params> <?/jdbc-data-source>
Test The Data Source
The following demo can be used to verify your JDBC GridLink Data Source from a Web application.
- Download and extract the file gridlink_ha.zip onto your file system.
- Deploy the file gridlank_ha.war to your managed server. It should show as follows in the WebLogic console once deployed.
- Invoke the application as shown below.
http://<host>:<port>/gridlinkha/
- At this point let's simply retrieve five connections and test our JDBC gridLink Data Source to verify that it load-balances across our Oracle RAC nodes as shown below. If you followed this how-to then the data source location will default to the correct name; if not, be sure to enter the JNDI location you used.
- Press the Test JDBC GridLink Data Source button.
- At this point we are going to shut down one of our Oracle RAC instances as shown below. In the example below we perform a ungraceful shutdown to simulate a instance crash.
[oradb1@auw2k3 admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 1 09:34:50 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> shutdown abort; ORACLE instance shut down. SQL> - Run the test again to verify that instance is no longer being returned by our JDBC GridLink Data Source. You will see node auw2k3 no longer exists.
- Restart the instance which was brought 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> < - Now run the Web test again to verify it has brought the instance back into the JDBC GridLink Data Source.
- At this point we can monitor the activity of the data source by logging into the console application using an URL as follows.
http://<host>:<port>/console
- Under the Domain Structure tree and expand the Services node.
- Click on Data Sources.
- Click on jdbc/gridlinkDS.
- Click on the Monitoring tab.
- Click on the managed server to which your GridLink Data Source is targeted.
- You should see something as follows, which shows the instances to which the GridLink Data Source is connected.
For a graphical view use the following steps:
- Go to http://<host>:<port>/console/dashboard.
- Click the + symbol for the managed server to which the GridLink Data Source is targeted.
- Click on JDBC Data Sources for {maned-server-name}.
- Press the Start icon at the top of the screen to begin the monitoring. Ensure you have run a few tests to get some connections and then verify the graphs below.
Conclusion
For more information on Oracle WebLogic 10.3.4 and GridLink Data Sources see the links below.