BPM Admin Guide

Using Oracle Server as Pentaho Server Database

This section will describe how to change the backend database of Pentaho Server from the default PostgreSQL installed by the evaluation installation to Oracle database.

This is not required when using Oracle database as a data source for the ETL.
Note: More information is available here.

Prepare the environment

  1. Extract pentaho-server-ee-9.3.0.0-428-dist.zip.
  2. Run installer.bat as Administrator.
  3. Accept the license term agreement, then click Next .
  4. Change the installation path to <Installation_directory>/Pentaho/server.
  5. A warning message that the directory already exists appears. Click Yes. Any existing files in the directory will be retained.

Configuring Oracle Pentaho Repository Database

Note: More information is available here.
Initialize Values
  1. Navigate to <INSTALL_DIR>\server\pentaho-server\data\oracle10g to find the following SQL scripts:
    • create_jcr_ora.sql
    • create_repository_oral.sql
    • create_quartz_ora.sql
  2. Edit the above files and uncomment the following lines:

    -- conn admin/password@pentaho

  3. Update the files with the proper database credentials.

    Example when connecting to a remote host:

    conn admin/password@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXX)(PORT = 1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = XXXXXXX)))

  4. Update the default passwords in the SQL scripts.

    create user jcr_user identified by "password" ...

  5. Run SQL Scripts using SQL*Plus (or any Oracle SQL development tool) in the following order:
    • start <INSTALL_DIR>/server/pentaho-server/data/oracle10g/create_jcr_ora.sql
    • start <INSTALL_DIR>/server/pentaho-server/data/oracle10g/create_repository_ora.sql
    • start <INSTALL_DIR>/server/pentaho-server/data/oracle10g/create_quartz_ora.sql
    • start <INSTALL_DIR>/server/pentaho-server/data/oracle10g/pentaho_mart_ora.sql (Optional)
  6. Verify that databases and user roles have been created by running the following in SQL*Plus (or any Oracle SQL development tool):

    SELECT USERNAME FROM DBA_USERS;

Set Up Quartz

  1. Open the <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file in any text editor.
  2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown here.

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

  3. Locate the # Configure Datasources section and set the org.quartz.dataSource.myDS.jndiURL equal to Quartz, like this.

    org.quartz.dataSource.myDS.jndiURL = Quartz

  4. Save the file and close the text editor.

Set Hibernate Settings

  1. Open <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/hibernate/hibernate-settings.xml file in any text editor and then change the <config-file>

    From:

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

    To:

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

  2. Open <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/hibernate/oracle10g.hibernate.cfg.xml and update the following properties:

    <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
    <property name="connection.url">jdbc:oracle:thin:@ <host>:<port>:<SID> </property>
    <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
    <property name="connection.username">hibuser</property>
    <property name="connection.password"> <password> </property>
    <property name="show_sql">false</property>
    <property name="hibernate.jdbc.use_streams_for_binary">true</property>
    Note: Connection.username and connection.password is from create_repository_ora.sql.

Set Audit Log file with SQL Server version

  1. Copy the file <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/oracle10g/audit_sql.xml.
  2. Paste and replace the existing audit_sql.xml in <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/.

Set Jackrabbit Repository Information

  1. Open <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml file in any text editor.
  2. Comment out (<!—code -->) the code section for PostgreSQL and uncomment the Oracle counterpart of the following and then update the parameters:
    Code Section
    Repository

    <FileSystem class = "org.apache.jackrabbit.core.fs.db.OracleFileSystem">
    <param name = "url" value="jdbc:oracle:thin:@ <host>:<port>/<SID> "/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value=" password "/>
    <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 = "url" value=" jdbc:oracle:thin:@ <host>:<port>/<SID "/>
    <param name = "driver" value="oracle.jdbc.OracleDriver"/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value=" password "/>
    <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 = "url" value="jdbc:oracle:thin:@ <host>:<port>/<SID "/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value=" password "/>
    <param name = "schemaObjectPrefix" value="fs_ws_"/>
    <param name = "tablespace" value="pentaho_tablespace"/>
    </FileSystem>      
    Persistence Manager (Part 1)

    <PersistenceManager class = "org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
    <param name = "url" value="jdbc:oracle:thin:@ <host>:<port>/<SID "/>
    <param name = "driver" value="oracle.jdbc.OracleDriver"/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value=" password "/>
    <param name = "schema" value="oracle"/>
    <param name = "schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
    <param name = "tablespace" value="pentaho_tablespace"/>
    </PersistenceManager>

    Code Section

    <FileSystem class = "org.apache.jackrabbit.core.fs.db.OracleFileSystem">
    <param name = "url" value="jdbc:oracle:thin:@ <host>:<port>/<SID "/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value=" password "/>
    <param name = "schemaObjectPrefix" value="fs_ver_"/>
    <param name = "tablespace" value="pentaho_tablespace"/>
    </FileSystem>      
    Persistence Manager (Part 2)

    <PersistenceManager class = "org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
    <param name = "url" value="jdbc:oracle:thin:@ <host>:<port>/<SID "/>
    <param name = "driver" value="oracle.jdbc.OracleDriver"/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value=" password "/>
    <param name = "schema" value="oracle"/>
    <param name = "schemaObjectPrefix" value="pm_ver_"/>
    <param name = "tablespace" value="pentaho_tablespace"/>
    </PersistenceManager>      
    Note: The password for jcr_user is the one defined in create_jcr_ora.sql.
  3. Comment out the DatabaseJournal code section:

    <!-- <Cluster id="node1">
    <Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
    <param name="revision" value="${rep.home}/revision.log" />
    <param name="url" value="jdbc:postgresql://localhost:5432/jackrabbit"/>
    <param name="driver" value="org.postgresql.Driver"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="postgresql"/>
    <param name="schemaObjectPrefix" value="cl_j_"/>
    <param name="janitorEnabled" value="true"/>
    <param name="janitorSleep" value="86400"/>
    <param name="janitorFirstRunHourOfDay" value="3"/>
    </Journal>
    </Cluster> -->

Apply JDBC Drivers to the Pentaho Server

  1. Download the ojdbc8.jar oracle driver.
  2. Copy the jar file to <INSTALL_DIR>/pentaho/server/tomcat/lib.

Update Tomcat Context XML File with JDBC Connection

  1. Open <INSTALL_DIR>/pentaho/server/tomcat/webapps/pentaho/META-INF/context.xml in any text editor.
  2. Comment out the existing <Resources> in the file and add the following lines:

    <Resource validationQuery = "select 1 from dual"
    url = "jdbc:oracle:thin:@ <host>:<port>/<SID> "
    driverClassName = "oracle.jdbc.OracleDriver"
    password = " password " username = "hibuser"
    maxWaitMillis = "10000" maxIdle = "5" maxTotal = "20"
    factory = "org.apache.commons.dbcp.BasicDataSourceFactory"
    type = "javax.sql.DataSource" auth = "Container" name = "jdbc/Hibernate" />

    <Resource validationQuery = "select 1 from dual"
    url = "jdbc:oracle:thin:@ <host>:<port>/<SID> "
    driverClassName = "oracle.jdbc.OracleDriver"
    password = " password " username = "hibuser"
    maxWaitMillis = "10000" maxIdle = "5" maxTotal = "20"
    factory = "org.apache.commons.dbcp.BasicDataSourceFactory"
    type = "javax.sql.DataSource" auth = "Container" name = "jdbc/Audit" />

    <Resource validationQuery = "select 1 from dual"
    url = "jdbc:oracle:thin:@ <host>:<port>/<SID> "
    driverClassName = "oracle.jdbc.OracleDriver"
    password = " password " username = "quartz"
    maxWaitMillis = "10000" maxIdle = "5" maxTotal = "20"
    factory = "org.apache.commons.dbcp.BasicDataSourceFactory"
    type = "javax.sql.DataSource" auth = "Container" name = "jdbc/Quartz" />

    <Resource validationQuery = "select 1 from dual"
    url = "jdbc:oracle:thin:@ <host>:<port>/<SID> "
    driverClassName = "oracle.jdbc.OracleDriver"
    password = " password " username = "hibuser"
    maxWaitMillis = "10000" maxIdle = "5" maxTotal = "20"
    factory = "org.apache.commons.dbcp.BasicDataSourceFactory"
    type = "javax.sql.DataSource" auth = "Container" name = "jdbc/pentaho_operations_mart" />

    <Resource validationQuery = "select 1 from dual"
    url = "jdbc:oracle:thin:@ <host>:<port>/<SID> "
    driverClassName = "oracle.jdbc.OracleDriver"
    password = " pentaho_operations_mart " username = "pentaho_operations_mart"
    maxWaitMillis = "10000" maxIdle = "5" maxTotal = "20"
    factory = "org.apache.commons.dbcp.BasicDataSourceFactory" type = "javax.sql.DataSource"
    auth = "Container" name = "jdbc/PDI_Operations_Mart" />

    Note: Jdbc/pentaho_operations_mart and jdbc/PDI_Operations_Mart resources needs to be included even if it the pentaho_mart_oracle.sql was not run.

Clean Pentaho Server Repository Cache

The evaluation installation created samples that are still referencing the previous PostgreSQL database. This need to be removed for Pentaho Repository to recreate the cache with the right database references.

Follow these simple instructions:

  1. Stop Pentaho Server.
    1. Open a command prompt.
    2. Run stop-pentaho.bat in <INSTALL_DIR>/server/pentaho-server/.
  2. Navigate to <INSTALL_DIR>/ server/pentaho-server/pentaho-solutions/system/jackrabbit/.
  3. Rename or delete the repository folder.

Start Pentaho Server

  1. Open a command prompt.
  2. Run start-pentaho.bat as Administrator in <INSTALL_DIR>/ server/pentaho-server/.
  3. Verify that the server is running by opening Pentaho User Console. In any browser open http:<hostname>:<port>/pentaho.