Oracle Installation

Last modified by Vincent Massol on 2020/07/08

Installation steps

Steps:

  • Download and install a version of Oracle Database. For example Oracle Express.
  • 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 wiki user and schema:
    create user xwiki
    identified by xwiki;
  • Create a tablespace (which is the Oracle term for what you know as a "database" on Microsoft SQL Server):
    select *
    from   dba_data_files
    ;
    --
    -- Pick an adequate name from the list above.
    --
    create tablespace xwiki
    datafile '/opt/SOMETHING/oracle/oradata/SID/xwiki01.dbf'
    size 1m
    autoextend on
    maxsize 1g
    ;
  • Create tables of the xwiki user by default in the new tablespace:
    alter user xwiki
    default tablespace xwiki
    temporary tablespace temp
    ;
    alter user xwiki quota unlimited on xwiki
    ;
  • Give sufficient privileges to the xwiki user:
    grant connect 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:XE</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).

Installing Oracle 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 create table to xwiki;
    grant create sequence 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 emoticon_wink

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: sqlplus xwiki/[email protected]//localhost:1521/xwiki

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 ase_page_date on  activitystream_events (ase_page, ase_date);
create index xda_docid1 on xwikiattrecyclebin (xda_docid);
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)');

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.

Tags:
Created by Vincent Massol on 2017/09/06
   

Get Connected