This guide covers deploying the Confluence or Confluence WAR distributions with an Oracle database.
If you are not a DBA, you should not attempt to set up this 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.
This setup guide must be used in conjunction with the list of Database Troubleshooting for Oracle. Please review that page before continuing.
Confluence can be deployed to a schema in any Oracle instance.
Please refer to Supported Platforms for information about supported database versions. If your version of Oracle is not supported, please upgrade to a supported version before installing Confluence.
Check your database drivers, to see if you need an update.
Tip: search for the jar filename on the download site.
Check that your version of Oracle does not have any known issues:
Oracle Version | Oracle Driver | Issue | Solution |
|---|---|---|---|
Any | Pre 10g | Driver incompatibilities | Upgrade to latest 10g drivers if compatible |
You may be also interested in the relevant JIRA documentation to check the compatibility of your Oracle server and driver.
Complete the instructions for installing Confluence, then return to this document instead of proceeding to the Confluence Setup Guide.
Tailor these instructions to your particular database version:
Create a Confluence user and grant the appropriate roles only to the user (connect role is required to set up a connection, while resource role is required to allow the user to create objects in it's own schema. Create table, sequence and trigger are required to configure the schema):
create user <user> identified by <password>; grant connect to <user>; grant resource to <user>; grant create table to <user>; grant create sequence to <user>; grant create trigger to <user>; |
Add a local "all_objects" view to the user's schema, so that there is no possibility that a table with the same name as one of the Confluence tables in another schema will cause any conflicts. This is a workaround for the bug CONF-3613:
create view <user>.all_objects as
select *
from sys.all_objects
where owner = upper('<user>'); |
Do not grant the database user the |
The JDBC thin driver for Oracle use three different styles of URL:
jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE |
jdbc:oracle:thin:@[HOST][:PORT]:SID |
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)))
|
The tnsnames style is required for connecting to an Oracle RAC cluster. This example has been broken up over multiple lines for clarity, but it should be compacted into a single line. These may need more analysis than documented below, 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, but if you use the old style URL, it can only be the SID.
You should be able to determine the host, port, service name, and/or SID by getting a DBA to run the following command as the user running oracle (by default "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 |
PROTOCOL=tcp, without Presentation=HTTP.Service is a service name for connecting to the database name following Instance on the next line.$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. If you want to use a datasource, please follow the instructions of the next two sections Adding a Datasource to Tomcat and Configuring Confluence Datasource Access, or if you'd prefer to just use a direct JDBC URL, skip to Running the Confluence Setup Wizard.
For further information on Oracle JDBC URLs, see this page.
<INSTALL>/conf/server.xml for editing.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>
|
Paste in the Resource section provided, before 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.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>
|
username and password to match the Oracle login.Change url to match what you determined in Determining the JDBC URL. For example:
jdbc:oracle:thin:@example.atlassian.com:1521:confluencedb |
Configure Confluence to use this datasource:
<INSTALL>/confluence/WEB-INF/web.xmlGo 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> |
Now Confluence is ready to attempt to connect to Oracle:
<confluence install>/WEB-INF/lib. This directory path is potentially <INSTALL>/lib if Confluence is running off Apache Tomcat version 6 or above.<INSTALL>/bin/startup.bat or <INSTALL>/bin/startup.shjava: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.
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".
Database Troubleshooting for Oracle