The Nuxeo Platform supports the following version of Oracle:

Oracle 12c

Oracle Text (Full-Text)

If you've configured your Nuxeo Platform instance to index full-text using the SQL database (by disabling the default configuration which uses Elasticsearch), Oracle Text needs to be enabled in your database, please consult your Oracle documentation.

If you fail to install Oracle Text, you will get on startup the following error:

java.sql.SQLException: ORA-29833: indextype does not exist

In addition, if you want to configure specific lexers or word lists then check http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cdatadic.htm for configuration parameters and syntax. Lexers and word lists are used by the Nuxeo Platform when configured in its default-repository-config.xml file.

DB_BLOCK_SIZE Configuration

The Nuxeo Platform requires the configuration of DB_BLOCK_SIZE, the size of Oracle database blocks. Whereas typical values are 4096 and 8192 bytes, you need to set it to 8192 to make the Nuxeo Platform properly work. Use this query to get the current value set in your Oracle database:

SELECT value FROM v$parameter WHERE name = 'db_block_size';

Possible error if the correct value is not used:

java.sql.SQLException: Error executing: CREATE INDEX DC_CONTRIBUTORS_ITEM_IDX ON DC_CONTRIBUTORS(ITEM) : ORA-01450: maximum key length (3118) exceeded

Grant on DBMS_CRYPTO

In order for optimizations to be working in Nuxeo when using Oracle, hashing functions are needed and you need to grant DBMS_CRYPTO execution:

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO nuxeo;

(Replace nuxeo with the database user.)

Note that for Oracle running on Amazon RDS, DBMS_CRYPTO is directly accessible and you may simply do:

GRANT EXECUTE ON DBMS_CRYPTO TO nuxeo;

Possible errors if you don't do this grant are:

java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00905: object NUXEO.NX_REBUILD_READ_ACLS is invalid

Create Hibernate Sequence

If the first startup fails with the following error in your logs,

ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-02289: sequence does not exist

you need to run this statement as your Oracle user

CREATE SEQUENCE HIBERNATE_SEQUENCE;

Grant for CREATE TABLE

As the Nuxeo Platform creates tables in the database at first startup, you need to grant CREATE TABLE to your database user.

GRANT CREATE TABLE TO nuxeo;

Other Grants

The following more standard grants must also be executed :

GRANT CONNECT TO nuxeo;
GRANT RESOURCE TO nuxeo;

The following is sometimes needed, if you have several schemas:

GRANT SELECT ANY TABLE TO nuxeo;

Restricted Environment with No GRANT

Some DBA will provide a restricted schema where there is no GRANT on your database.

In that case, you'll have to run this command, whereas usually DBMS_LOB is granted to public.

GRANT EXECUTE ON DBMS_LOB TO nuxeo;

To summarize, here are the list of all GRANTs needed:

- CONNECT
- RESOURCE
- SELECT ANY TABLE
- CREATE TABLE
- CREATE PROCEDURE
- CREATE SEQUENCE
- CREATE TRIGGER
- CREATE TYPE
- CREATE VIEW
- EXECUTE ON DBMS_LOB
- EXECUTE ON DBMS_CRYPTO

Character Set

Your database must be configured with NLS_CHARACTERSET set to AL32UTF8. If your database character set is not AL32UTF8, you may observe incorrect behavior including:

  • Error while trying to insert null values into acl_user table ( ORA-01400: cannot insert NULL into ("NUXEO"."ACLR_USER"."USER_ID") ).
  • Incorrect storage of accented or special characters.
  • No tree structure visible on the left of the Nuxeo Platform.
  • Queries returning no document.

To check the character set on your server, execute:

SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET';

If you need to change the character set of you database, please check https://docs.oracle.com/database/121/NLSPG/ch11charsetmig.htm.

If for some reason you must use an unsupported character set that is not in the list: AL32UTF8, UTF8, US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, then you will need an additional orai18n.jar JAR in your Java class path.

  1. Download orai18n.jar at http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.
  2. Add it in the class path for your Nuxeo server. For instance, in JBoss, you just put the jar in $JBOSS/server/default/lib. (The file orai18n.jar replaces the nls_charset*.* files in the Oracle 9i and older releases.) However this may or may not work depending on your Oracle version, installation specifics and other factors, and the only supported solution by the Nuxeo Platform is to use the AL32UTF8 charset.

Technical explanation

Internally, for security checks, the Nuxeo Platform executes SQL statements that need to be passed ARRAY objects (for the list of principals and permissions), but if the correct character set is not installed then the Oracle JDBC driver behaves incorrectly and Oracle actually receives empty strings. This in turn results in empty results for the queries as none of the documents will match due to incorrect security checks. The orai18n.jar fixes this.

Import/Export

Starting with 11g R2, Oracle does not allocate space for a table until the first row gets inserted into the table. What happens is if you take an export of the schema/database, the dump would not include any of the tables that havn't got any space allocations yet. A configuration change needs to be done to allocate space even for tables with no records.

ALTER SYSTEM SET deferred_segment_creation=false;

JDBC Driver

The Nuxeo Platform needs the Oracle JDBC driver to establish a connection to the database.

The driver can be downloaded from the Oracle JDBC driver downloads site. We recommend the latest version for 12.1.0.2 : ojdbc7.jar for JDK 8.

The driver must be in the $NUXEO_HOME/lib directory. If you are using the oracle template (nuxeo.templates=oracle in nuxeo.conf), just put the driver in the $NUXEO_HOME/templates/oracle/lib directory.

Limitations

Oracle is used by a lot of our customers. Nevertheless it's not perfect.

Oracle limitations:

  • The well-known Oracle-specific equivalence of NULL and an empty string causes headaches and performance degradations in a number of places.
  • Oracle has problems on some LEFT OUTER JOINS using the standard ANSI syntax when combined with full-text queries, and has to be reverted to its non-standard implicit join syntax using (+) which cannot be used in all cases (NXP-5410).
  • PL/SQL pipelined functions or functions called from a SELECT cannot do DML (INSERT, UPDATE, DELETE) (ORA-14551) without starting an autonomous transaction. For consistency reasons starting an autonomous transaction is out of the question. This makes some code (used in clustering) inherently slower.
  • Table name limitation to 30 characters makes for very unreadable table names when using user-defined schemas.

Reporting Problems

If you have a database configuration problem and you want to fill a JIRA ticket, there are some information to report:

  • The Oracle server state: is it dedicated or shared, which OS, how many CPU, RAM, is it a virtual machine...

  • How much memory is available on the database server (free -m output).

  • Amount of Nuxeo documents and Oracle configuration. Using the "sqlplus" command line tool connect to your Nuxeo database and execute the following commands:

# Get the Nuxeo SQL script to dump your configuration
wget --no-check-certificate https://gist.github.com/bdelbosc/7766893/raw/dump-nuxeo-oracle-conf.sql
# Run the script against your database
sqlplus nuxeo/[email protected] @dump-nuxeo-oracle-conf.sql

and attach the output file located in /tmp/oraconf.txt into the JIRA ticket.


9 days ago manonlumeau NXDOC-1346-FT review screenshot
a year ago Manon Lumeau 55
a year ago Solen Guitter 54
2 years ago Florent Guillaume 53
2 years ago Florent Guillaume 52 | removed v$session grant now unneeded
2 years ago Florent Guillaume 51
2 years ago Alain Escaffre 50
3 years ago Solen Guitter 49
3 years ago Solen Guitter 48
3 years ago Solen Guitter 47 | typos and terminology
3 years ago Thierry Martins 46 | Add DB_BLOCK_SIZE configuration
3 years ago Florent Guillaume 45
3 years ago Solen Guitter 44 | Updated supported versions for 6.0
3 years ago Solen Guitter 43 | Remove 5.3 reference
3 years ago Solen Guitter 42 | Moved limitations from independent page into Oracle page
4 years ago Solen Guitter 41
4 years ago Thierry Martins 40 | DB with no GRANT
4 years ago Florent Guillaume 39
4 years ago Benoit Delbosc 37
4 years ago Benoit Delbosc 38
4 years ago Solen Guitter 36 | Removed related topics from TOC
4 years ago Benoit Delbosc 34
4 years ago Benoit Delbosc 35 | Migration of unmigrated content due to installation of a new plugin
5 years ago Benoit Delbosc 32
5 years ago Benoit Delbosc 33 | Migration of unmigrated content due to installation of a new plugin
5 years ago Thierry Martins 31 | add hibernate sequence paragraph
5 years ago Benoit Delbosc 30
5 years ago Benoit Delbosc 29
5 years ago Florent Guillaume 25 | no GRANT ALTER TABLE exists
5 years ago Florent Guillaume 26 | Migrated to Confluence 4.0
5 years ago Florent Guillaume 27 | Migration of unmigrated content due to installation of a new plugin
5 years ago Florent Guillaume 28 | Migration of unmigrated content due to installation of a new plugin
5 years ago Benoit Delbosc 24
5 years ago Benoit Delbosc 23 | Adding a reporting problems section
5 years ago Solen Guitter 22
5 years ago Solen Guitter 21 | Added related pages
6 years ago Solen Guitter 20 | Added TOC
6 years ago Stéphane Lacoin 19
6 years ago Florent Guillaume 18 | Amazon RDS
6 years ago Florent Guillaume 17
6 years ago Florent Guillaume 16
6 years ago Antoine Taillefer 15
6 years ago Stéphane Lacoin 14
6 years ago Stéphane Lacoin 13
7 years ago Wojciech Sulejman 12
7 years ago Solen Guitter 11
7 years ago Florent Guillaume 10
7 years ago Florent Guillaume 9
7 years ago Florent Guillaume 8
7 years ago Florent Guillaume 7
7 years ago Florent Guillaume 6
7 years ago Julien Carsique 5 | moved driver information to [NXDOC:Configuring Nuxeo EP]
7 years ago Julien Carsique 4 | Add jdbc driver information
7 years ago Florent Guillaume 3
7 years ago Florent Guillaume 2
7 years ago Florent Guillaume 1
History: Created by Florent Guillaume