Table of Contents
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 !
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 :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 :
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 :
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 :
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 ]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 :
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
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:
Testing Pool Limits
Settings: Set Connection Count : 4 Press 3x :
Testing UCP Purge Operation
UCP Pool status after Purge :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 :
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 :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 :
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[ but don't run Unbind UCP Pool via JNDI ] Lookup UCP Pool via JNDI :
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_poolLookup UCP Pool via JNDI fails:
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 :
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 :
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: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
- Create and Manage a RAC Datasource with WildFly
- How to integrate the Oracle JDBC driver as a Wildfly module ?
- Wildfly: Testing Oracle JDBC driver UCP Connection pool setup with Arquillian/JUnitWildfly
- Debugging UCP Connection Pool using XA Datasource pointing to a RAC database
- UCP Developers Guide : https://docs.oracle.com/database/121/JJUCP/E49541-01.pdf