Home > Server > Administration > Database Configuration > Oracle
Server

Oracle

Updated: March 18, 2024

The Nuxeo Platform supports the following version of Oracle:

Oracle 18c

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.

Error DRG-50857

On Oracle 11.2.0.3 and 11.2.0.4, the installation of Oracle Text may fail because some information on the CTXSYS user is not correctly configured. This exception will be thrown in Nuxeo logs:

org.nuxeo.ecm.core.api.NuxeoException: java.sql.SQLException: Error executing: CREATE INDEX "FULLTEXT_FULLTEXT_IDX" ON "FULLTEXT"("FULLTEXT") INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(' SYNC (ON COMMIT) TRANSACTIONAL') : ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drstldef
DRG-50858: OCI error: OCI_INVALID_HANDLE
DRG-50857: oracle error in get functional cache size
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drixmd.GetSysParam
ORA-01403: no data found
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.DRVXMD", line 639
ORA-06512: at line 1

The following command has to be run with the CTXSYS user to fix the problem:

INSERT INTO CTX_PARAMETERS (PAR_VALUE,PAR_NAME) VALUES ('20971520','FUNCTIONAL_CACHE_SIZE');

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

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

Related Documentation