Intermittend ORA-12519, TNS:no appropriate service handler found
in [Oracle]
|
Prev: Oracle MERGE to remote DB with ORA-01008: not all variables bound
Next: Oracle RAC 10g_r2: Oracle Notification Server Configuration Assistant - FAILED
From: Robert Klemme on 27 Feb 2006 04:09 ianal Vista wrote: > "Robert Klemme" <bob.news(a)gmx.net> wrote in > news:468up4Fa0hipU1(a)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: - We use a connection pool, so we should not see so many new connections with HOST=__jdbc__. I'll have to dig into this (could be a timeout issue or a bug in the pool implementation). - Fequency of service_update is actually quite high so I would believe that the listener's idea of live connections matches the databases's idea pretty well (and timely so). > 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
From: Maxim Demenko on 27 Feb 2006 05:23 Robert Klemme schrieb: > Sybrand Bakker wrote: > >>On 24 Feb 2006 11:35:34 -0800, "shortcutter(a)googlemail.com" >><shortcutter(a)googlemail.com> wrote: >> >> >>>Second, it was explained that the >>>error will occur if "processes" is set too low. Third, the parameter >>>"processes" is derived from "parallel_max_servers" (indicated by >>>online doc, and you cannot change it directly via EM). >> >>Your understanding is incorrect. Processes doesn't derive from >>parallel_max_servers at all. > > > This is the documentation linked from the EM page: > > PROCESSES > Property Description > Parameter type Integer > Default value Derived from PARALLEL_MAX_SERVERS > Modifiable No > Range of values 6 to operating system-dependent > Basic Yes > Real Application Clusters Multiple instances can have different values. > > PROCESSES specifies the maximum number of operating system user > processes that can simultaneously connect to Oracle. Its value should > allow for all background processes such as locks, job queue processes, > and parallel execution processes. > > The default values of the SESSIONS and TRANSACTIONS parameters are > derived from this parameter. Therefore, if you change the value of > PROCESSES, you should evaluate whether to adjust the values of those > derived parameters. > > http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10755/initparams167.htm#REFRN10175 > > Kind regards > > robert > Sybrand is in my opinion correct. First, the doc states "PROCESSES" is not derived from "PARALLEL_MAX_SERVERS", but only its *default value* - it means, it take place only if you don't have the value for "PROCESSES" specified ( which is IMO anyway bad idea, especially, if you have more than one database on the machine - you should specify this parameter) Second, the from you cited description of "PROCESSES" is similar in the documentation 8i - 10gR1, but in 10gR2 default value is described as "40 to operating system-dependent". I tested on 9.2 with parallel_max_servers set to 0,10,100,1000 and processes unset - got every time 30 as default value for processes. The same test on 10gR2 showed me 40 ( as per docs ). I'll check it for other plattforms/releases for sure, but i tend to believe right now - there is a documentation bug regarding default value for "PROCESSES" which is fixed in 10gR2. Best regards Maxim
From: Robert Klemme on 27 Feb 2006 07:56 Maxim Demenko wrote: > Sybrand is in my opinion correct. > First, the doc states "PROCESSES" is not derived from > "PARALLEL_MAX_SERVERS", but only its *default value* - it means, it > take place only if you don't have the value for "PROCESSES" specified > ( which is IMO anyway bad idea, especially, if you have more than one > database on the machine - you should specify this parameter) > Second, the from you cited description of "PROCESSES" is similar in > the documentation 8i - 10gR1, but in 10gR2 default value is described > as "40 to operating system-dependent". > I tested on 9.2 with parallel_max_servers set to 0,10,100,1000 and > processes unset - got every time 30 as default value for processes. > The same test on 10gR2 showed me 40 ( as per docs ). I'll check it > for other plattforms/releases for sure, but i tend to believe right > now - there is a documentation bug regarding default value for > "PROCESSES" which is fixed in 10gR2. Thanks for the analysis. I'll change that to 250 and how this works out. I also noticed lines like this in listener.log WARNING: Subscription for node down event still pending A forum posting indicates that the error might be related to the machine's LANG setting. Maybe this is related and the listener doesn't get properly notified of events. I'll chang LANG setting to "en" and restart the DB. I'll let you know the outcome. Kind regards robert
From: frank.van.bortel@gmail.com on 27 Feb 2006 08:39 This info is new, and crucial. I think you have a problem with your connection pool; it should not connect at a rate of one or more connections per second. Try to change the database setup and create one or more dispatchers. I have seen *x systems go down to their knees with many connections, especially because most systems take their time to clean up disconnected sessions. netstat may be of help here - check if you have many waiting (TIME_WAIT). If you do, the rate of connect/disconnect per time unit may be what's causing your troubles. I do not know of a way to speed up the cleanup of disconnected sessions, but that does not mean there isn't - just my lack of knowledge ;). Anyway - if indicators point towards disconnects/connects you pooling doesn't work, and dispatchers is the way to go. Regards, Frank van Bortel
From: Robert Klemme on 27 Feb 2006 12:47
frank.van.bortel(a)gmail.com wrote: > This info is new, and crucial. > I think you have a problem with your connection pool; it should not > connect at a rate of one or more connections per second. It might not be the pool but how it's used. Or something else altogether (for example, connections are closed if there is an error under certain conditions => could be that these errors happen more frequently than expected). > Try to change the database setup and create one or more dispatchers. I'll look into this. Thanks for the input! > I have seen *x systems go down to their knees with many connections, > especially because most systems take their time to clean up > disconnected sessions. > netstat may be of help here - check if you have many waiting > (TIME_WAIT). From what I've seen so far the machine is far from going down because of this but I'll try to dig into that. > If you do, the rate of connect/disconnect per time unit may be what's > causing your troubles. I do not know of a way to speed up the cleanup > of disconnected sessions, but that does not mean there isn't - just my > lack of knowledge ;). > Anyway - if indicators point towards disconnects/connects you pooling > doesn't work, and dispatchers is the way to go. Thanks again! Kind regards robert |