Connecting JIRA to Oracle

These instructions will help you connect JIRA to an Oracle 10g or 11g database. Oracle 9i is no longer a supported database for use with JIRA and the 11.2.x drivers from Oracle do not support 9i.

On this page:

1. Before you begin

1.1 Check the compatibility of your Oracle server

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

We recommend that you avoid using version 10.2.0.3 of the Oracle server. Oracle server version 10.2.0.3 has been noted to produce occurrences of error ORA-01461. Oracle metalink Note:461670.1 has further details on this Oracle server issue (note, you will need an Oracle support account to view this document). JIRA customers with this problem have reported that upgrading to Oracle server version 10.2.0.4 resolves the issue.

1.2 Export your existing JIRA data

If you are already using JIRA, create an export of your data as an XML backup. You will then be able to transfer data from your old database to your new database, as described in Switching databases.

1.3 Shut down JIRA

2. Configure Oracle

Ensure that you have a database instance available for JIRA (either create a new one or use an existing one). Ensure that this database instance uses character set UTF-8.
To check your current character set, run the following SQL as a user with system privileges:
```
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET'
```
Within that database instance, create a user which JIRA will connect as (e.g. jirauser).
When you create a user in Oracle, Oracle will create a 'schema' automatically.
Ensure that the user has permission to create and populate tables.

3. Copy the Oracle driver to your application server

Skip this step if you are using JIRA Standalone, as JIRA Standalone includes the driver.

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

Please note that a number of the Oracle 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. Oracle metalink Note:461670.1 has further details on this Oracle server issue (note, you will need an Oracle support account to view this document).
- Version 10.2.0.1.0 of the 10g Release 2 JDBC driver hangs with some databases.
- The 10g Release 1 JDBC driver (10.1.0.4) does not hang, but throws ArrayIndexOutOfBoundsExceptions.

4. Use the JIRA Configuration Tool

The JIRA Configuration Tool is a GUI tool only available if you are using JIRA Standalone distribution. If you are running a WAR-EAR distribution of JIRA, or have a text-only connection to the JIRA server, you will need to configure the files manually.

For Windows: Run config.bat in the bin subdirectory of the JIRA Installation Directory.
For other Operating Systems: Run config.sh in the bin subdirectory of the JIRA Installation Directory.

The Configuration Tool will display your current configuration settings if any are already set.

Please Note: You may need to set the JAVA_HOME environment variable to run the JIRA Configuration Tool. See Installing Java for details.

Click the 'Database' tab.
From the 'Database type' drop-down, choose 'Oracle'.
Fill in the connection details for your Oracle database:
- Hostname — The name or IP address of the machine that the Oracle DB is installed on.
- Port — The TCP/IP port that the Oracle server is listening on. The default port number for Oracle is '1521'.
- SID — This is 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 — The user which JIRA will use to connect to the Oracle server.
- Password — The password which JIRA will use to authenticate with the Oracle server.
After typing in your settings, use the 'Test Connection' button to test the connection settings. The tool will attempt to connect to the database, and give a message with the results:
Click 'Save' to save your settings when you are done.
Restart JIRA in order for your new settings to take effect.

Congratulations — you have finished!

5. Configure the database connection manually

Skip this step if you used the JIRA Configuration Tool (see above).

5.3 Configure your application server to connect to Oracle

Tomcat (or JIRA Standalone)

In an editor, open conf/server.xml (JIRA Standalone) or conf/Catalina/localhost/jira.xml (regular Tomcat). Locate the section:

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
username="sa"
password=""
driverClassName="org.hsqldb.jdbcDriver"
url="jdbc:hsqldb:${catalina.home}/database/jiradb"
minEvictableIdleTimeMillis="4000"
timeBetweenEvictionRunsMillis="5000"
maxActive="20" />

(Note: if you can't find a section like this at all, you've probably got the wrong file. Search for mentions of 'jira' in the files under conf/).

Replace this section with the following:

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
username="<username>"
password="<password>"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@<hostname>:<port number>:<SID>"
connectionProperties="SetBigStringTryClob=true"
maxActive="20"/>

Customise the username, password, database server hostname, port number and SID (Oracle 'System Identifier').

If you were previously using hsqldb make sure you have removed have the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis attributes. They will slow JIRA down if present.

JIRA WAR/EAR distribution

If you are deploying JIRA on another application server, you will need to build a JIRA Webapp from the WAR/EAR distribution. Refer primarily to the application server setup guides for details of configuring your application server.

5.2 Configure the JIRA Entity Engine

Edit atlassian-jira/WEB-INF/classes/entityengine.xml (if you are using JIRA Standalone) or edit-webapp/WEB-INF/classes/entityengine.xml (JIRA WAR/EAR), and make the following changes:
- Change the field-type-name attribute to oracle10g. If you forget to do this and start JIRA, it may create database tables incorrectly. See this page if this happens to you.
- Remove the schema-name="PUBLIC" attribute.

5.3 Next steps

You should now have an application server configured to connect to a database, and JIRA configured to use the correct database type. If you are using JIRA Standalone, start it up and watch the logs for any errors. If you are using the JIRA WAR/EAR distribution, rebuild and redeploy the webapp in your application server.

User-contributed notes

Have experiences to share with Oracle and JIRA? We welcome your thoughts. Please see the user-contributed Oracle notes.

Child pages