Database Setup for Oracle

This guide covers deploying Confluence standalone or WAR distribution with an Oracle database.

Oracle has a history of being extremely difficult to set up. If you do not have access to an experienced Oracle DBA in your organisation, you are recommended to select any free, scalable and easy-to-install alternative rather than proceeding with Oracle. Users evaluating Confluence are recommended to start with an alternative database and only consider migrating to Oracle after approval from their DBA. Atlassian's technical support for Oracle setup difficulties will also reflect the high minimum skill requirements for attempting an Oracle setup.

Database Setup Information

This setup guide must be used in conjunction with the list of Known Issues For Oracle. Please review that page before continuing.

Schema Requirements

Confluence must be deployed to a schema in it's own, separate Oracle instance. This avoids a Hibernate bug triggered by the existence of duplicate tablenames in other schemas, outlined here.

Database Incompatibilities

Orcale 9i and later are supported, please upgrade any older versions prior to installing Confluence.

Please check that your version of Oracle does not have any known issues:

You might be also interested in a relevant JIRA documentation to check the compatibility of your Oracle server and driver.

Deploying Against Oracle

Complete the instructions for installing Confluence standalone, then return to this document instead of proceeding to the Confluence Setup Guide.

Database Preparation

Tailor these instructions to your particular database version:

1. Perform any necessary database or driver upgrades. Download the latest compatible database drivers - click here for Oracle JDBC driver FAQ.
   
   
2. Create a separate instance of Oracle and apply any configuration tasks.
   
   
3. Create a Confluence user configuration and grant access permissions.

Adding a Datasource to Tomcat

1. Open <INSTALL>/conf/server.xml for editing.
   
   
2. Locate the section Host -> Context

   <Host name="localhost" debug="0" appBase="webapps" unpackWARs="true" autoDeploy="false">
       <Context path="" docBase="../confluence" debug="0" reloadable="true">
            <!-- Logger is deprecated in Tomcat 5.5. Logging configuration for Confluence is specified in confluence/WEB-INF/classes/log4j.properties -->
            <Manager pathname="" />
       </Context>
   </Host>
   

3. Paste in the Resource section provided, prior to Manager as shown

   <Host name="localhost" debug="0" appBase="webapps" unpackWARs="true" autoDeploy="false">
       <Context path="" docBase="../confluence" debug="0" reloadable="true">
            <!-- Logger is deprecated in Tomcat 5.5. Logging configuration for Confluence is specified in confluence/WEB-INF/classes/log4j.properties -->
   
            <Resource
            name="jdbc/confluence"
            auth="Container"
            type="javax.sql.DataSource"
            driverClassName="oracle.jdbc.driver.OracleDriver"
            url="jdbc:oracle:thin:@hostname:port:sid"
            username="<username>"
            password="<password>"
            connectionProperties="SetBigStringTryClob=true"
            maxActive="25"
            maxIdle="5"
            maxWait="10000"
            />
   
            <Manager pathname="" />
       </Context>
   </Host>
   

4. Change the username and password to match the Oracle login.
   
   
5. Change url to match hostname, port and sid of the Oracle server. sid stands for the Schema ID. For example:

   jdbc:oracle:thin:@example.atlassian.com:1521:confluencedb
   

   For connecting to an Oracle RAC cluster, you'll need to edit the connection string using Oracle's connection syntax like this:

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

   This example has been broken up over multiple lines for clarity, but it should be compacted into a single line.
   
6. If required, choose different maxActive and maxIdle values. These set how many total database connections will be allowed at one time, and how many will be kept open even when there is no database activity.

Configure Confluence Datasource Access

Confluence must be configured to use this datasource:

1. Edit the file <INSTALL>/confluence/WEB-INF/web.xml
   
   
2. Go to the end of the file and just before </web-app>, insert the following:

   <resource-ref>
   <description>Connection Pool</description>
   <res-ref-name>jdbc/confluence</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
   </resource-ref>
   

3. Download the Oracle JDBC database drivers for your JDK version via the Database JDBC drivers page. We recommend using the thin drivers only. Copy the jar file into <confluence install>/WEB-INF/lib (for Confluence 2.10 onwards) or <confluence install>/common/lib (for earlier versions). This directory path is potentially <INSTALL>/lib if Confluence is running off Apache Tomcat version 6 or above.

Confluence Setup Wizard

Now Confluence is ready to attempt to connect to Oracle:

1. Startup Confluence using <INSTALL>/bin/startup.bat or <INSTALL>/bin/startup.sh
   
   
2. Insert your licence and select External Database.
   
   
3. Select Datasource Connection using your Oracle version.
   
   
4. Enter java:comp/env/jdbc/confluence for the name of the datasource.

Confluence should now deploy using the Oracle database specified. Please read this comment on Oracle database optimisation.

Generic Oracle Configuration Tips

24-hour time format with Oracle 8i

We have received a report from a user that when an Oracle 8i database is configured to use 24-hour time as the default format, an exception like this may occur:

005-12-06 13:23:20 Loading root WebApplicationContext

2005-12-06 13:24:34 StandardContext[]: Exception sending context initialized event to listener instance
of class com.atlassian.confluence.util.ConfluenceContextLoaderListener

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userAccessor' defined in class path resource [applicationContext.xml]:
Can't resolve reference to bean 'userAccessorTarget' while setting property 'target';
nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userAccessorTarget' defined in class path
resource [applicationContext.xml]: Can't resolve reference to bean 'spacePermissionManager' while setting property 'spacePermissionManager';
nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'spacePermissionManager' defined in class path resource [securityContext.xml]:
Can't resolve reference to bean 'spacePermissionManagerTarget' while setting property 'target';
nested exception is org.springframework.beans.factory.BeanCreationException:
Error creating bean with name 'spacePermissionManagerTarget' defined in class path resource [securityContext.xml]: Initialization of bean failed;
nested exception is org.springframework.jdbc.UncategorizedSQLException: (Hibernate operation): encountered SQLException [Cannot create PoolableConnectionFactory];
nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory

...

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory, cause:


java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1


ORA-12705: invalid or unknown NLS parameter value specified

One symptom of this problem is that Confluence may refuse to start after midday.

The workaround is to go to 'General Configuration' and set the default time format to "HH:mm".

RELATED TOPICS

Known Issues For Oracle