How do I resolve ORA-00018 or ORA-00020 errors for an Amazon RDS Oracle DB instance?

Last updated: 2020-02-04

I'm trying to connect as the master user or DBA user on an Amazon Relational Database Service (Amazon RDS) Oracle DB instance, but I receive one of the following errors:

  • ORA-00018 maximum sessions exceeded
  • ORA-00020 maximum processes exceeded

How do I resolve these errors?

Short Description

These errors can be caused by either a planned scaling exercise or an unplanned event that causes a large number of DB connections. In these cases, many client sessions are initiated against a DB instance, and one of the following database limits is reached:

  • PROCESSES – the maximum number of user processes allowed.
  • SESSIONS – the maximum number of user sessions allowed.

If the maximum connections are reached because of a planned scaling exercise, you might need to increase SESSIONS or PROCESSES, or both, to accommodate your application’s new scale. These two parameters are not dynamic, so modify the parameters and then reboot the instance.

If the maximum connections are reached because of an unplanned event, identify the cause of the event and take appropriate action. For example, your application might overwhelm the database when response times increase because of locking or block contention. In this case, increasing SESSIONS or PROCESSES only increases the number of connections before maxing out again, which can worsen issues caused by contention. This can prevent the Amazon RDS monitoring system from logging in, performing health checks, or taking corrective action, such as rebooting.

Resolution

Using LICENSE_MAX_SESSIONS

The LICENSE_MAX_SESSIONS parameter specifies the maximum number of concurrent user sessions allowed. This doesn't apply to Oracle background processes or to users with RESTRICTED SESSION privileges, such as users with the DBA role (master user or RDSADMIN). By setting the LICENSE_MAX_SESSIONS limit to a value that is lower than SESSIONS and PROCESSES, you can force the client connections to receive an ORA-00019 error (Maximum number of licenses exceeded) instead of an ORA-18 or ORA-20 error. Because an ORA-00019 error doesn’t apply to users who have RESTRICTED SESSION privileges, the MASTER user and the RDSADMIN users are able to log on to the DB instance and perform administrative troubleshooting and corrective actions. Also, Amazon RDS monitoring can continue to connect to the database (by using RDSADMIN) to perform health checks.

It's important to note that LICENSE_MAX_SESSIONS was originally intended to limit the usage based on the number of concurrent sessions. Oracle no longer offers licensing based on the number of concurrent sessions, and the LICENSE_MAX_SESSIONS initialization parameter is deprecated. However, you can still use the parameter if you use Oracle versions up to 19c. Also, application users shouldn't be granted either the DBA role or the RESTRICTED SESSION privileges. For more information, see the Oracle documentation for LICENSE_MAX_SESSIONS.

LICENSE_MAX_SESSIONS is a dynamic parameter, so it can be set without restarting the RDS instance. For more information, see Working with DB Parameter Groups. See the following steps based on whether you use SHARED or DEDICATED sessions.

Using DEDICATED sessions

If you use DEDICATED sessions, your client connections might exceed the limit of the PROCESSES parameter (ORA-20). If your client connections exceed the limit, set the value of the LICENSE_MAX_SESSIONS below PROCESSES as follows:

  • LICENSE_MAX_SESSIONS = maximum number of client connections only.
  • PROCESSES = LICENSE_MAX_SESSIONS + all background processes, including parallel queries, DBA users including master users, and a buffer. A buffer allows for unexpected background processes that might occur later. To see how many background processes you have now, run a query similar to the following:
SQL> select count(*) from v$session where type= 'BACKGROUND';

Note: SESSIONS, which defaults to (1.5 * PROCESSES) + 22, should be sufficient. For more information, see the Oracle documentation for SESSIONS.

To manually connect to your instance to verify the SESSIONS, run a command similar to the following:

SQL> select name, value from v$parameter where upper(name) in ('SESSIONS','PROCESSES','LICENSE_MAX_SESSIONS');
NAME   VALUE
------------------------------ ------------------------------
processes   84
sessions   148
license_max_sessions   0

Using SHARED sessions

If you use SHARED sessions, your client connections might exceed the limit of the SESSIONS parameter (ORA-0018). If your client connections exceed the limit, set the PROCESSES parameter to a higher value.

  • LICENSE_MAX_SESSIONS = maximum number of client connections only.
  • PROCESSES = all background processes, including parallel queries, and DBA users including master users, and a buffer. Be sure to include settings of SHARED_SERVERS and DISPATCHERS with the count of background processes.
  • SESSIONS = (1.5 * PROCESSES) + 22

If you use SHARED servers, and you receive a max processes (ORA-20) error instead of max sessions (ORA-18) error, it’s likely that your dispatchers are overwhelmed. When dispatchers are overwhelmed, the connections are forced to come in as DEDICATED. In this case, increase the number of DISPATCHERS to allow more sessions to connect shared. The SHARED_SERVERS parameter might also need to be increased. Consult Oracle documentation for more information.

If you don't know whether you're using SHARED or DEDICATED servers, you can verify which server you are using by running a query similar to the following:

SQL> select decode(server, 'NONE', 'SHARED', server) as SERVER, count(*)
from v$session group by decode(server, 'NONE', 'SHARED',server)

Did this article help you?

Anything we could improve?


Need more help?