Wiki source code of Oracle Installation

Last modified by Vincent Massol on 2023/12/07

version	line-number	content
4.2	1	{{box cssClass="floatinginfobox" title="Contents"}}
	2	{{toc/}}
	3	{{/box}}
1.1	4
4.2	5	= Installation steps =
1.7	6
25.1	7	== Local install ==
4.1	8
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/]].
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/##)
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}}
23.1	12	* Create the user for the main wiki:(((
26.1	13	{{code language="sql"}}
8.1	14	create user xwiki
8.3	15	identified by xwiki;
4.1	16	{{/code}}
5.2	17	)))
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:(((
26.1	20	{{code language="sql"}}
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:(((
26.1	26	{{code language="sql"}}
8.1	27	create tablespace xwiki
25.1	28	datafile '/opt/<SOMETHING>/oracle/oradata/<SID>/<PDB>/xwiki01.dbf'
23.1	29	size 1m
	30	autoextend on
8.1	31	maxsize 1g
	32	;
4.1	33	{{/code}}
5.2	34	)))
23.1	35	* Modify the ##XWIKI## user to use it, and make sure it has quotas on it (by default it doesn't):(((
26.1	36	{{code language="sql"}}
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	)))
23.1	45	)))
	46	* If you use the default ##USERS## tablespace you may still want to make it large enough. For example:(((
26.1	47	{{code language="sql"}}
25.1	48	alter database datafile '/opt/<SOMETHING>/oradata/<SID>/<PDB>/users01.dbf' resize 100M;
23.1	49	{{/code}}
	50	)))
	51	)))
25.2	52	* Give sufficient privileges to the ##xwiki## user:(((
26.1	53	{{code language="sql"}}
32.3	54	grant create session to xwiki;
28.1	55	grant resource to xwiki;
	56	grant dba to xwiki;
8.1	57	{{/code}}
	58	)))
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}}(((
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}}
5.2	63	)))
14.1	64
40.1	65	* To clean Oracle Database (remove the user, contents and data files):(((
	66	{{code language="sql"}}
	67	drop tablespace xwiki including contents and datafiles;
	68	drop user xwiki cascade;
	69	{{/code}}
	70	)))
	71
	72	{{info}}
	73	Please note that in Oracle any subwiki created represents an user, so if there are any subwikis created, they also should be deleted when cleaning the database.
	74
	75	For example, by using:
	76
	77	{{code}}
	78	drop user subwikiname cascade;
	79	{{/code}}
	80
	81	where {{code}}subwikiname{{/code}} is the subwiki's identifier.
	82	{{/info}}
	83
25.1	84	== From a VM ==
17.1	85
	86	An easy to test Oracle is to use a VM. Here are some easy steps:
	87
	88	* [[Download the VM from the Oracle web site>>http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html]]
	89	* Install [[VirtualBox>>https://www.virtualbox.org/]] and import the VM in it
	90	* 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
	91	* Start the VM and log in as ##oracle##/##oracle##
	92	* Note that IP address printed in the shell that opens up, for example: 192.168.0.49
	93	* In the shell, starts the manager: ##emctl start dbconsole##
	94	* 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}}:(((
26.1	95	{{code language="sql"}}
17.1	96	drop user xwiki cascade;
	97	create user xwiki identified by xwiki;
	98	select * from dba_data_files;
	99	create tablespace xwiki datafile '/home/oracle/app/oracle/oradata/orcl/xwiki01.dbf' size 1m autoextend on maxsize 1g;
	100	alter user xwiki default tablespace xwiki temporary tablespace temp;
	101	alter user xwiki quota unlimited on xwiki;
32.3	102	grant create session to xwiki;
28.1	103	grant resource to xwiki;
	104	grant dba to xwiki;
17.1	105	{{/code}}
	106	)))
	107	* From your host machine, point your browser on http:~/~/192.168.0.49:1158/em and connect as ##system/oracle##
	108	* In your XWiki's ##hibernate.cfg.xml##, use:(((
26.1	109	{{code language="xml"}}
17.1	110	<property name="connection.url">jdbc:oracle:thin:@192.168.0.49:1521:orcl</property>
	111	{{/code}}
	112	)))
	113	* Don't forget to drop the Oracle JDBC driver in your ##WEB-INF/lib## directory!
	114	* Enjoy ;)
	115
25.1	116	== Using Docker ==
20.1	117
	118	Follow these steps:
26.1	119
	120	* 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}}
20.1	121	* Download the corresponding [[JDBC driver>>https://repo1.maven.org/maven2/com/oracle/ojdbc/ojdbc8/]] and put it in XWiki's ##WEB-INF/lib## directory
	122	* Edit XWiki's ##hibernate.cfg.xml## file, comment out the Oracle section and make sure you use the following settings:(((
26.1	123	{{code language="xml"}}
24.1	124	<property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521/xwiki</property>
20.1	125	<property name="hibernate.connection.username">xwiki</property>
	126	<property name="hibernate.connection.password">xwiki</property>
	127	{{/code}}
	128	)))
	129
21.1	130	And if you need to execute some SQL command you can do the following:
26.1	131
	132	* Connect inside the docker container with: {{code language="shell"}}docker -it exec <container id> bash -l{{/code}}
24.1	133	* Run ##sqplplus## with one of:
26.1	134	** {{code language="sql"}}sqlplus system/xwiki@//localhost:1521/XWIKI{{/code}} to execute commands in the XWiki PDB
	135	** {{code language="sql"}}sqlplus sys/xwiki@//localhost:1521/XWIKICDB as sysdba{{/code}} if you need to execute commands in the CDB
21.1	136
22.1	137	{{info}}
	138	You can check [[how the XWiki Oracle docker image is built>>https://github.com/xwiki/xwiki-docker-build/tree/master/build-oracle]].
	139	{{/info}}
	140
25.1	141	= Multi wiki support =
	142
26.1	143	{{version since="12.8RC1"}}
25.1	144	When creating a (sub)wiki, XWiki will create a new Oracle user using the equivalent of:
	145
	146	{{code language='sql'}}
	147	CREATE USER <wikiId> IDENTIFIED BY <wikiId> QUOTA UNLIMITED ON USERS;
	148	GRANT RESOURCE TO <wikiId>;
	149	{{/code}}
	150
	151	Notes:
	152	* The default ##USERS## tablespace is used. Thus make sure that this tablespace is large enough. You may need to execute:(((
	153	{{code language='sql'}}
	154	alter database datafile '/opt/<SOMETHING>/oradata/<SID>/<PDB>/users01.dbf' resize 100M;
	155	{{/code}}
	156	)))
	157	{{/version}}
	158
19.1	159	= Indexes =
	160
	161	See [[Database Administration>>Documentation.AdminGuide.Performances.Database Administration.WebHome]].
	162
	163	{{code}}
	164	CREATE INDEX XWLS_VALUE ON XWIKILARGESTRINGS (XWL_VALUE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
	165	create index xwd_parent on xwikidoc (xwd_parent) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
	166	create index xwd_class_xml on xwikidoc (xwd_class_xml) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
	167	create index xda_docid1 on xwikiattrecyclebin (xda_docid);
27.1	168	create index ase_page_date on activitystream_events (ase_page, ase_date);
19.1	169	create index ase_param1 on activitystream_events (ase_param1) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
	170	create index ase_param2 on activitystream_events (ase_param2) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
	171	create index ase_param3 on activitystream_events (ase_param3) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
	172	create index ase_param4 on activitystream_events (ase_param4) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
	173	create index ase_param5 on activitystream_events (ase_param5) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
27.1	174	create index solr_iterate_all_documents on xwikidoc (XWD_WEB, XWD_NAME, XWD_LANGUAGE, XWD_VERSION);
19.1	175	{{/code}}
	176
29.1	177	= Permissions =
	178
29.2	179	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:
29.1	180
40.1	181	{{code language="sql"}}
29.1	182	-- Common operations
32.3	183	grant create session to xwiki -- Login to DB;
30.2	184	grant resource to xwiki -- Create tables, sequences, etc;
36.1	185	grant select any table to xwiki;
	186	grant select any sequence to xwiki;
	187	grant insert any table to xwiki;
	188	grant update any table to xwiki;
	189	grant delete any table to xwiki;
29.1	190
	191	-- Subwiki creation
	192	-- Note: creating a subwiki executes the migration step, see below
30.2	193	grant create user to xwiki -- Creation of a schema (ie user);
29.1	194
	195	-- Subwiki deletion
30.2	196	grant drop user to xwiki -- Remove user;
29.1	197
	198	-- Hibernate migrations (at startup and when creating new subwikis only)
	199	-- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
	200	grant create any table to xwiki;
	201	grant drop any table to xwiki;
	202	grant alter any table to xwiki;
	203	grant create any index to xwiki;
	204	grant alter any index to xwiki;
	205	grant create any sequence to xwiki;
	206	{{/code}}
	207
30.1	208	{{version before="14.8RC1"}}
	209	You also needed to give permissions to the new subwiki user to create objects (tables, indexes, etc).
	210
	211	{{code language='sql'}}
	212	grant resource to xwiki with admin option;
	213	{{/code}}
	214	{{/version}}
	215
33.1	216	== Using scripts ==
	217
	218	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.
	219
	220	* Script to run to set the rights to normal operations(((
40.1	221	{{code language="sql"}}
33.1	222	-- Remove rights for subwiki creation/deletion or upgrades
	223	revoke create user from xwiki;
34.1	224	revoke drop user from xwiki;
37.1	225	revoke create any table from xwiki;
33.1	226	revoke drop any table from xwiki;
	227	revoke alter any table from xwiki;
	228	revoke create any index from xwiki;
	229	revoke alter any index from xwiki;
	230	revoke create any sequence from xwiki;
	231
	232	-- Common operations
	233	grant create session to xwiki -- Login to DB;
	234	grant resource to xwiki -- Create tables, sequences, etc;
36.1	235	grant select any table to xwiki;
	236	grant select any sequence to xwiki;
	237	grant insert any table to xwiki;
	238	grant update any table to xwiki;
	239	grant delete any table to xwiki;
33.1	240	{{/code}}
	241	)))
	242	* 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(((
40.1	243	{{code language="sql"}}
33.1	244	-- Subwiki creation
	245	-- Note: creating a subwiki executes the migration step, see below
	246	grant create user to xwiki -- Creation of a schema (ie user);
	247
	248	-- Hibernate migrations (at startup and when creating new subwikis only)
	249	-- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
	250	grant create any table to xwiki;
	251	grant drop any table to xwiki;
	252	grant alter any table to xwiki;
	253	grant create any index to xwiki;
	254	grant alter any index to xwiki;
	255	grant create any sequence to xwiki;
	256	{{/code}}
33.2	257	)))
34.1	258	* 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(((
40.1	259	{{code language="sql"}}
34.1	260	-- Subwiki deletion
	261	grant drop user to xwiki -- Remove user;
	262	{{/code}}
	263	)))
33.1	264	* 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(((
40.1	265	{{code language="sql"}}
33.1	266	-- Hibernate migrations (at startup and when creating new subwikis only)
	267	-- Executed by the "xwiki" user which needs to be able to create tables, sequences, indexes in other user's namespace
	268	grant create any table to xwiki;
	269	grant drop any table to xwiki;
	270	grant alter any table to xwiki;
	271	grant create any index to xwiki;
	272	grant alter any index to xwiki;
	273	grant create any sequence to xwiki;
	274	{{/code}}
	275	)))
	276
40.1	277	= Oracle Wallet =
32.1	278
32.2	279	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]].
32.1	280
	281	Follow these steps:
40.1	282
32.1	283	* On the machine having the Oracle DB installed, create a Wallet using {{code language="shell"}}mkstore -wrl <wallet_location> -create{{/code}}
	284	* 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)
	285	* 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##.
	286	* 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.
38.1	287	* Edit the XWiki ##hibernate.cfg.xml## file and:
38.2	288	** 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}}
38.1	289	** Remove the following 2 properties or set the username and password to empty values:(((
	290	{{code language="xml"}}
	291	<property name="hibernate.connection.username">xwiki</property>
	292	<property name="hibernate.connection.password">xwiki</property>
	293	{{/code}}
	294	)))
32.1	295	* Modify the way you start XWiki to pass the following system property: {{code language="non"}}-Doracle.net.wallet_location=<WALLETLOCALDIR>{{/code}}
	296
39.1	297	= Delegate user creation =
	298
	299	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>>extensions:Extension.Wiki Application#HDelegatedatabasecreation]] in order to control the user password used or to use a specific tablespace (just to give 2 examples, there are more).
	300
41.1	301	== Changing DB passwords for wikis ==
	302
	303	If you have not been [[delegating creation of the DB users for wikis>>extensions:Extension.Wiki Application#HDelegatedatabasecreation]], 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).
	304
7.1	305	= Troubleshooting =
	306
	307	== ORA-01400: cannot insert NULL into ("XWIKI"."XWIKILARGESTRINGS"."XWL_ID") ==
	308
8.3	309	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.
6.1	310
7.1	311	== SetString can only process strings of less than 32766 chararacters ==
4.1	312
8.3	313	If you see an error that says something like this:
1.7	314
8.3	315	{{code}}
1.7	316	Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
	317	Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
	318	com.xpn.xwiki.XWikiException: Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
	319	Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
	320	...
	321	Wrapped Exception:
	322
	323	java.sql.SQLException: setString can only process strings of less than 32766 chararacters
	324	...
4.1	325	{{/code}}
1.7	326
8.3	327	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:
1.7	328
4.1	329	{{code}}
1.7	330	<property name="hibernate.connection.SetBigStringTryClob">true</property>
	331	<property name="hibernate.jdbc.batch_size">0</property>
4.1	332	{{/code}}
10.1	333
13.1	334	== NullPointerException at HqlSqlWalker ==
	335
	336	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.
	337
17.1	338	== Errors due to missing RAM ==
10.1	339
17.2	340	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.
14.1	341
26.1	342	{{code language="none"}}
17.1	343	SQL Error: 0, SQLState: null
	344	Cannot get a connection, pool error Timeout waiting for idle object
	345	ORA-12519, TNS:no appropriate service handler found
	346	ORA-01435: user does not exist
12.1	347	{{/code}}
17.1	348
	349	Note that it's possible to have these errors for other reasons too but you should at least check that the RAM is enough.

Wiki source code of Oracle Installation

Quick Links

Navigation

About

About

Support

Platform

User Guide

Admin Guide

Developer Guide

Projects

XWiki

Extensions

Other

Contribute

Status

Practices

Under the Hood

Get Involved

Get Connected