 Posted 2/27/2009 2:36:01 PM
|
|
|
|
00020, 00000, "maximum number of processes (%s) exceeded"
// *Cause: All process state objects are in use.
// *Action: Increase the value of the PROCESSES initialization parameter.
What this means is that you have too many database connections. It also means that you as a DBA cannot connect internal to do something about it! When you try to connect to 'sys as sysdba' it gets you to the SQL prompt but you won't be able to do anything.
Here's a couple of things you can do to fix this:
* You need to ask a couple of users to log out of the database, or
* You need to kill some sessions at the OS level.
Once you managed to connect after some sessions have been cleared, see if you can spot anything unordinary making abnormal numbers of connections. If you can't find anything directly you'll need to increase the init parameter 'processes'. Unfortunately, this will require a restart of the database to take effect in some cases where spfile is not used.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 100
SQL> select count(*) from v$process;
COUNT(*)
----------
94
SQL> alter system set processes=500 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 500
In my case I had 94 processes which about 5 of them got cleaned up which allowed me to login. The maximum processes alllowed in the db is 1 - the max init parameter called processes. So if you set your parameter to be 100 then max that can login is 99.
When you reach this you can occasionally get a TNS ora error connecting via TNS when the database is out of processes:
ORA-12516: TNS:LISTENER could not find available handler with matching protocol stack
This is another indication that possible max processes have been reached.
|
|
|
Posted 12/22/2010 9:27:42 AM
|
|
|
|
| ORA-00020, maximum number of processes (%s) exceed Today i ran into the same ora error code regarding maximum number of processes exceeded. I checked and the error message was this: sqlplus '/ as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 22 08:56:15 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR:
ORA-00020: maximum number of processes (1000) exceeded
I couldn't figure out why it was doing this even after checking what the first post mentioned. I did a ps -aef|grep oracle|grep LOCAL and saw that there were only 8 processes running. I thought it reached a 1000 ... couldn't figure that one out and i couldn't login to the database to check. So i went and killed ora_smon process. It crashed the database, then I went to update the init file in $ORACLE_HOME/dbs and when i was saving the file with updated 5000 value for processes, the file saving complained. It said that there is no space left on the /u01 file system where the binaries were sitting. I immediately thought , bingo! i found the problem. I think the OS (RedHat Linux 5) filled/queued up so many zombie processes that the ps -aef|grep LOCAL command didn't pick it up. I should've checked that first. Any how , after updating the parameter to 5000 and re-starting the database all was ok. hope this helps.
|
|
|
|