Fixing the ORA-12519: TNS:no appropriate service handler found error

Share this:

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

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

ORA-12519: TNS

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. 🙂

Share this:

You may also like...

52 Responses

  1. Christian says:

    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.

  2. Thanks for the good article. It helped a lot. I wrote an blog entry in german myself and referenced to your one.

  3. Ken Ng says:

    Thanks for the linkback! Glad I helped in my little way.

  4. thaaaaaaaaaaaanks! It helped me a lot.
    I solved 2 minutes before a Demo.

    Thanks again men!

    Regards!

  5. Ken Ng says:

    Glad I was able to help 😀 That’s why I posted the blog post in the first place!

  6. Sameer says:

    Thanks a lot. it worked for me

  7. mihai says:

    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.

  8. Yenny says:

    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. =(

  9. ..tia.. says:

    Thanks a lot! anybody else that hates Oracle docs/error descriptions?

  10. Anonymous says:

    LOL. Ditto!

  11. Onetwo says:

    Fixed my issue! thanks a million!

  12. Anonymous says:

    Glad my post was helpful 🙂

  13. Santoshkosgi says:

    thanks a lot…..i was tryin to figure out this since a month but couldn’t……..it was very useful post for me

  14. Apalop says:

    Thanks a lot!!! 2 days creating a deleting weblogic domains thinking it was a WLP problem

  15. Suhas Phanasalkar says:

    Thank you so much! This is really helpful.
    -Suhas

  16. Jon Perry says:

    Thanks so much. A lot of time and effort saved by reading this.

  17. Andyj748 says:

    Excellent help. Thanks.

  18. Robin says:

    Super duper! Was fighting with same issue for some time today…

  19. Anonymous says:

    Glad I’m able to help 🙂

  20. Thanks for this!

  21. carlossc says:

    Thanks a lot.

  22. Kevin says:

    Thank you.  This fixed my problem.
     

  23. Perlcat says:

    I think it was the beer that did it.

  24. ratnakar srivastava says:

    Thanks a lot this doc is really awesome

  25. Guest says:

    I was analyzing for hours. This article was the solution for my problem. Thanks a lot.

  26. Esso6919 says:

    thank you — muchas gracias !

  27. Matejbor says:

    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.

  28. Ken Rach says:

    Thanks… good explanation!

  29. Ajinkya says:

    You are life saver 🙂

  30. umesh says:

    Its great man!

  31. Din says:

    Thanks much. Helped a lot.

  32. Yanko says:

    Thanks buddy! This helped me a lot!

  33. rose says:

    thanks a lot!your advice is probably the most useful i’ve found

  34. Dinakar says:

    thanks a lot friend!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  35. Amgad Gorgui, says:

    Thank you, i kept searching for days, why my insert is getting such an error.

  36. Jaime says:

    Worked for me. The message was really misleading. Thank you.

  37. Saurabh says:

    Awesome Man 🙂 Saved a lot of time which I would have further wasted.

  38. dede says:

    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

  39. Ken Ng says:

    Try the following set of commands to fix that.

    create spfile from pfile;
    shutdown immediate;
    startup;
    show parameter spfile;

  40. felix says:

    Thanks.

  41. vikas says:

    Thanks a lot….I was facing this stupid issue while running my kettle transformation.

  42. abuzuhair says:

    Great solution, thanks

  43. great tip dude!!! thanks a lot!!!

  44. JJ says:

    Many thanks it’s working. I keep my hairs.
    For non DBA people after command on spfile do:
    shut immediate
    startup

  45. tumbgur says:

    thank you very much! It worked for me too!

  46. Jean Levy says:

    Thanks my friend!!

  47. sikharis says:

    very usefull for my problem, thanks 🙂

  48. RikArdo Villarruel says:

    Thnx man!! finally, a useful post without turkish bla bla bla and straight to the point.

  49. Shubhashish says:

    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.

  50. Chandrasekhar says:

    It didn’t work for me. Can you give me reset command for above and let me know any other solution for it

  51. Ken Ng says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.