JRuby Script to Verify Your 11g R2 RAC Setup using SCAN

The following demo can be used to verify failover using an 11g R2 (11.2.0.2) RAC cluster from a JRuby script. For this example we are using an Oracle UCP Pool setup to use FCF to receive FAN events / notifications.

1. Download ucp.jar and ojdbc6.jar from the links below.

ojdbc6.jar (11.2.0.2)
ucp.jar (11.2.0.1)

2. In a "lib" sub directory create a JRuby script called "ucppool-singleton.rb" with content as follows

require 'java'
require 'C:/jdev/jdcbdrivers/11.2/11202/ojdbc6.jar'
require 'C:/jdev/jdcbdrivers/11.2/11202/ons.jar'
require 'C:/jdev/jdcbdrivers/11.2/ucp/ucp.jar'

java_import 'oracle.ucp.jdbc.PoolDataSource'
java_import 'oracle.ucp.jdbc.PoolDataSourceFactory'

API_VERSION = 1.0

class MyOracleUcpPool
   
   def load_properties(properties_filename)
    properties = {}
    File.open(properties_filename, 'r') do |properties_file|
      properties_file.read.each_line do |line|
        line.strip!
        if (line[0] != ?# and line[0] != ?=)
          i = line.index('=')
          if (i)
            properties[line[0..i - 1].strip] = line[i + 1..-1].strip
          else
            properties[line] = ''
          end
        end
      end      
    end
    return properties
  end
  
  def initialize()
    props = load_properties(File.dirname(__FILE__) + "/ucp.properties")

    @user = props["user"]
    @passwd = props["password"]
    @url = props["url"]
    @minsize = props["minpoolsize"].to_i
    @maxsize = props["maxpoolsize"].to_i
    @initialsize = props["initialpoolsize"].to_i
    @factoryclassname = props["connectionfactory"]
    @onsconfig = props["onsconfig"]
    
    #create pool for use here
    @pds = PoolDataSourceFactory.getPoolDataSource
    @pds.set_user user
    @pds.set_password passwd
    @pds.set_url url
    @pds.set_connection_factory_class_name factoryclassname
    @pds.set_connection_pool_name "ruby-fcfucppool"
    @pds.set_initial_pool_size initialsize
    @pds.set_min_pool_size minsize
    @pds.set_max_pool_size maxsize
    @pds.setONSConfiguration(onsconfig)
    @pds.set_fast_connection_failover_enabled true
    
  end

  #add getters and setters for all attrributes
  attr_reader :user, :passwd, :url, :minsize, :maxsize, :initialsize, :factoryclassname, :onsconfig
  
  @@instance = MyOracleUcpPool.new
  @pds = nil
  
  def self.instance()
    return @@instance
  end
  
  def get_connection()
    return @pds.get_connection
  end
  
  def return_connection(conn)
    conn.close
  end

  def to_s
    "MyOracleUcpPool [user=#{@user}, passwd=#{@passwd}, " +
    "url=#{@url}, minsize=#{@minsize}, maxsize=#{@maxsize}, " +
    "initialsize=#{@initialsize}], factoryclassname=#{factoryclassname}"
  end
  alias_method :to_string, :to_s
    
  def display_pool_details()
    return "\n** FCF Enabled UCP Pool Details **\n" + 
            "NumberOfAvailableConnections: #{@pds.getAvailableConnectionsCount()}\n" +
            "BorrowedConnectionsCount: #{@pds.getBorrowedConnectionsCount()}\n";
  end
  
  private_class_method :new

 end    

3. In the same "lib" directory create a properties file called "ucp.properties" with content as follows. Ensure you set the correct details for your 11g RAC Cluster.

user=scott
password=tiger
url=jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv
connectionfactory=oracle.jdbc.pool.OracleDataSource
initialpoolsize=5
minpoolsize=5
maxpoolsize=20
onsconfig=nodes=auw2k3.au.oracle.com:6200,auw2k4.au.oracle.com:6200

4. Edit the 3 lines below in the file "lib/ucppool-singleton.rb" to indicate the PATH to the 2 JAR files downloaded at step #1. For ons.jar you would obtain that from your CRS_HOME or RAC node at "$ORACLE_HOME/opmn/lib/ons.jar".

require 'C:/jdev/jdcbdrivers/11.2/11202/ojdbc6.jar'

require 'C:/jdev/jdcbdrivers/11.2/11202/ons.jar'

require 'C:/jdev/jdcbdrivers/11.2/ucp/ucp.jar'

5. Create a JRuby script called "ucp_fcf_test.rb" with content as follows.

require 'lib/ucppool-singleton'

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

def get_instance_details (conn, index)
  stmt = nil
  rset = nil
  
  begin
    stmt = conn.create_statement  
    rset = stmt.execute_query INSTANCE_SQL  
    rset.next  
    result = "\n--> Connection #{index} : instance [#{rset.get_string 1}], " +   
         "host[#{rset.get_string 2}], service[#{rset.get_string 3}]"  
    
    rset.close
    stmt.close
    
    return result
  rescue
    if (!rset.nil?)
      rset.close
    end
    if (!stmt.nil?)
      stmt.close
    end
    raise
  end
end

print "Run at ", Time.now , "\n"

conn = nil
ucppool = nil
i = 0

begin

  #use as a singleton class enusuring only one instance can exist
  ucppool = MyOracleUcpPool.instance()
  print ucppool , "\n"
  
  while (true)
    #get 5 connections from pool and insert into ruby hash
    conn = []
    for y in 0..4
      conn[y] = ucppool.get_connection
    end
    
    #  print instance details for each connection
    x = 0
    conn.each {|connection| print get_instance_details connection, x += 1}
    
    #print pool details
    puts
    print ucppool.display_pool_details

    #return connections
    conn.each {|connection| ucppool.return_connection connection}
    
    # sleep for 20 seconds
    puts "\nSleeping for 20 seconds....\n"
    sleep 20
  end
  
rescue 
  print "\n** Error occured **\n"
 print "Failed executing FCF UCP Pool demo from JRuby ", $!, "\n"
  
end

print "\nEnded at ", Time.now , "\n"

6. Connect to one of your RAC node instances in preparation to perform a ungraceful instance crash.

[oradb1@auw2k3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 3 21:27:13 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>

7.  Run the JRuby script "ucp_fcf_test.rb" as shown below.

This script will create a UCP Pool setup for use with FCF,  obtain 5 connections and then sleep for 20 seconds and continue the process of obtaining another 5 connections and so on until the program is ended using CNTRL-C.

> jruby ucp_fcf_test.rb

Run at Thu Feb 03 21:30:58 +1100 2011

MyOracleUcpPool [user=scott, passwd=tiger, url=jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv, minsize=5, maxsize=20, initialsize=5]

, factoryclassname=oracle.jdbc.pool.OracleDataSource

--> Connection 1 : instance [A11], host[auw2k3], 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]

--> Connection 5 : instance [A11], host[auw2k3], service[pas_srv]

** FCF Enabled UCP Pool Details **

NumberOfAvailableConnections: 0

BorrowedConnectionsCount: 5

Sleeping for 20 seconds....

8. Now once the program output shows it has obtained 5 connections and is currently sleeping return to your SQL*Plus session at step #6 and perform an ungraceful shutdown using "shutdown abort" as shown below.

SQL> shutdown abort;
ORACLE instance shut down. 

9. Return to the JRuby script and wait for it to wake up and verify that the node which has crashed is no longer in the list of connected instances.

Run at Thu Feb 03 21:30:58 +1100 2011

MyOracleUcpPool [user=scott, passwd=tiger, url=jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv, minsize=5, maxsize=20, initialsize=5]

, factoryclassname=oracle.jdbc.pool.OracleDataSource

--> Connection 1 : instance [A11], host[auw2k3], 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]

--> Connection 5 : instance [A11], host[auw2k3], service[pas_srv]

** FCF Enabled UCP Pool Details **

NumberOfAvailableConnections: 0

BorrowedConnectionsCount: 5

Sleeping for 20 seconds....

*** RAC instance on auw2k3 shutdown at this point **

--> Connection 1 : instance [A12], host[auw2k4], service[pas_srv]

--> Connection 2 : instance [A12], host[auw2k4], service[pas_srv]

--> Connection 3 : instance [A12], host[auw2k4], service[pas_srv]

--> Connection 4 : instance [A12], host[auw2k4], service[pas_srv]

--> Connection 5 : instance [A12], host[auw2k4], service[pas_srv]

** FCF Enabled UCP Pool Details **

NumberOfAvailableConnections: 0

BorrowedConnectionsCount: 5

Sleeping for 20 seconds....

10. Return to your SQL*Plus session at step #6 and start the instance back up using "startup".

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. 

11. Verify from the running JRuby script that connections from the recently returned instance are coming up in the list as shown below.

.....

Sleeping for 20 seconds....

*** RAC instance on auw2k3 started at this point **

--> Connection 1 : instance [A12], host[auw2k4], service[pas_srv]

--> Connection 2 : instance [A11], host[auw2k3], service[pas_srv]

--> Connection 3 : instance [A11], host[auw2k3], service[pas_srv]

--> Connection 4 : instance [A11], host[auw2k3], service[pas_srv]

--> Connection 5 : instance [A12], host[auw2k4], service[pas_srv]

** FCF Enabled UCP Pool Details **

NumberOfAvailableConnections: 5

BorrowedConnectionsCount: 5

Sleeping for 20 seconds....

For more information on using SCAN with 11g R2 RAC see the white paper below.

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