ORA-12519, TNS:no appropriate service handler found

Published by Luigi June 1st, 2007 in Java, Internet, Software, Web

Oracle buildings...

I used to hate Oracle Database (and other Oracle products too) because of it is much more complicated / heavyweight / slow / buggy and full of useless and sometime harmful stuff than needed. Also when you install you can’t remove it without leaving tons of zombie files around, breaking your JVM/Apache/Windows/etc. After an Oracle installation, your system will never be as before. And hey, have you ever seen an “universal installer” more pathetic than the oracle one?

But I see they are finally realizing that, so they released a “no-frills edition” of their database (160MB sized download, 700MB of memory used on my laptop, 1.2GB of disk space. “no frills”? wow! I’ll try to install it on my cellphone) that can be freely used for development and production. With native installer (happy to see that installer in the garbage)

The good news is that “Oracle Database XE is created from the same code base as the Oracle Database 10g product line”, so that you still have - for free! - the same usual Oracle buggy stuff (“varchar 2, the revenge!”, empty string == null, blob/clob limitation to 4k, etc), bugs, and nice useless error messages like

“ORA-00020: Maximum number of processes (%s) exceeded” (notice the %s)
“ORA-00904: invalid column name” (now guess which one!)
“ORA-00942: table or view does not exist” (when you join 96 tables in a query)
“ORA-00911: invalid character” (when your query is 1 billion character long…)
“ORA-01722: invalid number” (maybe I typed slevin?)
…and much more!

But now you have additional bugs due to the “no-frills edition”.

For example this one: when you use your poor Oracle connected application, after some time of activity you get “ORA-12519, TNS:no appropriate service handler found”, and you don’t get any more connection from the db, with SQLExceptions instead. The quick way to solve, is to restart the listener. Not bad uh, for a “proven industry database”, isn’t that?

Googling around I found that this is a bug on how OracleXE monitors processes, and issuing the following command at SQL command line will fix it (after restarting the listener): “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;”.
Worked for me. This saved me to install Oracle Enterprise on my laptop.

So, let’s run to download this new gem from Oracle and have fun!

57 Responses to “ORA-12519, TNS:no appropriate service handler found”

Feed for this Entry Trackback Address

1 Mikael Gueck Jun 1st, 2007 at 15:06

What are you smoking? I use CLOBs larger than 4k every day. It used to be that you had to handle CLOBs using the standard JDBC Clob type like all JDBC manuals tell you to, but these days the convenience direct read as String works too.
2 Luigi Jun 1st, 2007 at 15:12

:-)

Mikael, check the link
3 James Horsley Jun 15th, 2007 at 00:31

Thanks for the “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;” fix, I found your blog before the other forums page and was very happy to have my unit tests running again. Got to love the spurious oracle weirdness
4 Dharmesh Jun 29th, 2007 at 01:25

I found your page after a lot of searching. How Oracle can do this? I wasted almost a day in searching for this solution to this issue.
5 Xamul Jun 29th, 2007 at 11:10

Mikael, it works for you because you use a patched driver without knowing it
6 Shree Menon (its a Bug's life) Jun 29th, 2007 at 15:42

I stumbled on your blog when I searched “see number of processes in oracle xe”. I been trying to figure out why this is happening and even though I got lot information from other sites (over last 3 days) nothing gave me more relief than to see my problems termed as “BUG(s)”. So its not only Microsoft Monopoly on Bugs. I also got my application running back thanks to your suggestion of “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;”

Just for Information I was using ASP.NET 2.0 and ODP.Net (2.111.5.10)

Thanks a lot Buddy. It might be that streets in Dallas are getting flooded but I am relaxed now.
7 Doug Matthews Nov 8th, 2007 at 16:06

a big THANK YOU for this post, it also saved me from the hell that is Oracle’s confusing error messages!
8 Rene Nov 20th, 2007 at 11:16

Thank you soooooooo much. If I would not have found your fix it would have cost me days. I’ll spend you a few beers, if you are in zurich once.
9 Giovanni Dec 12th, 2007 at 21:39

Hello Luigi,

friendsforfree brought me here…it’s 8:30PM and finally I have been able to install CS (you know what I am talking about…).

Many thanks for this…and I am sure I will become a frequent reader of your blog!

P.S. What about arranging a beer party someday with friendsforfree in duss?

Cheers,
Giovanni
10 dbg Apr 2nd, 2008 at 20:09

Thanks for the TNS workaround, will buy you a beer if you make it to Geneva!
11 Ken Apr 3rd, 2008 at 12:44

Thanks for this, just spent two days fixing after a corrupt database and had to do a complete reinstall of OracleXE and got very frustrated with the ORA-12519 error.

Going to take some headache tablets now and go home )
12 Richard Apr 29th, 2008 at 15:10

Thanks soo much…

You have saved me lots of time.

I appreciate your blog entry.

Regards,
Richard
India
13 tibi May 22nd, 2008 at 22:29

amazing that an product with so much money behind it can have a stupid bug in there product for so many months!!

thanks and nice to read
14 Andree May 30th, 2008 at 11:24

Hi

I had the same problem and find a quite easy solution: Just use less Connections. So if you instantiate the Connection in a class, lett other classes using a db connection know this class. Add a method like getConnection() to give a reference to the connection to every class needing a db connection. than you create statements, close them and so on, but don’t close the connection! That way heavy traffic, many statements or multithreading won’t be a problem any more.

Regards
15 Sean Oct 13th, 2008 at 23:07

I was also getting the “ORA-12519, TNS:no appropriate service handler found” error with Oracle Express. Your fix worked great for me. Thanks for helping me solve this.
16 Luigi Oct 14th, 2008 at 09:26

Happy to be helpful to you!
17 Grupo MightyKhronos Nov 8th, 2008 at 21:12

Obrigado, você salvou nosso tcc =D
18 Michael Hardy Dec 18th, 2008 at 01:25

Greetings,
I obtained this error today after an update of NetBeans to version 6.5. Once I added the ojdbc jar to the lib directory and restarted GlassFish, the error went away.
-Michael
19 siavash Jan 15th, 2009 at 23:25

Thanks Luigi for the post, now I can run my unit tests.
Thanks google for showing this post on top of the search results for TNS:no appropriate service handler found
Thanks oracle for having the bug in the first place
20 eldani Jan 29th, 2009 at 21:27

Great job man! Thank you very much!
21 John Feb 10th, 2009 at 01:47

OMG!!! i lost the whole day for this unkown bug, and your solution was the answer….i keept looking at my code becose i moved the data base… and the code was fine…. its was oracle O_O!!! DAMM thxs
22 Danai SAE-HAN Feb 11th, 2009 at 16:40

Hi!

Your blog post has helped me a lot. Thanks!
Now I can still use Oracle 10g XE within some VMware images instead of having to resort to a full blown Oracle 10g.

–
Danai
23 JP Feb 23rd, 2009 at 08:47

Worked great!
Probably saved me a day or two of running around completely mad, throwing things by the window, getting mad at my wife, thus eventually divorced, probably alcoholic, out of job, etc…
Saved my life, really

Thanks!
–JP
24 Andrey Mar 3rd, 2009 at 07:19

Good sense of humor and a great fix!

P.S. - Oracle does suck.
25 Rajan Mar 11th, 2009 at 23:09

Thanks Buddy.. This post was very useful. Solved my issue right away !!
Thanks again !
26 atomax72 Mar 26th, 2009 at 16:52

ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;

don’t worked for me
I’m running a java program in witch I open and close connection
sometimes, when I try opening one the last was not steel closer and the listener refuses to connect
I put a cicle for sleeping before opening and all work

the solution is to use one connection only (reusing it) as suggested by Andree implementing a getConnection metod and reusing it till the end
27 Luigi Mar 26th, 2009 at 19:28

A connection pool could help reusing the connections, and keeping their number low even if you open/close the connection every time.
28 Nishant Apr 27th, 2009 at 11:56

Im also facing the problem
“Failed to connect to database: ORA-12519: TNS:no appropriate service handler found (DBD ERROR: OCIServerAttach).”

this problem started recently after we created a new pfile to start up database.
Then we created new spfile also.

As per the comments above I checked my parameter list

both Spfile and Pfile is showing
processes=150

Do I again need to run the query “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;” ?
29 Luigi Apr 27th, 2009 at 12:21

I don’t think you need. BTW, if you get the problem, you know how to solve it.
30 Nishant May 4th, 2009 at 15:44

Nooo.. i dont know how to solve it …

I will copy the message from Alert file below.

Alert file
=========
Mon May 04 11:44:05 2009
Process q002 started up but failed with error = 20
Process q002 started up but failed with error = 20
Process q002 started up but failed with error = 20
Process q002 started up but failed with error = 20
Process q002 started up but failed with error = 20
Mon May 04 11:49:18 2009
Process q005 started up but failed with error = 20
Process q005 started up but failed with error = 20
Process q005 started up but failed with error = 20
Process q005 started up but failed with error = 20
Process q005 started up but failed with error = 20
Mon May 04 11:51:02 2009
Private_strands 7 at log switch
Thread 1 advanced to log sequence 44692
Current log# 3 seq# 44692 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLJDE\REDO03.LOG
Mon May 04 11:52:31 2009
Errors in file d:\oracle\product\10.1.0\admin\orcljde\udump\orcljde_ora_3800.trc:
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at “SYS.DBMS_AQ”, line 577
ORA-06512: at “SYSMAN.EMD_NOTIFICATION”, line 492
ORA-06512: at line 1
————————————————————————————————

When I checked individual trace file it shows as below
=====================================

Dump file d:\oracle\product\10.1.0\admin\orcljde\bdump\orcljde_ora_4392.trc
Mon May 04 11:02:28 2009
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 4 - type 586, 2 Physical Cores
Process Affinity: 0×00000000
Memory (A/P) : PH:20392M/24575M, PG:27290M/32151M, VA:940M/2047M
Instance name: orcljde

Redo thread mounted by this instance: 1

Oracle process number: 0

4392

Died during process startup with error 20 (seq=1464)
OPIRIP: Uncaught error 20. Error stack:
ORA-00020: maximum number of processes (150) exceeded
31 Luigi May 4th, 2009 at 15:48

You may try to increase the number of processes further (200 or 250 or more), or call the Oracle support. This issue is an old bug on how Oracle is tracing the processes; so it may have a fix now, hopefully.
32 mhchai Jun 2nd, 2009 at 10:57

Hi

You save my life.. I have been spending days and nights just trying to figure out what’s wrong. I tried many different approached but end up failed.

Thank you so much.

Thank Oracle for giving me such a nightmare experience.
33 krishna Jun 10th, 2009 at 12:05

Thanks Buddy, that was a great tip!!!!!!
34 Oracle Jul 14th, 2009 at 16:16

Nice! Couldn’t have figured it out myself.
35 Jason Jul 24th, 2009 at 22:19

Thanks, this saved me days of frustration, too. Oracle: so much to hate, so much to pay for the privilege of hating it properly.
36 TheKing Aug 20th, 2009 at 16:27

Hi,

Im a db qa, i was facing this ORA-12519 error when ever i build a Oracle base, your blog really helps me in fixing this issue without contacting my devl….
37 Gordon Sep 2nd, 2009 at 17:42

Beautiful fix - worked like a charm!
38 LazyCoder Oct 1st, 2009 at 08:53

Thanks mate, works like a charm, I was worried why I was getting that exception until I landed up on your blog.

Cheers,
LC
39 stephen Coughlan Oct 11th, 2009 at 00:42

next time your in bucharest, ill buy you some beers too
40 JavaGuy Oct 30th, 2009 at 20:33

Works like a champ! Thanks buddy!
41 DaveR Nov 10th, 2009 at 16:06

The logs did not show the real problem. Once I ran the command, the app server “magically” came to life. I was about to re-install the whole thing. Thanks for the tip.
42 ipolevoy Nov 22nd, 2009 at 04:37

Thanks for this post. I implemented the change a bit differently:
Edited file:

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/init.ora

added this at the bottom of file:
processes = 150
SCOPE = SPFILE

I then restarted Oracle, and the problem was gone.
I second that Oracle has just about every default set to some wrong value, and it takes hours/days to get it behave. Coming from MySQL, thi sis really annoying.

cheers
43 melih Nov 24th, 2009 at 15:24

Thanks buddy, it solves the my problem
44 Richard in Cambridge Dec 8th, 2009 at 15:09

Hi Guys,
Thanks to everyone who has contributed to this blog - you have saved me a great deal of heartache!
Luigi - it looks like you can do a ‘pub crawl’ around the world collecting your free beers! You can have one in Cambridge (UK) too!
45 peter43 Jan 4th, 2010 at 20:47

Uff! Now my connection pool is working!

Next time in Berlin, …
46 Zahid Rahim Jan 5th, 2010 at 13:04

Hi my spfile already show 150 processes but i still get this error periodically and this error disappears automatically as well.

can i increase those to around 200 or executing 150 processes statement again will stop this error appearing again??
47 Luigi Jan 5th, 2010 at 13:57

Zahid, you can do that. It’s an Oracle bug, this is just a workaround, until Oracle decides to fix it.
48 Zahid Rahim Jan 5th, 2010 at 14:01

Thanx Luiqi,

I am going to increase process to 200.

Well sorry can’t offer you a beer as i am muslim , But can offer you something else if u ever visit Lahore, Pakistan
49 Miiklay Feb 10th, 2010 at 19:48

Unbelievable. So here I am in 2010 reading a blog post from 2007, and sure enough the fix works. I thought surely this wouldn’t be applicable 3 years later, because surely the bug/fix/ridiculous incompetence by Oracle would be fixed. Right? Wrong.

Thank you very much.
50 Dinesh Feb 19th, 2010 at 00:19

Thanks a lot. I had to increase to 450 processes before my issue went away, because I an inserting a whole bunch of rows using OpenJPA and why a separate process is spawned for every insert is beyond me.