Fixing the ORA-12519: TNS:no appropriate service handler found error
Here’s a quick post to share a quick fix to a problem that I faced this morning while trying to setup a proof-of-concept environment for the Oracle Service Bus. If you think I’m writing Greek, then you may skip this rather g(r)eeky post. However, if you’re like me having this problem and wondering why there are so little post online that easily explains what the problem is and how to quickly fix it, then here’s a quick tip to save your time trying to figure out what’s wrong.
Perhaps I should have consulted a more experienced DBA. Afterall, a database to me is just a database, and it should just work. So, when WebLogic Server threw me the “ORA-12519: TNS:no appropriate service handler found” exception, I too wondered if I was reading Greek, or perhaps maybe even Ebonian. I knew it had to do with server’s connection to the Oracle DB XE that I was using for the environment setup, but I just couldn’t figure out what it was, especially since the connection test worked!
Googling ORA-12519 initially didn’t help much. Most of the search results mentions the following:-
Cause: The listener could not find any available service handlers that are appropriate for the client connection.
Action: Run “lsnrctl services” to ensure that the instance(s) have registered with the listener, and are accepting connections
This is perhaps due to the fact that the Oracle documentation on this error states exactly that. And the database listener is running alright. So that’s not going to solve my problem.
After fumbling about a
little lot, and spending hours trying to ‘rambo’ myself over this problem, I figured that it might be due to the rather limited concurrent connections or db processes that was allowed by this license-free Express Edition. So there I went searching for the command to increase something around this area for Oracle Database XE. And sure enough, I found recommendations to do this exact step on this ‘limited’ version of the database.
Cutting the long story short, all you need to do is the few simple steps as the following:-
- Run SQL*Plus and 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 the SQL*Plus
- VERY IMPORTANT: Restart the database.
Once done. you should no longer get the ORA-12519 error!
So there you go, a simple line of command to save hours of hair pulling trying to figure out what’s wrong. Don’t blame yourself for being incompetent. Just blame poor documentation and console yourself that this is a case where even if you RTFM, it won’t help anyway. But perhaps getting yourself skilled up with a book like the following won’t hurt either. 🙂
Please Google +1 my article if you think it was useful. Thanks!