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.

    Things to note

    • Oracle will create a schema with the same name as the user's automatically (this is the schema JIRA will access);

    • A tablespace in which the user has the the correct permissions (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 permissions:

    If the incorrect permissions (either less or more than required) are applied, it's possible that some JIRA functions do not work properly, as described in this article.

  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 Unresolved Link 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.

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!

26 Archived comments

  1. User avatar

    Mladen Maravic

    There is an known bug (JRA-19893) when using JIRA and Oracle. To avoid the bug, add the following trigger to the PROJECTVERSION table:

    See the issue for more details.

    02 Nov 2010
    1. User avatar

      Jason Brison

      Fixed in JIRA 4.4.1 and JIRA 5.0.

      01 Feb 2013
  2. User avatar

    Anonymous

    hello. I'm attempting to create the connection file. The test is successful. When I save I recieve the following error:

    ..\atlassian-jira\WEB-INF\classes\Jira-application.properties (Access is denied)

    I have administrator priveliges on the server. Suggestions?


    08 Feb 2011
    1. User avatar

      Anonymous

      I had the same problem.  And I fixed it by running the config.bat as administration and then it works perfectly.  Apparently this is only would happen in Windows 7. :(

      08 Aug 2011
      1. User avatar

        Anonymous

        Awesome!! This helped me. 

        03 Feb 2012
  3. User avatar

    Anonymous

    this is incomplete. there is a change that needs to be made in entityengine the database. This page http://confluence.atlassian.com/display/JIRA/Configuring+the+Entity+Engine+for+JIRA sheds some light but still not clear exactly what needs to be changed when the database is changed.

    09 Feb 2011
  4. User avatar

    Anonymous

    I having some problems after done these configurations. When i started JIRA again, it returns me an error:

    Error occurred while starting Plugin Manager. org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:PluginVersion][id,null][created,2011-03-10 17:22:27.536][name,com.atlassian.gadgets.directory][key,com.atlassian.gadgets.directory][version,0.0] (SQL Exception while executing the following:INSERT INTO pluginversion (ID, pluginname, pluginkey, pluginversion, CREATED) VALUES (?, ?, ?, ?, ?) (ORA-01950: não há privilégios no tablespace USERS' ))

    Anyone with the same problem? what should i do?

    10 Mar 2011
  5. User avatar

    Anonymous

    Hello All,

            I have an oracle database created and I have made the changes according to the document above,

    in the server.xml<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"username="user"password="password123"driverClassName="oracle.jdbc.OracleDriver"url="jdbc:oracle:thin:@rpc7467:1521:JIRADEV"connectionProperties="SetBigStringTryClob=true"maxActive="20"/>

    in my  entityengine.xml I have done this

    <datasource name="defaultDS" field-type-name="oracle10g" <---- I am actually using oracle 11 g I think it is
          <!- Changed field-type-name from hsql ->
         <!-  schema-name="public" -><!- Turn this on if going back to original configuration ->

    I am hoping I have done this right as my Jira is throwing off errors when I try and start it. The tablespace name as I understand is USERS .....

    Thank you for your help in advance.
    Michael

    28 Mar 2011
  6. User avatar

    Anonymous

    I am sorry but How do you create the new db for Oracle there is no instructions here on how to create a new db from scratch to import your data to? Or am I missing something in this documentation?

    This doesn't make sense all I see is migrating to a new db ...... How is the db created ..... this does not make sense to me ....

    Michael

    29 Mar 2011
    1. User avatar

      Anonymous

      Michael, you need to create the db yourself ( see Oracle's documentation and use the Oracle dbca tool if you want a wizard to do it).

      Create the username yourself,either in some SQL tool like sqlplus or through enterprise manager.

      The Jira installation process will create the schema (the tables, indexes, core data etc) with this username in the database. Some other databases eg MYSQL refer to the schema as the database. Don't get them confused.

      31 Aug 2011
  7. User avatar

    Anonymous

    Are there recommendations for the size of the database that should be allocated?

    15 Sep 2011
  8. User avatar

    Anonymous

    You can't just "create a user" in Oracle and have it work; the user must be granted the proper subset of several hundred privileges.What privileges are needed by the Oracle user that owns the Jira objects?

    12 Oct 2011
    1. User avatar

      Anonymous

      Oh sorry, now I see it, under "Configure Oracle"...I wasn't expecting it under that heading, was expecting to see a "Create Oracle user" heading...

      12 Oct 2011
  9. User avatar

    Anonymous

    Hello,

    Our company currently uses Jira in Version 4.x on an Oracle DB 11g

    There are rumors that prevent us fom migrating to the newest version 5.x with greenhopper 5.x

    Is it still valid, that it's not recommended to step up the version to 5.x on an oracle 11g database system?

    Any comments appreciated and

    kind regards

     

    03 May 2012
  10. User avatar

    Luis DIaz

    Hello all

    I'm new here, I would like to know if there is a doc to migrate JIRA database from SQL server to ORACLE.

    I anderstand the procedure above, but in the case that we already have a production server with data, how could I pass it to oracle? Thanks a lot in advance

    07 Jun 2012
  11. User avatar

    Anonymous

    Hello Luis,

    I suggest you to follow the instructions listed at Switching Databases to migrate your data from SQL server to Oracle.

    Cheers,

    Rodrigo Brandao

    07 Jun 2012
    1. User avatar

      Anonymous

      Hello Rodrigo

      ok thanks I'll follow this instructions. I see this in the web:

      Create an export of your data as an XML backup. See Backing Up Data for details.
      (warning) Please note that JIRA's XML backup utility does not back up attachments (if you have attachments enabled).


      I know in our installation the attachment ARE enabled. So what can I do? Is there any workaround to move this objetcs? What does the attatchemtn looks like into JIRA, does db just store the PATHES of the files? in this case maybe I can move the files and adapt even manually the pathes.

      No?


      Thanks a lot for your attention 

      Cheers

      07 Jun 2012
      1. User avatar

        Luis DIaz

        Hello Every one

        about the question of the attachment when we decide to migrate to ORACLE. I have see this doc, some one can confirm this is the correct workaround to do this?

        Migrating to Another Database#StepThreeMovingyourattachments

         

        Thanks to tell me

        Cheers

         

        15 Jun 2012
  12. User avatar

    Anonymous

    Hi, I have JIRA 3.9 and want to upgrade my Oracle database to 11.2.0.3, do you guys foresee any problems with that upgrade?

    22 Oct 2012
    1. User avatar

      Andrew Lui [Atlassian Technical Writer]

      Hi,

      According to the docs, JIRA 3.9 did not officially support Oracle 11 (I don't believe Oracle 11 was released). I'm not sure if you will have problems.

      I recommend that you upgrade your JIRA version to JIRA 5.1, if possible. JIRA 5.1 does support Oracle 11. If this is not an option, I'd recommend contacting our support team for assistance, by raising a support request at https://support.atlassian.com.

      Kind Regards,
      Andrew

      23 Oct 2012
  13. User avatar

    Anonymous

    hi,this's my problem
    /secure/SetupDatabase.jspa [core.entity.transaction.JNDIFactory] NamingException while finding UserTransaction named java:comp/env/UserTransaction in JNDI.
    javax.naming.NameNotFoundException: Name [UserTransaction] is not bound in this Context. Unable to find [UserTransaction].
    at org.apache.naming.NamingContext.lookup(NamingContext.java:820)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:154)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:831)
    i can connect oracle  .when JIRA create table ,the tomcat tell me this . 

    18 Oct 2013
  14. User avatar

    Michael Lambert

    Hi,

    after the migration from the HSQL to an Oracle 11.2.x DB I can not login in Jira!!

    User and/or Password wrong!

    What can I do?

    04 Aug 2014
  15. User avatar

    Michael Andreacchio [Atlassian]

    Hi there,

    The best thing you can do is contact us at https://support.atlassian.com, from there we will be able to work with you to get the right information to troubleshoot the issue and get your JIRA up and running as soon as possible.

    Cheers,

     

    Michael

    JIRA Support TL

    04 Aug 2014
  16. User avatar

    Erik Mason

    Our DBAs don't like giving applications these rights:

    grant connect to <user>;
    grant create table to <user>;
    grant create sequence to <user>;
    grant create trigger to <user>;
    Is there a way to get the DDL for JIRA? We would have to create the tables, sequences, etc. manually (we use Liquibase for this) before we deploy the application in our data center. Does JIRA do these types of things on the fly? If I have more information I might be able to make a case for giving the JIRA application these rights. We'll be using Oracle, if that matters in the answer.
    12 Mar 2015
    1. User avatar

      Dave Mason [Atlassian]

      Unfortunately JIRA does create its own tables from the application code, and doesn't have a static DDL to create tables before running. There is an entity definition inside the application distribution, which generates tables using the library OfBiz. Additionally, JIRA will need to create/alter tables after initial install, for example if you install a plugin or upgrade JIRA, there may be some changes needed. It would be impossible to avoid giving these permissions to JIRA's user, however you don't have to have those permissions granted on any other schema besides the JIRA schema.

      12 Mar 2015
  17. User avatar

    Felix Herzog

    Hi,

    why does the 'recommended distribution of Jira' ship with ojdbc6-11.2.0.2.0? The currenty bug fixed version of 11g is 11.2.0.4.

    Also ojdbc7-12.1.0.2 is the most actual at time and has backwards compatibility.

    Also: JIRA recommends to use Java 8 but gets shipped with an old ojdbc6 for 1.6 jdks. I'd expect to at least have it shipped with the 1.7 optimized drivers (ojdbc7).

    And I like to ask where to find a list of actual database-types. Is there really only that

    <database-type>oracle10g</database-type>

    available? Is there no

    <database-type>oracle11g</database-type>

    or

    <database-type>oracle12c</database-type>

    available?

    This sounds like JIRA is not optimized for this new Database Technologies and only uses Features from 10g?

    21 Jul 2015
Powered by Confluence and Scroll Viewport