BPM Admin Guide

Using Oracle Server

This section describes how to use Oracle as the solution repository.

Note: A repository database stores the metadata of the Pentaho server, and does not serve as the data source for the ETL. The steps in this guide assume you have followed the Pentaho Server Archive Installation up to step 4.

Workflow:

Components

  • Jackrabbit: Contains the solution repository, examples, security data and content data from reports for which you use the Pentaho software to create.
  • Quartz: Holds data related to scheduling reports and jobs.
  • Hibernate: Holds data related to audit logging.

Initialize Oracle Pentaho Repository Database

To initialize Oracle so that it serves as the Pentaho Repository, you must run several SQL scripts to create Hibernate, Quartz and Jackrabbit (JCR).

Step 1: Change Default Passwords

For the production server, follow best practices to change the default passwords in the following SQL script files to secure the databases.

  1. Navigate to the <pentaho_directory>/pentaho-server/data/oracle folder.
  2. Use any text editor to modify the following CREATE scripts as needed according to the existing setup for the user, password, database and other required information:
    • create_jcr_ora.sql
    • create_quartz_ora.sql
    • create_repository_ora.sql

  3. Save and close the files.

Warning:

The scripts provided by Pentaho assume you are using the default TEMP tablespace. If you have modified the temporary tablespace of your database, update the scripts accordingly.

For example:

In create_jcr_ora.sql with the following lines, the highlighted word is the name of the tablespace to update:

create user jcr_user identified by "password" default tablespace pentaho_tablespace quota unlimited on pentaho_tablespace temporary tablespace mactmp quota 5M on system;

Step 2: Run SQL Scripts

Procedure:

  1. Open a command prompt window and run sqlplus on your database instance, for example, sqlplus user/pass@servicename
  2. Run the following scripts in the order listed below:
    • @"<pentaho_directory>/pentaho-server/data/oracle/create_jcr_ora.sql"
    • @"<pentaho_directory>/pentaho-server/data/oracle/create_quartz_ora.sql"
    • @"<pentaho_directory>/pentaho-server/data/oracle/create_repository_ora.sql"
  3. Log in as the following database users to verify they are created:
    • hibuser
    • jcr_user
    • quartz
Note: If you did not modify the scripts, the default password for all the new database users is password.

Configure Oracle Pentaho Repository Database

After initializing the repository database, you must configure Quartz, Hibernate and Jackrabbit for an Oracle database. Make sure that you change the password and port number in these examples to match the ones in your configuration.

Step 1: Set Up Quartz on Oracle

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

  1. Open the hibernate-settings.xml file in a text editor.
  2. Find the <config-file> 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>

  3. Save and close the file.

Step 3: Replace Default Version of Audit Log File with Oracle Version

  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

  1. Download PentahoServer-config.zip.
  2. Copy oracle_repository.xml from /config/jackrabbit/oracle.
  3. Place oracle_repository.xml into pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit.
  4. Backup and delete the repository.xml file in the same folder as the previous step.
  5. Rename oracle_repository.xml to repository.xml

Perform Tomcat-Specific Connection Tasks

After the repository has been configured, you must configure the web application servers to connect to the Pentaho repository. In this step, you make JDBC and JNDI connections to the Hibernate, Jackrabbit and Quartz components.

Step 1: Download Driver and Apply to Pentaho Server

To connect to a database, including the Pentaho repository database, you must download and copy a JDBC driver to the appropriate locations for the Pentaho server, as well as on the web application server.

  1. Download a JDBC Driver JAR from your database vendor or a third-party driver developer:

    JDBC and UCP Downloads page

  2. Copy the JDBC driver JAR downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.

Step 2: Modify JDBC Connection Information in the Tomcat XML File

  1. Download and extract PentahoServer-config.zip.
  2. Copy oracle_context.xml from /config/tomcat/oracle.
  3. Place the file in /pentaho-server/tomcat/webapps/pentaho/META-INF.
  4. Backup and delete context.xml on the same folder as the previous step.
  5. Rename oracle_context.xml to context.xml.
  6. Open the new context.xml in any text editor.
  7. Replace all the password="password" with the corresponding database user's password.
  8. Replace all the url="jdbc:oracle:thin:@localhost:1521/XE" value with the correct host port and service name/SID.
  9. Save the context.xml file, then close it.
    Warning:

    After changing to another database type (for example, PostgreSQL to Oracle) make sure to clean up the following cache:

    • \pentaho-solutions\system\jackrabbit\repository\*
    • \pentaho-solutions\system\karaf\cache\*

    Otherwise, it could result in unpredictable errors like "java.sql.SQLSyntaxErrorException: ORA-00906: missing left parenthesis" as it tries to use the older syntax for the new database type.

Start the Server

Now that you have completed the initial Pentaho archive installation steps, you are ready to start the Pentaho Server. Follow these steps:

  1. Navigate to the <pentaho_directory>/server/pentaho-server folder.
  2. Run the startup script start-pentaho.bat.
  3. From a workstation, open a web browser and enter http://<hostname>:<port>/pentaho in the address bar.
    Note: The default port is 8080.
  4. In the the Pentaho User Console (PUC), enter your username and password then click Login.