Configure Oracle Pentaho Repository database

Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for an Oracle database.

By default, the examples in this section are for an Oracle database that runs on port 1521. The default password is also in these examples.

If you use a different port or password, update the examples to match your environment.

Step 1: Set up Quartz on Oracle

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located by modifying the quartz.properties file.

1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties file in any text editor.

2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown:

   org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate

3. Save the file and close the text editor.

Step 2: Set Hibernate settings for Oracle

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file. The Hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.

Note: The Hibernate database is also where the Pentaho Server stores the audit logs that act as source data for the Pentaho Operations Mart.

The files in this section are located in the pentaho/server/pentaho-server/pentaho-solutions/system/hibernate directory.

Perform the following steps to specify where Pentaho can find the Hibernate configuration file.

1. Open the hibernate-settings.xml file in a text editor. Find the tags and change postgresql.hibernate.cfg.xml to oracle10g.hibernate.cfg.xml as shown.

   From:

   <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

   To:

   <config-file>system/hibernate/oracle10g.hibernate.cfg.xml</config-file>

2. Save and close the file.

Step 3: Replace default version of audit log file with Oracle version

Since you are using Oracle to host the Pentaho Repository, you need to replace the audit_sql.xml file with one that is configured for Oracle.

1. Locate the pentaho-solutions/system/dialects/oracle10g/audit_sql.xml file.

2. Copy it into the pentaho-solutions/system directory.

Step 4: Modify Jackrabbit repository information for Oracle

Edit the following code to change the default Jackrabbit repository to Oracle.

1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.

2. As shown in the table below, locate and verify or change the code so that the Oracle lines are not commented out, but the MySQL, PostgreSQL, and MS SQL Server lines are commented out.

Item	Code Section
Repository	<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schemaObjectPrefix" value="fs_repos_"/> <param name="tablespace" value="pentaho_tablespace"/> </FileSystem>
DataStore	<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="databaseType" value="oracle"/> <param name="minRecordLength" value="1024"/> <param name="maxConnections" value="3"/> <param name="copyWhenReading" value="true"/> <param name="tablePrefix" value=""/> <param name="schemaObjectPrefix" value="ds_repos_"/> </DataStore>
Workspaces	<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schemaObjectPrefix" value="fs_ws_"/> <param name="tablespace" value="pentaho_tablespace"/> </FileSystem>
PersistenceManager (1st part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="oracle"/> <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/> <param name="tablespace" value="pentaho_tablespace"/> </PersistenceManager>
Versioning	<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schemaObjectPrefix" value="fs_ver_"/> <param name="tablespace" value="pentaho_tablespace"/> </FileSystem>
PersistenceManager (2nd part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="oracle"/> <param name="schemaObjectPrefix" value="pm_ver_"/> <param name="tablespace" value="pentaho_tablespace"/> </PersistenceManager>
DatabaseJournal	<Journal class="org.apache.jackrabbit.core.journal.OracleDatabaseJournal"> <param name="revision" value="${rep.home}/revision.log" /> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="oracle"/> <param name="schemaObjectPrefix" value="J_C_"/> <param name="janitorEnabled" value="true"/> <param name="janitorSleep" value="86400"/> <param name="janitorFirstRunHourOfDay" value="3"/> </Journal>