How To: Use a JRuby Script to Verify an Oracle RAC Setup using SCAN

by Pas Apicella    

Published April 2011

The Oracle Universal Connection Pool (UCP) for JDBC is a full-featured connection pool for managing database connections not only for Oracle but also for other non-Oracle databases. The advantage with using UCP with Oracle is that UCP JDBC connection pools provide a tight integration with various Oracle Real Application Clusters (RAC) features. These features include Fast Connection Failover (FCF), Run-Time Connection Load Balancing, and Connection Affinity.

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

Software Requirements

The demo below assumes the following are installed; the demo was tested using the these versions of the software. You can use the links below for more information including download pages but you will be instructed as required to download the software should you not already have it.

Demo

Install/ Setup

  1. Verify you have JRuby 1.5.6 installed as shown below. 
    
[pas@papicell-au]/export/home/pas/scripting/demos/jruby/ucp-fcf-scan> jruby -v
jruby 1.5.6 (ruby 1.8.7 patchlevel 249) (2010-12-03 9cf97c3) 
  (Java HotSpot(TM) Client VM 1.6.0_22) [x86-java]

    You can download JRuby here.
  2. Download ucp.jar and ojdbc6.jar from the links below.

    ojdbc6.jar (11.2.0.2)
    ucp.jar (11.2.0.1)
  3. Copy ojdbc6.jar, ucp.jar and ons.jar into $JRUBY_HOME/lib directory as shown below. For ons.jar you would obtain that from your CRS_HOME or RAC node at $ORACLE_HOME/opmn/lib/ons.jar. 
    
[pas@papicell-au]/export/home/pas/scripting/jruby-1.5.6/lib> ls
jruby.jar   native      ojdbc6.jar  ons.jar     ruby        ucp.jar
  4. Verify you have JDK 1.6 installed as shown below. We could easily use JDK 1.5 as well but this demo is based on JDK 1.6 and the supplied Oracle JDBC driver for the demo is a JDK 1.6 compliant JAR file so you won't be able to use JDK 1.5 without downloading ojdbc5.jar.
    
[pas@papicell-au]/export/home/pas/scripting/demos/jruby/ucp-fcf-scan> java -version
java version "1.6.0_22"
Java(TM) SE Runtime Environment (build 1.6.0_22-b04)
Java HotSpot(TM) Client VM (build 17.1-b03, mixed mode, sharing)

    You can download JDK 1.6 from here.

Running the Demo

  1. Download and unzip the source code for this demo here.

    Note: Once extracted the file system would look as follows. 
    
[pas@papicell-au]/export/home/pas/scripting/demos/jruby/ucp-fcf-scan> d
total 10
-rw-r--r--   1 pas      usergrp     1699 Feb  3 14:45 ucp_fcf_test.rb
drwxr-xr-x   2 pas      usergrp      512 Feb  3 21:12 lib/
drwxr-xr-x   3 pas      usergrp      512 Feb  9 07:48 ./
drwxr-xr-x   4 pas      usergrp      512 Feb  9 07:50 ../
  2. Edit "lib/ucp.properties" to set the connection properties for your 11g R2 RAC cluster as shown below. 
    
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
  3. 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>
  4. 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. 
    
C:\temp\notes\ucp-fcf-scan>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....
  5. Now once the program output shows it has obtained 5 connections and is currently sleeping return to your SQL*Plus session at step #7 and perform an ungraceful shutdown using shutdown abort as shown below. 
    
SQL>shutdown abort;

ORACLE instance shut down.
  6. 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....
  7. Return to your SQL*Plus session at step #9 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.
  8. 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 [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: 5 
BorrowedConnectionsCount: 5  

Sleeping for 20 seconds....


For more information about using SCAN with Oracle RAC 11g R2, see this white paper.

Learn More