Oracle データベースセットアップ

This guide covers deploying the Confluence or Confluence WAR distributions 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 Database Troubleshooting for Oracle. Please review that page before continuing.

Schema Requirements

Confluence can be deployed to a schema in any Oracle instance.

Database Compatibility

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.

• For Oracle 11.1, use the 10.2.0.4 or 11.1.0.7.0 driver (Java 6 ojdbc6.jar).
• For Oracle 11.2, use the 11.2.0.1.0 driver (Java 6 ojdbc6.jar).

Tip: search for the jar filename on the download site.

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

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

Deploying Confluence with Oracle

Complete the instructions for installing Confluence, 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. See the Oracle JDBC driver FAQ.

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

3. 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>');

Determining Your JDBC URL

Oracle 用の JDBC Thin ドライバーは、3種類のスタイルの 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)))

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

参考として、サンプル出力を以下に示します。

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

1. The host and port is determined by the line containing PROTOCOL=tcp, without Presentation=HTTP.
2. 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.
3. 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. 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.

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

4. Change the username and password to match the Oracle login.

5. Change url to match what you determined in Determining the JDBC URL. For example:

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

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.

Configuring Confluence Datasource Access

次のデータソースを使用するように Confluence を設定します。

1. ファイル <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>
   

Running the Confluence Setup Wizard

Now Confluence is ready to attempt to connect to Oracle:

1. 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. This directory path is potentially <INSTALL>/lib if Confluence is running off Apache Tomcat version 6 or above.
2. Startup Confluence using <INSTALL>/bin/startup.bat or <INSTALL>/bin/startup.sh
3. Insert your licence and select External Database.
4. If you are using a Datasource:
   
   1. Select Datasource Connection using your Oracle version.
   2. Enter java:comp/env/jdbc/confluence for the name of the datasource.
5. If you are using a Direct JDBC URL:
   
   1. Select Direct JDBC URL using your Oracle version.
   2. Enter your JDBC URL to match what you determined in Determining the JDBC URL

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

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

関連トピック

Oracle データベースのトラブルシューティング