Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Go to solution
Michele (Mike) Alberton
Regular Advisor

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

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
9 REPLIES
Thierry Poels_1
Honored Contributor

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

Hi,

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

good luck,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Michele (Mike) Alberton
Regular Advisor

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

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
Honored Contributor

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


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
Honored Contributor

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

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.
what you do not see does not mean you should not believe
Sanjiv Sharma_1
Honored Contributor

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

Hi Mike,

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

hth.
Everything is possible
Michele (Mike) Alberton
Regular Advisor

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

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
Regular Advisor

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

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
Honored Contributor
Solution

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

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
Regular Advisor

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

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