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

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

Cause

Maximum number of processes is specified in the initiatlization parameter PROCESSES is reached, no more requests are processed. User will get above error. New connection will not establish.

Solution

Some of the Rogue “zombie” connections  to Oracle that are idle (not working). 

To fix this,

If you are already connected with sys user,

use the ALTER SYSTEM KILL command. 

Once you reach maximum number of processes, you cannot connect to database with sys user also unless you already connected to your database. In this scenario you need to kill some processes at the OS level.

On Unix/Linux

$ kill -9

To fix the this issue permanent, increase the processes parameter value in init.ora file.

SQL> alter system set processes= scope=spfile;

SQL>shutdown;

SQl>startup;

Some FAQs,

Q. Why ORA-00020  occur and what should be the ideal value?

Ans. Maximum number of processes is specified in the initiatlization parameter PROCESSES is reached, no more requests are processed. User will get above error. New connection will not establish.

Q. Is backup jobs need separate process I.e RMAN or import/export?

Ans. Yes, RMAN and import/export are connecting to oracle hence new process is assigned to those connections.

It is recommended that such backup jobs (i.e. RMAN or others) should run on non-business hours. So that required process can available.

Q. How to identify how many processes need to be set with process parameter ?

Ans. Please check the Current used and Max used number of PROCESSES from the view gv$resource_limit and decide for the number of PROCESSES accordingly.

Use the following SQL query:

select RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION from gv$resource_limit where RESOURCE_NAME = 'processes';

Increase the size accordingly.

Or you can calculate like this also,

Processes = Oracle background process (20) + your user sessions (number of shared servers/dedicated servers) + number of job processes/aq processes you want to permit + parallel execution servers if any.

Q. Can one change process parameter run time ?

Ans. No, process parameter is initialization parameter hence it cannot be change run time. It requires database restart.

Q. Steps to Change process parameter?

Ans.

Step 1: Login with sys user

Step 2: run below command

            sql> alter system set process=2000 scope=spfile;

            sql> shutdown immediate;

            sql> startup

Here I am assuming you are using SPFILE. If not then you need to modify process parameter in PFILE manually and need to start database with PFILE.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert