EM Event: Warning:ORCL_dbpilot.net – Metrics Process Limit % is at 98

 
SYMPTOMS

Host=dbpilot.net 
Target type=Database Instance 
Target name=ORCL_dbpilot.net
Categories=Capacity 
Message=Metrics Process Limit % is at 98
Severity=Warning 
Event reported time=Aug 11, 2020 4:46:01 PM MSK 
Comment=
Operating System=Linux
Platform=x86_64
Event Type=Metric Alert 
Event name=Database_Resource_Usage:process_usage 
Metric Group=Database Limits
Metric=Process Limit Usage (%)
Metric value=98
Key Value= 
Rule Name=ROOT_NOTIFICATION_RULE,ALL TARGET EVENTS 
Rule Owner=SYSMAN 
Update Details:
Metrics Process Limit % is at 98 

Tue Aug 11 17:05:11 2020
ORA-00020: maximum number of processes (800) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process W001 submission failed with error = 20

 
 
REASON
 
A number of processes in a database are reaching a maximum permitted value.

 
 
CONSEQUENCES
 
When a number of processes has been reached the maximum permited value no one can login to database.

[oracle@dbpilot.net ~]$ sqlplus dbsnmp/password

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 11 17:44:12 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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


Enter user-name:

 
Even a database cannot spawn a process

Process W001 submission failed with error = 20

 
You can find how many processes in a database by the following query

SET LINES 300
SET PAGES 999
COL RESOURCE_NAME FOR A15
COL CURRENT FOR 999999
COL MAX FOR 99999
COL LIMIT FOR 99999

SELECT RESOURCE_NAME,
       CURRENT_UTILIZATION "CURRENT",
       MAX_UTILIZATION "MAX_REACHED", 
       LIMIT_VALUE "LIMIT",
       ROUND(CURRENT_UTILIZATION/LIMIT_VALUE*100,1) PCT_USED
FROM 
       V$RESOURCE_LIMIT
WHERE
       RESOURCE_NAME IN ( 'sessions', 'processes');

RESOURCE_NAME   CURRENT MAX_REACHED     LIMIT    PCT_USED
--------------- ------- ----------- ---------- ----------
processes           799         800        800       99.9
sessions            815         822       1224       66.6

 
Where

CURRENT is a current number of processes in a database right now.
MAX_REACHED is a maximum number of processes reached since an instance was started.
LIMIT is what you cannot go higher than.
PCT_USED is how many processes in percentage of permitted limit.

 
 
SOLUTION
 

There are 2 options

1) Increase your processes parameter in a database to workaround the issue. The restart of a database is required.

SQL> ALTER SYSTEM SET PROCESSES=1000 SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

 
2) Do some analysis as to why you have so many processes connected to a database. You might have lots of inactive sessions that do nothing for a long period of time.