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

7 minuto de leitura
0

I'm trying to connect as the primary user or DBA user on an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance. But I receive one of these 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, then increase SESSIONS or PROCESSES, or both. This accommodates 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, then 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. This can worsen issues caused by contention. This in turn can prevent the Amazon RDS monitoring system from logging in, performing health checks, or taking corrective action, such as rebooting.

Resolution

Oracle user PROFILE

One of the most common causes for the ORA-18 and ORA-20 is the large number of idle connections. Idle connections remain in a database without being properly closed by either the application or by database administrators. The increase of IDLE connections can cause the database to reach the maximum limit of SESSIONS/PROCESSES parameter. As a result, no new connections are allowed. It's a best practice to set a PROFILE for application connections with a limited IDLE_TIME value.

In Oracle databases, each user is assigned to a PROFILE. An Oracle PROFILE is a set of resources that is assigned to each user attached to this PROFILE. One of those resources is IDLE_TIME. IDLE_TIME specifies in minutes the amount of continuous inactivity that's allowed during a session before the query is killed by the database. Long-running queries and other operations are not subject to this limit.

This example shows how to create a profile with maximum IDLE_TIME of 30 minutes and assign it to an application user. Any IDLE connection for more than 30 minutes is automatically killed by the database:

Create a PROFILE with a limited IDLE_TIME parameter:

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

Assign this PROFILE to a specific user:

  SQL> ALTER USER <username> PROFILE <profile_name>;

Scaling RDS instance size

The maximum number of SESSIONS/PROCESSES might be reached because of an increase of the incoming workload from application users. By default, in RDS for Oracle, both parameter limits are calculated based on a pre-defined formula that depends on the DB instance class memory. It's not a best practice to manually modify SESSIONS/PROCESSES parameter in this case. Instead, scale up/down the instance based on the workload.

RDS default setting for PROCESSES/SESSIONS parameter are calculated using these formulas:

  • PROCESSES= LEAST({DBInstanceClassMemory/9868951}, 20000)
  • SESSIONS = Oracle Default = (1.5 * PROCESSES) + 22

Manually setting either PROCESSES or SESSIONS parameters beyond their default limit might cause an increase in memory consumption. As a result, the database might crash due to out of memory issues. Also, manually setting PROCESSES or SESSIONS might cause configuration mismatch when scaling up/down the instance. This happens because the PROCESSES and SESSIONS parameters don't depend on the allocated instance memory anymore.

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, including users with the DBA role. Set LICENSE_MAX_SESSIONS to a lower value than SESSIONS and PROCESSES. This causes the client connections get an ORA-00019 error instead of an ORA-18 or ORA-20 error. An ORA-00019 error doesn’t apply to users who have RESTRICTED SESSION privileges. So, primary and 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.

Note: 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 LICENSE_MAX_SESSIONS is deprecated. But, 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 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 DB instance. For more information, see Working with parameter groups.

LICENSE_MAX_SESSIONS parameter can be set as a formula, similar to the PROCESSES parameter. It's a best practice to set the LICENSE_MAX_SESSIONS parameter to be based on a formula rather than a static value. This helps to avoid mis-configuration when scaling up/down the instance size. For example:

  • To set it same as PROCESSES parameter: LICENSE_MAX_SESSIONS= LEAST({DBInstanceClassMemory/9868951}, 20000)
  • To set it to 4/5 of PROCESSES parameter: LICENSE_MAX_SESSIONS= LEAST({DBInstanceClassMemory/12336188}, 20000)

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:

  • LICENSE_MAX_SESSIONS = maximum number of client connections only.
  • PROCESSES = LICENSE_MAX_SESSIONS + all background processes, including parallel queries, DBA users including primary 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 like this:
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 like this:

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 primary 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, then your dispatchers might be overwhelmed. When dispatchers are overwhelmed, the connections are forced to come in as DEDICATED. Increase the number of DISPATCHERS to allow more sessions to connect shared. The SHARED_SERVERS parameter might also need to be increased.

To check if you are using SHARED or DEDICATED servers, run a command like this:

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

Related information

Best practices for working with Oracle

AWS OFICIAL
AWS OFICIALAtualizada há um ano