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

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

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

java.sql.SQLException: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

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:

    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:

    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.

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: 
    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.

 

 

Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.