How to Connect Jira to Oracle in 4 Simple Steps?

Do you use JIRA for bug and issue tracking? Do you want to export your data from JIRA to Oracle? If yes, then you have landed at the right place. In this blog, you will learn about JIRA, Oracle, and the steps to connect these applications with ease. JIRA is a proprietary product used for bug tracking and agile project management. It creates an immense amount of data that needs the right architecture for storage. In this case, Oracle is a wise choice.

Oracle provides a structured platform for your data. It is an object-relational database management system. Oracle is capable of running different kinds of workloads, such as transaction loads and data warehouse loads. This post aims to provide you with a solution to connect JIRA to Oracle in 4 simple steps.

What is JIRA?

JIRA is a proprietary product developed by an Australian company, Atlassian. It is used for bug and issue tracking. It leverages all kinds of project management skills, including bug tracking, software development, etc. It is one of the most popular testing tools. JIRA is based on Agile methodology. JIRA is offered in four different packages, as mentioned below:

1. JIRA Core: It is a generic project management platform.
2. JIRA Software: It is a base software with agile functionality.
3. JIRA Service Desk: It is for IT or other business service tasks.
4. JIRA Ops: It is for intended management.

What is Oracle Database?

Oracle is a relational database management system. It is a popular enterprise application. It is known as Oracle database, OracleDB, and Oracle. It provides a logical and physical structure to your data. Oracle offers recovery in case of failure, portability on all platforms, high security, etc. Four different editions of Oracle are as follows:

1. Enterprise Edition: It is the most robust and secure edition. It offers high security, enhanced performance, etc. 
2. Standard Edition: It provides all the basic functionality to its users.
3. Express Edition (XE): It is a lightweight and free edition for Windows and Linux. 
4. Oracle Lite: It is for mobile devices. 

Prerequisites

• You can use Oracle version 12c R2, 18c, or 19c.
• You can install JDBC 19.3 from here.
• Create an XML backup, if you are migrating your data to another server. 
• Shut down your JIRA before you begin this process. 

How to Setup JIRA to Oracle Migration?

To connect JIRA to Oracle, you need to follow these steps:

• Jira to Oracle Migration: Configure your Oracle Database
• Jira to Oracle Migration: JDBC Driver Set-up
• Jira to Oracle Migration: Configure your JIRA Server
  • Configure your JIRA Server Using JIRA Set-up Wizard
  • Configure your JIRA Server Using JIRA Configuration Tool

Jira to Oracle Migration: Configure your Oracle Database

You should have an instance dedicated to JIRA. Either create a new instance or use an old one. Create a new user in your desired instance, using the following command:

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

Grant the following permissions to this user:

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

Jira to Oracle Migration: JDBC Driver Set-up

After you have installed the JDBC driver, copy the downloaded .jar file to your lib/ in the JIRA installation directory.

Jira to Oracle Migration: Configure your JIRA Server

You can configure your JIRA server in 2 ways, as mentioned below:

Let’s see how you will configure your JIRA server for Jira to Oracle Migration in these two methods.

Configure your JIRA Server Using JIRA Set-up Wizard

This method is used when you have just installed JIRA and set it up for the first time. All your settings will be saved in dbconfig.xml. The JIRA set-up wizard will be visible to you when you access JIRA for the first time. Use the following steps to configure your connection using the JIRA set-up wizard:

1. In ‘Configure Language and Database’, set Database Connection as ‘My own database’.
2. Set your database type as ‘Oracle’.
3. Fill the fields according to your dbconfig.xml file.

<?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:port:SID</url> //url
    <driver-class>oracle.jdbc.OracleDriver</driver-class>
    <username>jiradbuser</username> //username
    <password>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>

• Hostname: This is your “dbserver” in the <url> tag. It is the IP address of the machine on which Oracle is installed.
• Port: This is your port in the <url> tag. It specifies the TCP/IP port that your Oracle server is listening to.
• SID: This is your Oracle System Identification (SID) in <url> tag. By default, it is ORCL.
• Username: It is the JIRA user that connects to Oracle. It is placed inside the <username> tag.
• Password: It specifies the password of the user that connects with the Oracle. It is placed inside the <password> tag.

Configure your JIRA Server Using JIRA Configuration Tool

This method is for an existing JIRA instance. All settings will be saved in dbconfig.xml file. Use the following steps to configure your connection using the JIRA Configuration Tool:

• Run the JIRA Configuration tool, as mentioned below:
  In Windows, open a command prompt and run config.sh file in the bin directory of your JIRA installation directory.
  In Linux, open a console and run config.sh file in the bin directory of your JIRA installation directory.
• In the Database tab, select database type as “Oracle”.
• Fill the fields according to your dbconfig.xml file.

<?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:port:SID</url> //url
    <driver-class>oracle.jdbc.OracleDriver</driver-class>
    <username>jiradbuser</username> //username
    <password>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>

• Hostname: This is your dbserver in the <url> tag. The IP address of the machine on which Oracle is installed.
• Port: This is your port in the <url> tag. It specifies the TCP/IP port that your Oracle server is listening to.
• SID: This is your Oracle System Identification in <url> tag. By default, it is ORCL.
• Username: The JIRA user that connects to Oracle. It is placed inside the <username> tag.
• Password: It specifies the password of the user that connects with the Oracle. It is placed inside the <password> tag.

Troubleshooting and Possible Errors

1. JIRA displays the error message, “Something went wrong while executing your request”.

This might be if the user attempting to initiate SSO does not have a domain that is verified in JIRA.

Solution: Ensure that the user domain is verified in JIRA and the user that you assign has an account in both Oracle Identity Cloud Service and JIRA with the same email address.

2. JIRA displays the error message, “Your email address Email_Address doesn’t have access to Account_Name.atlassian.net”

This error occurs when the user attempting to initiate SSO has a verified domain, but the email attribute sent by Oracle Identity Cloud Service during SSO doesn’t match any existing user in JIRA.

Solution: Ensure that the user that you assign to the JIRA app has an account in both Oracle Identity Cloud Service and JIRA with the same email address.

3. Oracle Identity Cloud Service displays the message, “You are not authorized to access the app. Contact your system administrator.”

This can happen due to a variety of reasons, such as:

• Reason 1: The SAML 2.0 integration between the Oracle Identity Cloud Service JIRA app and JIRA is deactivated.
  Solution: Access the Oracle Identity Cloud Service administration console, select Applications, and then select JIRA. In the App Details section, click Activate, and then click Activate Application. Oracle Identity Cloud Service displays a confirmation message.

• Reason 2: The administrator revokes access for the user at the same time that the user tries to access the JIRA app using Oracle Identity Cloud Service.
  Solution: Access the Oracle Identity Cloud Service administration console, select Applications, and then select JIRA. In the App Details section, select Users, and then click Assign to re-assign the user.

For more information and unknown errors, you can contact oracle support.

Use Cases of Connecting Jira to Oracle

1. Project Status Tracking: Sync Jira issues with Oracle to monitor project progress in real-time.
2. Resource Allocation: Track team workload and optimize resource planning using Jira data in Oracle.
3. Budget Management: Align Jira project timelines with Oracle’s financial data for better cost tracking.
4. Custom Reporting: Generate detailed reports combining Jira’s task data with Oracle’s analytics.

Conclusion

In this blog, you learnt two methods to migrate data from Jira to Oracle. The manual method using JDBC driver is a long and tedious process that is more error prone. Instead, use Hevo to enhance your data migration process and get rid of long lines of code.

Simplify data management by connecting Jira with MongoDB. Learn more at Jira to MongoDB.

Hevo is a reliable, cost-effective, no-code platform that automates the process of data migration for you and provides a seamless experience. Sign up for Hevo’s 14-day free trial and enjoy efficient data migration.

Frequently Asked Questions