Testing UCP Connection Pool with FCF against a RAC 12.1.0.2 database

Overview

• Set CLASSPATH : $ export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc7.jar:.:$ORACLE_HOME/ucp/lib/ucp.jar:$ORACLE_HOME//opmn/lib/ons.jar
•  Always use the newest version of:  ojdbc7.jar ucp.jar ons.jar [ 12.1.0.2 ]
• Download location for java test program:  UcpRacTest.java

Run java test program and monitor UCP pool after and before instance shutdown

Check current instance status 
[oracle@hract21 ~]$ srvctl status database  -db banka
Instance bankA_1 is running on node hract21
Instance bankA_2 is running on node hract22

Java code to create UCP connection pool supporting FCF 
 public UcpRacTest() throws SQLException
    {
    // Create pool-enabled data source instance.
    pds = PoolDataSourceFactory.getPoolDataSource();
        // PoolDataSource and UCP configuration
         //set the connection properties on the data source and pool properties
    String ONS_CONFIG = "nodes=hract21:6200,hract22:6200,hract23:6200";
    pds.setONSConfiguration ( ONS_CONFIG);
    pds.setFastConnectionFailoverEnabled( true);
    pds.setUser("scott");
    pds.setPassword("tiger");
    pds.setURL("jdbc:oracle:thin:@ract2-scan.grid12c.example.com:1521/banka");
    pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
    pds.setInitialPoolSize(5);
    pds.setMinPoolSize(5);
    pds.setMaxPoolSize(20);
    System.out.println("--> UCP Pool wtih FCF sucessfuly initialized !");
    }

Java code to check closed connections 
  public String getInstanceInfo (Connection c) throws SQLException
    {
        String query1= "select name from v$database";
        String query2 = "select host_name,instance_name from v$instance";
        String query = null;

        String inst_info ="";
      // Statement stmt = c.createStatement();
        try ( Statement stmt =  c.createStatement() ) 
        {
            inst_info ="   RAC DB: ";
            query = query1;
            ResultSet rset = stmt.executeQuery (query);
            rset.next ();
            inst_info = inst_info +  rset.getString (1) ;

            query = query2;
            rset = stmt.executeQuery (query);
            while( rset.next() )
                inst_info =  inst_info + " - Instance Name:" +  rset.getString (2)  + " - Host: " +  rset.getString(1) ;
        } catch ( SQLException e1)
          {
            throw e1;
          }
    return inst_info;
    }

   public void displayPoolDetails (Connection c) throws SQLException
     {
     // System.out.println("-----------");
     System.out.print("NumberOfAvailableConnections: " + pds.getAvailableConnectionsCount());
     System.out.println(" - BorrowedConnectionsCount: " + pds.getBorrowedConnectionsCount());
     if ( c != null )
         System.out.println(getInstanceInfo(c));
     }
               
Run test program: 
[oracle@hract21 UCP]$  java UcpRacTest
Started at Wed Mar 11 12:21:30 CET 2015
--> UCP Pool wtih FCF sucessfuly initialized !
--> Opening 5 connections to a RAC DB !
getConnection(): NumberOfAvailableConnections: 4 - BorrowedConnectionsCount: 1
   RAC DB: BANKA - Instance Name:bankA_1 - Host: hract21.example.com
getConnection(): NumberOfAvailableConnections: 3 - BorrowedConnectionsCount: 2
   RAC DB: BANKA - Instance Name:bankA_2 - Host: hract22.example.com
getConnection(): NumberOfAvailableConnections: 2 - BorrowedConnectionsCount: 3
   RAC DB: BANKA - Instance Name:bankA_2 - Host: hract22.example.com
getConnection(): NumberOfAvailableConnections: 1 - BorrowedConnectionsCount: 4
   RAC DB: BANKA - Instance Name:bankA_1 - Host: hract21.example.com
getConnection(): NumberOfAvailableConnections: 0 - BorrowedConnectionsCount: 5
   RAC DB: BANKA - Instance Name:bankA_1 - Host: hract21.example.com
--> Closing all opend  RAC connections !
--> Pool stats After Closing all opened connection: NumberOfAvailableConnections: 5 - BorrowedConnectionsCount: 0

--> Shutdown an Instance and press <CR>:

Now shutdown instance bankA_1 
[oracle@hract21 ~]$  srvctl stop instance -db banka -i bankA_1 -o abort
[oracle@hract21 ~]$  srvctl status database  -db banka
Instance bankA_2 is running on node hract22
Instance bankA_1 is not running on node hract21
and continue program by pressing <CR> :
--> Opening 5 connections to a RAC DB !
getConnection(): NumberOfAvailableConnections: 4 - BorrowedConnectionsCount: 1
   RAC DB: BANKA - Instance Name:bankA_2 - Host: hract22.example.com
getConnection(): NumberOfAvailableConnections: 3 - BorrowedConnectionsCount: 2
   RAC DB: BANKA - Instance Name:bankA_2 - Host: hract22.example.com
getConnection(): NumberOfAvailableConnections: 2 - BorrowedConnectionsCount: 3
   RAC DB: BANKA - Instance Name:bankA_2 - Host: hract22.example.com
getConnection(): NumberOfAvailableConnections: 1 - BorrowedConnectionsCount: 4
   RAC DB: BANKA - Instance Name:bankA_2 - Host: hract22.example.com
getConnection(): NumberOfAvailableConnections: 0 - BorrowedConnectionsCount: 5
   RAC DB: BANKA - Instance Name:bankA_2 - Host: hract22.example.com
--> Closing all opend  RAC connections !
--> Pool stats After Closing all opened connection: NumberOfAvailableConnections: 5 - BorrowedConnectionsCount: 0
Ended at Wed Mar 11 12:23:43 CET 2015

Test Summary

• All connections are reconnected to  instance bankA_2 after shutdown of instance  bankA_1 immediately
•   The failed RAC system generates a DOWN FAN EVENT and send a message to our UCP pool
•    Note we see no errors when we reusing the staled connections from the UCP pool
  
•    The RAC instance has notified the UCP Pool via ONS to close and reconnect the stale connections pointing to hract21