The Nuxeo Platform supports the following version of Oracle:
Oracle 12c and 18c
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
On Oracle 184.108.40.206 and 220.127.116.11, 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');
The Nuxeo Platform requires the configuration of
DB_BLOCK_SIZE, the size of Oracle database blocks. Whereas typical values are
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
In order for optimizations to be working in Nuxeo when using Oracle, hashing functions are needed and you need to grant
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO nuxeo;
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
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;
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;
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
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
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:
WE8MSWIN1252, then you will need an additional
orai18n.jar JAR in your Java class path.
- 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
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
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.
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;
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 18.104.22.168 :
ojdbc7.jar for JDK 8.
The driver must be in the
$NUXEO_HOME/lib directory. If you are using the
oracle template (
nuxeo.conf), just put the driver in the
Oracle is used by a lot of our customers. Nevertheless it's not perfect.
- The well-known Oracle-specific equivalence of
NULLand an empty string causes headaches and performance degradations in a number of places.
- Oracle has problems on some
LEFT OUTER JOINSusing 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
SELECTcannot do DML (
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.
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.