Use Oracle as your repository database (Manual installation)

Legacy page. Content moved to the main topic.

This page is kept for existing links. Go to Manual installation process.

This article includes the steps for installing Oracle as the host database for the Pentaho Server in a manual installation of Pentaho.

Before you begin

Prerequisite

Before you prepare your Pentaho Repository, prepare either a Windows or Linux environment.

Components

The Pentaho Repository resides on the database that you installed during the Windows or Linux environment preparation step.

It consists of the following components:

• Jackrabbit

  Contains the solution repository, examples, security data, and content data from reports that you use Pentaho software to create.

• Quartz

  Holds data that is related to scheduling reports and jobs.

• Hibernate

  Holds data that is related to audit logging.

• (Optional) Pentaho Operations Mart

  To report on system usage and performance.

Initialize Oracle Pentaho Repository database

The sections in this article take you through the steps to initialize the Oracle Pentaho Repository database.

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

CAUTION:

Use the ASCII character set when you run these scripts. Do not use UTF-8 because there are text string length limitations that might cause the scripts to fail.

CAUTION:

If you have a different password or user, make sure that you change the password or port number in these examples to match the ones in your configuration.

CAUTION:

Pentaho 10.2.0.0 and earlier versions use Quartz 1.x library, which is designated with a QRTZ5_ prefix in the database. Newer versions, beginning with 10.2.0.1, use Quartz 2.x library, which is designated with a QRTZ6_prefix in the database. When upgrading from a previous version to Pentaho 10.2.0.1 and later, you must create the QRTZ6_ prefixed table in the database by repeating the procedure for Quartz database creation, and then optionally migrating the existing Quartz schedules to the new Quartz database using the migration script. During this upgrade procedure, executing the SQL create script resets the corresponding Quartz database without loss of the original data. However, the other associated repository databases in 10.2.0.1 will be reset by SQL scripts if run and data will be potentially deleted. Always follow best practices and backup your data prior to proceeding.

Important: Failure to complete the Quartz upgrade results in a Pentaho Server error at start-up. In addition to the server error, the following exception message is generated in the catalina.log file:

Missing Quartz library database error

Step 1: Change default passwords

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

Note: If you are evaluating Pentaho, you might want to skip this step.

1. Browse to the <*your pentaho directory*>/pentaho-server/data/oracle folder.

2. Use any text editor to modify these create scripts as needed according to your existing setup for the user, password, database, and other required information:

   • create_jcr_ora.sql

   • create_quartz_ora.sql

   • create_repository_ora.sql

   • pentaho_mart_oracle.sql

3. Save and close the files.

Step 2: Run SQL scripts

When upgrading from previous Pentaho versions, including 10.2.0.0 GA to 10.2.0.1 and later, you must manually initialize a new Quartz database. A new Quartz library is created in the repository database as a result. Always follow best practices and backup your data prior to proceeding. If you want to keep your existing Quartz library data, you can migrate the current tables to the new tables.

Run the SQL creation scripts.

Note: You may need administrator permissions to run these scripts on the host OS server.

1. Backup your data. See Back up your existing Pentaho products and install Pentaho 11.0.

2. If the Pentaho Server is currently running, stop it.

   For instructions on stopping the server, see Stop and start the Pentaho Server and repository.

3. Make sure that your Oracle instance is up and running.

4. Open a Command Prompt window or a terminal window that runs SQL*Plus and then run the following SQL creation scripts in the order shown:

   1. <*your pentaho directory*>/pentaho-server/data/oracle/create_jcr_ora.sql

   2. <*your pentaho directory*>/pentaho-server/data/oracle/create_quartz_ora.sql

   3. <*your pentaho directory*>/pentaho-server/data/oracle/create_repository_ora.sql

   4. <*your pentaho directory*>/pentaho-server/data/pentaho_mart_oracle.sql

   The Quartz database, Jackrabbit and Oracle repositories, and Pentaho Operations Mart are created. The new Quartz scheduler table prefixed with QRTZ6_ is created. Any existing QRTZ5_ scheduler database is retained. However, starting the Pentaho Server at this point will result in an empty schedule.

5. If you want to retain your existing scheduler database, using any text editor, open the migrate_old_quartz_data_oracle.sql file.

6. As needed, modify the migration script according to your existing setup for the user, password, database, and other required information and then save and close the file.

7. Run the migration script:

   <*your pentaho directory*>/pentaho-server/data/oracle/migrate_old_quartz_data_oracle.sql

The original Quartz scheduler database is migrated to the new Quartz database.

Note: The original QRTZ5_ data is retained. You can delete it at your discretion after confirming the migration was successful and complete within the Pentaho Server repository.

Step 3: Verify Oracle initialization

Note: Unless you change it in Step 1, above, the default password for each username below is password.

After you run the scripts, perform the following steps to verify that databases and user roles have been created properly:

1. Open a Terminal or Command Prompt window that is running SQL*Plus or a similar client tool and connect to the Oracle database.

2. Log in as: hibuser

3. Verify that you can see the tables under hibernate.

4. If you installed Pentaho Operations Mart, verify that you can see the tables under pentaho_operations_mart.

5. Log in as: jcr_user

6. Verify that you can see the tables under jackrabbit.

   The jackrabbit tables may not appear until you have started Pentaho for the first time.

7. Log in as: pentaho_user

8. Verify that you can see the tables under quartz.

9. Exit from the tool.

You have initialized the Oracle Pentaho Repository database.

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.

CAUTION:

If you have a different port or different password, 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

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

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>

Perform Tomcat-specific connection tasks

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

Note: By default, the Pentaho Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and the Tomcat context.xml file must be modified only if you have changed the default ports or passwords.

The next couple of sections guide you through the process of working with the JDBC drivers and connection information for Tomcat.

Step 1: Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, you will need to download and copy a JDBC driver to the appropriate places for the Pentaho Server as well as on the web application server.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install the file yourself.

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

   The JDBC drivers reference has a list of supported drivers.

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

3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to retain the sample provided by Pentaho.

Step 2: Modify JDBC connection information in the Tomcat XML file

Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information for your operating environment.

CAUTION:

If you have a different port, password, user, driver class information, or IP address, make sure that you change the password and port number in these examples to match the ones in your configuration environment.

1. Consult your database documentation to determine the JDBC class name and the connection string for your Pentaho Repository database.

2. Navigate to the server/pentaho-server/tomcat/webapps/pentaho/META-INF directory and open the context.xml file with any text editor.

3. Add the following code to the file if it does not already exist.

   <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   <Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1" />
   <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="pentaho_user" password="password" testOnBorrow="true"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
           validationQuery="select 1"/>
   <Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1"/>
   <Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1"/>
   <Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="hibuser" password="password" driverClassName="org.postgresql.Driver"
           url="jdbc:postgresql://localhost:5432/hibernate?searchpath=pentaho_dilogs"
           validationQuery="select 1"/>
   <Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="pentaho_user" password="password"
           driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
           validationQuery="select 1"/>
   <Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="pentaho_admin" password="password"
           driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
           validationQuery="select 1"/>
   <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="jcr_user" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jackrabbit"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

4. Modify the username, password, driver class information, IP address (or domain name), and port numbers to match the correct values for your environment.

5. Comment out any resource references that refer to other databases.

6. Verify that the validationQuery variable for your database is set to this: validationQuery="select 1"

7. Save the context.xml file and close it.

Next steps

After installing the database that will host the Pentaho Repository, see Configure and start the Pentaho Server after manual installation for further instructions.