Database Setup for Oracle

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).
• Collation should be set to BINARY.

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>;

   • 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 table, create sequence, and create trigger shouldn't be assigned as part of a role. 

   • 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.

lsnrctl status

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

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

Use a JNDI datasource

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

• If Confluence complains that it is missing a class file, you may have placed the JDBC driver in the wrong folder.
• The following page contains common issues encountered when setting up your Oracle database to work with Confluence: Known Issues for Oracle.
• There's a known issue when running Oracle with Native Network Encryption that can cause Confluence to become unresponsive. See CONFSERVER-60152 - Getting issue details... STATUS  for more details and Confluence Unresponsive Due to High Database Connection Latency for some suggested mitigation strategies. 
• There's a known issue when username or schema names contain dots. See  CONFSERVER-60274 - Getting issue details... STATUS  for more information.