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 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>;
    • Specify the tablespace for the table objects as shown above.
    • The connect role is required to set up a connection.
    • The resource role is required to allow the user to create objects in its own schema. The resource role includes create table, create sequence, and create trigger by default. If you've altered the resource role to remove these, you'll need to grant these privileges to the user directly, or through some other role. 
    • Don't grant the select any table permission as this can cause problems with other schemas. 
    • Confluence 7.130 - 7.13.5 only: 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. create tablecreate sequence, and create trigger shouldn't be assigned as part of a role. 

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 typeFieldDescription
Simple Hostname

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

Simple PortThis is the Oracle port. If you didn't change the port when you installed Oracle, it will default to 1521.
SimpleService name This is the service name (of your confluence database.
By connection stringDatabase 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.

BothUsernameThis is the username of your dedicated database user. In the example above, this is confluenceuser.
BothPasswordThis 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 May 16, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.