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: Sybrand Bakker on 24 Feb 2006 15:18 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. -- Sybrand Bakker, Senior Oracle DBA
From: ianal Vista on 24 Feb 2006 22:39 "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. 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.
From: Robert Klemme on 27 Feb 2006 03:16 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
From: Robert Klemme on 27 Feb 2006 03:34 Frank van Bortel wrote: > shortcutter(a)googlemail.com wrote: >> Yes. First of all, the setup works normal most of the time, so the >> registration has actually taken place (this is true even after the >> intermittend failure). Also, the listener is local and firewall rules >> are ok (=> no network problems). 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). Fourth, we >> actually saw an improvement because before we changed the value we >> saw the error more frequently. >> > > "Most of the time" ... ok, you investigate then. That's exactly what I am trying but until now I didn't see any hint as to *how* to investigate this. > Listener been down? DB been down? Nope. > Registration takes time, you know. Guess so. > And if it's local, then why bother with a listener *at all*?!? The listener is local to the db server. The application sits on a different machine. > And if it's local, then what would a firewall have to do > with it? You're local, you shouldn't even see your > packets on the net. Switch to bequeath, it's faster, too. With iptables it's actually possible to close the loopback device and thus to prevent even local communication. I just wanted to exclude a possible cause of connection problems. That's why I mentioned it. > And if you (still) believe processes could be too low, > then increase that, not your parallel_max_servers. > > And your improvement is coincidence, nothing else I'd be glad for a hint to actually verify that. Any tracing or debugging I can do? robert
From: Robert Klemme on 27 Feb 2006 03:43
bdbafh(a)gmail.com wrote: > Robert, > > How many physical CPUs (and logical, if hyperthreading is enabled) are > in that server? 2 CPU with HT. > What type of storage subsystem is supporting the database? RAID 10, 1% of space used. > Can you reasonably expect a degree of parallelism of greater than 4 > off of a dual CPU box? > > If you don't need to support parallelism, I'd suggest turning it off. Thanks for that hint. I'll dig into that. At the moment there are 42 processes: [root] r1:/root: ps -e | fgrep oracle | wc -l 42 (no kidding :-)) > Are you sure that you didn't hit some other resource restriction other > than "processes"? No. That's why I try to find out what's going on. Any hints how to debug this? > Might you have hit the process limit (memory consumption) or have > exhausted a kernel resource? I don't think so (see below). Mem is plenty and CPU load is quite low. > Check your OS kernel settings (sysctl) I had a look and couldn't see anything obvious. Might need to look a bit more into this. > and memory allocations. [root] r1:/root: cat /proc/meminfo MemTotal: 4151320 kB MemFree: 744332 kB Buffers: 126580 kB Cached: 2699632 kB SwapCached: 508 kB Active: 2660996 kB Inactive: 579012 kB HighTotal: 3276544 kB HighFree: 215104 kB LowTotal: 874776 kB LowFree: 529228 kB SwapTotal: 4194288 kB SwapFree: 4171008 kB Dirty: 284 kB Writeback: 0 kB Mapped: 981652 kB Slab: 111280 kB Committed_AS: 1677032 kB PageTables: 42460 kB VmallocTotal: 106488 kB VmallocUsed: 4432 kB VmallocChunk: 101588 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB Looks as if there were plenty resources available. Thanks again! Kind regards robert |