Configuring Oracle

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';

VALUE
--------------------------------------------------------------------------------
8192

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

You need to grant DBMS_CRYPTO execution (replace nuxeo with the database user):

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO nuxeo;

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

GRANT EXECUTE ON DBMS_CRYPTO TO nuxeo;

This is due to optimizations now enabled on Oracle that need hashing functions (MD5), available in this package.

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 on V$SESSION and GV$SESSION

If you use Nuxeo Clustering ( repository.clustering.enabled=true), then you must make sure that your database user has access to the system views V$SESSION and GV$SESSION (replace nuxeo with the database user):

GRANT SELECT ON SYS.V_$SESSION TO nuxeo;
GRANT SELECT ON SYS.GV_$SESSION TO nuxeo;

You can check that this works as intended by doing, as the database user:

SELECT SID FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID');
SELECT SID FROM GV$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID');

(V$SESSION is a public synonym for SYS.V_$SESSION.)

Note: the view GV$SESSION is used in recent Nuxeo Platform version instead of V$SESSION to allow working with Oracle RAC.

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

java.sql.SQLException: ORA-00942: table or view does not exist

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 resume, 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 ("HUDSON"."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.

Import/Export

Starting 11gR2, 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 hasn't got any space allocations yet. A configuration change needs to be done to allocate space even more 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 1.6. It is compliant with Oracle 10g.

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.

 

---