Client applications connecting to an Amazon RDS Oracle database instance are refused with one of the following errors:

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

Attempts to connect to the instance as the master user or other dba user with the "RESTRICTED SESSION" privilege are also refused, making it difficult to resolve the problem without rebooting the instance.

This issue can occur during either a planned scaling exercise or an unplanned "connection storm," when a larger-than-expected number of client sessions are initiated against an RDS Oracle DB instance and hit one of these database limits

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

When this happens, you might want to connect as the master user or other dba user and take corrective action, such as killing superfluous connections or lock holders, but you find that you cannot connect because the master user hits the same errors as the application.

To prevent this problem from occurring in the future, set LICENSE_MAX_SESSIONS to some number less than SESSIONS or PROCESSES using the guidelines below. LICENSE_MAX_SESSIONS applies to client connections only, not to background processes or users with “RESTRICTED SESSION” privilege such as the master user. By setting it to some number less than SESSIONS or PROCESSES, you force the clients to hit ORA-00019 “Maximum number of licenses exceeded” instead of ORA-18 or ORA-20. And because “maximum licenses” doesn’t apply to restricted users, your master user will still be able to connect and resolve the situation without a reboot.

LICENSE_MAX_SESSIONS is a “dynamic” parameter, which means it can be set without restarting the RDS instance. How you set it depends on whether you’re using SHARED or DEDICATED sessions.

If you are primarily using DEDICATED sessions, your client connections will likely exceed the value of the PROCESSES parameter (ORA-20). In this scenario, you should 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 query slaves, and any DBA users including master user + fudge factor. The "fudge factor" should permit sufficient overhead to allow for unexpected new background processes that might launch later. To see how many background processes you have now:

SQL> select count(*) from v$session where type= 'BACKGROUND';

  • SESSIONS, which defaults to (1.1*PROCESSES)+5, should be fine

If you have configured the database for SHARED servers, your client connections will likely hit max SESSIONS (ORA-18), so in this case set the LICENSE_MAX_SESSIONS parameter well below SESSIONS as follows:

  • LICENSE_MAX_SESSIONS = maximum number of client connections only
  • PROCESSES = all background processes, including parallel query slaves, and any DBA users including master user + fudge factor. Be sure to include settings of SHARED_SERVERS and DISPATCHERS with the count of background processes.

If you do not know whether you are using SHARED or DEDICATED servers, you are likely using DEDICATED. This is because SHARED servers must be configured explicitly. However, you can verify this with the following query:

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

It is important to understand why your clients hit max connections in the first place so that you can take appropriate action:

  • If max connections was the result of a planned scaling exercise, you might need to adjust SESSIONS and/or PROCESSES higher to accommodate your application’s new scale. These two parameters are not dynamic, so they require a reboot to set.
  • If max connections was due to an unplanned connection storm, then the appropriate action is to identify the cause of the storm and rectify it. For example, your application might inappropriately storm the database when response times increase due to locking or other contention. Simply increasing SESSIONS or PROCESSES in this case only increases the number of connections before maxing out again, and it exacerbates any contention in the meantime.
  • If you are using SHARED servers, and hitting max processes (ORA-20) instead of max sessions (ORA-18), it’s likely your dispatchers are getting overwhelmed, forcing the connections to come in DEDICATED. In this case, you should 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.

Amazon RDS, LICENSE_MAX_SESSIONS, ORA-00018, ORA-00020, failed connections, connection error

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2016-06-24