Configuring Oracle

Oracle Text (fulltext)

Oracle Text needs to be enabled in your database for fulltext indexing, please consult your Oracle documentation.

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

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 Nuxeo when configured in its default-repository-config.xml file.

Grant on DBMS_CRYPTO

Since Nuxeo 5.3.2, you need to grant DBMS_CRYPTO execution (replace nuxeo with the database user):

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

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:

Grant on V$SESSION and GV$SESSION

If you use Nuxeo Clustering (<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):

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

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

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

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

Grants for CREATE TABLE and ALTER TABLE

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

Other grants

The following more standard grants must also be executed :

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

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 Nuxeo DM,
• queries returning no document.

To check the character set on your server, execute:

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. Download orai18n.jar at http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html.
Then 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.)

Technical explanation Internally, for security checks, Nuxeo 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 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

Nuxeo 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 recommand 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.