Home > Installation and Administration > Setup > Database > Oracle
Installation and Administration

Oracle

Updated: July 17, 2023

The Nuxeo Platform supports the following versions of Oracle:

11g, 12c

Oracle Text (Full-Text)

Oracle Text needs to be enabled in your database for full-text indexing, 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 <span style="color: rgb(0,0,0);">DB_BLOCK_SIZE</span>, 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 http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/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/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.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 11.2.0.* : ojdbc6.jar for JDK 8. It is compliant with Oracle 11g.

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/nuxeo@NUXEO @dump-nuxeo-oracle-conf.sql

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

 

Related Documentation

Connecting Nuxeo to the Database How to Estimate Disk, Database and Elasticsearch Usage