CoreMedia Content Server Manual, Version 7.5.45-10
3.3. Configuring the Database | 3.3.2. Oracle Database

For the Oracle database you need the Oracle JDBC Thin Driver oracle.jdbc.driver.OracleDriver. Copy the driver file to the WEB-INF/lib directory. There also exists the Oracle OCI driver which could be used. However, this driver can cause some problems:

Furthermore, the driver needs an additional Oracle client installation on the Content Server host. Therefore, the OCI driver is not recommended.

Important: Always use the latest version of the JDBC driver for your database version and the JDK being used. New driver versions can be obtained from the Oracle website http://technet.oracle.com.

In order to optimize queries, Oracle gathers statistics about the database content. By default, this process runs once per day, which is an appropriate setting.

In sql.properties, the following settings must be made for an Oracle database:

sql.store.driver=oracle.jdbc.driver.OracleDriver
sql.store.url=jdbc:oracle:thin:@<DB-HOST>:<DB-PORT>:<DB-INSTANCE>
sql.store.user=<DB-USER>
sql.store.password=<DB-USER-PASSWORD>

The name of the database user may be given in lowercase in the sql.properties, but it must be stored as all uppercase in the database.

The Oracle database instance must be configured with a sufficient number of DB Cursors. The number is set in the Oracle initialization script init<Instancename>.ora in the entry open_cursors (for further details see the documentation of the database manufacturer).

The number of cursors for a JDBC connection in the CoreMedia Server is calculated with the following formula:

<Number of Cursors> = 110 + (<Number of content types> * 7)

For a server with 15 content types therefore, 215 cursors are needed per connection and an open_cursors value of 215 has to be configured, unless other applications require a higher value.

Alternatively the property sql.store.preparedStatementCacheSize in the sql.properties can be used to control the number of open cursors from the Content Server. This property limits the number of cached prepared SQL statements. If the property is not set, the cache has an unlimited size and the above formula applies. Otherwise, the value of the property should be at least 50. Increasing this parameter generally improves the database performance. Set the number of Oracle cursor to the size of the statement cache plus 10 for other statements that are not prepared, but that still use a cursor when executed.

create user <DB-USER> identified by <DB-USER-PASSWORD>;
grant connect, create view, resource to <DB-USER>;

For Oracle 12c you need to add privileges to the user as described in http://docs.oracle.com/database/121/DBSEG/release_changes.htm#BABEBGDI.

alter user <DB-USER> quota unlimited on users;

drop user <DB-USER> cascade;