Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Intermittend ORA-12519, TNS:no appropriate service handler found

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

From: Robert Klemme <bob.news_at_gmx.net>
Date: Mon, 27 Feb 2006 10:09:33 +0100
Message-ID: <46ftudFaunjsU1@individual.net>


ianal Vista wrote:
> "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.

I had found this article which basically states the same: http://forums.oracle.com/forums/thread.jspa?threadID=360226&tstart=0

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

Yep. Looks like you described:

27-FEB-2006 08:52:42 * service_update * orcl * 0

27-FEB-2006 08:52:42 *
(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=80.66.20.195)(PORT=37006)) * establish * orcl * 0

27-FEB-2006 08:52:43 *
(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=80.66.20.195)(PORT=37007)) * establish * orcl * 0

(several lines with TNS-12519)

27-FEB-2006 08:52:45 *
(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=80.66.20.195)(PORT=37038)) * establish * orcl * 12519
TNS-12519: TNS:no appropriate service handler found

27-FEB-2006 08:52:45 *
(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=80.66.20.195)(PORT=37039)) * establish * orcl * 12519
TNS-12519: TNS:no appropriate service handler found

27-FEB-2006 08:52:45 * service_update * orcl * 0

27-FEB-2006 08:52:45 *
(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=80.66.20.195)(PORT=37040)) * establish * orcl * 0

27-FEB-2006 08:52:45 *
(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=80.66.20.195)(PORT=37043)) * establish * orcl * 0

(several more likes with connects ok)

Two things are strange:

> Just keep raising the processes value until the errors stay away.

Hmm... I'd rather find a better solution as this sounds a bit like a workaround.

Thanks for your help!

Kind regards

    robert Received on Mon Feb 27 2006 - 03:09:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US