Framework for testing UCP Pool & RAC 12.1.0.2 with Wildfly 8.2

Overview

  • This Framework is easily extensible to test add. UCP features
  • Currently Implemented Features are : FCF, Standard Pool Operations, some Timeouts
  • Note: Using XA Datasoruce and UCP does not work with Wildfly !!
  • The Framework display Instance Status, Session Status, Pool Status and FCF Status
  • All top level function starting with jsf* should catch all excepetions and display these Exceptions via an Exception Window
  • getConnection() initializes the Pooled Datasource  and returns a Connection object
  • getConnection() stores the Pooled Datasource in a static variable and it is only loaded once per JVM during first usage of UCPPoolBean class
  • initPool() creates and starts the named UCP pool :   my_ucp_pool
  • Following UCP Pool Operations are supported : INIT  STOP  START  DESTROY  PURGE REFRESH
  • The JNDI functions creates its own JNDI entry named:  java:global/myUcpDs
  • The Code expects that you have created a Datasource named myRacDS
  • This Datasource is created via Annotations:  @Resource(lookup=”java:jboss/datasources/myRacDS”)  
  • Above mentioned datasource myRACDS is only used in jsfCheckDriver() function and works even without the UCP POOL
  • Take A first look on the Operation Panel – Click on the image to see details !UCP_Panel

Source Code:  getConnection() –  An important Code Detail

Central part of the Java Code is the getConnection() Function     
public class UcpPoolBean
    {
    private static oracle.ucp.jdbc.PoolDataSource pds = null;
    ..      
    public Connection getConnection() throws Exception
      {
        Connection c = null;
        if (pds == null )
          {
            setPoolStats("UCP POOL not yet initialized - creating  UCP pool in getConection()  !");
            initPool();
              // This is just an informational message that the Pooled Datasource was recreated !              
            genericExp("INFO: UCP pool created via getConnection() as Pooled DataSource == null ! ", null);
          }
        if ( pds == null )
            genericExp("FATAL: Pooled DataSource == null after init in getConnection()  ", null);       
        else
            c = pds.getConnection();
         return c;
      }

 

Framework: Generic Display Options

Instance status :  CheckInstance

11:51:32.479:    RAC DB: BANKA
Instance Name:bankA_1 - Host: hract21.example.com
Instance Name:bankA_2 - Host: hract22.example.com
Instance Name:bankA_1 - Session Count 5
Instance Name:bankA_2 - Session Count 2

UCP Pool status :  CheckUCPPool  
Press -> Check UCP Pool Status 
11:52:49.906: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@932eae2
11:52:49.907: Available Pools:  my_ucp_pool
11:52:49.907:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
11:52:49.907:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
11:52:49.907:   PoolLifeCycle state:  Running
11:52:49.907:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
     
Session Info :  CheckUCPSessions    
12:04:46.824: Available Connections in UCP POOL: 5
12:04:46.876: Instance Name:bankA_2 - Host: hract22.example.com
12:04:46.944: Instance Name:bankA_1 - Host: hract21.example.com
12:04:46.946: Instance Name:bankA_2 - Host: hract22.example.com
12:04:46.948: Instance Name:bankA_1 - Host: hract21.example.com
12:04:46.950: Instance Name:bankA_2 - Host: hract22.example.com

To display all of the above statistics press : CheckAll

Testing Abandon Connection Timeout

The abandoned connection timeout (ACT) enables borrowed connections to be
reclaimed back into the connection pool after a connection has not been used for a
specific amount of time. Abandonment is determined by monitoring calls to the
database. This timeout feature helps maximize connection reuse and conserves system
resources that are otherwise lost on maintaining borrowed connections that are no
longer in use.

Settings:
Set Connection Count         : 5   
Abandoned Connection Timeout : 10 [seconds ]
   AllocateConnections
UCP Pool Status:
12:31:41.341: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@932eae2
12:31:41.341: Available Pools:  my_ucp_pool
12:31:41.341:   Connection Count : Total : 5 - Available: 0 - Borrowed: 5
12:31:41.341:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
12:31:41.341:   PoolLifeCycle state:  Running
12:31:41.341:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
12:31:41.341: Connections in static ArrayList  jsfOpenConnections(): 5
12:31:41.341: jsfOpenConnections(): Connection - Opened:  5
-> 5 connections are now in use !

Session INFO :
12:31:41.335: jsfOpenConnections(): Connections newly Opened:  5
12:31:41.335: verifyConnections() - Connections in static ArrayList :
12:31:41.336: Instance Name:bankA_2 - Host: hract22.example.com
12:31:41.336: Instance Name:bankA_2 - Host: hract22.example.com
12:31:41.339: Instance Name:bankA_2 - Host: hract22.example.com
12:31:41.340: Instance Name:bankA_1 - Host: hract21.example.com
12:31:41.340: Instance Name:bankA_2 - Host: hract22.example.com

Now wait about 30 seconds and press : CheckUCPPool

12:35:27.493: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@932eae2
12:35:27.494: Available Pools:  my_ucp_pool
12:35:27.494:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
12:35:27.494:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
12:35:27.494:   PoolLifeCycle state:  Running
12:35:27.494:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
-> The connections are reclaimed by the UCP pool after reaching the timeout

Verify the connection status by pressing : VerifyConnections

Exceptions :
12:37:12.567: Error in retSessionInfo() - Failed SQL:
12:37:12.567: The connection is closed: The connection is closed
12:37:12.568: java.sql.SQLException: The connection is closed: The connection is closed
at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:464)
at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:448)
at oracle.ucp.jdbc.proxy.JDBCConnectionProxyFactory.invoke(JDBCConnectionProxyFactory.java:307)
at oracle.ucp.jdbc.proxy.ConnectionProxyFactory.invoke(ConnectionProxyFactory.java:50)
at com.sun.proxy.$Proxy17.createStatement(Unknown Source)
at UcpPool.UcpPoolBean.retSessionInfo(UcpPoolBean.java:1201)
at UcpPool.UcpPoolBean.verifyConnections(UcpPoolBean.java:1113)
-> The connections are reclaimed by the pool - Note : the above Exception is expected !

Release the stale Connections 
Finally Press ReleaseConnections  to cleanup the static Connection Pool Array

The Release Operation should report:
12:43:33.146: Connections in static ArrayList  jsfCloseConnections(): 0
12:43:33.146: jsfCloseConnections(): Connections Closed:  1

 

Testing Inactive Connection Timeout

The inactive connection timeout specifies how long an available connection can remain
idle before it is closed and removed from the pool. This timeout property is only
applicable to available connections and does not affect borrowed connections. This
property helps conserve resources that are otherwise lost on maintaining connections
that are no longer being used. The inactive connection timeout (together with the
Note: UCP for JDBC either cancels or rolls back connections that
have local transactions pending before reclaiming connections for
reuse.

Settings: 
Set Connection Count       :  4  
Inactive Connection Timeout:     30
Press 2x:  AllocateConnections
Press 2x:  ReleaseConnections

UCP Pool Status :     
12:54:32.053: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@359db23
12:54:32.054: Available Pools:  my_ucp_pool
12:54:32.054:   Connection Count : Total : 8 - Available: 8 - Borrowed: 0
12:54:32.054:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
12:54:32.054:   PoolLifeCycle state:  Running
12:54:32.054:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
12:54:32.054: Connections in static ArrayList  jsfCloseConnections(): 0
12:54:32.054: jsfCloseConnections(): Connections Closed:  4

Session Info :     
12:57:24.381: Available Connections in UCP POOL: 8
12:57:24.383: Instance Name:bankA_1 - Host: hract21.example.com
12:57:24.384: Instance Name:bankA_2 - Host: hract22.example.com
12:57:24.385: Instance Name:bankA_2 - Host: hract22.example.com
12:57:24.386: Instance Name:bankA_1 - Host: hract21.example.com
12:57:24.387: Instance Name:bankA_2 - Host: hract22.example.com
12:57:24.388: Instance Name:bankA_2 - Host: hract22.example.com
12:57:24.389: Instance Name:bankA_2 - Host: hract22.example.com
12:57:24.390: Instance Name:bankA_1 - Host: hract21.example.com
-> The UCP Connection Count is increased to 8 !

Now wait about 60 seconds:
UCP Pool status :     
12:58:19.972: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@359db23
12:58:19.972: Available Pools:  my_ucp_pool
12:58:19.972:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
12:58:19.972:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
12:58:19.973:   PoolLifeCycle state:  Running
12:58:19.973:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
-> UCP Pool has reduced the Connection Count to 5 from 8 

 

Testing Pool Limits

Settings:
Set Connection Count       :  4  
Press  3x                  : AllocateConnections

UCP Pool status :     
13:00:08.544: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@359db23
13:00:08.544: Available Pools:  my_ucp_pool
13:00:08.544:   Connection Count : Total : 10 - Available: 0 - Borrowed: 10
13:00:08.544:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
13:00:08.544:   PoolLifeCycle state:  Running
13:00:08.544:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
13:00:08.544: Connections in static ArrayList  jsfOpenConnections(): 10
13:00:08.544: jsfOpenConnections(): Connection - Opened:  2   

Exceptions :
13:00:08.531: Error in openConnections() + Already opened Connections: 2
13:00:08.531: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: 
              All connections in the Universal Connection Pool are in use
13:00:08.531: java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException:
     All connections in the Universal Connection Pool are in use
    at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:479)
    at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:154)
    at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1127)
    at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1031)
-> The lastest Allocate Connection request can only allocate 2 connections. 
   After that the get getConnection() fails with above Exception.

 

Testing UCP Purge Operation

UCP Pool status after Purge  :     PurgeUCP

13:04:23.530: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@57e31b7
13:04:23.530: Available Pools:  my_ucp_pool
13:04:23.531:   Connection Count : Total : 0 - Available: 0 - Borrowed: 0
13:04:23.531:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
13:04:23.531:   PoolLifeCycle state:  Running
13:04:23.531:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
13:04:23.531: Connections in static ArrayList after openConnections(): 0
13:04:23.531: Connections remaining in static ArrayList after closeConnections() : 0
13:04:23.531: checkSessionInPool(): Connection - Opened:  0 - Closed: 10       
 -> After a purge pool operation all connections get closed and the pool  
    Total Connection Count is : 0 even MinPoolSize is set to 5 !

Reallocate the connections :
Set Connection Count       :  5  
Press  1x                  : AllocateConnections
Press  1x                  : ReleaseConnections

UCP Pool Status :
13:07:02.775: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@57e31b7
13:07:02.775: Available Pools:  my_ucp_pool
13:07:02.775:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
13:07:02.775:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
13:07:02.775:   PoolLifeCycle state:  Running
13:07:02.775:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
13:07:02.775: Connections in static ArrayList  jsfCloseConnections(): 0
13:07:02.775: jsfCloseConnections(): Connections Closed:  5    

Testing UCP Refresh Operation

Current Pool Status
13:08:46.268: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@57e31b7
13:08:46.268: Available Pools:  my_ucp_pool
13:08:46.269:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
13:08:46.269:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
13:08:46.269:   PoolLifeCycle state:  Running
13:08:46.269:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
13:08:46.269: Connections in static ArrayList after openConnections(): 0
13:08:46.269: Connections remaining in static ArrayList after closeConnections() : 0
13:08:46.269: checkSessionInPool(): Connection - Opened:  5 - Closed: 5

Session Info :     
13:08:46.258: Available Connections in UCP POOL: 5
13:08:46.262: Instance Name:bankA_1 - Host: hract21.example.com
13:08:46.264: Instance Name:bankA_2 - Host: hract22.example.com
13:08:46.266: Instance Name:bankA_1 - Host: hract21.example.com
13:08:46.267: Instance Name:bankA_2 - Host: hract22.example.com
13:08:46.268: Instance Name:bankA_1 - Host: hract21.example.com

Press : RefreshUCP
13:10:03.940: Available Connections in UCP POOL: 5
13:10:03.940: Instance Name:bankA_2 - Host: hract22.example.com
13:10:03.942: Instance Name:bankA_2 - Host: hract22.example.com
13:10:03.943: Instance Name:bankA_2 - Host: hract22.example.com
13:10:03.945: Instance Name:bankA_1 - Host: hract21.example.com
13:10:03.945: Instance Name:bankA_2 - Host: hract22.example.com
-> Refresh UCP Pool has closed and recreated the physical Connections 
   The connections point now to different RAC instances.

 

Testing FCF functionality


Current UCP Pool status :     
11:42:07.415: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@44d2df43
11:42:07.415: Available Pools:  my_ucp_pool
11:42:07.416:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
11:42:07.416:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
11:42:07.416:   PoolLifeCycle state:  Running
11:42:07.416:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
11:42:07.416: Connections in static ArrayList after openConnections(): 0
11:42:07.416: Connections remaining in static ArrayList after closeConnections() : 0
11:42:07.416: checkSessionInPool(): Connection - Opened:  5 - Closed: 5

Session Info :     
11:42:07.313: Available Connections in UCP POOL: 5
11:42:07.317: Instance Name:bankA_2 - Host: hract22.example.com
11:42:07.319: Instance Name:bankA_2 - Host: hract22.example.com
11:42:07.321: Instance Name:bankA_2 - Host: hract22.example.com
11:42:07.413: Instance Name:bankA_3 - Host: hract21.example.com
11:42:07.415: Instance Name:bankA_3 - Host: hract21.example.com
-> All UCP session are  equally distributed on both available RAC instances 

Now stop one of the instances 
[oracle@hract21 ~]$ srvctl stop instance -db banka -n hract22 -o abort

Verify Session Info again  :     
11:44:39.005: Available Connections in UCP POOL: 5
11:44:39.124: Instance Name:bankA_3 - Host: hract21.example.com
11:44:39.127: Instance Name:bankA_3 - Host: hract21.example.com
11:44:39.129: Instance Name:bankA_3 - Host: hract21.example.com
11:44:39.130: Instance Name:bankA_3 - Host: hract21.example.com
11:44:39.132: Instance Name:bankA_3 - Host: hract21.example.com
-> As expected - all UCP sessions are now pointing to the surviving instance.
   Note: We don't need a server roundtrip for this operation - ONS does a great job here ! 
    
FCF Info:
11:44:39.133:  FCF status:  {Apr 11, 2015 11:44 AM SUCCESS      
    Connections:(Available=5 Opened=2 FailedToProcess=0 MarkedDown=0 Closed=2)(Borrowed=0 FailedToProcess=0 MarkedDown=0 Closed=0)}


Restart Instance  and Refresh the pool
[oracle@hract21 ~]$ srvctl start instance -db banka -n hract22

Press : RefreshUCP
11:57:14.914: Available Connections in UCP POOL: 5
11:57:14.915: Instance Name:bankA_2 - Host: hract22.example.com
11:57:14.916: Instance Name:bankA_3 - Host: hract21.example.com
11:57:14.917: Instance Name:bankA_2 - Host: hract22.example.com
11:57:14.918: Instance Name:bankA_3 - Host: hract21.example.com
11:57:14.919: Instance Name:bankA_2 - Host: hract22.example.com
-> All UCP session are now again equally redistributed on both RAC instances 

 

Testing  JNDI

  • The Framework uses ctx.bind() to temporary create a JNDI entry on the fly
  • After ctx.bind() the Framework use ctx.lookup() to create the pooled Datasource
  • Run ctx.lookup() only once and store the created Pooled Datasource in a static variable for re-using.
  • Note: After a JVM reboot the JNDI resource/UCP Pool must be recreated running rebindPool() and lookupPool()
  • The JNDI functions create its own JNDI entry named: java:global/myUcpDs

Complete JAVA Code for JNDI lookup

private static oracle.ucp.jdbc.PoolDataSource pds = null;
   public void lookupPool() throws Exception
      {
        if ( pds != null)
          {
            setPoolStats(Tools.add_hmtl_pre_tag("Pooled Datasource already exist - don't run ctx.lookup() again  "));
            return;
          }    
        cleanAll();
        setPoolStats(Tools.add_hmtl_pre_tag("Testing UPC Pool via JNDI in progress JNDI name: " +  jndiName));
        ctx = new InitialContext();  
        pds = (PoolDataSource)ctx.lookup("java:global/myUcpDs");
        checkAll();
        setPoolStats(getPoolStats() + Tools.add_hmtl_pre_tag("lookupPool(): Pooled Datasource initialized: " + jndiName ));
      }
       
    public void rebindPool() throws Exception
      {
        pds =null;
        cleanAll();                                 
        UniversalConnectionPoolManager ucpMgr = createPoolManager();
        if ( ucpMgr == null)
          {
            setPoolStats(Tools.add_hmtl_pre_tag("Could not create UCP Manager "));
            return;
          }            
        setPoolStats(Tools.add_hmtl_pre_tag("Destroying UCP Pool in progres ..."));
                // We need to destroy the pool first if the pool exist
                // If we recreate the pool without destroying we will get a session leak at the RAC layer
        try
          {    
            ucpMgr.destroyConnectionPool(poolName);
            setPoolStats(getPoolStats() + Tools.add_hmtl_pre_tag("UCP Pool destroyed !"));
          } catch ( Exception e2)         
          {
            setPoolStats(getPoolStats () +Tools.add_hmtl_pre_tag("Exception during delete Pool - [ ignore the error]  "  ));  
                  // genericExp("Execption duringe deleteing pool - If pool does not exists ignore this exception ",  e2);
          }  
        ctx = new InitialContext();
        PoolDataSource lpds = setupPooledDS();
            // ucpMgr.createConnectionPool((UniversalConnectionPoolAdapter)pds);
        ctx.rebind(jndiName, lpds);
        setPoolStats(getPoolStats () +Tools.add_hmtl_pre_tag("JNDI context rebind ok for :  " + jndiName ));  
            // Disable JMX - if needed
             // ucpManager.setJmxEnabled(false);
            // ucpManager.createConnectionPool((UniversalConnectionPoolAdapter)pds);                                                                              
        return;
      }
        
   public  PoolDataSource setupPooledDS() throws Exception
      {
        
        PoolDataSource lpds = oracle.ucp.jdbc.PoolDataSourceFactory.getPoolDataSource();        
         //set the connection properties on the data source and pool properties
        String ONS_CONFIG = "nodes=hract21:6200,hract22:6200,hract23:6200";
        lpds.setONSConfiguration ( ONS_CONFIG);
        lpds.setFastConnectionFailoverEnabled( true);
        lpds.setUser("scott");
        lpds.setPassword("tiger");
        lpds.setURL("jdbc:oracle:thin:@ract2-scan.grid12c.example.com:1521/banka");
        lpds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource"); 
        lpds.setInitialPoolSize(minPoolSize);
        lpds.setMinPoolSize(minPoolSize);
        lpds.setMaxPoolSize(maxPoolSize);
        lpds.setAbandonedConnectionTimeout(abandonedConnectionTimeout);
        lpds.setInactiveConnectionTimeout(inactiveConnectionTimeout);
        lpds.setTimeoutCheckInterval(10);
        lpds.setConnectionPoolName(poolName);
        return lpds;
      } 

JNDI: Use Case I – Destroy Pooled DS and recreate UCP Pool

Destroy UCP Pool :  my_ucp_pool   DestroyUCP         [  but don't run Unbind UCP Pool via JNDI ]    
Lookup UCP Pool via JNDI  :       Lookup
16:49:48.930: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@7ffa742a
16:49:48.930: Available Pools:  my_ucp_pool
16:49:48.930:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
16:49:48.930:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
16:49:48.930:   PoolLifeCycle state:  Running
16:49:48.930:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
16:49:48.930: Connections in static ArrayList after openConnections(): 0
16:49:48.930: Connections remaining in static ArrayList after closeConnections() : 0
16:49:48.930: checkSessionInPool(): Connection - Opened:  5 - Closed: 5
16:49:48.930: lookupPool(): Pooled Datasource initialized: java:global/myUcpDs

-> After destruction of UCP pool the pool can be re-created by runing lookupPool()
   Note: A destruction of the UCP pool does not delete the JNDI entry.

JNDI: Use Case II – Destropy Pooled DS, Unbind JNDI and recreate JNDI and UCP Pool

Destroy UCP Pool :  my_ucp_pool          DestroyUCP
Unbind JNDI      :  java:global/myUcpDs  JndiUnbindUCP
Lookup UCP Pool via JNDI  fails:         Lookup

16:28:12.673: Exception inside lookupPool() :  JNDI Lookup 
16:28:12.673: myUcpDs -- service jboss.naming.context.java.global.myUcpDs
16:28:12.673: javax.naming.NameNotFoundException: myUcpDs -- service jboss.naming.context.java.global.myUcpDs
    at org.jboss.as.naming.ServiceBasedNamingStore.lookup(ServiceBasedNamingStore.java:104)
    at org.jboss.as.naming.NamingContext.lookup(NamingContext.java:202)
    at org.jboss.as.naming.NamingContext.lookup(NamingContext.java:179)  
    
Solution: 
Rebind the Pooled Datasource :  JndiRebindUCP
16:29:22.214: Destroying UCP Pool in progres ...
16:29:22.214: Exception during delete Pool - [ ignore the error]  
16:29:22.215: JNDI context rebind ok for :  java:global/myUcpDs

Run JNDI Lookup again :                Lookup 
16:29:48.930: Intialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@7ffa742a
16:29:48.930: Available Pools:  my_ucp_pool
16:29:48.930:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
16:29:48.930:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
16:29:48.930:   PoolLifeCycle state:  Running
16:29:48.930:   InactiveConnectionTimeout: 30 - AbandonedConnectionTimeout: 10
16:29:48.930: Connections in static ArrayList after openConnections(): 0
16:29:48.930: Connections remaining in static ArrayList after closeConnections() : 0
16:29:48.930: checkSessionInPool(): Connection - Opened:  5 - Closed: 5
16:29:48.930: lookupPool(): Pooled Datasource initialized: java:global/myUcpDs
-> After a JVM reboot or after deletion of UPC Pool and JNDI entry the UCP pool must be 
   recreated by calling rebindPool() and lookupPool().

Problem : JNDI lookup fails when calling lookup a 2.nd time

If you re-running the following JNDI code without destryoing the UCP pool first you will get an Exception
    public String lookupPool()
      {  
        cleanAll();
        setPoolStats(Tools.add_hmtl_pre_tag("Testing UPC Pool via JNDI in progress  "));
        genericExp("INFO:  JNDI Lookup doesn't work yet", null);
        try
          {
          ctx = new InitialContext();  
          pds = (PoolDataSource)ctx.lookup("java:global/myUcpDs");
          setPoolStats(getPoolStats() + Tools.add_hmtl_pre_tag("Testing UPC Pool via JNDI - Pooled Datasource initialized "));
          checkAll();
          } catch ( Exception e1)
              {
              genericExp("Exception inside lookupPool() :  JNDI Lookup ", e1);
            
              return "index";
              }
       
        setPoolStats(getPoolStats() + "- JNDI lookup ok  ! ");
        return "index";
      } 


1.st Run ( Note UCP pool was already destroyed )

Instance status :     RAC DB: BANKA
Instance Name:bankA_3 - Host: hract21.example.com
Instance Name:bankA_2 - Host: hract22.example.com
Instance Name:bankA_3 - Session Count 35
Instance Name:bankA_2 - Session Count 35

UCP Pool status :     
15:42:36.773: Initialized Pooled DS: oracle.ucp.jdbc.PoolDataSourceImpl@2daf23a3
15:42:36.773: Available Pools:  my_ucp_pool
15:42:36.773:   Connection Count : Total : 5 - Available: 5 - Borrowed: 0
15:42:36.773:   Pools Size       : Inital: 5 - Min      : 5 - Max     : 10
15:42:36.773:   PoolLifeCycle state:  Running
-> Status: UCP pool was created and started by  JNDI lookup
    
2.nd Run ( Note: UCP pool is already active ! )    
Exceptions :
15:48:09.493: Exception inside lookupPool() :  JNDI Lookup 
15:48:09.493: Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: 
   Error during pool creation in Universal Connection Pool Manager MBean: oracle.ucp.UniversalConnectionPoolException: 
   Error during pool creation in Universal Connection Pool Manager: oracle.ucp.UniversalConnectionPoolException: 
   Universal Connection Pool already exists in the Universal Connection Pool Manager. 
   Universal Connection Pool cannot be added to the Universal Connection Pool Manager
    at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:479)
    at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:154)
    at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:730)
    at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1093)
    at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1031)

Instance Status     
15:49:47.032:    RAC DB: BANKA
Instance Name:bankA_2 - Host: hract22.example.com
Instance Name:bankA_3 - Host: hract21.example.com
Instance Name:bankA_2 - Session Count 38
Instance Name:bankA_3 - Session Count 37    
    
-> The 2.nd run leads to a databases session leak
    
WA : Store the Pooled DataSource in a static variable and run lookup only a single time! 

Using XA Datasource with UCP and Wildfly fails with  loader constraint violation

Init XA Datasource:  InitUCPXA
12:20:50.557: Initializing XA UCP Pool in progress ...
12:20:50.561: INFO : Jar File     for XAResource.class:jar:file:/usr/local/wildfly-8.2.0.Final/modules/system/layers/base/javax/transaction/api/main/jboss-transaction-api_1.2_spec-1.0.0.Final.jar!/javax/transaction/xa/XAResource.class
12:20:50.561: INFO : Class Loader for XAResource.class :ModuleClassLoader for Module "javax.transaction.api:main" from local module loader @7fe9a140 (finder: local module finder @4359b25b (roots: /usr/local/wildfly-8.2.0.Final/modules,/usr/local/wildfly-8.2.0.Final/modules/system/layers/base))
12:20:50.784: XA Initializing UCP Pool failed !

Exceptions :
12:20:50.784: Runtime exception in jsfInitPoolXA() : 
12:20:50.785: loader constraint violation: loader (instance of ) previously initiated loading for a different type with name "javax/transaction/xa/XAResource"
12:20:50.787: java.lang.LinkageError: loader constraint violation: loader (instance of ) previously initiated loading for a different type with name "javax/transaction/xa/XAResource"
    at java.lang.Class.getDeclaredMethods0(Native Method)
    at java.lang.Class.privateGetDeclaredMethods(Class.java:2615)
    at java.lang.Class.privateGetPublicMethods(Class.java:2733)
    at java.lang.Class.getMethods(Class.java:1472)
    at sun.misc.ProxyGenerator.generateClassFile(ProxyGenerator.java:426)
    at sun.misc.ProxyGenerator.generateProxyClass(ProxyGenerator.java:323

For Details please check ... 
 Debugging UCP Connection Pool using XA Datasource pointing to a RAC database - http://www.hhutzler.de/blog/debugging-ucp-connection-pool-using-xa-datasource-pointing-to-a-rac-database/

 

Source Code

 JSF code:   index.xhtml      
 Java Code:  UcpPoolBean.java 

JAVADOC

Reference

Leave a Reply

Your email address will not be published. Required fields are marked *