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
or in the alert log of your database you observe the errors like the following
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.
Database : 11.2.0.4
OEM : 13.3.0.0.0
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Certificates
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Cipher Suites (1)
- Database Errors (5)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)