ora-00020 maximum number of processes 200 exceeded

ora-00020 maximum number of processes 200 exceeded

I've got a database instance that has apparently run out of processes, however, I cannot even seem to log on as sys to fix things..!!

ora.sujeet # sqlplus / as sysdba

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

The ORA-00020 is caused by two things:  

1. Disconnected processes:  sonu “sujeet” connections  to Oracle that are idle (not working).  To fix this, use the ALTER SYSTEM KILL command.  You may also need to kill session at the OS level with the KILL -9  command.  
2. Too few process buckets:  Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage. 

Solution:-

If your database is running on a UNIX system, and if you can kill session without disturbing (too much) the application. 
You can identify server processes that are connected to the instance, for example, if your instance name is "PRODDB", identify oracle server processes that are connected : 

ora.sujeet $ ps -ef | grep oracle PRODDB

 kill one & more process (with kill -9 <pid>), After that try to connect you to the instance.

OR

Cause: All process state objects are in use.  

Action: Increase the value of the PROCESSES initialization parameter

Simply increase your processes parameter and you are all set!

or

Checked test db process count it was only 200 as the error says…

Resolution

SQL> Alter system set processes=800 scope=spfile;

System altered.

shutdown immediate;
startup;

How to increase PROCESSES initialization parameter: 

1. Login as sysdba 

sqlplus / as sysdba 

2. Check Current Setting of Parameters 

sql> show parameter sessions 

sql> show parameter processes 

sql> show parameter transactions 

3. 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 

4. 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=500 scope=spfile; 

sql> alter system set sessions=555 scope=spfile; 

sql> alter system set transactions=610 scope=spfile; 

sql> shutdown abort 

sql> startup