Thursday, 17 February 2011

JRuby Script to Monitor a Oracle WebLogic GridLink Data Source Remotely

In WebLogic 10.3.4 release, a single data source implementation has been introduced to support Oracle RAC cluster. 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. For more information on this see the URL below.

http://download.oracle.com/docs/cd/E17904_01/web.1111/e13737/gridlink_datasources.htm

In the example below we show how we can monitor this new Active GridLink Data Source from a JRuby script to get runtime information as the Data Source is in use from the MBean. In this example we are assuming our Data Source has a name "jdbc/gridlinkDS" which was created in the console of WebLogic itself.

1. Determine the remote connection URL to connect to WebLogic 10.3.4. The URL will be something as follows which shows are are connecting to the runtime MBean server of the managed server itself. In short this will be where you have targeted your GridLink Data Source to, it's not the ADMIN SERVER it's the managed server itself.

Format:

service:jmx:iiop://{hostname}:{managed-server-port}/jndi/weblogic.management.mbeanservers.runtime

Example:

service:jmx:iiop://wayne-p2.au.oracle.com:7003/jndi/weblogic.management.mbeanservers.runtime

2. At this point it's worth connecting using JConsole remotely to the WebLogic server using the URL above as this will help you see how the MBeans are structured and what you then need to alter the JRuby script with. Start jconsole ensuring your using wlfullclient.jar in the classpath from $WLS_HOME/server/lib directory.

Windows Example: (Connecting to a remote linux x86 server)

> jconsole -J-Djava.class.path=C:\jdev\weblogic-client\1034\wlfullclient.jar;C:\jdev\jdk\jdk1.6.0_21\lib\jconsole.jar -J-Djmx.remote.protocol.provider.pkgs=weblogic.management.remote

3. Connect using the service url from #1 above and with your username/password for the managed server where the GridLink Data Source is targeted for.



4. Click on the "MBeans" tab

5. At this point we want to determine what the MBean for the GridLink Data Source is but also what the MBean names are for the instances within the RAC cluster. To do that click on "com.bea" using the + symbol to expand the tree.

6. Scroll down and locate your GridLink Data Source in this case it's "jdbc/gridlinkDS" , use the + symbol to expand the tree

7. Click on the + symbol next to "JDBCOracleDataSourceRuntime"

8. Now actually click on "JDBCOracleDataSourceRuntime" to reveal the MBean name for the actual GidLink Data Source. Make a note of it which is displayed in the field "Object Name" in the right hand side of the dialog.

com.bea:ServerRuntime=apple,Name=jdbc/gridlinkDS,Type=JDBCOracleDataSourceRuntime

9. Select the "Attributes" node in the tree to view the GridLink Data Source runtime attributes.

10. Double click on the "Instances" Value field to reveal the MBean names for the individual instances within your cluster. Make a note of them as we will need them shortly. For my example there as follows.

com.bea:ServerRuntime=apple,Name=A11,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS
com.bea:ServerRuntime=apple,Name=A12,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS

11. Create a test client as follows
require 'java'
require 'rubygems'
require 'jmx4r'
require 'C:/jdev/weblogic-client/1034/wlfullclient.jar'

java_import 'javax.management.ObjectName'
java_import java.lang.System

class MonitorGridLinkDataSource
  def initialize(user, passwd, url)
    System.setProperty("jmx.remote.protocol.provider.pkgs", "weblogic.management.remote")
    @user, @passwd, @url = user, passwd, url
       
    @conn = JMX::MBean.establish_connection :url => @url, :username => @user, :password => @passwd 
  end

  # add getters and setters for all attrributes we wish to expose    
  attr_reader :user, :passwd, :url, :conn
  
  def display_array (value)
    data = "" 
    value.each do |x|
      data += "\n\t" + x.to_s
    end
    return data
  end

  def display_attribute_data(conn, object_name, attribute)  
    s = conn.get_attribute object_name, attribute
    search_str = s.to_s
    if (/^\[Ljava.lang.String/.match(search_str)) or 
       (/^\[I/.match(search_str)) or
       (/^\[Ljavax.management.ObjectName/.match(search_str))
      # we have a array with data
      return display_array s
    else
      return search_str
    end 
  end
  
  def to_s    
    "MonitorGridLinkDataSource [user=#{@user}, passwd=#{@passwd}, " +    
    "url=#{@url}]"    
  end  
  
  def run(parent_ds_runtime_mbean_name, child_ds_instances)

    mbean = JMX::MBean.find_by_name parent_ds_runtime_mbean_name
    object_name = ObjectName.new parent_ds_runtime_mbean_name

    #
    # Display main JDBC grindlink Data Source MBean info
    # 
    printf "\n** JDBC GridLink Data Source mbean \"jdbc/gridlinkDS\" ** \n\n"

    # display attributes key/values
    mbean.attributes.each do |key, value|  
     puts "Name: #{value}, Value: #{display_attribute_data conn, object_name, value}\n"
    end

    #
    # Now display info about each RAC instances within the gridlink DS
    #
    # Note: there will be as many instances as your RAC cluster contains here, in my case 
    # just a 2 node RAC cluster here

    child_ds_instances.each {|mbean_name| 
      ds1_mbean = JMX::MBean.find_by_name mbean_name
      object_name1 = ObjectName.new mbean_name
      
      printf "\n** Instance MBean \n\t[#{mbean_name}] \n\n"
      ds1_mbean.attributes.each do |key, value|  
       puts "Name: #{value}, Value: #{display_attribute_data conn, object_name1, value}\n"
      end
    }
    
  end

end

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

#Connection details to MBeanServer within Weblogic 10.3.4 
url = "service:jmx:iiop://wayne-p2.au.oracle.com:7003/jndi/weblogic.management.mbeanservers.runtime"
username = "weblogic"
password = "welcome1"

# parent GridLink Data Source MBean name
parent_ds_runtime_mbean_name = 
      "com.bea:ServerRuntime=apple,Name=jdbc/gridlinkDS,Type=JDBCOracleDataSourceRuntime"
      
# child instance MBean array, add for each instance in the RAC cluster
child_ds_instances = 
  ["com.bea:ServerRuntime=apple,Name=A11,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS", 
   "com.bea:ServerRuntime=apple,Name=A12,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS"]

begin
  mon_grid_link_ds = MonitorGridLinkDataSource.new username, password, url
  puts mon_grid_link_ds
  while (true)
    mon_grid_link_ds.run parent_ds_runtime_mbean_name, child_ds_instances
    # sleep for 20 seconds
    puts "\nSleeping for 20 seconds....\n"   
    sleep 20
  end
rescue 
  print "\n** Error occured **\n"
 print "Failed executing GridLink Data Source Monitoring from JRuby ", $!, "\n"  
end

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

12. Edit the following lines as per the information we collected above.

require 'C:/jdev/weblogic-client/1034/wlfullclient.jar'
...

#Connection details to MBeanServer within Weblogic 10.3.4 
url = "service:jmx:iiop://wayne-p2.au.oracle.com:7003/jndi/weblogic.management.mbeanservers.runtime"
username = "weblogic"
password = "welcome1"

# parent GridLink Data Source MBean name
parent_ds_runtime_mbean_name = 
      "com.bea:ServerRuntime=apple,Name=jdbc/gridlinkDS,Type=JDBCOracleDataSourceRuntime"
      
# child instance MBean array, add for each instance in the RAC cluster
child_ds_instances = 
["com.bea:ServerRuntime=apple,Name=A11,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS", 
 "com.bea:ServerRuntime=apple,Name=A12,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS"]

...



Note: We previously installed "jmx4r" ruby gem to allow us to use JMX API to communicate with the WebLogic server as follows


> jruby -S gem install jmx4r

13. Run the JRuby script as follows it will continually run sleeping every 20 seconds to display runtime information regarding your GridLink Data Source. To end it simply use CNRL-C to stop it running.

> jruby gridlink-monitor.rb

Output

Run at Thu Feb 17 21:55:32 +1100 2011
MonitorGridLinkDataSource [user=weblogic, passwd=welcome1, url=service:jmx:iiop://wayne-p2.au.oracle.com:7003/jndi/weblogic.management.mbean
servers.runtime]


** JDBC GridLink Data Source mbean "jdbc/gridlinkDS" **


Name: ActiveConnectionsCurrentCount, Value: 0
Name: WaitingForConnectionFailureTotal, Value: 0
Name: Instances, Value:
        com.bea:ServerRuntime=apple,Name=A11,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS
        com.bea:ServerRuntime=apple,Name=A12,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS
Name: FailedRCLBBasedBorrowCount, Value: 0
Name: WaitingForConnectionTotal, Value: 0
Name: ConnectionsTotalCount, Value: 28
Name: SuccessfulRCLBBasedBorrowCount, Value: 0
Name: DriverVersion, Value: 11.2.0.2.0
Name: WaitingForConnectionCurrentCount, Value: 0
Name: LeakedConnectionCount, Value: 0
Name: HighestNumUnavailable, Value: 20
Name: PrepStmtCacheAccessCount, Value: 0
Name: LastTask, Value:
Name: Parent, Value: com.bea:Name=apple,Type=ServerRuntime
Name: WaitSecondsHighCount, Value: 0
Name: ActiveConnectionsHighCount, Value: 20
Name: FailedAffinityBasedBorrowCount, Value: 0
Name: PrepStmtCacheHitCount, Value: 0
Name: ActiveConnectionsAverageCount, Value: 0
Name: VersionJDBCDriver, Value: oracle.jdbc.OracleDriver
Name: DatabaseProductVersion, Value: 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
Name: SuccessfulAffinityBasedBorrowCount, Value: 0
Name: PrepStmtCacheDeleteCount, Value: 0
Name: ReserveRequestCount, Value: 36
Name: FailuresToReconnectCount, Value: 0
Name: WaitingForConnectionHighCount, Value: 0
Name: PrepStmtCacheMissCount, Value: 0
Name: Enabled, Value: true
Name: FailedReserveRequestCount, Value: 0
Name: Type, Value: JDBCOracleDataSourceRuntime
Name: HighestNumAvailable, Value: 20
Name: WorkManagerRuntimes, Value:
Name: DatabaseProductName, Value: Oracle
Name: ONSClientRuntime, Value: com.bea:ServerRuntime=apple,Name=jdbc/gridlinkDS,Type=ONSClientRuntime,JDBCOracleDataSourceRuntime=jdbc/gridl
inkDS
Name: Properties, Value: {user=scott}
Name: CurrCapacityHighCount, Value: 20
Name: PrepStmtCacheCurrentSize, Value: 0
Name: ServiceName, Value:
Name: JDBCDriverRuntime, Value:
Name: WaitingForConnectionSuccessTotal, Value: 0
Name: NumUnavailable, Value: 0
Name: PrepStmtCacheAddCount, Value: 0
Name: DriverName, Value: Oracle JDBC driver
Name: NumAvailable, Value: 20
Name: ModuleId, Value: jdbc/gridlinkDS
Name: Name, Value: jdbc/gridlinkDS
Name: State, Value: Running
Name: ConnectionDelayTime, Value: 133
Name: DeploymentState, Value: 2
Name: CurrCapacity, Value: 20


** Instance MBean
        [com.bea:ServerRuntime=apple,Name=A11,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS]


Name: Parent, Value: com.bea:ServerRuntime=apple,Name=jdbc/gridlinkDS,Type=JDBCOracleDataSourceRuntime
Name: Enabled, Value: true
Name: ActiveConnectionsCurrentCount, Value: 0
Name: Type, Value: JDBCOracleDataSourceInstanceRuntime
Name: NumAvailable, Value: 10
Name: Signature, Value: instance=A11,service=pas_srv,database=A1,host=auw2k3
Name: CurrentWeight, Value: 1
Name: Name, Value: A11
Name: ConnectionsTotalCount, Value: 14
Name: ReserveRequestCount, Value: 19
Name: State, Value: Enabled
Name: NumUnavailable, Value: 0
Name: CurrCapacity, Value: 10
Name: InstanceName, Value: A11


** Instance MBean
        [com.bea:ServerRuntime=apple,Name=A12,Type=JDBCOracleDataSourceInstanceRuntime,JDBCOracleDataSourceRuntime=jdbc/gridlinkDS]


Name: Parent, Value: com.bea:ServerRuntime=apple,Name=jdbc/gridlinkDS,Type=JDBCOracleDataSourceRuntime
Name: Enabled, Value: true
Name: ActiveConnectionsCurrentCount, Value: 0
Name: Type, Value: JDBCOracleDataSourceInstanceRuntime
Name: NumAvailable, Value: 10
Name: Signature, Value: instance=A12,service=pas_srv,database=A1,host=auw2k4
Name: CurrentWeight, Value: 1
Name: Name, Value: A12
Name: ConnectionsTotalCount, Value: 14
Name: ReserveRequestCount, Value: 17
Name: State, Value: Enabled
Name: NumUnavailable, Value: 0
Name: CurrCapacity, Value: 10
Name: InstanceName, Value: A12


Sleeping for 20 seconds....

Thursday, 10 February 2011

JRuby script to access Oracle Coherence MBean attributes

I found a ruby gem known as "jmx4r" to use JMX from JRuby. The aim here was to use that to display details of an MBean's attributes but found it not as easy as I thought it would be. Here are 2 ways I did that, to be honest using option 2 seemed the better way to do this.

Option 1

In this example here I have using Ruby to make dynamic method calls based on the attribute value.

require 'java'
require 'rubygems'
require 'jmx4r'

def display_array (meth)
  data = ""
  
  meth.call.send("each") do |x|
    data += "\n\t" + x.to_s
  end
  
  return data
end

def display_attribute_data(key, mbean)
  meth = mbean.method(key)
  data = ""
  s = meth.call.to_s
  if (/^\[Ljava.lang.String/.match(s))
    # we have a String[] array with data
    return display_array meth
  elsif (/^\[I/.match(s))
    return display_array meth
  else
    return s
  end

end

url = "service:jmx:rmi://localhost:3000/jndi/rmi://localhost:9000/server"
conn = JMX::MBean.establish_connection :url => url
    
mbean = JMX::MBean.find_by_name "Coherence:type=Cluster"

# display attributes key/values
mbean.attributes.each do |key, value|  
 puts "Name: #{key}, Value: #{display_attribute_data key, mbean}\n"
end

# puts "\n\n** PRETTY PRINT DISPLAY attributes/method descriptions ** \n"
#JMX::MBean.pretty_print "Coherence:type=Cluster", :url => url
Output

Name: local_member_id, Value: 2
Name: cluster_size, Value: 2
Name: license_mode, Value: Development
Name: members_departed, Value:
Name: refresh_time, Value: Thu Feb 10 14:40:21 EST 2011
Name: cluster_name, Value: cluster:0xC4DB
Name: running, Value: true
Name: oldest_member_id, Value: 1
Name: members, Value:
        Member(Id=1, Timestamp=2011-02-10 14:38:05.783, Address=10.187.114.243:8088, MachineId=50163, Location=machine:paslap-au,process:662
0, Role=CoherenceServer)
        Member(Id=2, Timestamp=2011-02-10 14:38:15.57, Address=10.187.114.243:8090, MachineId=50163, Location=machine:paslap-au,process:2992
, Role=TangosolNetMBeanConnector)
Name: version, Value: 3.6.0.0
Name: members_departure_count, Value: 0
Name: member_ids, Value:
        1
        2


Option 2

In this example I get a JAVA javax.management.ObjectName so I can easily obtain attribute values by name. In this example I don't make any effort to format the value for the attribute should it be an array type. I could easily just use the code I did in option 1 above.
require 'java'
require 'rubygems'
require 'jmx4r'

java_import 'javax.management.ObjectName'

def display_attribute_data(conn, object_name, attribute)  
  return conn.get_attribute object_name, attribute
end

url = "service:jmx:rmi://localhost:3000/jndi/rmi://localhost:9000/server"
conn = JMX::MBean.establish_connection :url => url
    
mbean = JMX::MBean.find_by_name "Coherence:type=Cluster"
object_name = ObjectName.new "Coherence:type=Cluster"

# display attributes key/values
mbean.attributes.each do |key, value|  
 puts "Name: #{value}, Value: #{display_attribute_data conn, object_name, value}\n"
end 
Output

Name: LocalMemberId, Value: 2
Name: ClusterSize, Value: 2
Name: LicenseMode, Value: Development
Name: MembersDeparted, Value: [Ljava.lang.String;@725967
Name: RefreshTime, Value: Thu Feb 10 14:41:25 EST 2011
Name: ClusterName, Value: cluster:0xC4DB
Name: Running, Value: true
Name: OldestMemberId, Value: 1
Name: Members, Value: [Ljava.lang.String;@6e3e5e
Name: Version, Value: 3.6.0.0
Name: MembersDepartureCount, Value: 0
Name: MemberIds, Value: [I@9b87f6

Friday, 4 February 2011

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