oracle 具有多个数据源的 Tomcat 6/7 JNDI

Tomcat 6/7 JNDI with multiple datasources

提问by Dreamer

When there are more than one <Resource>elements in context.xml and more than one <resource-ref>elements in web.xml, my application begins to throw

当<Resource>context.xml中有多个元素并且<resource-ref>web.xml中有多个元素时，我的应用程序开始抛出

TNS:no appropriate service handler found

and

和

ORA-01017: invalid username/password; logon denied

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

但是，如果 JNDI 中只有一个数据源，这意味着另一个使用常规 JDBC 数据源，则应用程序运行起来就像一个魅力

Both data sources come from same db URL but use different schema.

两个数据源来自相同的数据库 URL，但使用不同的架构。

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?

我的猜测是它可能是由于每个资源的相同数据库 URL 具有不同的用户名/密码（架构）引起的。但是 tomcat 应该能够处理这种情况，所以我的推理是我错过了一些配置？

Another interesting finding is: When I use jdbc url jdbc:oracle:thin:@myhost:1521:orclwith SQL Developer to setup a connection, sometimes it connects without issue, but sometimes it gets rejected with the same issue: appropriate service handler foundwhile 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?

另一个有趣的发现是：当我使用 jdbc urljdbc:oracle:thin:@myhost:1521:orcl和 SQL Developer 设置连接时，有时它连接没有问题，但有时它会因相同的问题appropriate service handler found而被拒绝：同时此 Web 应用程序处于活动状态。但是，相同的 JDBC URL 可以与具有常规 JDBC 连接（不是 JNDI）的另一个 Spring 应用程序一起正常工作。那么诀窍是什么？

Here are the details of current config:

以下是当前配置的详细信息：

In Context.xml

在 Context.xml 中

<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" />
<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" />

In Web.xml of the application:

在应用程序的 Web.xml 中：

<resource-ref>
    <description>Oracle Datasource for app_A</description>
    <res-ref-name>jdbc/app_A</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>
<resource-ref>
    <description>Oracle Datasource for app_B</description>
    <res-ref-name>jdbc/app_B</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

In ApplicationContext.xml

在 ApplicationContext.xml 中

<jee:jndi-lookup id="dataSource1" jndi-name="java:comp/env/jdbc/app_A" resource-ref="true" />
<jee:jndi-lookup id="dataSource2" jndi-name="java:comp/env/jdbc/app_B" resource-ref="true" />

And finally I get exception piled up like this:

最后我得到这样的异常堆积：

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.<init>(PhysicalConnection.java:531)
at oracle.jdbc.driver.T4CConnection.<init>(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.<init>(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

Really not sure why the no appropriate service handler founderror pops up, it seems the connection is not accepted/understood by orcl Listener.

真的不确定为什么会no appropriate service handler found弹出错误，似乎 orcl 侦听器不接受/理解该连接。

Here is what I insert into persisntence.xml

这是我插入perssntence.xml的内容

<persistence-unit name="persistenceUnit1">
....
<jta-data-source>jdbc/app_A</jta-data-source>
....
</persistence-unit>

<persistence-unit name="persistenceUnit2">
....
<jta-data-source>jdbc/app_B</jta-data-source>
....
</persistence-unit>

采纳答案by maksim_khokhlov

ORA-12519, TNS:no appropriate service handler founderror might be the result of using an old-style JDBC connection string. According to chapter 8 Data Sources and URLsof Oracle 11.1 JDBC Developer's Guide and Reference, connection string format is following:

ORA-12519, TNS:no appropriate service handler found错误可能是使用旧式 JDBC 连接字符串的结果。根据Oracle 11.1 JDBC Developer's Guide and Reference 的第 8 章数据源和 URL，连接字符串格式如下：

jdbc:oracle:thin:@//host_name:port_number/service_name

There's also a note saying "Starting Oracle Database 10g, Oracle Service IDs are not supported".So the syntax you're using must have been suitable for Oracle 9i. It might work on newer versions, but that's not guaranteed.

还有一条注释说“从 Oracle 数据库 10g 开始，不支持 Oracle 服务 ID”。所以您使用的语法一定适用于 Oracle 9i。它可能适用于较新的版本，但不能保证。

So consider changing the format of your JDBC connection strings to follow the format suggested in the guide.

因此，请考虑更改 JDBC 连接字符串的格式以遵循指南中建议的格式。

Also, for Oracle 9i onwards you should use oracle.jdbc.OracleDriverrather than oracle.jdbc.driver.OracleDriveras Oracle have stated that oracle.jdbc.driver.OracleDriveris deprecated and support for this driver class will be discontinued.

此外，对于 Oracle 9i 以后的版本，您应该使用oracle.jdbc.OracleDriver而不是oracle.jdbc.driver.OracleDriver像 Oracle 声明的那样oracle.jdbc.driver.OracleDriver不推荐使用，并且将停止支持该驱动程序类。