Database Setup for Oracle

Step 2. Install your Oracle server

If you do not already have an operational Oracle database server, download the installation package from the Oracle download page and follow the instructions in the Oracle documentation.

Then follow the steps below, to deploy Confluence to a schema in your Oracle server.

Note: Your database should be configured to use the same character encoding as Confluence. The recommended encoding is AL32UTF8 (the Oracle equivalent of Unicode UTF-8). See Configuring Database Character Encoding.

Step 3. Set up your Oracle 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:

     • Do not grant the user the select any table permission. That permission can cause problems with other schemas. See the bug report CONF-3613.

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

3. Add a local all_objects view to the user's schema, to prevent a conflict that can occur when a table exists in another schema with the same name as one of the Confluence tables. This is a workaround for the bug CONF-3613:

   create view <user>.all_objects as
   select *
   from sys.all_objects
   where owner = upper('<user>');

Step 4. Install Confluence

Install Confluence if you have not done so already. See the Confluence Installation Guide. Stop immediately after the installation, before opening the Confluence Setup Wizard in your browser, and follow the steps below.

If you have already got part-way through the Confluence Setup Wizard, stop at the database setup step and follow the steps below. You will be able to restart the setup wizard at the same step later.

Step 5. Determine your JDBC URL

The JDBC thin driver for Oracle use three different styles of URL:

• New style:

  New Style

   jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE

• Old style:

  Old Style

  jdbc:oracle:thin:@[HOST][:PORT]:SID

• 'tnsnames' style:

  tnsnames

  jdbc:oracle:thin:@(DESCRIPTION=
                      (SDU=32768)
                      (enable=broken)
                      (LOAD_BALANCE=yes)
                      (FAILOVER=yes)
                      (ADDRESS=
                        (PROTOCOL=TCP)
                        (HOST=dbserver1.example.com)
                        (PORT=1525))
                      (ADDRESS=
                        (PROTOCOL=TCP)
                        (HOST=dbserver2.example.com)
                        (PORT=1525))
                      (CONNECT_DATA=
                        (SERVICE_NAME=CONFDB)))
  

Notes:

• The tnsnames style is required for connecting to an Oracle RAC cluster. For easy reading, we have split the example above over multiple lines, but you should compact it into a single line. These values may need more analysis than documented here, so you should seek the assistance of an experienced DBA.
• If you use the new style URL, then SERVICE can be either an SID or Service Name.
• If you use the old style URL, then SERVICE can only be the SID.

To determine the host, port, service name, and/or SID, execute the following command as the user running Oracle. (By default, the user is "oracle"):

 lsnrctl status

For reference, here is a sample 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

Notes:

• The host and port are determined by the line containing PROTOCOL=tcp, 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, assuming that 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 using a Tomcat datasource.

For further information on Oracle JDBC URLs, see the Oracle JDBC FAQ.

Step 6. Download and install the Oracle database driver

Decide whether you will set up a direct JDBC connection or a datasource connection to Oracle, to suit your environment. If unsure, choose direct JDBC.

To set up a direct JDBC connection:

If you plan to set up a direct JDBC connection to Oracle, you will need to copy the Oracle JDBC driver to your Confluence installation.

1. Download the latest compatible database driver. Links to the appropriate database drivers are available on this page: Database JDBC Drivers.
2. Copy the driver JAR file to the <Confluence installation>/confluence/WEB-INF/lib folder in your new Confluence installation.

To set up a datasource connection:

If you plan to set up a datasource connection to Oracle, follow the steps described in Configuring an Oracle Datasource in Apache Tomcat.

Step 7. Set up your database connection in the Confluence Setup Wizard

Start Confluence, and go to the Confluence Setup Wizard in your browser. Follow these steps to set up the new configuration:

1. Follow the initial steps in the Confluence Setup Guide.
2. When prompted to choose an evaluation or production installation, choose production installation.
3. When prompted to choose an embedded or external database, select Oracle xx from the dropdown list, where 'xx' is your Oracle version, and choose External Database.
4. Choose either the direct JDBC or the datasource connection, to suit the choice you made earlier when setting up the Oracle database driver.
   • For the JDBC connection: Enter the database URL to match the JDBC URL you determined in the previous section. Enter the user name (for example, confluenceuser) and password you chose when adding the Confluence database to Oracle.
   • For a datasource connection: Set the JNDI name to java:comp/env/jdbc/confluence.
5. Wait a while, as Confluence will create the schema in Oracle.

Congratulations! Confluence is now using your Oracle database to store its data.

Troubleshooting

• The following page contains common issues encountered when setting up your Oracle database to work with Confluence: Known Issues for Oracle.
• If Confluence complains that it is missing a class file, you may have placed the JDBC driver in the wrong folder.
• If none of the above describes your issue, please create a support ticket at http://support.atlassian.com and be sure to include your logs (found in <CONFLUENCE-INSTALLATION>/logs and <CONFLUENCE-HOME>/logs).