Installation steps

Local install

• Download and install a version of Oracle Database. For example Oracle Express or Oracle Standard Edition.
• Download the corresponding Oracle JDBC Drivers and copy the JAR (e.g. ojdbc8.jar in WEB-INF/lib/)
• Start Oracle and connect to it with a DBA or system user. For example use the Oracle SQL*Plus command-line tool: connect system;
• Create the user for the main wiki:
  create user xwiki
  identified by xwiki;
• (Optional) Create a tablespace (files to hold database data). This is optional as Oracle provides a default USERS tablespace which is used when you create a user as above without specifying a tablespace:
  • List existing tablespaces:
    select * from dba_data_files;
  • Create an xwiki tablespace (adjust the `datafile` path by checking from the listed existing tablespaces), giving it enough space, and then make sure that the XWIKI user uses it:
    • Create the custom tablespace:
      create tablespace xwiki
      datafile '/opt/<SOMETHING>/oracle/oradata/<SID>/<PDB>/xwiki01.dbf'
      size 1m
      autoextend on
      maxsize 1g
      ;
    • Modify the XWIKI user to use it, and make sure it has quotas on it (by default it doesn't):
      alter user xwiki
      default tablespace xwiki
      temporary tablespace temp
      ;
      alter user xwiki quota unlimited on xwiki
      ;
  • If you use the default USERS tablespace you may still want to make it large enough. For example:
    alter database datafile '/opt/<SOMETHING>/oradata/<SID>/<PDB>/users01.dbf' resize 100M;
• Give sufficient privileges to the xwiki user:
  grant create session to xwiki;
  grant resource to xwiki;
  grant dba to xwiki;
• Tell XWiki to use Oracle. To do this, edit the WEB-INF/hibernate.cfg.xml file where you have expanded the XWiki WAR file and uncommented the Oracle part. Make sure to review the connection.url property. For example a typical Oracle Express would be:<property name="connection.url">jdbc:oracle:thin:@localhost:1521:<SID></property>

  XE is the default name of the ORACLE SID created by default by the installation for Oracle Express. If it is another you should change it. You can find the correct SID in app/oracle/product/10.2.0/server/NETWORK/ADMIN/tnsnames.ora in the Oracle installation directory (for Windows).

• To clean Oracle Database (remove the user, contents and data files):
  drop tablespace xwiki including contents and datafiles;
  drop user xwiki cascade;

From a VM

An easy to test Oracle is to use a VM. Here are some easy steps:

• Download the VM from the Oracle web site
• Install VirtualBox and import the VM in it
• Note: I had to configure the VM network settings to use the "Bridged Adapter" instead of "NAT" in order to be able see it from my host machine
• Start the VM and log in as oracle/oracle
• Note that IP address printed in the shell that opens up, for example: 192.168.0.49
• In the shell, starts the manager: emctl start dbconsole
• Execute all the instructions above in the shell to create the xwiki database (don't forget to download the JDBC driver and put it in WEB-INF/lib). Tip: Put the following in a file (for example xwiki.sql located on the Desktop) and execute echo @Desktop/xwiki.sql | sqlplus system/oracle@orcl
  :
  drop user xwiki cascade;
  create user xwiki identified by xwiki;
  select * from dba_data_files;
  create tablespace xwiki datafile '/home/oracle/app/oracle/oradata/orcl/xwiki01.dbf' size 1m autoextend on maxsize 1g;
  alter user xwiki default tablespace xwiki temporary tablespace temp;
  alter user xwiki quota unlimited on xwiki;
  grant create session to xwiki;
  grant resource to xwiki;
  grant dba to xwiki;
• From your host machine, point your browser on http://192.168.0.49:1158/em and connect as system/oracle
• In your XWiki's hibernate.cfg.xml, use:
  <property name="connection.url">jdbc:oracle:thin:@192.168.0.49:1521:orcl</property>
• Don't forget to drop the Oracle JDBC driver in your WEB-INF/lib directory!
• Enjoy

Using Docker

Follow these steps:

• Start Oracle: docker run --name oracle-xwiki -d -p 1521:1521 -v [<host mount point>:]/opt/oracle/oradata xwiki/oracle-database:19.3.0-se2
• Download the corresponding JDBC driver and put it in XWiki's WEB-INF/lib directory
• Edit XWiki's hibernate.cfg.xml file, comment out the Oracle section and make sure you use the following settings:
  <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521/xwiki</property>
  <property name="hibernate.connection.username">xwiki</property>
  <property name="hibernate.connection.password">xwiki</property>

And if you need to execute some SQL command you can do the following:

• Connect inside the docker container with: docker -it exec <container id> bash -l
• Run sqplplus with one of:
  • sqlplus system/xwiki@//localhost:1521/XWIKI to execute commands in the XWiki PDB
  • sqlplus sys/xwiki@//localhost:1521/XWIKICDB as sysdba if you need to execute commands in the CDB

Multi wiki support

Indexes

See Database Administration.

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:

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:

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:

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.

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