ORA-00020 and Impact on database on increasing processes values

The maximum number of processes is specified by the initialization parameter "processes" . When this maximum number of process is reached, no more requests will be processed. If we are try to connect with database then we get the below errors . 

Here for testing purpose, i have set  my processes value to 30 for this demo .

[oracle@server1 ~]$ sqlplus "sys/xxx as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:36:53 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-00020: maximum number of processes (30) exceeded

I usually check my alert logfile for any oracle errors  and find following info .

Alert logfile
Fri May 24 13:38:30 2013
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.

According  to oracle docs :

Error:  ORA 20  
Text:   maximum number of processes <num> 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.

Finally , i have decided to increase the no. of processes but didn't find any exact formula or any optimal value  to set this parameter . So i have set it to 200 for now . Another issue here with us to connect with oracle, since we getting error while connecting with oracle . Here is one trick to create a session  by using  "Prelim" option . Interesting things about this option is that we can only use the "shut abort" command nothing else (AFAIK).  Here are the steps to set the processes value : 

[oracle@server1 ~]$  sqlplus -prelim  "sys/xxxx as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:38:55 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> shut immediate
ORA-01012: not logged on

SQL>  shut abort
ORACLE instance shut down.

SQL> exit 

Once the instance is down we can easily increase the process value at mount stage  .

[oracle@server1 ~]$ sqlplus "sys/sys as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:51:40 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             310380956 bytes
Database Buffers          100663296 bytes
Redo Buffers                6103040 bytes
Database mounted.

SQL> alter system set processes=200 scope=spfile;
System altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             310380956 bytes
Database Buffers          100663296 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE


Impact on db while increasing the processes  :: 

While googling , i found a very useful comment  by "Jonathan Lewis" .According to him , Increasing processes from say 1000 to 5000 increases the amount of shared memory that needs to be reserved  at the O/S level and disrupt the memory in the SGA. The OS must be configured to support the larger amount of shared memory.

The impact at the O/S is that every process that starts up will want to build a memory map for the SGA - depending on the way we have configured memory pages and the way that strategy our O/S adopts to build maps it could demand a huge amount of O/S memory in a short time. The technology we need to avoid this issue comes in two different flavours: large memory pages, and shared memory maps.

The impact on the SGA is two-fold - each process and session has to create an entry in v$process and v$session, and allocate various memory structures in the SGA: acquiring the rows in v$session and v$process are serial actions, and the memory allocation in the SGA can cause massive flushing of the library cache . 

So , it is advisable to increase the number of processes while keeping it's impact in mind .


Enjoy    :-)