Installation steps

Local install

See Configure Oracle Manually.

From a VM

See Configure Oracle in a Virtual Machine.

Using Docker

See Configure Oracle for XWiki in Docker.

Multi wiki support

CREATE USER <wikiId> IDENTIFIED BY <wikiId> QUOTA UNLIMITED ON USERS;
GRANT RESOURCE TO <wikiId>;

Indexes

See Database Administration.

CREATE INDEX XWLS_VALUE ON XWIKILARGESTRINGS (XWL_VALUE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index xwd_parent on xwikidoc (xwd_parent) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index xwd_class_xml on xwikidoc (xwd_class_xml) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index xda_docid1 on xwikiattrecyclebin (xda_docid);
create index ase_page_date on activitystream_events (ase_page, ase_date);
create index ase_param1 on activitystream_events (ase_param1) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param2 on activitystream_events (ase_param2) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param3 on activitystream_events (ase_param3) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param4 on activitystream_events (ase_param4) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param5 on activitystream_events (ase_param5) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index solr_iterate_all_documents on xwikidoc (XWD_WEB, XWD_NAME, XWD_LANGUAGE, XWD_VERSION);

Permissions

In the instructions above, the DBA role is given to the xwiki user for simplicity. However, that could be a security risk in your environment and you might want to reduce the permissions given to the xwiki user. Here's a minimal list of rights needed by the xwiki user:

-- Common operations
grant create session to xwiki -- Login to DB;
grant resource to xwiki -- Create tables, sequences, etc;
grant select any table to xwiki;
grant select any sequence to xwiki;
grant insert any table to xwiki;
grant update any table to xwiki;
grant delete any table to xwiki;

-- Subwiki creation
-- Note: creating a subwiki executes the migration step, see below
grant create user to xwiki -- Creation of a schema (ie user);

-- Subwiki deletion
grant drop user to xwiki -- Remove user;

-- Hibernate migrations (at startup and when creating new subwikis only)
-- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
grant create any table to xwiki;
grant drop any table to xwiki;
grant alter any table to xwiki;
grant create any index to xwiki;
grant alter any index to xwiki;
grant create any sequence to xwiki;

grant resource to xwiki with admin option;

Using scripts

You may want to only give the more permissive rights ony when creating or deleting a subwiki or when upgrading an XWiki instance. If this is the case here are some scripts you could use.

• Script to run to set the rights to normal operations

  -- Remove rights for subwiki creation/deletion or upgrades
  revoke create user from xwiki;
  revoke drop user from xwiki;
  revoke create any table from xwiki;
  revoke drop any table from xwiki;
  revoke alter any table from xwiki;
  revoke create any index from xwiki;
  revoke alter any index from xwiki;
  revoke create any sequence from xwiki;
  
  -- Common operations
  grant create session to xwiki -- Login to DB;
  grant resource to xwiki -- Create tables, sequences, etc;
  grant select any table to xwiki;
  grant select any sequence to xwiki;
  grant insert any table to xwiki;
  grant update any table to xwiki;
  grant delete any table to xwiki;

• Script to run prior to creating a new wiki. Once the wiki has been created, run the "Script to run to set the rights to normal operations" above

  -- Subwiki creation
  -- Note: creating a subwiki executes the migration step, see below
  grant create user to xwiki -- Creation of a schema (ie user);
  
  -- Hibernate migrations (at startup and when creating new subwikis only)
  -- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
  grant create any table to xwiki;
  grant drop any table to xwiki;
  grant alter any table to xwiki;
  grant create any index to xwiki;
  grant alter any index to xwiki;
  grant create any sequence to xwiki;

• Script to run prior to deleting a wiki. Once the wiki has been deleted, run the "Script to run to set the rights to normal operations" above

  -- Subwiki deletion
  grant drop user to xwiki -- Remove user;

• Script to run prior to upgrading to new XWiki version (in case there are schema changes that require executing some SQL commands by XWiki). Once the wiki has been upgraded, run the "Script to run to set the rights to normal operations" above

  -- Hibernate migrations (at startup and when creating new subwikis only)
  -- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
  grant create any table to xwiki;
  grant drop any table to xwiki;
  grant alter any table to xwiki;
  grant create any index to xwiki;
  grant alter any index to xwiki;
  grant create any sequence to xwiki;

Oracle Wallet

If you consider that the hibernate.cfg.xml file is not secure and you wish to not have credentials stored in plain text, you can use Oracle Wallet.

Follow these steps:

• On the machine having the Oracle DB installed, create a Wallet using mkstore -wrl <wallet_location> -create
• Then add the database credentials to the Wallet: mkstore -wrl <wallet_location> -createCredential <db_connect_string> <username> <password> (for example: mkstore -wrl <wallet_location> -createCredential "localhost:1521/xwiki" xwiki xwiki. Of course you should use a better password for the xwiki user)
• Copy the cwallet.sso and ewallet.p12 files to the machine where XWiki is started. Make sure you place them in a secure directory. Let's call it WALLETLOCALDIR.
• Download the full Oracle JDBC zip, unzip and copy oraclepki.jar, osdt_cert.jar and osdt_core.jar files to XWiki's WEB-INF/lib/ directory.
• Edit the XWiki hibernate.cfg.xml file and:
  • Modify the connection URL for Oracle (notice the / before the @ symbol): <property name="hibernate.connection.url">jdbc:oracle:thin:/@localhost:1521/xwiki</property>
  • Remove the following 2 properties or set the username and password to empty values:

    <property name="hibernate.connection.username">xwiki</property>
    <property name="hibernate.connection.password">xwiki</property>

• Modify the way you start XWiki to pass the following system property: -Doracle.net.wallet_location=<WALLETLOCALDIR>

Delegate user creation

When creating a wiki, XWiki will automatically create a new user (and thus a new schema). However, you could want to delegate that to a DBA or infra admin in order to control the user password used or to use a specific tablespace (just to give 2 examples, there are more).

Changing DB passwords for wikis

If you have not been delegating creation of the DB users for wikis, the XWiki will have created one user per wiki with the password being the same as the wiki id. Since XWiki users the DB user for the main wiki for all its DB operations, there's no problem changing the passwords for the DB users created for the various wikis (they are not used anyway).

Troubleshooting

ORA-01400: cannot insert NULL into ("XWIKI"."XWIKILARGESTRINGS"."XWL_ID")

This error can appear if you're using Oracle JDBC driver 10.2.0.1.0. The solution is to use version 10.2.0.2 or greater of the driver.

SetString can only process strings of less than 32766 chararacters

If you see an error that says something like this:

Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
com.xpn.xwiki.XWikiException: Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
...
Wrapped Exception:

java.sql.SQLException: setString can only process strings of less than 32766 chararacters
...

Then that's because Oracle has a limitation of 32K for CLOBs. To overcome it you need to add the following 2 properties in the hibernate.cfg.xml file, as specified in the installation steps section above:

<property name="hibernate.connection.SetBigStringTryClob">true</property>
<property name="hibernate.jdbc.batch_size">0</property>

NullPointerException at HqlSqlWalker

This is actually caused by a wrong Oracle ojdbc JAR being used. There are different JARs for every different minor version of Oracle. For example if you use the JDBC connector for Oracle 11g version 11.2.0.4.0 and your Oracle db version is 11.2.0.1.0 then you'll have the problem.

Errors due to missing RAM

If you see one of the following errors in the XWiki logs, it may simply be that you don't allocate enough RAM to Oracle. This is especially true if you're running it in a VM or in a Docker Container. For example we know that 2GB is not enough for Oracle and 2.5GB+ is ok.

SQL Error: 0, SQLState: null
Cannot get a connection, pool error Timeout waiting for idle object 
ORA-12519, TNS:no appropriate service handler found
ORA-01435: user does not exist

Note that it's possible to have these errors for other reasons too but you should at least check that the RAM is enough.