Show last authors
1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 = Installation steps =
6
7 == Local install ==
8
9 * Download and install a version of Oracle Database. For example [[Oracle Express or Oracle Standard Edition>>https://www.oracle.com/database/technologies/]].
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/##)
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}}
12 * Create the user for the main wiki:(((
13 {{code language="sql"}}
14 create user xwiki
15 identified by xwiki;
16 {{/code}}
17 )))
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:(((
20 {{code language="sql"}}
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:(((
26 {{code language="sql"}}
27 create tablespace xwiki
28 datafile '/opt/<SOMETHING>/oracle/oradata/<SID>/<PDB>/xwiki01.dbf'
29 size 1m
30 autoextend on
31 maxsize 1g
32 ;
33 {{/code}}
34 )))
35 * Modify the ##XWIKI## user to use it, and make sure it has quotas on it (by default it doesn't):(((
36 {{code language="sql"}}
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 )))
45 )))
46 * If you use the default ##USERS## tablespace you may still want to make it large enough. For example:(((
47 {{code language="sql"}}
48 alter database datafile '/opt/<SOMETHING>/oradata/<SID>/<PDB>/users01.dbf' resize 100M;
49 {{/code}}
50 )))
51 )))
52 * Give sufficient privileges to the ##xwiki## user:(((
53 {{code language="sql"}}
54 grant create session to xwiki;
55 grant resource to xwiki;
56 grant dba to xwiki;
57 {{/code}}
58 )))
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}}(((
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}}
63 )))
64
65 == From a VM ==
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/oracle@orcl{{/code}}:(((
76 {{code language="sql"}}
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;
83 grant create session to xwiki;
84 grant resource to xwiki;
85 grant dba to xwiki;
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:(((
90 {{code language="xml"}}
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
97 == Using Docker ==
98
99 Follow these steps:
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}}
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:(((
104 {{code language="xml"}}
105 <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521/xwiki</property>
106 <property name="hibernate.connection.username">xwiki</property>
107 <property name="hibernate.connection.password">xwiki</property>
108 {{/code}}
109 )))
110
111 And if you need to execute some SQL command you can do the following:
112
113 * Connect inside the docker container with: {{code language="shell"}}docker -it exec <container id> bash -l{{/code}}
114 * Run ##sqplplus## with one of:
115 ** {{code language="sql"}}sqlplus system/xwiki@//localhost:1521/XWIKI{{/code}} to execute commands in the XWiki PDB
116 ** {{code language="sql"}}sqlplus sys/xwiki@//localhost:1521/XWIKICDB as sysdba{{/code}} if you need to execute commands in the CDB
117
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
122 = Multi wiki support =
123
124 {{version since="12.8RC1"}}
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
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);
149 create index ase_page_date on activitystream_events (ase_page, ase_date);
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)');
155 create index solr_iterate_all_documents on xwikidoc (XWD_WEB, XWD_NAME, XWD_LANGUAGE, XWD_VERSION);
156 {{/code}}
157
158 = Permissions =
159
160 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:
161
162 {{code language='sql'}}
163 -- Common operations
164 grant create session to xwiki -- Login to DB;
165 grant resource to xwiki -- Create tables, sequences, etc;
166 grant select any table to xwiki;
167 grant select any sequence to xwiki;
168 grant insert any table to xwiki;
169 grant update any table to xwiki;
170 grant delete any table to xwiki;
171
172 -- Subwiki creation
173 -- Note: creating a subwiki executes the migration step, see below
174 grant create user to xwiki -- Creation of a schema (ie user);
175
176 -- Subwiki deletion
177 grant drop user to xwiki -- Remove user;
178
179 -- Hibernate migrations (at startup and when creating new subwikis only)
180 -- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
181 grant create any table to xwiki;
182 grant drop any table to xwiki;
183 grant alter any table to xwiki;
184 grant create any index to xwiki;
185 grant alter any index to xwiki;
186 grant create any sequence to xwiki;
187 {{/code}}
188
189 {{version before="14.8RC1"}}
190 You also needed to give permissions to the new subwiki user to create objects (tables, indexes, etc).
191
192 {{code language='sql'}}
193 grant resource to xwiki with admin option;
194 {{/code}}
195 {{/version}}
196
197 == Using scripts ==
198
199 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.
200
201 * Script to run to set the rights to normal operations(((
202 {{code language='sql'}}
203 -- Remove rights for subwiki creation/deletion or upgrades
204 revoke create user from xwiki;
205 revoke drop user from xwiki;
206 revoke create any table from xwiki;
207 revoke drop any table from xwiki;
208 revoke alter any table from xwiki;
209 revoke create any index from xwiki;
210 revoke alter any index from xwiki;
211 revoke create any sequence from xwiki;
212
213 -- Common operations
214 grant create session to xwiki -- Login to DB;
215 grant resource to xwiki -- Create tables, sequences, etc;
216 grant select any table to xwiki;
217 grant select any sequence to xwiki;
218 grant insert any table to xwiki;
219 grant update any table to xwiki;
220 grant delete any table to xwiki;
221 {{/code}}
222 )))
223 * 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(((
224 {{code language='sql'}}
225 -- Subwiki creation
226 -- Note: creating a subwiki executes the migration step, see below
227 grant create user to xwiki -- Creation of a schema (ie user);
228
229 -- Hibernate migrations (at startup and when creating new subwikis only)
230 -- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
231 grant create any table to xwiki;
232 grant drop any table to xwiki;
233 grant alter any table to xwiki;
234 grant create any index to xwiki;
235 grant alter any index to xwiki;
236 grant create any sequence to xwiki;
237 {{/code}}
238 )))
239 * 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(((
240 {{code language='sql'}}
241 -- Subwiki deletion
242 grant drop user to xwiki -- Remove user;
243 {{/code}}
244 )))
245 * 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(((
246 {{code language='sql'}}
247 -- Hibernate migrations (at startup and when creating new subwikis only)
248 -- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
249 grant create any table to xwiki;
250 grant drop any table to xwiki;
251 grant alter any table to xwiki;
252 grant create any index to xwiki;
253 grant alter any index to xwiki;
254 grant create any sequence to xwiki;
255 {{/code}}
256 )))
257
258 = Oracle Wallet ==
259
260 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>>https://docs.oracle.com/middleware/1213/wls/JDBCA/oraclewallet.htm#JDBCA598]].
261
262 Follow these steps:
263 * On the machine having the Oracle DB installed, create a Wallet using {{code language="shell"}}mkstore -wrl <wallet_location> -create{{/code}}
264 * Then add the database credentials to the Wallet: {{code language="shell"}}mkstore -wrl <wallet_location> -createCredential <db_connect_string> <username> <password>{{/code}} (for example: ##mkstore -wrl <wallet_location> -createCredential "localhost:1521/xwiki" xwiki xwiki##. Of course you should use a better password for the xwiki user)
265 * 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##.
266 * Download the [[full Oracle JDBC zip>>https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html]], unzip and copy ##oraclepki.jar##, ##osdt_cert.jar## and ##osdt_core.jar## files to XWiki's ##WEB-INF/lib/## directory.
267 * Edit the XWiki ##hibernate.cfg.xml## file and:
268 ** Modify the connection URL for Oracle (notice the ##/## before the ##@## symbol): {{code language="xml"}}<property name="hibernate.connection.url">jdbc:oracle:thin:/@localhost:1521/xwiki</property>{{/code}}
269 ** Remove the following 2 properties or set the username and password to empty values:(((
270 {{code language="xml"}}
271 <property name="hibernate.connection.username">xwiki</property>
272 <property name="hibernate.connection.password">xwiki</property>
273 {{/code}}
274 )))
275 * Modify the way you start XWiki to pass the following system property: {{code language="non"}}-Doracle.net.wallet_location=<WALLETLOCALDIR>{{/code}}
276
277 = Troubleshooting =
278
279 == ORA-01400: cannot insert NULL into ("XWIKI"."XWIKILARGESTRINGS"."XWL_ID") ==
280
281 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.
282
283 == SetString can only process strings of less than 32766 chararacters ==
284
285 If you see an error that says something like this:
286
287 {{code}}
288 Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
289 Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
290 com.xpn.xwiki.XWikiException: Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
291 Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
292 ...
293 Wrapped Exception:
294
295 java.sql.SQLException: setString can only process strings of less than 32766 chararacters
296 ...
297 {{/code}}
298
299 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:
300
301 {{code}}
302 <property name="hibernate.connection.SetBigStringTryClob">true</property>
303 <property name="hibernate.jdbc.batch_size">0</property>
304 {{/code}}
305
306 == NullPointerException at HqlSqlWalker ==
307
308 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.
309
310 == Errors due to missing RAM ==
311
312 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.
313
314 {{code language="none"}}
315 SQL Error: 0, SQLState: null
316 Cannot get a connection, pool error Timeout waiting for idle object
317 ORA-12519, TNS:no appropriate service handler found
318 ORA-01435: user does not exist
319 {{/code}}
320
321 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