問題

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. 

atlassian-jira.log に次のメッセージが出力される。

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

診断

環境

• 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>'; 

原因

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.

ソリューション

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.