This discussion is archived
12 Replies Latest reply: Nov 5, 2013 11:45 AM by 35ad3a60-77e5-4581-927b-9e518275243b RSS

jdbc connections pooling using Universal Connection pool

vr*446726*ik Newbie
Currently Being Moderated
Hi,

I get during tomcat start this error:

java.sql.SQLException: Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Error during pool creation in Universal Connection Pool Manager MBean: orac
le.ucp.UniversalConnectionPoolException: Error during pool creation in Universal Connection Pool Manager: oracle.ucp.UniversalConnectionPoolException: Universal Connection Pool already exist
s 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:488) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:163) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:646) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:907) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:863) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:855) ~[ucp.jar:11.2.0.3.0]
.........
.........
at java.lang.Thread.run(Thread.java:722) [na:1.7.0_05]
Caused by: 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 Conne
ction Pool cannot be added to the Universal Connection Pool Manager
at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:368) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:49) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:80) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.admin.UniversalConnectionPoolManagerMBeanImpl.createConnectionPool(UniversalConnectionPoolManagerMBeanImpl.java:316) ~[ucp.jar:11.2.0.3.0]
at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:623) ~[ucp.jar:11.2.0.3.0]
... 19 common frames omitted

I use the UCP same way as I used the previous pooling which was part of oracle jdbc driver jar file. When I catch the exception and repeate the get connection request I get the connection and all works as expected.
Unfortunatelly there are places where I can't modify existing code to retry twice if any exception happens during getting connection from pool.

My configuration is:
linux64bit
Java 7
Tomcat 7
Oracle jdbc driver (ojdbc6) 11.2.0.3.0
Oracle UCP 11.2.0.3.0, build info 110823
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

My tomcat 7 datasource configuration is this, there are now only ucp pools configured everywhere:
<Resource name="spatial_data"
user="SPATIAL_DATA"
password="SPATIAL_DATA"
connectionPoolName="spatialDataUCP"
auth="Container"
factory="oracle.ucp.jdbc.PoolDataSourceImpl"
type="oracle.ucp.jdbc.PoolDataSource"
description="ucp ismart metadata connection"
connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
minPoolSize="0"
maxPoolSize="20"
initialPoolSize="0"
url="jdbc:oracle:thin:@192.168.0.101:1521:orcl"
inactiveConnectionTimeout="30"
validateConnectionOnBorrow="true"
abandonConnectionTimeout="30"
timoutCheckInterval="60"
maxStatements="10"
/>

Is there way I could avoid this exception?
Seems like two thread are creating same pool and one fails because of the other thread registered the pool sooner.

Thanks.

Regards,
Zdenek
  • 1. Re: jdbc connections pooling using Universal Connection pool
    gimbal2 Guru
    Currently Being Moderated
    This is neither a Tomcat 7 or an UCP support forum. Doing a Google for "tomcat 7 UCP" returns some mighty interesting results for you to examine, for example this one:

    http://www.mt-ag.com/fileadmin/fachbeitraege/UCP_with_Tomcat_7_and_Oracle_11g.pdf

    People have succesfully used the combination, now go figure out what you need to do to repeat the success.
  • 2. Re: jdbc connections pooling using Universal Connection pool
    vr*446726*ik Newbie
    Currently Being Moderated
    Hi,

    thanks for reply. I am aware of the documentation available.
    The tomcat description is there just to describe my environment.

    Where should I post UCP related questions? I am sorry but I haven't found any better forum to ask question related oracle jdbc pool quesiton.

    According the stacktrace I provided the problem is inside Oracle UCP.

    Regards,
    Zdenek
  • 3. Re: jdbc connections pooling using Universal Connection pool
    rp0428 Guru
    Currently Being Moderated
    >
    According the stacktrace I provided the problem is inside Oracle UCP.
    >
    But you also said this
    >
    Seems like two thread are creating same pool and one fails because of the other thread registered the pool sooner.
    >
    And you haven't posted ANY code that shows how the pool is being created or the calls that do that.
  • 4. Re: jdbc connections pooling using Universal Connection pool
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    gimbal2 wrote:
    This is neither a ... an UCP support forum.
    Yes it is.

    UCP is an Oracle generic connection pool. This site is the support site for Oracle products.

    The UCP document page

    http://docs.oracle.com/cd/E11882_01/java.112/e12265/toc.htm#BEGIN

    From the bottom of that page

    +"Use the OTN forums for product questions."+

    And the link from that is this site.

    And this would appear to be the best forum for questions related to both the Oracle JDBC driver and UCP.
  • 5. Re: jdbc connections pooling using Universal Connection pool
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    vr**** wrote:
    which was part of oracle jdbc driver jar file.
    Where is the driver jar loaded from?
  • 6. Re: jdbc connections pooling using Universal Connection pool
    vr*446726*ik Newbie
    Currently Being Moderated
    rp0428 wrote:
    >
    According the stacktrace I provided the problem is inside Oracle UCP.
    >
    But you also said this
    >
    Seems like two thread are creating same pool and one fails because of the other thread registered the pool sooner.
    >
    And you haven't posted ANY code that shows how the pool is being created or the calls that do that.
    I use tomcat resource which calls the oracle.ucp.jdbc.PoolDataSourceImpl factory to create oracle.ucp.jdbc.PoolDataSource
  • 7. Re: jdbc connections pooling using Universal Connection pool
    vr*446726*ik Newbie
    Currently Being Moderated
    jschell wrote:
    vr**** wrote:
    which was part of oracle jdbc driver jar file.
    Where is the driver jar loaded from?
    ojdbc and ucp jars are in tomcat/lib directory
    no war file contains any jdbc nor ucp jars.

    Pools are defined in Tomcat's conf/context.xml and are created per web application.
  • 8. Re: jdbc connections pooling using Universal Connection pool
    vr*446726*ik Newbie
    Currently Being Moderated
    I use JGroups distributed lock service, because of Amazone EC2 doesn't allow UDP multicast I have to use JDBC_PING which needs jdbc pool
    to use database as cluster topology broadcast "service".

    I have created dedicated jdbc pool only for JGroups JDBC_PING which doesn't use Oracle Universal connection pool but the older pooling
    and the exception is never thrown.

    Still I think this is something UCP should handle correctly too.
  • 9. Re: jdbc connections pooling using Universal Connection pool
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    vr**** wrote:
    jschell wrote:
    vr**** wrote:
    which was part of oracle jdbc driver jar file.
    Where is the driver jar loaded from?
    ojdbc and ucp jars are in tomcat/lib directory
    no war file contains any jdbc nor ucp jars.
    Which, I believe, means that the driver/ucp is loaded by the system class loader (or up chain from the app class loader.)

    So only one instance gets loaded.

    Then you initialize it twice.

    You can put it into the war or create an initializer in each app which, on app start up, does a do nothing db query, and drops the expected exception.
  • 10. Re: jdbc connections pooling using Universal Connection pool
    vr*446726*ik Newbie
    Currently Being Moderated
    >>
    >
    Which, I believe, means that the driver/ucp is loaded by the system class loader (or up chain from the app class loader.)

    So only one instance gets loaded.

    Then you initialize it twice.

    You can put it into the war or create an initializer in each app which, on app start up, does a do nothing db query, and drops the expected exception.
    That is exacly way I have it now. Works for 99% of my code, but I use the JDBC_PING in JGroups and user realm uses database too. I don't have any access to the code from my application to wrap db calls and retry if any exception is thrown.

    I tried moving the resources to global resources and use only references in web applications, but I get the exception too.
    The pool is created just once as it is global resource, I thought that should help avoid multiple initializations, but didn't. I can't move the UCP to war file lib directory as the connections are used not only in the web application.

    Any ideas what to test more are welcome.
  • 11. Re: jdbc connections pooling using Universal Connection pool
    SamWu Newbie
    Currently Being Moderated

    I had the same exception every time my code tries to get a connection after restarting/reloading the tomcat app. I had to catch the exception and get a connection again if the exception is this kind and it works well. I am running Tomcat 6 in a Windows 2008 server. Gosh, this has consumed a few days of time by trying all the possible solutions on the Internet.

  • 12. Re: jdbc connections pooling using Universal Connection pool
    35ad3a60-77e5-4581-927b-9e518275243b Newbie
    Currently Being Moderated

    I was getting the same error reported because Tomcat was creating multiple instances of my ObjectFactory after Tomcat started and I got several concurrent requests for the same resource.  I am using Tomcat 7 and my datasources are defined in server.xml with ResourceLinks in my context.xml.  I fixed my problem by synchronizing my DataSource JNDI lookup calls.  This was easy for me because I have a wrapper for looking up JNDI DataSources in my code.  If you don't, you might be able to add the Global Resources Lifecycle Listener to your server.xml.  It appears as if Tomcat will load your global resources at startup if this is enabled, but I haven't tested it completely:

     

      <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />

     

     

    gl

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points