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
http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
