oracle 什么会导致间歇性 ORA-12519（TNS：找不到合适的处理程序）错误

What can cause intermittent ORA-12519 (TNS: no appropriate handler found) errors

提问by cynicalman

We are running our Junit 4 test suite against Weblogic 9 in front of an Oracle 10 database (using Hudson as a continuous integration server) and occasionally we will get an ORA-12519 crash during script teardown. However, the error is very intermittent:

我们在 Oracle 10 数据库（使用 Hudson 作为持续集成服务器）前针对 Weblogic 9 运行我们的 Junit 4 测试套件，偶尔我们会在脚本拆卸期间遇到 ORA-12519 崩溃。但是，错误非常间歇性：

• It usually happens for the same Test class
• It doesn't always happen for the same test cases (sometimes they pass)
• It doesn't happen for the same number of test cases (anywhere from 3-9)
• Sometimes it doesn't happen at all, everything passes

• 它通常发生在同一个 Test 类中
• 对于相同的测试用例，它并不总是发生（有时它们会通过）
• 对于相同数量的测试用例（3-9 之间的任何地方）不会发生这种情况
• 有时它根本不发生，一切都过去了

While I can't guarantee this doesn't happen locally (when running against the same database, of course), I have run the same suite of class multiple times with no issues.

虽然我不能保证这不会在本地发生（当然，当针对同一个数据库运行时），我已经多次运行同一套类，没有任何问题。

Any ideas?

有任何想法吗？

采纳答案by cynicalman

Don't know if this will be everybody's answer, but after some digging, here's what we came up with.

不知道这是否会是每个人的答案，但经过一番挖掘，这就是我们想出的。

The error is obviously caused by the fact that the listener was not accepting connections, but why would we get that error when other tests could connect fine (we could also connect no problem through sqlplus)? The key to the issue wasn't that we couldn't connect, but that it was intermittent

该错误显然是由于侦听器不接受连接造成的，但是当其他测试可以正常连接时，为什么我们会收到该错误（我们也可以通过 sqlplus 连接没有问题）？问题的关键不是我们无法连接，而是它是间歇性的

After some investigation, we found that there was some static data created during the class setup that would keep open connections for the life of the test class, creating new ones as it went. Now, even though all of the resources were properly released when this class went out of scope (via a finally{} block, of course), there were some cases during the run when this class would swallow up all available connections (okay, bad practice alert - this was unit test code that connected directly rather than using a pool, so the same problem could not happen in production).

经过一番调查，我们发现在类设置期间创建了一些静态数据，这些数据会在测试类的整个生命周期内保持打开连接，并在进行时创建新的连接。现在，即使当这个类超出范围时所有资源都被正确释放（当然是通过一个 finally{} 块），在运行期间也有一些情况下这个类会吞掉所有可用的连接（好吧，坏练习警报 - 这是直接连接而不是使用池的单元测试代码，因此在生产中不会发生同样的问题）。

The fix was to not make that class static and run in the class setup, but instead use it in the per method setUp and tearDown methods.

解决方法是不使该类静态并在类设置中运行，而是在每个方法的 setUp 和 tearDown 方法中使用它。

So if you get this error in your own apps, slap a profiler on that bad boy and see if you might have a connection leak. Hope that helps.

因此，如果您在自己的应用程序中遇到此错误，请对那个坏男孩使用分析器，看看您是否可能有连接泄漏。希望有帮助。

回答by edwardsmatt

Another solution I have found to a similar error but the same error message is to increase the number of service handlers found. (My instance of this error was caused by too many connections in the Weblogic Portal Connection pools.)

我发现了类似错误但相同错误消息的另一个解决方案是增加找到的服务处理程序的数量。（我的此错误实例是由 Weblogic 门户连接池中的连接过多引起的。）

• Run SQL*Plusand login as SYSTEM. You should know what password you've used during the installation of Oracle DB XE.
• Run the command alter system set processes=150 scope=spfile;in SQL*Plus
• VERY IMPORTANT: Restart the database.

• 运行SQL*Plus并以SYSTEM. 您应该知道在安装 Oracle DB XE 期间使用的密码。
• alter system set processes=150 scope=spfile;在 SQL*Plus 中运行命令
• 非常重要：重新启动数据库。

From here:

从这里：

http://www.atpeaz.com/index.php/2010/fixing-the-ora-12519-tnsno-appropriate-service-handler-found-error/

http://www.atpeaz.com/index.php/2010/fixing-the-ora-12519-tnsno-property-service-handler-found-error/

回答by Rahul Vishwakarma

I also had the same problem, I searched for the answers many places. I got many similar answers to change the number of process/service handlers. But I thought, what if I forgot to reset it back?

我也遇到了同样的问题，我在很多地方搜索了答案。我得到了许多类似的答案来更改进程/服务处理程序的数量。但我想，如果我忘记重新设置回来怎么办？

Then I tried using Thread.sleep()method after each of my connection.close();.

然后我尝试Thread.sleep()在每个connection.close();.

I don't know how, but it's working at least for me.

我不知道如何，但它至少对我有用。

If any one wants to try it out and figure out how it's working then please go ahead. I would also like to know it as I am a beginner in programming world.

如果有人想尝试一下并弄清楚它是如何工作的，请继续。我也想知道它，因为我是编程世界的初学者。

回答by Andrew McGregor

I had this problem in a unit test which opened a lot of connections to the DB via a connection pool and then "stopped" the connection pool (ManagedDataSource actually) to release the connections at the end of the each test. I always ran out of connections at some point in the suite of tests.

我在单元测试中遇到了这个问题，该测试通过连接池打开了与数据库的大量连接，然后“停止”了连接池（实际上是 ManagedDataSource）以在每次测试结束时释放连接。我总是在测试套件中的某个时刻耗尽连接。

Added a Thread.sleep(500) in the teardown() of my tests and this resolved the issue. I think that what was happening was that the connection pool stop() releases the active connections in another thread so that if the main thread keeps running tests the cleanup thread(s) got so far behind that the Oracle server ran out of connections. Adding the sleep allows the background threads to release the pooled connections.

在我的测试的 teardown() 中添加了 Thread.sleep(500)，这解决了问题。我认为发生的事情是连接池 stop() 释放了另一个线程中的活动连接，这样如果主线程继续运行测试，清理线程就会远远落后于 Oracle 服务器耗尽连接。添加睡眠允许后台线程释放池连接。

This is much less of an issue in the real world because the DB servers are much bigger and there is a healthy mix of operations (not just endless DB connect/disconnect operations).

这在现实世界中不是一个问题，因为数据库服务器要大得多，并且有一个健康的操作组合（不仅仅是无休止的数据库连接/断开操作）。

回答by Shendor

I had the similar issue. It happened every time when I run a pack of database (Spring JDBC) tests with SpringJUnit4ClassRunner, so I resolved the issue putting @DirtiesContextannotation for each test in order to cleanup the application context and release all resources thus each test could run with a new initalization of the application context.

我有类似的问题。每次我使用 运行一包数据库（Spring JDBC）测试时都会发生这种情况SpringJUnit4ClassRunner，因此我解决了@DirtiesContext为每个测试添加注释以清理应用程序上下文并释放所有资源的问题，因此每个测试都可以通过新的初始化来运行应用上下文。