Increasing Processes, Sessions in Oracle Database ORA-00020: maximum number of processes (%s) exceeded

Increasing Processes, Sessions in Oracle Database

Issue 

ORA-12516: TNS:listener could not find available handler with matching protocol stack
ORA-00020: maximum number of processes (%s) exceeded

Solution 

1. Log in as SYSDBA

     login to server in O/S level using  oracle user

     

      $sqlplus "/as sysdba" 

2.  Check existing values for the parameter  processes and sessions

     As per oracle Doc ID 1682295.1 SESSIONS is derived from PROCESSES. 

But it can be set to the required value  or let Database choose its value automatically.

       SESSIONS parameter is the maximum number of sessions that can be created in the system. Every login requires a session.

It is calculated from PROCESSES parameter:

10g    --   ((1.1 * PROCESSES) + 5)
11g    --   ((1.5 * PROCESSES) + 22)

12.2   --   ((1.5 * PROCESSES) + 22)  

      SQL> select name,value from v$parameter where name in('processes','sessions');

    

 

3. Change the values 

    SQL> alter system set processes = 3500 scope = spfile;

     

Scope=spfile  option requires the database bounce ( means shutdown and startup)  to take effect

4. Verify the parameter values again 

    SQL> select name,value from v$parameter where name in('processes','sessions');