Connecting JIRA to Oracle

These instructions will help you connect JIRA to an Oracle database.

Before you begin

On this page:

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 <some-user> IDENTIFIED BY <some-password> DEFAULT TABLESPACE <tablespace_name> QUOTA UNLIMITED ON <tablespace_name>;
    

    Things to note

    • Oracle will create a schema with the same name as the user's automatically (this is the schema JIRA will access). Accessing a schema owned by another user is unsupported and may cause JIRA not to work, as described on the below suggestion;

      JRA-45387 - Add official support to the usage of a different schema on Oracle Verified

    • A tablespace in which the user has the the correct privileges (see below) must be specified for the table objects to be created correctly by JIRA's setup;

    • The username must only contain characters that Oracle supports without quotes, due to there being at least one known problem when using characters not supported in non-quoted identifier;
  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 UTF-8).

2. Copy the Oracle JDBC Driver to Your Application Server (JIRA WAR Only)

Skip this step if you installed a 'Recommended' distribution of JIRA, which includes the Oracle JDBC driver. The JIRA WAR distribution does not include this driver.

  1. Download the Oracle JDBC driver (from Oracle's site).
  2. Add the appropriate Oracle JDBC driver jar (ojdbc6.jar for JDK 1.6) to the lib/ directory.

Please note that a number of the Oracle JDBC driver versions cannot be used with JIRA or are inherently unstable. The known issues with Oracle drivers are as follows:

  • We recommend that you use the 11.2.x version of the driver for all versions of Oracle (it is backwards compatible). Many other versions of the driver have been noted to have problems, such as:
    • Version 10.2.0.3.0 of the 10g Release 2 JDBC driver has been noted to produce occurrences of error ORA-01461. The Oracle Support site has further details on this Oracle server issue (you will need an Oracle Support account to access this site);
    • Version 10.2.0.1.0 of the 10g Release 2 JDBC driver hangs in some databases;
    • The 10g Release 1 JDBC driver (10.1.0.4) does not hang, but usually throws ArrayIndexOutOfBoundsExceptions;

Note that, while JDK 1.6 is not supported on  JIRA 6.0 and later, you should be able to use the Oracle JDBC driver for JDK 1.6 with JDK 1.7.

3. Configure Your JIRA Server to Connect to Your Oracle Database

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

  • Using the JIRA setup wizard (not applicable to JIRA WAR) — 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 (not applicable to JIRA WAR) — 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.
  • Manually — Only use this method if you have a JIRA WAR instance or you have a console-only connection to your JIRA server. You will be required to manually update the dbconfig.xml file in your JIRA Home Directory.

Instructions for each configuration method

JIRA setup wizard

The JIRA setup wizard will display when you access JIRA for the first time in your browser.

  1. In the first screen, 'Configure Language and Database', set Database Connection to My own database.
  2. Set Database Type to Oracle.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
 

JIRA configuration tool

  1. Run the JIRA configuration tool as follows:
  2. Navigate to the Database tab and set Database type to Oracle.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save. Any custom settings specified while manually configuring JIRA with Oracle (e.g., adding the <connection-properties>SetBigStringTryClob=true</connection-properties>) will be deleted. You will need to reinstate them manually.
  5. Restart JIRA.
 

Manually

  1. Locate the dbconfig.xml file at the root of your JIRA Home Directory.
    • If this file does not exist, create the file, copy and paste the example XML code (shown below) into this file.
  2. Update the file, as described in the Database connection fields section below. Escape any '&' characters by adding 'amp;' to the end of each one.
    • Note, the <database-type/> element must specify your type of database, e.g. oracle10g. If you forget to do this and you start JIRA, your database tables may be created incorrectly. See Incorrect database type specified if this happens to you.
  3. Save the file and restart JIRA.

Database connection fields

Setup Wizard / Configuration Tool dbconfig.xml Description
Hostname

Located in the <url> tag (bold text in example below):<url>jdbc:oracle:thin:@dbserver:1521:ORCL</url>

The name or IP address of the machine that the Oracle server is installed on.
Port

Located in the <url> tag (bold text in example below):
<url>jdbc:oracle:thin:@dbserver:1521:ORCL</url>

The TCP/IP port that the Oracle server is listening on. The default port number for Oracle is '1521'.
SID

Located in the <url> tag (bold text in example below):<url>jdbc:oracle:thin:@dbserver:1521:ORCL</url>

The Oracle "System Identifier". The default value for most Oracle servers is 'ORCL'. If you are using the Oracle Express Edition, this will be 'XE'.
Username

Located in the <username> tag (see bold text in example below):
<username>jiradbuser</username>

The user that JIRA uses to connect to the Oracle server. You should have created this in Step 1 above.
Password Located in the <password> tag (see bold text in example below):
<password>jiradbuser</password>
The user's password — used to authenticate with the Oracle server.

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>

4. Start JIRA

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

  • If you are using a 'recommended' distribution of JIRA, start it up and watch the logs for any errors.
  • If you are using the JIRA WAR distribution, rebuild and redeploy the webapp in your application server.

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

Installation notes

Please see JIRA and Oracle.

Known issues and troubleshooting

  • 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. Adding this connection property may overcome these problems. Be aware that you will need to restart JIRA for this setting to take effect.

Was this helpful?

Thanks for your feedback!

Why was this unhelpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport