Database Setup for Oracle

This page provides instructions for configuring Confluence to use an Oracle database.

Before you start

  • See Supported Platforms to check your version of Oracle is supported. You may need to upgrade your database before installing Confluence.
  • If you're switching from another database, including the embedded evaluation database, read Migrating to Another Database before you begin.

You'll need an experienced Oracle database administrator (DBA) to set up and maintain your database.

Our support team can assist with Confluence problems, but are unable to help you administer your Oracle database.

If you don't have access to an experienced Oracle DBA, consider using a different supported database.

1. Install Oracle

If you don't already have an operational Oracle server, download and install it now. See the Oracle documentation for instructions. 

When setting up your Oracle server:

  • Character encoding must be set to AL32UTF8 (this the Oracle equivalent of Unicode UTF-8).

2. Create database user

To create the user and assign its privileges:

  1. Use the sqlplus command to access Oracle via the command line

    sqlplus user/password <as sysdba|as sysoper>

    If you're logging in with the user 'sys' you'll need to include the "as sysdba" or "as sysoper" to determine which sys role you want to use. 

  2. Create a Confluence user (for example confluenceuser), and grant the following roles only to that user:

    create user <user> identified by <password> default tablespace <tablespace_name> quota unlimited on <tablespace_name>;
    grant connect to <user>;
    grant resource to <user>;
    grant create table to <user>;
    grant create sequence to <user>;
    grant create trigger to <user>;

2. Create user with schema-creation privileges

In this step you will create a Confluence user in Oracle and grant the appropriate roles to the user, so that the user can set up a connection, can create objects in its own schema, and can configure the schema.

To create the user and assign its privileges:

  1. Access the command line interface to Oracle via the 'sqlplus' command.

    sqlplus user/password <as sysdba|as sysoper>

    You must add the 'as sysdba' or 'as sysoper' option if you are logging in with the user 'sys'. This determines which sys role you are using.
    Once logged in, you can type arbitrary SQL commands.

  2. Create a Confluence user (<user>) in Oracle, and grant the appropriate roles only to the user:

    • connect role is required to set up a connection.
    • resource role is required to allow the user to create objects in its own schema. 
    • Create table, sequence and trigger are required to configure the schema.

      create user <user> identified by <password> default tablespace <tablespace_name> quota unlimited on <tablespace_name>;
      grant connect to <user>;
      grant resource to <user>;
      grant create table to <user>;
      grant create sequence to <user>;
      grant create trigger to <user>;
      


      Notes:

      • It is very important that the user is granted the exact privileges indicated above. Confluence requires only these privileges so you should grant specific privileges to the user, and not assign a role to the user.

      • Do not grant the user the select any table permission. That permission can cause problems with other schemas. 

      • When you create a user, specify the tablespace for the table objects as shown above.

3. Install Confluence 

Check out the Confluence Installation Guide for step-by-step instructions on how to install Confluence on your operating system. 

4. Download and install the Oracle thin driver

Due to licensing restrictions, we're not able to bundle an Oracle driver with Confluence. To make your database driver available to Confluence:

  1. Stop Confluence.
  2. Head to Database JDBC Drivers and download the appropriate driver. The driver file will be called something like ojdbc8.jar
  3. Drop the .jar file in your <installation-directory>/confluence/WEB-INF/lib directory.
  4. Restart Confluence then go to http://localhost:<port> in your browser to continue the setup process.

5. Enter your database details

The Confluence setup wizard will guide you through the process of connecting Confluence to your database. 

Use a JDBC connection (default)

JDBC is the recommended method for connecting to your database.

The Confluence setup wizard will provide you with two setup options:

  • Simple - this is the most straightforward way to connect to your database.
  • By connection string - use this option if you want to specify additional parameters and are comfortable constructing a database URL. 

Depending on the setup type, you'll be prompted for the following information.

Setup type Field Description
Simple  Hostname

This is the hostname or IP address of your database server.  

Simple  Port This is the Oracle port. If you didn't change the port when you installed Oracle, it will default to 1521.
Simple Service name  This is the service name (of your confluence database.
By connection string Database URL

The database URL is entered in this format:
jdbc:oracle:thin:@//<HOST>:<PORT>/<SERVICE>

<SERVICE> can be either the SID or Service Name. For example:  jdbc:oracle:thin:@//localhost:1521/confluence

By default, we use the new style URL provided by the thin driver. You can also use the tnsnames style.

Both Username This is the username of your dedicated database user. In the example above, this is confluenceuser.
Both Password This is the password for your dedicated database user.
Not sure how to find your hostname, port and SID?

To determine the host, port, service name, and/or SID, execute the following command as the user running Oracle (usually 'Oracle'):

lsnrctl status

Here's an example of the output:

SNRCTL for Linux: Version 11.2.0.2.0 - Beta on 29-JUN-2012 15:20:59
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Beta
Start Date                06-JUN-2012 08:36:34
Uptime                    23 days 6 hr. 44 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/<HOSTNAME>/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOSTNAME>)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOSTNAME>)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
  • The host and port are determined by the line containing PROTOCOL=tcp (the line without Presentation=HTTP).
  • Under Services Summary, each service which has an instance with READY status is a connectable service. The name following Service is a service name for connecting to the database name following Instance on the next line.
  • The SID is the name of the database instance, as defined by the $ORACLE_SID variable when you have sourced the Oracle environment to your shell.

For example, if you are running Confluence on the same server as the Oracle database, with the above lsnrctl status output, you would use one of the following URLs:

jdbc:oracle:thin:@//localhost:1521/XE
jdbc:oracle:thin:@localhost:1521:XE

The URL can be used in either a direct JDBC connection or a datasource.

See the Oracle JDBC FAQ for more information on Oracle JDBC URLs.

Use a JNDI datasource

If you want to use a JNDI datasource, see Configuring a datasource connection for the steps you'll need to take before you set up Confluence, as the setup wizard will only provide the option to use a datasource if it detects a datasource in your Tomcat configuration. 

6. Test your database connection

In the database setup screen, hit the Test connection button to check:

  • that Confluence can connect to your database server
  • that the database character encoding is correct
  • that your database user has appropriate permissions for the database
  • that your database user has NOT been granted the SELECT ANY TABLE privilege

Once the test is successful, hit Next to continue with the Confluence setup process. 

Troubleshooting

Last modified on Nov 13, 2017

Was this helpful?

Yes
No
Provide feedback about this article

Not finding the help you need?

Ask the community

Powered by Confluence and Scroll Viewport.