Hide last authors
Vincent Massol 4.2 1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
Vincent Massol 1.1 4
Vincent Massol 4.2 5 = Installation steps =
Vincent Massol 1.7 6
Vincent Massol 25.1 7 == Local install ==
Vincent Massol 4.1 8
Vincent Massol 23.1 9 * Download and install a version of Oracle Database. For example [[Oracle Express or Oracle Standard Edition>>https://www.oracle.com/database/technologies/]].
Thomas Mortagne 16.1 10 * Download the corresponding [[Oracle JDBC Drivers>>https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html]] and copy the JAR (e.g. ##ojdbc8.jar## in ##WEB-INF/lib/##)
Manuel Smeria 8.4 11 * Start Oracle and connect to it with a DBA or system user. For example use the Oracle SQL*Plus command-line tool: {{code}}connect system;{{/code}}
Vincent Massol 23.1 12 * Create the user for the main wiki:(((
Eduard Moraru 26.1 13 {{code language="sql"}}
guidol 8.1 14 create user xwiki
Manuel Smeria 8.3 15 identified by xwiki;
Vincent Massol 4.1 16 {{/code}}
Vincent Massol 5.2 17 )))
Vincent Massol 23.1 18 * (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:(((
19 * List existing tablespaces:(((
Eduard Moraru 26.1 20 {{code language="sql"}}
Vincent Massol 23.1 21 select * from dba_data_files;
22 {{/code}}
23 )))
24 * 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:(((
25 * Create the custom tablespace:(((
Eduard Moraru 26.1 26 {{code language="sql"}}
guidol 8.1 27 create tablespace xwiki
Vincent Massol 25.1 28 datafile '/opt/<SOMETHING>/oracle/oradata/<SID>/<PDB>/xwiki01.dbf'
Vincent Massol 23.1 29 size 1m
30 autoextend on
guidol 8.1 31 maxsize 1g
32 ;
Vincent Massol 4.1 33 {{/code}}
Vincent Massol 5.2 34 )))
Vincent Massol 23.1 35 * Modify the ##XWIKI## user to use it, and make sure it has quotas on it (by default it doesn't):(((
Eduard Moraru 26.1 36 {{code language="sql"}}
guidol 8.1 37 alter user xwiki
38 default tablespace xwiki
39 temporary tablespace temp
40 ;
41 alter user xwiki quota unlimited on xwiki
42 ;
43 {{/code}}
44 )))
Vincent Massol 23.1 45 )))
46 * If you use the default ##USERS## tablespace you may still want to make it large enough. For example:(((
Eduard Moraru 26.1 47 {{code language="sql"}}
Vincent Massol 25.1 48 alter database datafile '/opt/<SOMETHING>/oradata/<SID>/<PDB>/users01.dbf' resize 100M;
Vincent Massol 23.1 49 {{/code}}
50 )))
51 )))
Vincent Massol 25.2 52 * Give sufficient privileges to the ##xwiki## user:(((
Eduard Moraru 26.1 53 {{code language="sql"}}
Vincent Massol 28.1 54 grant connect to xwiki;
55 grant resource to xwiki;
56 grant dba to xwiki;
guidol 8.1 57 {{/code}}
58 )))
Vincent Massol 23.1 59 * 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:{{code}}<property name="connection.url">jdbc:oracle:thin:@localhost:1521:<SID></property>{{/code}}(((
Manuel Smeria 8.3 60 {{info}}
61 **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).
62 {{/info}}
Vincent Massol 5.2 63 )))
Vincent Massol 14.1 64
Vincent Massol 25.1 65 == From a VM ==
Vincent Massol 17.1 66
67 An easy to test Oracle is to use a VM. Here are some easy steps:
68
69 * [[Download the VM from the Oracle web site>>http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html]]
70 * Install [[VirtualBox>>https://www.virtualbox.org/]] and import the VM in it
71 * 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
72 * Start the VM and log in as ##oracle##/##oracle##
73 * Note that IP address printed in the shell that opens up, for example: 192.168.0.49
74 * In the shell, starts the manager: ##emctl start dbconsole##
75 * 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 {{code}}echo @Desktop/xwiki.sql | sqlplus system/[email protected]{{/code}}:(((
Eduard Moraru 26.1 76 {{code language="sql"}}
Vincent Massol 17.1 77 drop user xwiki cascade;
78 create user xwiki identified by xwiki;
79 select * from dba_data_files;
80 create tablespace xwiki datafile '/home/oracle/app/oracle/oradata/orcl/xwiki01.dbf' size 1m autoextend on maxsize 1g;
81 alter user xwiki default tablespace xwiki temporary tablespace temp;
82 alter user xwiki quota unlimited on xwiki;
Vincent Massol 28.1 83 grant connect to xwiki;
84 grant resource to xwiki;
85 grant dba to xwiki;
Vincent Massol 17.1 86 {{/code}}
87 )))
88 * From your host machine, point your browser on http:~/~/192.168.0.49:1158/em and connect as ##system/oracle##
89 * In your XWiki's ##hibernate.cfg.xml##, use:(((
Eduard Moraru 26.1 90 {{code language="xml"}}
Vincent Massol 17.1 91 <property name="connection.url">jdbc:oracle:thin:@192.168.0.49:1521:orcl</property>
92 {{/code}}
93 )))
94 * Don't forget to drop the Oracle JDBC driver in your ##WEB-INF/lib## directory!
95 * Enjoy ;)
96
Vincent Massol 25.1 97 == Using Docker ==
Vincent Massol 20.1 98
99 Follow these steps:
Eduard Moraru 26.1 100
101 * Start Oracle: {{code language="none"}}docker run --name oracle-xwiki -d -p 1521:1521 -v [<host mount point>:]/opt/oracle/oradata xwiki/oracle-database:19.3.0-se2{{/code}}
Vincent Massol 20.1 102 * Download the corresponding [[JDBC driver>>https://repo1.maven.org/maven2/com/oracle/ojdbc/ojdbc8/]] and put it in XWiki's ##WEB-INF/lib## directory
103 * Edit XWiki's ##hibernate.cfg.xml## file, comment out the Oracle section and make sure you use the following settings:(((
Eduard Moraru 26.1 104 {{code language="xml"}}
Vincent Massol 24.1 105 <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521/xwiki</property>
Vincent Massol 20.1 106 <property name="hibernate.connection.username">xwiki</property>
107 <property name="hibernate.connection.password">xwiki</property>
108 {{/code}}
109 )))
110
Vincent Massol 21.1 111 And if you need to execute some SQL command you can do the following:
Eduard Moraru 26.1 112
113 * Connect inside the docker container with: {{code language="shell"}}docker -it exec <container id> bash -l{{/code}}
Vincent Massol 24.1 114 * Run ##sqplplus## with one of:
Eduard Moraru 26.1 115 ** {{code language="sql"}}sqlplus system/[email protected]//localhost:1521/XWIKI{{/code}} to execute commands in the XWiki PDB
116 ** {{code language="sql"}}sqlplus sys/[email protected]//localhost:1521/XWIKICDB as sysdba{{/code}} if you need to execute commands in the CDB
Vincent Massol 21.1 117
Vincent Massol 22.1 118 {{info}}
119 You can check [[how the XWiki Oracle docker image is built>>https://github.com/xwiki/xwiki-docker-build/tree/master/build-oracle]].
120 {{/info}}
121
Vincent Massol 25.1 122 = Multi wiki support =
123
Eduard Moraru 26.1 124 {{version since="12.8RC1"}}
Vincent Massol 25.1 125 When creating a (sub)wiki, XWiki will create a new Oracle user using the equivalent of:
126
127 {{code language='sql'}}
128 CREATE USER <wikiId> IDENTIFIED BY <wikiId> QUOTA UNLIMITED ON USERS;
129 GRANT RESOURCE TO <wikiId>;
130 {{/code}}
131
132 Notes:
133 * The default ##USERS## tablespace is used. Thus make sure that this tablespace is large enough. You may need to execute:(((
134 {{code language='sql'}}
135 alter database datafile '/opt/<SOMETHING>/oradata/<SID>/<PDB>/users01.dbf' resize 100M;
136 {{/code}}
137 )))
138 {{/version}}
139
Vincent Massol 19.1 140 = Indexes =
141
142 See [[Database Administration>>Documentation.AdminGuide.Performances.Database Administration.WebHome]].
143
144 {{code}}
145 CREATE INDEX XWLS_VALUE ON XWIKILARGESTRINGS (XWL_VALUE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
146 create index xwd_parent on xwikidoc (xwd_parent) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
147 create index xwd_class_xml on xwikidoc (xwd_class_xml) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
148 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
Eduard Moraru 27.1 149 create index ase_page_date on activitystream_events (ase_page, ase_date);
Vincent Massol 19.1 150 create index ase_param1 on activitystream_events (ase_param1) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
151 create index ase_param2 on activitystream_events (ase_param2) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
152 create index ase_param3 on activitystream_events (ase_param3) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
153 create index ase_param4 on activitystream_events (ase_param4) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
154 create index ase_param5 on activitystream_events (ase_param5) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
Eduard Moraru 27.1 155 create index solr_iterate_all_documents on xwikidoc (XWD_WEB, XWD_NAME, XWD_LANGUAGE, XWD_VERSION);
Vincent Massol 19.1 156 {{/code}}
157
Vincent Massol 7.1 158 = Troubleshooting =
159
160 == ORA-01400: cannot insert NULL into ("XWIKI"."XWIKILARGESTRINGS"."XWL_ID") ==
161
Manuel Smeria 8.3 162 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.
Vincent Massol 6.1 163
Vincent Massol 7.1 164 == SetString can only process strings of less than 32766 chararacters ==
Vincent Massol 4.1 165
Manuel Smeria 8.3 166 If you see an error that says something like this:
Vincent Massol 1.7 167
Manuel Smeria 8.3 168 {{code}}
Vincent Massol 1.7 169 Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
170 Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
171 com.xpn.xwiki.XWikiException: Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
172 Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
173 ...
174 Wrapped Exception:
175
176 java.sql.SQLException: setString can only process strings of less than 32766 chararacters
177 ...
Vincent Massol 4.1 178 {{/code}}
Vincent Massol 1.7 179
Manuel Smeria 8.3 180 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:
Vincent Massol 1.7 181
Vincent Massol 4.1 182 {{code}}
Vincent Massol 1.7 183 <property name="hibernate.connection.SetBigStringTryClob">true</property>
184 <property name="hibernate.jdbc.batch_size">0</property>
Vincent Massol 4.1 185 {{/code}}
Vincent Massol 10.1 186
Vincent Massol 13.1 187 == NullPointerException at HqlSqlWalker ==
188
189 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.
190
Vincent Massol 17.1 191 == Errors due to missing RAM ==
Vincent Massol 10.1 192
Vincent Massol 17.2 193 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.
Vincent Massol 14.1 194
Eduard Moraru 26.1 195 {{code language="none"}}
Vincent Massol 17.1 196 SQL Error: 0, SQLState: null
197 Cannot get a connection, pool error Timeout waiting for idle object
198 ORA-12519, TNS:no appropriate service handler found
199 ORA-01435: user does not exist
Vincent Massol 12.1 200 {{/code}}
Vincent Massol 17.1 201
202 Note that it's possible to have these errors for other reasons too but you should at least check that the RAM is enough.

Get Connected