Connecting JIRA applications to Oracle

1. Configure Oracle

1. Ensure that you have a database instance available for JIRA (either create a new one or use an existing one).

2. Within that database instance, create a user which JIRA will connect as (e.g. jiradbuser).
   Remember this database user name, as it will be used to configure JIRA's connection to this database in subsequent steps.

   create user <user> identified by <user_pass> default tablespace <tablespace_name> quota unlimited on <tablespace_name>;
   

   Note:

   • When you create a user in Oracle, Oracle will create a 'schema' automatically.

   • When you create a user, the tablespace for the table objects must be specified.

3. Ensure that the user has the following privileges:

   grant connect to <user>;
   grant create table to <user>;
   grant create sequence to <user>;
   grant create trigger to <user>;

   It is very important that the user is granted the exact privileges indicated above. JIRA requires only these privileges — if either less or more than these privileges are applied, some JIRA functions may not work properly.

   Simply put, for JIRA functions to work as expected, we advise that you grant specific privileges to the user, and not assign a role to the user.

   For example, if you grant the RESOURCE role to a user, and the RESOURCE role grants the SELECT ANY TABLE privilege, then JIRA functions may not work as expected.

   We recommend that you grant the exact privileges indicated above to the user instead.

4. Ensure your database is configured to use the same character encoding as JIRA. The recommended encoding is AL32UTF8 (the Oracle equivalent of Unicode UTF-8).

2. Configure your JIRA Server to connect to your Oracle database

There are two ways to configure your JIRA server to connect to your Oracle database:

• Using the JIRA setup wizard — Use this method if you have just installed JIRA, and are setting it up for the first time. Your settings will be saved to the dbconfig.xml file in your JIRA home directory.
• Using the JIRA configuration tool — Use this method if you have an existing JIRA instance. Your settings will be saved to the dbconfig.xml file in your JIRA home directory.

Instructions for each configuration method

Database connection fields

Sample dbconfig.xml file

For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file above, see Tuning database connections.

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>oracle10g</database-type>
  <jdbc-datasource>
    <url>jdbc:oracle:thin:@dbserver:1521/ORCL</url>
    <driver-class>oracle.jdbc.OracleDriver</driver-class>
    <username>jiradbuser</username>
    <password>password</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 
    <validation-query>select 1 from dual</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
  
    <pool-test-while-idle>true</pool-test-while-idle>
    <pool-test-on-borrow>false</pool-test-on-borrow>
  </jdbc-datasource>
</jira-database-config>

3. Start JIRA

You should now have JIRA configured to connect to your Oracle database. The next step is to start it up!

 Congratulations, you now have JIRA connected to your Oracle database.

Known issues

• If you start experiencing problems when dealing with custom workflows or working with issues that have long descriptions, comments or custom field values, try adding the element <connection-properties>SetBigStringTryClob=true</connection-properties> as a child of the </jdbc-datasource> element in your dbconfig.xml file. 
  This connection property may solve these problems. You'll need to restart JIRA afterwards.