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