How-To: Use Oracle WebLogic Server with a JDBC GridLink Data Source

This recently created how-to I created show's how to configure and test your new Oracle WebLogic 10.3.4 JDBC GridLink Data Source. The demo here is using an 11g R2 RAC cluster with SCAN. The link is as follows which includes a web test client.

http://www.oracle.com/technetwork/middleware/weblogic/wls-jdbc-gridlink-howto-333331.html

JRuby Oracle RAC 11g R2 SCAN Client Demo

Previously I created a JDBC client against an 11g R2 RAC client using SCAN as shown here. That example simply ensures we are load balancing between the RAC nodes in the cluster because from the JDBC URL we would not even know we are connecting to a RAC cluster.

jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv

In the example below we use some JRuby code to do the exact same thing. We are using the 11.2.0.2 Oracle JDBC Driver and an 11.2.0.2 RAC cluster as well.

JRuby Code

require 'java'  
require 'C:/jdev/jdcbdrivers/11.2/11202/ojdbc6.jar'  
  
java_import java.sql.Statement
java_import java.sql.Connection
java_import java.sql.SQLException
java_import java.sql.DatabaseMetaData

java_import 'oracle.jdbc.OracleDriver'
java_import 'oracle.jdbc.pool.OracleDataSource'

INSTANCE_SQL = <<EOF
select sys_context('userenv', 'instance_name'),
sys_context('userenv', 'server_host'), 
sys_context('userenv', 'service_name')
from dual
EOF

class MyOracleDataSource   
  def initialize(user, passwd, url)  
    @user, @passwd, @url = user, passwd, url
      
    @ods = OracleDataSource.new
    @ods.set_user user
    @ods.set_password passwd
    @ods.set_url url
  end  
   
  # add getters and setters for all attrributes we wish to expose  
  attr_reader :user, :passwd, :url

  def get_connection()
    @ods.get_connection
  end
  
  def create_statement()
    @connection.create_statement
  end
  
  def to_s  
    "OracleDataSource  [user=#{@user}, passwd=#{@passwd}, " +  
    "url=#{@url}]"  
  end  

  def self.create(user, passwd, url)
    ods = new(user, passwd, url)
  rescue
    puts "\n** Error occured **\n"  
    puts "Failed executing SCAN Load Blance test from JRuby ", $!, "\n"
  end
end


user = "scott"
passwd = "tiger"
url = "jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv"

print "Run at #{Time.now} using JRuby #{RUBY_VERSION}\n\n"  

mods = MyOracleDataSource.create(user, passwd, url)

# empty array
conns = []

# obtain 5 connections
for i in 1..5 
  conns[i] = mods.get_connection
end

# determine instance details
for i in 1..5
  if (i == 1)
    meta = conns[i].get_meta_data
    puts "=============\nDatabase Product Name is ... #{meta.getDatabaseProductName()}"  
    puts "Database Product Version is  #{meta.getDatabaseProductVersion()}"  
    puts "=============\nJDBC Driver Name is ........ #{meta.getDriverName()}" 
    puts "JDBC Driver Version is ..... #{meta.getDriverVersion()}"
    puts "JDBC Driver Major Version is #{meta.getDriverMajorVersion()}"  
    puts "JDBC Driver Minor Version is #{meta.getDriverMinorVersion()}" 
    puts "============="    
  end
  
  stmt = conns[i].create_statement
  rset = stmt.execute_query INSTANCE_SQL
  rset.next
  puts "Connection #{i} : instance [#{rset.get_string 1}], " + 
       "host[#{rset.get_string 2}], service[#{rset.get_string 3}] "
  
  rset.close
  stmt.close
end

#close the 5 connections
for i in 1..5
  conns[i].close
end

print "\nEnded at #{Time.now}"

Output

C:\jdev\scripting\demos\jruby\jdbc\scan-load-balance-test>jruby scan-lbt.rb
Run at Mon Jan 31 14:28:11 +1100 2011 using JRuby 1.8.7


=============
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 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 [A11], host[auw2k3], service[pas_srv]
Connection 5 : instance [A12], host[auw2k4], service[pas_srv]


Ended at Mon Jan 31 14:28:12 +1100 2011

SCAN 11g R2 JDBC Load Balance Test

Now that I have a SCAN 11g R2 setup I was able to quickly verify the load balancing of connections using a SCAN URL as shown below. I find it very useful to have such a simple JDBC URL. No matter what nodes are added or removed I never have to alter my client JDBC URL again for this RAC cluster.

Output

Test Started at Mon Feb 08 13:59:21 EST 2010
Obtaining 5 connections

=============
Database Product Name is ... Oracle
Database Product Version is 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
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.1.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============
Connection #0 : instance[J11G22], host[auw2k4], service[J11G2]
Connection #1 : instance[J11G21], host[auw2k3], service[J11G2]
Connection #2 : instance[J11G22], host[auw2k4], service[J11G2]
Connection #3 : instance[J11G21], host[auw2k3], service[J11G2]
Connection #4 : instance[J11G22], host[auw2k4], service[J11G2]
Closing Connections
Test Ended at Mon Feb 08 13:59:22 EST 2010

Code

  
package au.support.jdbc.scan;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.util.Date;

import oracle.jdbc.pool.OracleDataSource;

public class LoadBalanceTest
{
  private OracleDataSource ods = null;
  public final String userId = "scott";
  public final String password = "tiger";
  
  private static final String url =
    "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
    "(HOST=apctcsol1.au.oracle.com)(PORT=1521))" +
    "(CONNECT_DATA=(SERVICE_NAME=J11G2)))";
  
  public LoadBalanceTest() throws SQLException
  {
    ods = new OracleDataSource();
    ods.setUser(userId);
    ods.setPassword(password);
    ods.setURL(url);  
  }

  public Connection getConnection() throws SQLException
  {
    return ods.getConnection();
  }
  
  public void run () throws SQLException
  {
    Connection[] connArray = new Connection[5];
    
    System.out.println("Obtaining 5 connections");
    for (int i = 0; i < connArray.length; i++)
    {
      connArray[i] = getConnection();
    }
    
    for (int j = 0; j < connArray.length; j++)
    {
      if (j == 0)
      {
        DatabaseMetaData meta = connArray[j].getMetaData ();

        // gets driver info:

        System.out.println("\n=============\nDatabase Product Name is ... " +
        meta.getDatabaseProductName());
        System.out.println("Database Product Version is  " +
        meta.getDatabaseProductVersion());
        System.out.println("=============\nJDBC Driver Name is ........ " +
        meta.getDriverName());
        System.out.println("JDBC Driver Version is ..... " +
        meta.getDriverVersion());
        System.out.println("JDBC Driver Major Version is " +
        meta.getDriverMajorVersion());
        System.out.println("JDBC Driver Minor Version is " +
        meta.getDriverMinorVersion());
        System.out.println("=============");                
      }
      
      getInstanceDetails(connArray[j], j);
    } 
    
    System.out.println("Closing Connections");
    for (int y = 0; y < connArray.length; y++)
    {
      connArray[y].close();
    } 
  }

  public void getInstanceDetails (Connection conn, int i) throws SQLException
  {
    String sql = 
      "select sys_context('userenv', 'instance_name'), " + 
      "sys_context('userenv', 'server_host'), " + 
      "sys_context('userenv', 'service_name') " + 
      "from dual";
    
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery(sql);
    while (rset.next())
    {
      System.out.println
        ("Connection #" + i + " : instance[" + rset.getString(1) + "], host[" + 
         rset.getString(2) + "], service[" + rset.getString(3) + "]");
    }
   
    stmt.close();
    rset.close();
  }
  
  public static void main(String[] args)
  {
    LoadBalanceTest loadBalanceTest;
    try
    {
      System.out.println("Test Started at " + new Date());
      loadBalanceTest = new LoadBalanceTest();
      loadBalanceTest.run();
      System.out.println("Test Ended at " + new Date());
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      System.exit(1);
    }
  }
}
 

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 ~]$