ORA-00020: maximum number of processes (XXXXX) exceeded

Recently we had situation where we reached max process limit Due to that unable to accept the new connections.   To fix this we need to increase process limit in database and since this parameter static parameter we need to restart the database.

In my situation I am able to login into the database.

First check weather its reached max allocation or not by using below query

 IF this rac database please execute below command in my case I am increasing  limit to 300 from 150.

Then restart the database by manually through sqlplus if  database is single instance  if database is rac  restart database with server control.

srvctl stop database -d database
srvctl start database -d database

          If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
    
        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1
        
  These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=300 scope=spfile;
    sql> alter system set sessions=335 scope=spfile;
    sql> alter system set transactions=380 scope=spfile;
    sql> shutdown abort
    sql> startup


In some cases we are not able to login into database due its reached max process ( it wont allocate new session ) In this we have to login as below & shutdown abort & startup database. Once database back online follow the above steps to change process parameter.



You can connected to database with this option:

sqlplus -prelim "/as sysdba"

Shutdown abort;

increase the processes in the database.

start the database

startup

Disclaimer: If you feel this content related to your content  please feel to contact me to remove this information.  I am sharing the knowledge with people whoever in the need which is learning daily basis.