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

Dead Connection in Oracle, ORA-00020

Dead Connection in Oracle

Reason 1

There is an application running and connecting to the database . The connection against the database is failing.

A likely cause is that the application tries a connect using an incorrect username and password.

Failed connections from an application are creating dead processes on the database server.

This might lead to large number of process generated on the server side which would eventually cause

the error ORA-00020 for any new connections.

Further analysis you can see the many processes in the view V$PROCESS may exist without having a related session in the view V$SESSION, so that V$SESSION has much less entries compared to V$PROCESS.

At the  O/S level,  there are numerous processes being generated with a LOCAL=NO description.

Reason 2

When the application is not used for a significant amount of time, the firewall setting between database & application server closes physical connection between application & database.

This will invalidate the connection in connection pool.

Reason 3

These are previously valid connections with the database but the connection between the client and server processes has terminated abnormally.

Examples of a dead connection:

A user reboots/turns-off their machine without logging off or disconnecting from the database.

A network problem prevents communication between the client and the server.

What happens if above situation arise,

This will become major problem as stated below,

     * The database exhausts PROCESSES and gives ORA-20 maximum number of processes exceeded

     * The OS can become exhausted due to the unneeded resources consumed by such oracle processes

SYMPTOMS are,

     * The database view V$PROCESS will have no corresponding V$SESSION entry

ñ  An OS process / thread still exists for the SNIPED session

Immediate solution or Workaround

The solutions to this scenario can are to cleanup the OS processes ... after which the V$PROCESS entries should be removed automatically

Dead Connection Detection (DCD)

Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later

DCD detects when a partner in a SQL*Net V2 client/server

or server/server connection has terminated unexpectedly, and flags the dead session

so PMON can release the resources associated with it.

How DCD works,

When DCD is enabled, Net8 (server-side) sends a packet to the client. If the client is active, the packet is discarded. If the client has terminated, the server will receive an error and Net8 (server-side) will end that session.

DCD is well explained on Oracle Metalink Note 151972.1

Solution

Enable Resource Limits & DCD.

See my Note and FAQs how to enable resource Limit.

DCD can be implemented by adding SQLNET.EXPIRE_TIME = to the sqlnet.ora file.

As per Oracle, It is strongly recommended that both DCD and Resource Limits with Profiles be implemented in order to clean up resources at both the database and OS level

FAQs

Q. Why dead connection happens?

Ans. Explained three scenarios above.

Q. What happen when we kill those inactive/dead connections?

Ans. Once kill, process in the database go away. PMON will release all the resources.

Here one thing need to understand that, The blocked session in all cases has to wait for the resources to be freed. If you alter system kill the session, they will be released after the session rolls back.

If the session died an unexpected death, it'll be after pmon does the same.

Q. How to kill session manually if situation arise like ora-00020 ?

Ans. Below are some methods you can use for some immediate remedy,

ALTER SYSTEM DISCONNECT SESSION ',' IMMEDIATE

OR

On Unix/Linux at OS level kill -9

OR

orakill  

Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

TAG:- Dead Connection in Oracle, Dead Connection Detection (DCD), ora-00020