Connecting Stash to Oracle

Prerequisites

Backup

If you are migrating your data from the internal Stash database, back up the Stash home directory.

If you are migrating your Stash data from a different external database, back up that database by following the instructions provided by the database vendor before proceeding with these instructions.

See Data recovery and backups.

Create the Stash database

Before you can use Stash with Oracle, you must set up Oracle as follows:

• Ensure that you have a database instance available for Stash (either create a new one or use an existing one)
  The character set of the database must be set to either AL32UTF8 or UTF8, to support storage of Unicode data as per the Oracle documentation.
  Note that it is important to the proper operation of Stash that the database store its data in a case-sensitive manner. By changing the values of the NLS_COMP and/or NLS_SORT variables, it is possible to cause Oracle to perform its searches in a case-insensitive manner. We therefore strongly recommend that those variables be left at their default values.
• Create a user that Stash will connect as (e.g. stash). 
   Remember the database user name; it will be used to configure Stash's connection to the database in subsequent steps.
   When you create a user in Oracle, a schema is automatically created.
  It is strongly recommended that you create a new database user for use by Stash rather than sharing one that is used by other applications or people.
• Grant the Stash user connect and resource roles only. The connect role is required to set up a connection, while resource role is required to allow the user to create objects in its own schema.
• Create a local all_objects view to the user's schema, so that there is no possibility that a table with the same name as one of the Stash tables in another schema will cause any conflicts.
• Note that Stash requires the datebase to keep idle connections alive for at least 10 minutes. If the database is configured with less than a 10 minute connection timeout, there will be seemingly random connection errors. 

The format of the command to create a user in Oracle is:

CREATE USER <user>
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE USERS
  QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE to <user>;
CREATE VIEW <user>.all_objects AS
  SELECT *
  FROM sys.all_objects
  WHERE owner = upper('<user>');

Here is a simple example, using SQL*Plus, of how one might create a user called stash with password jdHyd6Sn21 in tablespace users, and grant the user a minimal set of privileges. When you run the command on your machine, remember to replace the user name, password and tablespace names with your own values.

CREATE USER stash
  IDENTIFIED BY jdHyd6Sn21
  DEFAULT TABLESPACE USERS
  QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE to stash;
CREATE VIEW stash.all_objects AS
  SELECT *
  FROM sys.all_objects
  WHERE owner = upper('stash');

This creates an empty Oracle schema with the name stash, and a user that can log in from the host that Stash is running on and who has full access to the newly created schema. In particular, the user is allowed to create sessions and tables.

Stash will generally require about 25–30 connections to the database. The maximum number of connections is a configurable system property – see Database pool.

Connect Stash to the Oracle database

You can now connect Stash to the Oracle database, either:

• when you run the Setup Wizard, at install time,
• when you wish to migrate to Oracle, either from the embedded Stash database or from another external database.

When running the Setup Wizard at install time

1. Select External at the 'Database' step.
2. Select Oracle for Database Type.
3. Complete the form. See the table below for details.
4. Click Next, and follow the instructions in the Stash Setup Wizard.

When migrating to Oracle

1. In the Stash administration area, click Database (under 'Settings').
2. Click Migrate database.
3. Select Oracle for Database Type.
4. Complete the form. See the table below for details.
5. Click Start Migration.

Install the JDBC driver

This section is only relevant to some distributions of Stash, for example if you are running Stash via the Atlassian Plugin SDK, or have built Stash from source.

If the Oracle JDBC driver is not bundled with Stash, you will need to download and install the driver yourself.

1. Download the appropriate JDBC driver from the Oracle download site.
2. Copy the downloaded jar file to your <Stash home directory>/lib directory (for Stash 2.1 or later).
3. Stop, then restart, Stash. see Starting and stopping Stash.