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

See Oracle JDBC Driver Compatibility Issues.

SetString can only process strings of less than 32766 chararacters

See Oracle "setString" Error on Large Text Values.

NullPointerException at HqlSqlWalker

See Oracle JDBC Driver Compatibility Issues.

Errors due to missing RAM

See Oracle Listener Connection Errors.