Wildfly: Testing Oracle JDBC driver UCP Connection pool setup with Arquillian/JUnit

 

Why you should use Arquillian/Junit ?

  • Arquillian simplifies integration testing for Java apps
  • Integrates  Test runners like Junit
  • Integrates  Containers like JBoss,Glassfish,..
  • During the deplyoment step [ @Deployment ] ShrinkWrap utility does the following
    • creates a test archive on the fly like test.war,..
    • adds libraries if needed:  like addAsLibraries(new File(LOCAL_MAVEN_REPO + “/com/oracle/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar”));
    • loads all classes referenced in addPackage method: .addPackage(DriverBean.class.getPackage());
    • adds resources like bean.xml :     .addAsWebInfResource(EmptyAsset.INSTANCE, “beans.xml”) ;
  • Arquillian can inject Beans, EBJs by using Jnject annotations [ @Inject DriverBean db ] -> This makes Arquillian to a pretty cool and complete test framework
  • Uses @Test annotation to find the test methods to be run

pom.xml Configuration for Arquillian, UCP, ONS and Oracle JDBC driver

    <dependencyManagement>
        <dependencies>
        <!-- Override dependency resolver with latest version.  This must go *BEFORE* the Arquillian BOM. -->
        <dependency>
            <groupId>org.jboss.shrinkwrap.resolver</groupId>
            <artifactId>shrinkwrap-resolver-bom</artifactId>
            <version>2.0.2</version>
            <scope>import</scope>
            <type>pom</type>
        </dependency>    
        <dependency>            
            <groupId>org.jboss.arquillian</groupId>
            <artifactId>arquillian-bom</artifactId>
            <version>1.1.8.Final</version>
            <scope>import</scope>
            <type>pom</type>
        </dependency>         
    </dependencies>
    </dependencyManagement>
    
    <dependencies>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-web-api</artifactId>
            <version>7.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.2</version>
             <scope>runtime</scope>
        </dependency>  
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ucp</artifactId>
            <version>12.1.0.2</version>
        </dependency> 
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ons</artifactId>
            <version>12.1.0.2</version>
        </dependency> 
       <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
        </dependency> 
        
        <!-- Add support for enterprise feature like Transactions, EJBs  -->
        <dependency>
            <groupId>org.jboss.arquillian.junit</groupId>
            <artifactId>arquillian-junit-container</artifactId>
            <scope>test</scope>
        </dependency> 
        
        <!-- Add support Resolver support to load JDBC, UCP and ONS jar for Unit testing  -->
       <dependency>
            <groupId>org.jboss.shrinkwrap.resolver</groupId>
            <artifactId>shrinkwrap-resolver-depchain</artifactId>      
            <scope>test</scope>
            <type>pom</type>
       </dependency>                
    </dependencies>

Configure  arquillian.xml [ located at src/main/resources ]

<arquillian xmlns="http://jboss.org/schema/arquillian" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://jboss.org/schema/arquillian
        http://jboss.org/schema/arquillian/arquillian_1_0.xsd">
    <container qualifier="wildfly" default="true">
        <configuration>
            <property name="jbossHome">/usr/local/wildfly-8.2.0.Final</property>
            <property name="modulePath">/usr/local/wildfly-8.2.0.Final/modules</property>
            <!-- <property name="allowConnectingToRunningServer">true</property> -->
           <property name="allowConnectingToRunningServer">true</property>
        </configuration>
    </container>
</arquillian>

Write your JAVA testing class TestDriverBean.java [ located in src/test/java ]

Source Code:
import com.hhu.DriverBean;
import java.io.File;
import javax.inject.Inject;
import org.jboss.arquillian.container.test.api.Deployment;
import org.jboss.arquillian.junit.Arquillian;
import org.jboss.shrinkwrap.api.Archive;
import org.jboss.shrinkwrap.api.ShrinkWrap;
import org.jboss.shrinkwrap.api.asset.EmptyAsset;
import org.jboss.shrinkwrap.api.spec.WebArchive;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNull;
import org.junit.Test;
import org.junit.runner.RunWith;

@RunWith(Arquillian.class)
public class TestDriverBean
    {
    private static final String LOCAL_MAVEN_REPO =  
         System.getProperty("maven.repo.local") != null ?  
               System.getProperty("maven.repo.local") :  
               (System.getProperty("user.home") + File.separatorChar +  
               ".m2" + File.separatorChar + "repository");
     
   @Deployment
    public static Archive<?> createTestArchive() { 
 
        System.out.println("****************** Inside createTestArchive()" );
        System.out.println("****************** Local Maven Repsository: " + LOCAL_MAVEN_REPO );
        
        WebArchive res = ShrinkWrap.create(WebArchive.class, "testDriverBean.war");
     
        res.addPackage(DriverBean.class.getPackage());    
        res.addAsLibraries(new File(LOCAL_MAVEN_REPO + "/com/oracle/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar"));
        res.addAsLibraries(new File(LOCAL_MAVEN_REPO + "/com/oracle/ucp/12.1.0.2/ucp-12.1.0.2.jar"));
        res.addAsLibraries(new File(LOCAL_MAVEN_REPO + "/com/oracle/ons/12.1.0.2/ons-12.1.0.2.jar"));
        res.addAsWebInfResource(EmptyAsset.INSTANCE, "beans.xml") ;        
        System.out.println(res.toString(true));    
        System.out.println("****************** Leaving createTestArchive()" );
        return res;                
    }
 
    @Inject DriverBean db;
    
    @Test
    public void driverTest() throws Exception
      {
      System.out.println("-------------- driverTest() running ---------------");
      db.setPoolInfo(null);
      db.setJdbcInfo(null);
      db.setExceptInfo(null);
      db.setClname("oracle.jdbc.OracleDriver");  
      String drvRet =db.checkDriver();     
      System.out.println("driverTest() - return         : " + drvRet );
      System.out.println("driverTest() - JDBC INFO      : " + db.getJdbcInfo() );
      System.out.println("driverTest() - Exception INFO : " + db.getExceptInfo() );  
      assertNull( db.getExceptInfo());
      System.out.println("-------------- driverTest() finished --------------");             
      }
    
    @Test
    public void driverTest2() throws Exception
      {
      System.out.println("-------------- driverTest2() running - Creating a Class not found Exception ---------------");   
            // This will trigger a ClassNotFound Exception
      db.setClname("This_is_not_the_Oracle_JDBC_driver_class");  
      db.setPoolInfo(null);
      db.setJdbcInfo(null);
      db.setExceptInfo(null);
      String drvRet = db.checkDriver();
      System.out.println("driverTest2() - return         : " + drvRet );
      System.out.println("driverTest2() - JDBC INFO      : " + db.getJdbcInfo() );
      System.out.println("driverTest2() - Exception INFO : " + db.getExceptInfo() );     
      assertNull( db.getExceptInfo());
      System.out.println("-------------- driverTest2() finished --------------");          
      }
    
    @Test
    public void poolTest() throws Exception
      {
        System.out.println("-------------- poolTest() running ---------------");
        db.setPoolInfo(null);
        db.setExceptInfo(null);
        db.setJdbcInfo(null);
        String drvRet =db.initPool();     
        System.out.println("poolTest() - return         : " + drvRet );
        System.out.println("poolTest() - Pool INFO      : " + db.getPoolInfo() );
        System.out.println("poolTest() - Exception INFO : " + db.getExceptInfo() );
        String RACDB = "RAC DB: BANKB";
        if ( db.getPoolInfo().contains(RACDB))
            System.out.println("poolTest() - found: " + RACDB);
        else
          {
            int idx = db.getPoolInfo().indexOf("RAC DB:");
            String dbFound = db.getPoolInfo().substring( idx + 8 , idx+13);
              // assertFalse will fail if the checked value is ture  and assertTrue will do the opposite
            assertFalse("poolTest() does not found: " + RACDB +  " - found: " + dbFound,true);
          }
        System.out.println("-------------- poolTest() finished --------------");
      }
    }


Code details :

@RunWith(Arquillian.class)
-> Junit will use Arquillian runner to execute this class

private static final String LOCAL_MAVEN_REPO .. 
-> LOCAL_MAVEN_REPO defines our local Maven repository storing JDBC,UCP and ONS Maven archetypes

WebArchive res = ShrinkWrap.create(WebArchive.class, "testDriverBean.war");
-> Creates a Webarchive named testDriverBean.war. This War will be deployed to our Wildfly server  

res.addAsLibraries(new File(LOCAL_MAVEN_REPO + "/com/oracle/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar"));
-> Adds a Maven archetype from our local Maven repository 

System.out.println(res.toString(true));    
-> Display the content of our "testDriverBean.war" War file

@Inject DriverBean db;
--> Inject DriverBean

Run the test Class

Deployment step createTestArchive() should create the following output: 
Running TestDriverBean
****************** Inside createTestArchive()
****************** Local Maven Repsository: /home/oracle/.m2/repository
testDriverBean.war:
/WEB-INF/
/WEB-INF/lib/
/WEB-INF/lib/ons-12.1.0.2.jar
/WEB-INF/lib/ucp-12.1.0.2.jar
/WEB-INF/lib/ojdbc7-12.1.0.2.jar
/WEB-INF/beans.xml
/WEB-INF/classes/
/WEB-INF/classes/com/
/WEB-INF/classes/com/hhu/
/WEB-INF/classes/com/hhu/DriverBean.class
/WEB-INF/classes/com/hhu/Tools.class
****************** Leaving createTestArchive()
 -> Here we can see that our Deploy Step really adds the needed JAR files to use 
    Oracle JDBC driver with ONS and UCP.
 
Running the Maven Unit Tests
$ mvn -e test 
Results :
Failed tests:   
  poolTest(TestDriverBean): poolTest() does not found: RAC DB: BANKB - found: BANKA
  driverTest2(TestDriverBean): expected null, but was:<<pre>Error in checkDriver()</pre><pre>This_is_not_the_Oracle_JDBC_driver_class from [Module "deployment.testDriverBean.war:main" from Service Module Loader]</pre><pre>java.lang.ClassNotFoundException: This_is_not_the_Oracle_JDBC_driver_class from [Module "deployment.testDriverBean.war:main" from Service Module Loader]
Tests run: 3, Failures: 2, Errors: 0, Skipped: 0
  -> Two out of our three tests  failed: 
     The failed test are :  poolTest(TestDriverBean) and   driverTest2(TestDriverBean)

Review driverTest() method
Source Code: 
@Test
    public void driverTest() throws Exception
      {
      System.out.println("-------------- driverTest() running ---------------");
      db.setPoolInfo(null);
      db.setJdbcInfo(null);
      db.setExceptInfo(null);
      db.setClname("oracle.jdbc.OracleDriver");  
      String drvRet =db.checkDriver();     
      System.out.println("driverTest() - return         : " + drvRet );
      System.out.println("driverTest() - JDBC INFO      : " + db.getJdbcInfo() );
      System.out.println("driverTest() - Exception INFO : " + db.getExceptInfo() );  
      assertNull( db.getExceptInfo());
      System.out.println("-------------- driverTest() finished --------------");             
      }
Methode  driverTest() - prints out the following to Wildfly Server logs
14:26:40,760 INFO  [stdout] (default task-4) -------------- driverTest() running ---------------
14:26:40,761 INFO  [stdout] (default task-4) driverTest() - return         : index
14:26:40,762 INFO  [stdout] (default task-4) driverTest() - JDBC INFO      : 
                                            <pre>JDBC Driver Check - Loading Driver class ok : oracle.jdbc.OracleDriver</pre>
                                            <pre>JDK Version: 1.7.0_71</pre>
                                            <pre>ClassPath  : /usr/local/wildfly-8.2.0.Final/jboss-modules.jar</pre>
                                            <pre>Driver Name             : Oracle JDBC driver</pre>
                                            <pre>Driver Version          : 12.1.0.2.0</pre>
                                            <pre>Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
14:26:40,762 INFO  [stdout] (default task-4) With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
14:26:40,762 INFO  [stdout] (default task-4) Advanced Analytics and Real Application Testing options</pre>
14:26:40,762 INFO  [stdout] (default task-4) driverTest() - Exception INFO : null
14:26:40,763 INFO  [stdout] (default task-4) -------------- driverTest() finished --------------
   --> This test runs successfully as we have provided the correct Classname for the Oracle JDBC driver !
    
Review driverTest2() method
Source Code: 
 public void driverTest2() throws Exception
      {
      System.out.println("-------------- driverTest2() running - Creating a Class not found Exception ---------------");   
         // this creates a ClassNotFoundException
      db.setClname("This_is_not_the_Oracle_JDBC_driver_class");  
      db.setPoolInfo(null);
      db.setJdbcInfo(null);
      db.setExceptInfo(null);
      String drvRet = db.checkDriver();
      System.out.println("driverTest2() - return         : " + drvRet );
      System.out.println("driverTest2() - JDBC INFO      : " + db.getJdbcInfo() );
      System.out.println("driverTest2() - Exception INFO : " + db.getExceptInfo() );     
      assertNull( db.getExceptInfo());
      System.out.println("-------------- driverTest2() finished --------------");          
      }
 ->  - db.checkDriver() does not throw an exception as checkDriver() is a top level JSF function.
     - Instead all Exceptions are stored in an Exception String. 
     - If all works  db.getExceptInfo() should be still a null string. 
     - If not  db.getExceptInfo() returns the  Exception and the later  JUnit test:
: 
          assertNull( db.getExceptInfo());  will fail

Methode  driverTest2() - prints out the following to Wildfly Server logs
14:43:14,483 INFO  [stdout] (default task-5) -------------- driverTest2() running - Creating a Class not found Exception ---------------
14:43:14,486 INFO  [stdout] (default task-5) driverTest2() - return         : index
14:43:14,486 INFO  [stdout] (default task-5) driverTest2() - JDBC INFO      : null
14:43:14,486 INFO  [stdout] (default task-5) driverTest2() - Exception INFO : <pre>Error in checkDriver()</pre><pre>This_is_not_the_Oracle_JDBC_driver_class from [Module "deployment.testDriverBean.war:main" from Service Module Loader]</pre><pre>java.lang.ClassNotFoundException: This_is_not_the_Oracle_JDBC_driver_class from [Module "deployment.testDriverBean.war:main" from Service Module Loader]
14:43:14,487 INFO  [stdout] (default task-5)     at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:213)
14:43:14,487 INFO  [stdout] (default task-5)     at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:459)
14:43:14,487 INFO  [stdout] (default task-5)     at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:408)
14:43:14,487 INFO  [stdout] (default task-5)     at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:389)
14:43:14,487 INFO  [stdout] (default task-5)     at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:134)
14:43:14,487 INFO  [stdout] (default task-5)     at java.lang.Class.forName0(Native Method)
14:43:14,488 INFO  [stdout] (default task-5)     at java.lang.Class.forName(Class.java:191)
14:43:14,488 INFO  [stdout] (default task-5)     at com.hhu.DriverBean.checkDriver(DriverBean.java:163)
--> The stack shows we are failing in line 163 com.hhu.DriverBean.checkDriver because assertNull( db.getExceptInfo()); founds 
    that db.getExceptInfo() is not null 

Review poolTest() method
Source Code: 
    @Test
    public void poolTest() throws Exception
      {
        System.out.println("-------------- poolTest() running ---------------");
        db.setPoolInfo(null);
        db.setExceptInfo(null);
        db.setJdbcInfo(null);
        String drvRet =db.initPool();     
        System.out.println("poolTest() - return         : " + drvRet );
        System.out.println("poolTest() - Pool INFO      : " + db.getPoolInfo() );
        System.out.println("poolTest() - Exception INFO : " + db.getExceptInfo() );
        String RACDB = "RAC DB: BANKB";
        if ( db.getPoolInfo().contains(RACDB))
            System.out.println("poolTest() - found: " + RACDB);
        else
          {
            int idx = db.getPoolInfo().indexOf("RAC DB:");
            String dbFound = db.getPoolInfo().substring( idx + 8 , idx+13);
              // assertFalse will fail if the checked value is ture  and assertTrue will do the opposite
            assertFalse("poolTest() does not found: " + RACDB +  " - found: " + dbFound,true);
          }
        System.out.println("-------------- poolTest() finished --------------");
      }
    }
Methode  poolTest() - prints out the following to Wildfly Server logs
14:43:12,559 INFO  [stdout] (default task-10) -------------- poolTest() running ---------------
14:43:13,129 INFO  [oracle.ucp.common.UniversalConnectionPoolBase] (default task-10) inactive connection timeout timer scheduled
14:43:14,382 INFO  [stdout] (default task-10) poolTest() - return         : index
14:43:14,385 INFO  [stdout] (default task-10) poolTest() - Pool INFO      : <pre>Initializing UCP Pool in progress ...</pre>
            <pre>UCP Pool initialized ! </pre>   RAC DB: BANKA
            <pre>Instance Name:bankA_2 - Host: hract22.example.com</pre>
            <pre>Instance Name:bankA_1 - Host: hract21.example.com</pre>
            <pre>Instance Name:bankA_1 - Session Count 1</pre>
            <pre>Instance Name:bankA_2 - Session Count 49</pre>
14:43:14,386 INFO  [stdout] (default task-10) poolTest() - Exception INFO : null
--> poolTest() found RAC DB: BANKA but tested for Database name BANKB. 
    This makes assertFalse() failing and reporting a test failure !
    Note:  In this case we will not reach the statement : 
        System.out.println("-------------- poolTest() finished --------------");
    The Assert itself reports :  
    Failed tests:   poolTest(TestDriverBean): poolTest() does not found: RAC DB: BANKB - found: BANKA
    

 Summary

  • run Maven at least with -e switch to get a stack trace
  • careeful read the  Maven test summary :  Tests run: 3, Failures: 2, Errors: 0, Skipped: 0, Time elapsed: 10.197 sec <<< FAILURE! –> Results-> Failed tests
  • Check the Server logfile for stack traces

Download Source

Project Object Modul           ./pom.xml
Arquillian Config file         ./src/main/resources/arquillian.xml
JSF file                       ./src/main/webapp/index.xhtml 
Java Test program              ./src/test/java/TestDriverBean.java  
Java Bean                      ./src/main/java/com/hhu/DriverBean.java 

 

Reference

Integrate Oracle JDBC driver, UCP Pool, ONS and JSF with Maven

Add Oracle JDBC driver in your Maven local repository

[oracle@wls1 Driver]$ mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc7 \
 -Dversion=12.1.0.2 -Dpackaging=jar -Dfile=ojdbc7.jar -DgeneratePom=true
[INFO] Scanning for projects...
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building Maven Stub Project (No POM) 1
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-install-plugin:2.3.1:install-file (default-cli) @ standalone-pom ---
[INFO] Installing /home/oracle/JDBC/Driver/ojdbc7.jar to /home/oracle/.m2/repository/com/oracle/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar
[INFO] Installing /tmp/mvninstall5544900966056750978.pom to /home/oracle/.m2/repository/com/oracle/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.pom
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.552s
[INFO] Finished at: Wed Apr 15 08:45:56 CEST 2015
[INFO] Final Memory: 5M/42M
[INFO] ------------------------------------------------------------------------

 
Create a new Netbeans project -> Maven -> Web Application 
  Add            : Wildfly Server & JEE7 support 
  Add JSF Support:  Properties -> Framework -> Add -> JavaServer Faces 

This will create index.xhtml under Web Pages directory. Change it to  
    <h:body>
        Hello from Maven JSF and JDBC test
    </h:body>
Run it and verify that above string was printed 
  http://localhost:8180/WFMavenJDBC-1.0-SNAPSHOT/


Note As our JDBC driver is installed as a module add: <scope>runtime</scope>
pom.xml: 
   <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc7</artifactId>
      <version>12.1.0.2</version>
      <scope>runtime</scope>
    </dependency>
  
runtime:
This scope indicates that the dependency is not required for compilation, but is for execution. 
It is in the runtime and test classpaths, but not the compile classpath.
Note  <scope>runtime</scope> does not work 

For Details how to install Oracle JDBC driver please read following article: 
How to integrate the Oracle JDBC driver as a Wildfly module ?

Integrate ONS and UCP in your local Maven repository

[oracle@wls1 lib]$ pwd
/home/oracle/UCP/lib
[oracle@wls1 lib]$ ls
ojdbc7.jar  ons.jar  ucp.jar
[oracle@wls1 lib]$ mvn install:install-file -DgroupId=com.oracle -DartifactId=ucp \
  -Dversion=12.1.0.2 -Dpackaging=jar -Dfile=ucp.jar -DgeneratePom=true
[INFO] Scanning for projects...
--------------------------------------------------------------------
...
[INFO] --- maven-install-plugin:2.3.1:install-file (default-cli) @ standalone-pom ---
[INFO] Installing /home/oracle/UCP/lib/ucp.jar to /home/oracle/.m2/repository/com/oracle/ucp/12.1.0.2/ucp-12.1.0.2.jar
[INFO] Installing /tmp/mvninstall8385397296650809803.pom to /home/oracle/.m2/repository/com/oracle/ucp/12.1.0.2/ucp-12.1.0.2.pom
Add to pom.xml in your Maven project: 
   <dependency>
     <groupId>com.oracle</groupId>
     <artifactId>ucp</artifactId>
     <version>12.1.0.2</version>
   </dependency> 

[oracle@wls1 lib]$ mvn install:install-file -DgroupId=com.oracle -DartifactId=ons \
  -Dversion=12.1.0.2 -Dpackaging=jar -Dfile=ons.jar -DgeneratePom=true
[INFO] --- maven-install-plugin:2.3.1:install-file (default-cli) @ standalone-pom ---
[INFO] Installing /home/oracle/UCP/lib/ons.jar to /home/oracle/.m2/repository/com/oracle/ons/12.1.0.2/ons-12.1.0.2.jar
[INFO] Installing /tmp/mvninstall1244421194501868057.pom to /home/oracle/.m2/repository/com/oracle/ons/12.1.0.2/ons-12.1.0.2.pom

Add to pom.xml in your Maven project: 
   <dependency>
     <groupId>com.oracle</groupId>
     <artifactId>ons</artifactId>
     <version>12.1.0.2</version>
   </dependency> 

Manually compile and deploy the WAR file

[oracle@wls1 Desktop]$ cd  /home/oracle/NetBeansProjects/GIT/WFMavenJDBC
[oretacle@wls1 WFMavenJDBC]$ ls *.xml
nb-configuration.xml  pom.xml  
[oracle@wls1 WFMavenJDBC]$ mvn clean
[oracle@wls1 WFMavenJDBC]$ mvn package
[oracle@wls1 WFMavenJDBC]$ $WILDFLY_HOME/bin/jboss-cli.sh --connect --command="deploy --force target/WFMavenJDBC-1.0-SNAPSHOT.war"
[oracle@wls1 WFMavenJDBC]$ firefox http://localhost:8180/WFMavenJDBC-1.0-SNAPSHOT/

Testing the application

 
Invoke:  CheckJDBC
  JDBC Driver Check - Loading Driver class ok : oracle.jdbc.OracleDriver
  JDK Version: 1.7.0_71
  ClassPath  : /usr/local/wildfly-8.2.0.Final/jboss-modules.jar
  Driver Name             : Oracle JDBC driver
  Driver Version          : 12.1.0.2.0
  Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Advanced Analytics and Real Application Testing options

Invoke: IinitUCP
  Initializing UCP Pool in progress ...
  UCP Pool initialized !
  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 10
  Instance Name:bankA_3 - Session Count 9

 

Source and Configuration Files

Reference

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