search

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.

circle-exclamation

Use the ASCII character set when you run these scripts. Do not use UTF-8. UTF-8 may cause script failures due to text string length limits.

circle-exclamation

If you use different users or passwords, update the examples to match your environment.

circle-exclamation

Pentaho 10.2.0.0 and earlier use the Quartz 1.x library. Those tables use the QRTZ5_ prefix.

Pentaho 10.2.0.1 and later use Quartz 2.x. Those tables use the QRTZ6_ prefix.

If you upgrade from an earlier version to 10.2.0.1 or later, create the QRTZ6_ tables by repeating the Quartz database creation step. You can optionally migrate existing schedules with the migration script.

Back up your data before you run any scripts.

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

hashtag
Steps

  1. Step 1: Change default passwords

  2. Step 2: Run SQL scripts

  3. Step 3: Verify Oracle initialization

hashtag
Step 1: Change default passwords

For production systems, follow best practices and change the default passwords in the SQL scripts.

Note: If you are evaluating Pentaho, you can skip this step.

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

  2. Use a text editor to update the scripts as needed for your user, password, database, and other settings:

    • create_jcr_ora.sql

    • create_quartz_ora.sql

    • create_repository_ora.sql

    • pentaho_mart_oracle.sql

  3. Save and close the files.

hashtag
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 back up your data before you proceed. If you want to keep existing Quartz schedules, you can migrate the existing tables to the new tables.

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

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

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

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

  3. Make sure that your Oracle instance is running.

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

    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

    This creates the Quartz database, Jackrabbit repository, Hibernate repository, and Pentaho Operations Mart.

    The QRTZ6_ tables are created. Any existing QRTZ5_ tables remain. If you start the Pentaho Server now, the schedule is empty.

  5. If you want to retain your existing schedule data, open migrate_old_quartz_data_oracle.sql in a text editor.

  6. Update the script for your user, password, database, and other settings, then save it.

  7. Run the migration script:

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

Note: The original QRTZ5_ data remains available. Delete it only after you verify a successful migration.

Note: You unpacked the Pentaho Operations Mart SQL file while preparing your environment for the archive installation.

For details, see Prepare your Windows environment for an archive install or Prepare your Linux environment for an archive install.

hashtag
Step 3: Verify Oracle initialization

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

After you run the scripts, verify that databases and user roles were created correctly:

  1. Open a Terminal or Command Prompt window running SQL*Plus (or a similar client) 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 start Pentaho for the first time.

  7. Log in as pentaho_user.

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

  9. Exit the tool.

You have initialized the Oracle Pentaho Repository database.

Last updated

Was this helpful?