Issue with Oracle ORA-00020: maximum number of processes (850) exceeded

[Michele (Mike) Alberton]

Hi !

This is strange, we have an application on top of Hp-UX 11.00 and Oracle 9.2.0. Suddenly during startup one of the core processes dies with an ORA-00020 execeeding the current setting of 850 processes. The strange thing is that the number of Oracle instances at that time was abou 15, well below the limit. I tried to shutdown the DB, increase the value from 850 to 1500, but again the error showed up with (850). I thought of a bad shared memory segment but I could not find any SM segment nor hanging semaphore belonging to our oracle user.
Any idea ?

Thanks !

Mike

[Thierry Poels_1]

Hi,

one of the kernel parameters will probably be the cause:
check your shared memory kernel parameters
check maxuprc
...

good luck,
Thierry.

[Michele (Mike) Alberton]

Hi Thierry !

Unfortunately we're fine with maxuprc. My concern is with Oracle, why is it showing 850 when I have set up 1500 as max processes and recycled the instance ?

I ran ipcs -sa|grep ora and ipcs -ma|grep ora once the DB was shutdown and nothing was shown, but still the wrong information about the processes is around. Where should I look ?

Thank You !

Mike

[Hein van den Heuvel]

Maybe processes is limited by sessions?

>>> The strange thing is that the number of Oracle instances at that time was abou 15, well below the limit

Do you really mean instances here or connections?

What does a report from v$resource_limit show? Try this:

set pages 9999
column NAME format A25
column INIT format A10
column LIMIT format A10
SELECT RESOURCE_NAME "Name", CURRENT_UTILIZATION "Current",
MAX_UTILIZATION "Max", INITIAL_ALLOCATION "Init", LIMIT_VALUE "Limit"
FROM V$RESOURCE_LIMIT;

Hein.

[Joseph Loo]

hi,

it is the number of processes in the init.ora file.

refer to this metalink doc, 18225.1:

Error: ORA 20
Text: maximum number of processes exceeded
- Cause: An operation requested a resource that was unavailable. The maximum number of processes is specified by the initialization parameter PROCESSES. When this maximum is reached, no more requests are processed.

Action: Try the operation again in a few minutes. If this message occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

regards.

[Sanjiv Sharma_1]

Hi Mike,

Please check this earlier posts:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=222600

hth.

[Michele (Mike) Alberton]

Hi,

I took a better look and whenever I log into SQLplus using sqlplus USER/PASSWORD everything goes fine.

The issue arises when I try to sqplus USER/PASSWORD@NSMS

NSMS being our ORACLE_SID.

The v$resource is properly updated and shows the 1500 value I manually changed, but still trying the second version I get the 850 message.

Does it give you any more hints ?

Thanks !

Mike

[Michele (Mike) Alberton]

Some more clues:

shutting down the listener it still gives me the chance the login through sqlplus USER/PASSWORD@NSMS

which shouldn't be allowed.

Mike

[Hein van den Heuvel · Accepted solution]

You would not happen to be going to a whole different box no? :-).

The listener can be stopped if the connection method specified in tnsnames is the BEQuet protocol.

Check out your tnsnames.ora and sqlnet.ora with a fine comb. Be sure you are lookign in the right place! Verify $ORACLE_HOME for both the client processes and the oracle user itself.

Hein.

[Michele (Mike) Alberton]

Hein,

you won the full prize !

Actually that was the issue, someone modified the TNSNAMES file to allow SQLNet access from a remote host, mesing up the host names. We were going to a different box, with the same app running, so that the message was correct.

Nice shot, thanks to all for the precious suggestions !

Mike