"ORA-00020: maximum number of processes (500) exceeded" when attempting to connect to the Oracle Database on IGL

3 years ago

Originally Published: 2018-02-06

Article Number

000041933

Applies To

RSA Product Set: Identity Governance and Lifecycle
RSA Product/Service Type: Appliance
RSA Version/Condition: 7.x

Issue

When you attempt to connect to the Oracle database on a IGL hardware or software appliance with RSA-supplied database, you receive the following error:

ORA-00020: maximum number of processes (500) exceeded

Cause

The RSA-supplied Oracle database has a default limit of 500 processes. This error occurs when you exceed that limit. The cause is often due to zombie processes, connections from external tools that do not cleanly close their sessions (e.g. Oracle SQL Developer), or a heavy system load.

Resolution

You can increase the Oracle process limit to avoid hitting this limit. First, check to see what the current limit is set to.

select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');

Once you know what the current limit is set to, you can increase it using the following replacing X with a value higher than the process limit from the previous query:

alter system set processes=X scope=spfile;

Workaround

To resolve the issue immediately, you can restart the Oracle database or the entire appliance (recommended) to free up these processes.

You also have the option to kill any zombie process you have identified. In this situation you may not be able to get a free process to establish a connection to Oracle. This would prevent you from identifying which process belongs with which user or query. This makes killing individual processes carry some risk of killing a process you do not wish to.

Notes

Hardware appliances typically do not need to have the Oracle process limit increased. If you are running into this issue on a hardware appliance, you may have outgrown this deployment and need to consider a software appliance with RSA-supplied database or an external customer-managed database. Consult with professional services for questions on environment sizing and resources.

Don't see what you're looking for?

Related Articles

Trending Articles