Wiki source code of Oracle Installation

Version 24.1 by Vincent Massol on 2020/09/23

Show last authors
1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 = Installation steps =
6
7 Steps:
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}}
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}}
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}}
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}}
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}}
48 alter database datafile '/opt/oracle/oradata/<SID>/<PDB>/users01.dbf' resize 100M;
49 {{/code}}
50 )))
51 )))
52 * Give sufficient privileges to the ##XWIKI## user:(((
53 {{code}}
54 grant connect 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 = Installing Oracle 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}}
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 create table to xwiki;
85 grant create sequence 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}}
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 * 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}}
101 * Download the corresponding [[JDBC driver>>https://repo1.maven.org/maven2/com/oracle/ojdbc/ojdbc8/]] and put it in XWiki's ##WEB-INF/lib## directory
102 * Edit XWiki's ##hibernate.cfg.xml## file, comment out the Oracle section and make sure you use the following settings:(((
103 {{code language='none'}}
104 <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521/xwiki</property>
105 <property name="hibernate.connection.username">xwiki</property>
106 <property name="hibernate.connection.password">xwiki</property>
107 {{/code}}
108 )))
109
110 And if you need to execute some SQL command you can do the following:
111 * Connect inside the docker container with: {{code language='none'}}docker -it exec <container id> bash -l{{/code}}
112 * Run ##sqplplus## with one of:
113 ** {{code language='none'}}sqlplus system/xwiki@//localhost:1521/XWIKI{{/code}} to execute commands in the XWiki PDB
114 ** {{code language='none'}}sqlplus sys/xwiki@//localhost:1521/XWIKICDB as sysdba{{/code}} if you need to execute commands in the CDB
115
116 {{info}}
117 You can check [[how the XWiki Oracle docker image is built>>https://github.com/xwiki/xwiki-docker-build/tree/master/build-oracle]].
118 {{/info}}
119
120 = Indexes =
121
122 See [[Database Administration>>Documentation.AdminGuide.Performances.Database Administration.WebHome]].
123
124 {{code}}
125 CREATE INDEX XWLS_VALUE ON XWIKILARGESTRINGS (XWL_VALUE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
126 create index xwd_parent on xwikidoc (xwd_parent) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
127 create index xwd_class_xml on xwikidoc (xwd_class_xml) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
128 create index ase_page_date on activitystream_events (ase_page, ase_date);
129 create index xda_docid1 on xwikiattrecyclebin (xda_docid);
130 create index ase_param1 on activitystream_events (ase_param1) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
131 create index ase_param2 on activitystream_events (ase_param2) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
132 create index ase_param3 on activitystream_events (ase_param3) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
133 create index ase_param4 on activitystream_events (ase_param4) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
134 create index ase_param5 on activitystream_events (ase_param5) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
135 {{/code}}
136
137 = Troubleshooting =
138
139 == ORA-01400: cannot insert NULL into ("XWIKI"."XWIKILARGESTRINGS"."XWL_ID") ==
140
141 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.
142
143 == SetString can only process strings of less than 32766 chararacters ==
144
145 If you see an error that says something like this:
146
147 {{code}}
148 Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
149 Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
150 com.xpn.xwiki.XWikiException: Error number 3201 in 3: Exception while saving document XWiki.XWikiPreferences
151 Wrapped Exception: could not update: [com.xpn.xwiki.doc.XWikiDocumentArchive#104408758]
152 ...
153 Wrapped Exception:
154
155 java.sql.SQLException: setString can only process strings of less than 32766 chararacters
156 ...
157 {{/code}}
158
159 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:
160
161 {{code}}
162 <property name="hibernate.connection.SetBigStringTryClob">true</property>
163 <property name="hibernate.jdbc.batch_size">0</property>
164 {{/code}}
165
166 == NullPointerException at HqlSqlWalker ==
167
168 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.
169
170 == Errors due to missing RAM ==
171
172 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.
173
174 {{code language='none'}}
175 SQL Error: 0, SQLState: null
176 Cannot get a connection, pool error Timeout waiting for idle object
177 ORA-12519, TNS:no appropriate service handler found
178 ORA-01435: user does not exist
179 {{/code}}
180
181 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