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: TNS:no appropriate service handler found” 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.
Fixing the ORA-12519: TNS:no appropriate service handler found error
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: TNS:no appropriate service handler found 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. 🙂
This was great. I will be sure to link to this on an post I writing today on using Oracle BI Apps with an Oracle XE database. Upon my attempt, I bumped into the same issue as you did. Ultimately I bumped into another issue that was and impassible restriction of Oracle XE and had to chuck the idea. But good post by you none the less.
Thanks for the good article. It helped a lot. I wrote an blog entry in german myself and referenced to your one.
Thanks for the linkback! Glad I helped in my little way.
thaaaaaaaaaaaanks! It helped me a lot.
I solved 2 minutes before a Demo.
Thanks again men!
Regards!
Glad I was able to help 😀 That’s why I posted the blog post in the first place!
Ingenious !
Thanks a lot. it worked for me
I spent three days trying to resolve this problem, but after that, in a morning after drinking a beer, I found this article and it resolved the problem. Thanks, great work.
Tried but couldn’t get it to work. Or at least not as I want. Still with the value setted to 150, after 45 concurrent calls it throws the exception. =(
Thanks a lot! anybody else that hates Oracle docs/error descriptions?
LOL. Ditto!
Fixed my issue! thanks a million!
Glad my post was helpful 🙂
thanks a lot…..i was tryin to figure out this since a month but couldn’t……..it was very useful post for me
Thanks a lot!!! 2 days creating a deleting weblogic domains thinking it was a WLP problem
Thank you so much! This is really helpful.
-Suhas
Thanks so much. A lot of time and effort saved by reading this.
Excellent help. Thanks.
Super duper! Was fighting with same issue for some time today…
Glad I’m able to help 🙂
Thanks for this!
Thanks a lot.
Thank you. This fixed my problem.
I think it was the beer that did it.
Thanks a lot this doc is really awesome
I was analyzing for hours. This article was the solution for my problem. Thanks a lot.
thank you — muchas gracias !
Thank you for the idea but I cannot agree that this is the solution. It is merely a bypass. There was the same option in Snow Leopard and I didn’t want to use it as I didn’t want my computer waking up with the lid down. But still after sleep I got a connection with my mouse every time. This obviously remains a bug.
Thanks… good explanation!
You are life saver 🙂
Its great man!
Thanks much. Helped a lot.
Thanks buddy! This helped me a lot!
thanks a lot!your advice is probably the most useful i’ve found
thanks a lot friend!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thank you, i kept searching for days, why my insert is getting such an error.
Worked for me. The message was really misleading. Thank you.
Awesome Man 🙂 Saved a lot of time which I would have further wasted.
i was try this command, but can’t resolving this problem
after execute this command, i get this message
SQL> alter system set processes=150 scope=spfile;
alter system set processes=150 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
Try the following set of commands to fix that.
create spfile from pfile;
shutdown immediate;
startup;
show parameter spfile;
Thanks.
Thanks a lot….I was facing this stupid issue while running my kettle transformation.
Great solution, thanks
great tip dude!!! thanks a lot!!!
Many thanks it’s working. I keep my hairs.
For non DBA people after command on spfile do:
shut immediate
startup
thank you very much! It worked for me too!
Thanks my friend!!
very usefull for my problem, thanks 🙂
Thnx man!! finally, a useful post without turkish bla bla bla and straight to the point.
Thanks It’s work like a charm . Prior reading this article I started believing that there must be some glitch in my code. Thanks again.
It didn’t work for me. Can you give me reset command for above and let me know any other solution for it
Chandrasekhar: The command above only increases the number of processes the Oracle DB XE can run concurrently. So its actually good to have that increased even if this didn’t solve your problem.