Re: Intermittend ORA-12519, TNS:no appropriate service handler found

"Robert Klemme" <bob.news_at_gmx.net> wrote in news:468up4Fa0hipU1_at_individual.net:

>
> Hi all,
>
> we have an Oracle 10 (version details below) on Linux (RHEL 4). When
> connecting via JDBC we get intermittend ORA-12519 (reflected as
> TNS-12519 in listener.log). sqlldr also has a problem, although at
> the moment I can't exactly determine whether it's the same (I'm
> guessing it is because the happen about the same time).
>
> Research on the web revealed that a too low value for "processes"
> might be the reason. (The other possible cause I found was non
> matching versions of DB and client but this is not the case here.) So
> we increased DB param "parallel_max_servers" to 200. Since the error
> still showed up we went up to 400. It's been quiet since the last
> change of this parameter on Tuesday but some minutes ago I got an
> email notification that the error occurred again.
>
> I rather not want to increase the value by trial and error since we
> have only 36 sessions on the database right now and there seems to be
> a discrepancy between parameter "processes" (at 150 now, the value is
> derived from "parallel_max_servers") and the actual # of processes.
> Also the system is not much utilized and there's enough free resources
> (CPU wise and memory wise). So I'd like to first find out what is
> causing this error before I take further measures.
>
> I checked the alert log but there were no significant entries. I
> checked job scheduling to check whether there might be a job that eats
> up connections, but no. I guess switching on some trace might be
> helpful but at the moment I don't have an idea which one would be
> appropriate. Any ideas? Thanks for any insights!
>

I fought this battle earlier this month. The problem is that the more recent version listeners "count" the incominng connection requests. When the count would exceed the processes value the ORA-12519 error is raised. The problem is that the listener does not really count the disconnetions. I learns of them only periodically. If/when you have many, many short lived connections you can see this error.

You can plainly see this phenomenum by
tail -f listener.log

You see a rash of failures, a status update and all quiet until the failures return.

Just keep raising the processes value until the errors stay away. Received on Fri Feb 24 2006 - 21:39:47 CST