ORA-12519，TNS ：找不到适当的服务处理程序错误可能是使用旧式JDBC连接字符串的结果。根据第8章数据源和URL Oracle 11.1 JDBC开发人员指南和参考，连接字符串格式如下：

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

还有一个注释说em启动Oracle数据库10g，Oracle服务ID不是支持。所以您使用的语法必须适用于Oracle 9i。它可能适用于较新版本，但不能保证。

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

此外，对于Oracle 9i，您应该使用 oracle.jdbc.OracleDriver 而不是 oracle.jdbc。 driver.OracleDriver ，因为Oracle已经声明， oracle.jdbc.driver.OracleDriver 已被弃用，并且此驱动程序类的支持将被停用。

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

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

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

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?

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

Here are the details of current config:

In 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:

<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

<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 found error pops up, it seems the connection is not accepted/understood by orcl Listener.

Here is what I insert into persisntence.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>

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

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.

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

Also, for Oracle 9i onwards you should use oracle.jdbc.OracleDriver rather than oracle.jdbc.driver.OracleDriver as Oracle have stated that oracle.jdbc.driver.OracleDriver is deprecated and support for this driver class will be discontinued.