title: Tomcat 6/7 JNDI with multiple datasources
body: <p>When there are more than one <code>&lt;Resource&gt;</code> elements in context.xml and more than one <code>&lt;resource-ref&gt;</code> elements in web.xml, my application begins to throw</p>
  
  <pre><code>TNS:no appropriate service handler found
  </code></pre>
  
  <p>and</p>
  
  <pre><code>ORA-01017: invalid username/password; logon denied
  </code></pre>
  
  <p>However, if there is only one of the data sources in JNDI, meaning the other one use regular JDBC data source, the application runs like a charm</p>
  
  <p>Both data sources come from same db URL but use different schema.</p>
  
  <p>My guess is that it may be caused by the same database URL of each resources with different username/password(schema). But tomcat should be capable of handling such situation, so my reasoning is that there maybe some configuration I missed?</p>
  
  <p>Another interesting finding is:
  When I use jdbc url <code>jdbc:oracle:thin:@myhost:1521:orcl</code> with SQL Developer to setup a connection, sometimes it connects without issue, but sometimes it gets rejected with the same issue: <code>appropriate service handler found</code> while this web application is active at the same time. However, the same JDBC URL works fine with another Spring application with regular JDBC connection(not JNDI). So what is the trick?</p>
  
  <p>Here are the details of current config:</p>
  
  <p>In Context.xml</p>
  
  <pre><code>&lt;Resource name="jdbc/app_A" auth="Container" type="javax.sql.DataSource"
      driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@myhost:1521:orcl"
      username="usernameA" password="passwordA" maxActive="20" maxIdle="10" maxWait="-1" /&gt;
  &lt;Resource name="jdbc/app_B" auth="Container" type="javax.sql.DataSource"
      driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@myhost:1521:orcl"
      username="usernameB" password="usernameB" maxActive="20" maxIdle="10" maxWait="-1" /&gt;
  </code></pre>
  
  <p>In Web.xml of the application:</p>
  
  <pre><code>&lt;resource-ref&gt;
      &lt;description&gt;Oracle Datasource for app_A&lt;/description&gt;
      &lt;res-ref-name&gt;jdbc/app_A&lt;/res-ref-name&gt;
      &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
      &lt;res-auth&gt;Container&lt;/res-auth&gt;
  &lt;/resource-ref&gt;
  &lt;resource-ref&gt;
      &lt;description&gt;Oracle Datasource for app_B&lt;/description&gt;
      &lt;res-ref-name&gt;jdbc/app_B&lt;/res-ref-name&gt;
      &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
      &lt;res-auth&gt;Container&lt;/res-auth&gt;
  &lt;/resource-ref&gt;
  </code></pre>
  
  <p>In ApplicationContext.xml</p>
  
  <pre><code>&lt;jee:jndi-lookup id="dataSource1" jndi-name="java:comp/env/jdbc/app_A" resource-ref="true" /&gt;
  &lt;jee:jndi-lookup id="dataSource2" jndi-name="java:comp/env/jdbc/app_B" resource-ref="true" /&gt;
  </code></pre>
  
  <p>And finally I get exception piled up like this:</p>
  
  <pre><code>Jan 31, 2013 3:36:55 PM org.apache.catalina.core.NamingContextListener addResource
  WARNING: Failed to register in JMX: javax.naming.NamingException: ORA-01017: invalid username/password; logon denied
  
  Jan 31, 2013 3:36:56 PM org.apache.naming.NamingContext lookup
  WARNING: Unexpected exception resolving reference
  java.sql.SQLException: Listener refused the connection with the following error:
  ORA-12519, TNS:no appropriate service handler found
  
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)
  at oracle.jdbc.driver.PhysicalConnection.&lt;init&gt;(PhysicalConnection.java:531)
  at oracle.jdbc.driver.T4CConnection.&lt;init&gt;(T4CConnection.java:221)
  at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
  at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
  at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
  at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:699)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:631)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:485)
  at org.apache.tomcat.jdbc.pool.ConnectionPool.&lt;init&gt;(ConnectionPool.java:143)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116)
  at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103)
  at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:539)
  at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:237)
  at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:143)
  at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
  at org.apache.naming.NamingContext.lookup(NamingContext.java:843)
  at org.apache.naming.NamingContext.lookup(NamingContext.java:154)
  at org.apache.naming.NamingContext.lookup(NamingContext.java:831)
  at org.apache.naming.NamingContext.lookup(NamingContext.java:168)
  at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1061)
  at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:671)
  at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:270)
  at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
  at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
  at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5173)
  at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
  at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
  at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
  at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:618)
  at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1100)
  at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1618)
  at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
  at java.util.concurrent.FutureTask.run(FutureTask.java:138)
  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
  at java.lang.Thread.run(Thread.java:662)
  Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
  ORA-12519, TNS:no appropriate service handler found
  
  at oracle.net.ns.NSProtocol.connect(NSProtocol.java:385)
  at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1042)
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:301)
  ... 38 more
  Jan 31, 2013 3:36:56 PM org.apache.catalina.core.NamingContextListener addResource
  WARNING: Failed to register in JMX: javax.naming.NamingException: Listener refused the  connection with the following error:
  ORA-12519, TNS:no appropriate service handler found
  
  Jan 31, 2013 3:36:56 PM org.apache.naming.NamingContext lookup
  WARNING: Unexpected exception resolving reference
  java.sql.SQLException: Listener refused the connection with the following error:
  ORA-12519, TNS:no appropriate service handler found
  </code></pre>
  
  <p>Really not sure why the <code>no appropriate service handler found</code> error pops up, it seems the connection is not accepted/understood by orcl Listener.</p>
  
  <p>Here is what I insert into persisntence.xml</p>
  
  <pre><code>&lt;persistence-unit name="persistenceUnit1"&gt;
  ....
  &lt;jta-data-source&gt;jdbc/app_A&lt;/jta-data-source&gt;
  ....
  &lt;/persistence-unit&gt;
  
  &lt;persistence-unit name="persistenceUnit2"&gt;
  ....
  &lt;jta-data-source&gt;jdbc/app_B&lt;/jta-data-source&gt;
  ....
  &lt;/persistence-unit&gt;
  </code></pre>