How to fix ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit Errors in JIRA

Summary

Problem

Error's from the JIRA logs indicate an ORA-02391 error is being reported. JIRA may be unreliable, or performing poorly and the behaviour may be intermittent.

This behaviour may be particularly obvious when completing tasks that involve many queries to the JIRA database.

The following appears in the atlassian-jira.log

Diagnosis

Environment

• JIRA is connected to an Oracle 11g or Oracle 12c Database.

Diagnostic Steps

• The Oracle Alert Log will be reporting similar ORA-02391 errors.

• Attempting to connect to the Oracle Database as the user specified in the dbconflg.xml file, may not be permitted with the same error.

• Connecting to the JIRA as a DBA user and running the following will show a large number of connections to the database by the JIRA USER:

  1 2 3 4 5 SELECT count(*) as connections, username FROM v$session GROUP BY username ORDER BY username;

• Checking the Limits for the affected user shows the user is approaching the maximum connection limit (replace <JIRA_DATABASE_USER> with the username in your dbconfig.xml file:

  1 2 3 4 5 6 7 8 SELECT DISTINCT username, profile, resource_name, limit FROM dba_profiles NATURAL JOIN dba_users WHERE resource_name = 'SESSIONS_PER_USER' AND username = '<JIRA_DATABASE_USER>';

Cause

The total number of database connections to the Oracle Database by the specified user has reached the maximum, and therefore no more connections can be established for the current user.

Solution

Resolution

1. Ensure the maximum pool size in the JIRA instance's dbconfig.xml file is not greater than the SESSIONS_PER_USER limit in Oracle. To do this:

   1. Check the dbconfig.xml under the property: <pool-max-size>. This value should be no smaller than the values documented in:

      • Connecting JIRA to Oracle (JIRA 6.x)

      • Connecting JIRA applications to Oracle (JIRA 7.x)

   2. Check the SESSIONS_PER_USER setting is equal or higher than the maximum pool size

      1. If this value is low, increase this limit or set the limit to UNLIMITED from the Oracle Database.

   3. Confirm that no other applications are connecting to the Oracle database using the same user.