Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Management Console (3.7)
  3. Setting Up MMC
  4. Persisting MMC Data On External Databases

  5. Persisting MMC Data to Oracle

Persisting MMC Data to Oracle

MMC Version 3.4.2 and Later

This document describes how to persist environment and transaction data to Oracle databases. Follow the instructions to Installing the Oracle Database, then follow the specific instructions for Persisting Environment Data or Persisting Transaction Data persistence to set up your user, create your database, and configure your MMC instance to persist your data.

Remember that environment data and transaction data must exist in two separate schemas (regardless of whether they are on the same server or use the same platform).

Jump to:

Assumptions

This document assumes that you have an understanding of the basic Architecture of the Mule Management Console.

It also assumes that you have installed:

  • MMC Version 3.5.0. Download it from the customer portal if you need to upgrade to this version.

  • a Web application server such as Tcat, Tomcat, or JBoss. Please refer to the Hardware and Software Requirements for the latest information about hardware requirements and supported application servers, Java Runtime Environments, and browsers.

For practical purposes, this document mentions Tomcat as the servlet container; however, the instructions contained here apply also to Tcat or JBoss. If using Tcat or JBoss, make sure to unpack the Management Console .war file in the appropriate directory. For additional information, see Installing the Production Version of MMC.

In this document, <MMC_HOME> refers to the directory where MMC is installed.

If you are using the trial version, this directory is inside the root of the embedded Tomcat server from which MMC runs. Depending on where you placed the bundled installation, the path will be similar to <installation_root>/mmc-distribution-mule-console-bundle-3.5.0/mmc-3.5.0/apache-tomcat-7.0.52/webapps/mmc-3.5.0.

If you are using a production installation in an application server such as Tomcat, the path should be similar to <Tomcat root>/webapps/mmc-console-3.50. For example /var/lib/tomcat6/webapps/mmc-console-3.5.0.

Installing the Oracle Database

The following example uses Oracle Database Express Edition 11.*. The installation outlined is basic: the database server is installed on the local machine, listening on the default port with security deactivated (unsecured connection to the database).

Tools

Important!

Do not attempt to run SQL Developer with JDK 1.7. Even though the SQL installation guide lists “JDK 1.6.11 or later” as suitable for installation, attempting to run SQL Developer with JDK 1.7. results in a warning message and malformed SQL code from SQL Developer.

Installing SQL Developer

Follow the simple instructions provided by the documentation listed Tools to decompress the installer and run the executable for SQL Developer.

Installing Oracle Express

Oracle Express provides a straightforward graphical installer for Windows and an .rpm package for Linux. Simply follow the instructions provided by the documentation listed Tools.

Both Windows and Linux require you to have administrator privileges in order to install Oracle Express.

Configuration Files

The files listener.ora and tnsnames.ora are automatically generated during installation.

For Linux, these files typically reside at: /u01/app/oracle/product/11.2.0/xe/network/admin.

On Windows, listener.ora and tnsnames.ora typically reside at: C:\oraclexe\app\oracle\product\11.2.0\server\network\admin.

For the HOST value, MuleSoft recommends a static IP address (for example, 192.168.1.10), or the 0.0.0.0 address. MuleSoft further recommends against using localhost or a hostname for HOST.
Be aware that, if the file slqnet.ora exists (usually in /app/oracle/product/11.2.0/xe/network/admin), it may contain some options which have been known to cause problems. For example, unless you are fine-tuning the database, you should disable NFS security integration by including the following parameter: SQLNET.AUTHENTICATION_SERVICES = (NONE).

Persisting Environment Data

To set up an Oracle database to persist your MMC environment data, you need to complete three steps:

  1. Create the database user and connection

  2. Set up the database tables

  3. Set up MMC

The embedded database files for the Mule Management Console reside at:

<MMC installation path>/.mule/mmc/mmc-data/db

Creating the Database User and Connection

To use the SQL Developer GUI to create the database user, complete the following steps:

  1. Launch SQL Developer:

    1. Navigate to <sqldeveloper install>/sqldeveloper

    2. Launch SQL Developer according to your operating system:

      • On Linux and Mac OS X, run the sqldeveloper.sh shell script

      • On Windows, launch sqldeveloper.exe If SQL developer asks for the full Java path, enter the full path. Typical Java paths are:

      • Linux: /usr/java/jdk1.6.0_31/bin

      • Windows: C:\Program Files\Java\jdk1.6.0_31\bin

  2. In SQL Developer, create a new database connection:

    1. Display the New/Select Database connection dialog box. To do so, follow these steps:

      1. Click the Connections tab in the left-hand pane.

      2. Right-click Connections.

      3. Select New Connec tion.

    2. In the connection Name field, enter: mmc_persistency_status.

    3. In the Username field, enter: SYSTEM.

    4. In the Password field, enter the SYSTEM password that you issued during the Oracle Express installation process.

    5. In the Hostname field, ensure that the hostname is correct (it will be localhost, if Oracle is installed on the local machine).

    6. In the SID field, enter xe.

  3. Verify the connection by clicking Test. After testing, verify that the message Status: Success appears near the bottom of the dialog box.

  4. Click Save to save the connection settings you have specified.

  5. Click Connect, then expand the connection elements menu by clicking the plus sign next to the name of your connection on the Connections tab.

  6. Create a new database user by following these steps:

    1. Right-click Other Users, then select Create User to display the Create/Edit User dialog box.

    2. Complete the fields with the required information. The following provide sample guidance:

      • User Name: MMC_STATUS

      • New Password: mmc123

      • Default Tablespace: USERS

      • Temporary Tablespace: TEMP

      • Roles tab: RESOURCE, CONNECT

      • System Privileges tab: CREATE TRIGGER, CREATE TABLE, CREATE SEQUENCE

This setup was tested using an unlimited quota on the USERS tablespace. . Click Apply, then click Close.

Setting Up the Database Tables

On the first run, JCR automatically creates all the tables needed to store persistent MMC information. However, you must manually create tables that store Quartz job information; otherwise an error similar to the following will occur:

ERROR: relation "qrtz_locks" does not exist.

To create and insert the tables, follow these steps:

  1. Navigate to <MMC_HOME>/ WEB-INF/classes/quartz.

  2. Locate the SQL script tables_oracle.sql.

  3. Execute tables_oracle.sql on the target database as user MMC_STATUS.

How to use the sqlplus utility to execute tables_oracle.sql

  1. To run tables_oracle.sql using the sqlplus utility, complete one of the following steps depending on your operating system:

    • Windows: From the Windows Start menu: Select Programs (or All Programs) > Oracle Database Express 11g Edition > Run SQL Command Line. This will open the sqlplus command prompt.

    • In Linux: Open the appropriate menu (for example Applications in Gnome, or the K menu in KDE), then select Oracle Database 11g Express Edition, then Run SQL Command Line.

  2. After the sqlplus command prompt opens, type: connect MMC_STATUS/mmc123@XE

  3. Run the tables_oracle.sql script by entering the following:

  4. To exit sqlplus, type: exit

For detailed information about the sqlplus command, consult the SQL*Plus Reference Guide.

At this point, the Oracle database is completely defined.

Setting Up MMC to Use Oracle for Persisting Environment Data

  1. First, install the appropriate Drivers.

  2. Then, follow the instructions below for editing the following configuration files:

Drivers

Obtain the appropriate drivers using the links below:

The quartz Oracle jar must match the library version provided for quartz (i.e., quartz-1.8.5.jar )

Copy the Oracle drivers to the following directory: ` <MMC_HOME>/`WEB-INF/lib.

General Setup

This example uses the parameters employed for setting up the Oracle database earlier in this document.

  • Oracle is listening on localhost, port 1521, with SID: XE

  • User: MMC_STATUS

  • Password: mmc123

MMC Configuration

Configuring MMC to store data on an Oracle databse involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use Oracle instead of its default database

  • Modifying the file mmc-oracle.properties to set the parameters for connecting to the Oracle database

Modifying web.xml

  1. In the directory <MMC_HOME>/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  1. Delete the string env-derby, then replace it with env-oracle, as shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-oracle</param-value>
</context-param>

  1. If you are also planning to Persisting Transaction Data to Oracle, delete the string tracking-h2 and replace it with tracking-oracle.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data. For a quick instructions for all supported database servers, see Configuring MMC for External Databases - Quick Reference.
Modifying mmc-oracle.properties

In the directory <MMC_HOME>/WEB-INF/classes/META-INF/databases, locate the file mmc-oracle.properties, then open it for editing.

The table below lists the settings contained in the file. Modify the values as needed. In general, the only values that you should need to modify are env.username, env.password, env.host, env.port and env.dbschema.

Parameter Description Default

env.driver

Driver to use for connecting to the database

oracle.jdbc.driver.OracleDriver

env.script

Script to use for creating the tables in the target database

oracle

env.username

Database user

MMC_STATUS

env.password

Password for the database user

mmc123

env.host

Hostname or IP address where the database server is listening

localhost

env.port

Port where the database server is listening

1521

env.url

URL for connecting to the database

jdbc:oracle:thin:${env.username}/${env.password}@${env.host}:${env.port}/${env.servicename}

env.servicename

Service name for connecting to the external database

XEXDB

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store environment data on the external Oracle database that you specified.

Disaster Recovery of Environment Data

Out of the box, MMC stores persistent state data in the folder <Mule install path>/.mule/mmc/mmc-data. If for some reason database files become corrupted, you’ll probably have to delete mmc-data and start from scratch, unless you have a backup copy of mmc-data. But having a backup copy of mmc-data does not cover a catastrophic failure with complete data loss on the MMC host itself, nor does it allow for an active-passive configuration for immediate recovery.

One possible solution is to backup the database to a single file, which can then be copied to another machine. If the need for immediate recovery arises, you can use this file to restore the database to its original state.

When you restore MMC to a previous state, be aware of the following:

  • You are restoring MMC state data, which is not related to the persistence of Business Events, which use a completely different mechanism to store data.

  • Registered servers at the time of the backup are restored, which means that one of the following situations may arise:

    • A server is paired to another Mule instance. In this case, “unpair” the server through MMC, then re-pair it. This can affect deployments and server groups.

    • A server does not exist anymore. Unpair the server.

    • Another server is using the same IP and port as the original server. Try to identify the original server’s current IP and port, then re-pair.

    • A server is correctly connected, but after the backup, deployed and/or undeployed apps are not shown or are shown incorrectly. Undeploy/Redeploy as needed to eliminate the unreconciled state.

This scenario assumes the following conditions:

  • Oracle Xpress 11.x

  • The database has already been created, including the following data tables:

    • User: MMC_STATUS

    • Permissions:

      • EXP_FULL_DATABASE

      • IMP_FULL_DATABASE

      • DBA

  • Tool to access database: SQL Developer 3.0.04

  • Tool for backup: exp (bundled with the binaries of the Oracle distribution package)

  • Tool for restore: imp (bundled with the binaries of the Oracle distribution package)

  • Arbitrary dump file name: OracleMMCDB

Database Backup Procedure

Tables on the database contain Binary Large Objects (BLOBs). A regular database export using SQL Developer does not export BLOB contents, so when restoring the database those fields are marked as NULL .

To backup the database, open a terminal and issue the following command:

exp MMC_STATUS/mmc123 file=OracleMMCDB.dmp full=yes

The file OracleMMCDB.dmp will be created in the same folder where the exp utility resides.

For help on exp command parameters run:

exp help=yes

Database Restore Procedure

Open a terminal and run the following command:

imp MMC_STATUS/mmc123 file=OracleMMCDB.dmp full=yes
The example assumes that the dump file, OracleMMCDB.dmp, is located in the same folder as the imp utility. If this is not the case, specify the full path to the .dmp file when calling the imp command.

For help on imp command parameters run:

imp help=yes

Persisting Transaction Data

To set up Oracle to persist your MMC transaction data, you need to complete three steps:

  1. Create the database user and connection

  2. Determine the database quota

  3. Set up MMC

Creating the Database User and Connection

You can create the database user and connection using the SQL Developer GUI. The procedure is described in this document, in the Persisting Environment Data section. There you will find detailed instructions for launching SQL Developer and using it to create the database user and connection.

Using those instructions as reference, open the New/Select Database connection dialog box, then create a new database user and connection with the following parameters:

  • Connection name: mmc_persistency_tracking

  • Username: SYSTEM

  • Password: The password that you issued during the Oracle Express Installation Process

  • Hostname: Hostname of the machine where the Oracle server is installed

  • SID: xe

Verify and Save the Connection

  1. Click Test to verify the connection. After testing, verify that the message Status: Success appears near the bottom of the dialog box.

  2. Click Save to save the connection settings you have specified.

Configure the Connection

  1. Click Connect, then expand the connection elements menu by clicking the plus sign next to the name of your connection on the Connections tab.

  2. Right-click Other Users, then select Create User to display the Create/Edit User dialog box.

  3. Complete the fields with the required information. The following provide sample guidance:

    • User Name: TRACKER

    • New Password: tracker

    • Default Tablespace: USERS

    • Temporary Tablespace: TEMP

    • Roles tab: RESOURCE, CONNECT

    • System Privileges tab:`CREATE ANY TABLE`, CREATE ANY SEQUENCE

  4. Click Apply, then click Close.

Determining Database Quota

Database size will, of course, vary greatly depending on usage, and the database quota should be determined while taking into account the actual usage on the environment. One way to do this is by performing load tests and extrapolating the results to actual usage over a period of time.

To determine the actual size of the database, launch Oracle’s sqlplus utility (see below) and run the following command:

select sum(bytes) from user_segments;

Details on using the sqlplus utility to run commands

  1. Access the Oracle Express menu by completing one of the following steps, depending on your operating system:

    • On Windows: From the Windows Start menu: To open the sqlplus command prompt, select Programs (or All Programs) > Oracle Database Express 11g Edition > Run SQL Command Line.

    • On Linux: Open the appropriate menu (for example, Applications in Gnome, or the K menu in KDE), select Oracle Database 11g Express Edition, then select Run SQL Command Line.

  2. After the sqlplus command prompt opens, type: connect TRACKER/tracker@XE (in this example, TRACKER is the user and tracker is the password)

  3. Run the command select sum(bytes) from user segments;

  4. To exit sqlplus, type: exit

For detailed information about the sqlplus command, consult the SQL*Plus Reference Guide.

Output should be similar to the following.

SQL> connect TRACKER/tracker @XE
Connected.
SQL> select sum(bytes) from user_segments;

SUM(BYTES)
----------
    5832704

SQL>

This indicates that the current database size is 5.83 MB.

Setting Up MMC to Use Oracle for Persisting Transaction Data

Installing the Database Driver

The driver is ojdbc5.jar. Download the driver, then copy the ojdbc5.jar file to the directory <MMC_HOME>/WEB-INF/lib/.

MMC Configuration

Configuring MMC to store Business Events data on a Oracle database involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use Oracle instead of its default database

  • Modifying the file tracking-persistence-oracle.properties to set the parameters for connecting to the Oracle database

Modifying web.xml

  1. In the directory <MMC_HOME>/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  1. Delete the string tracking-h2, then replace it with tracking-oracle, as shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-oracle,env-derby</param-value>
</context-param>

  1. If you are also planning to Persisting Environment Data to Oracle, delete the string env-derby and replace it with env-oracle.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data. For a quick instructions for all supported database servers, see Configuring MMC for External Databases - Quick Reference.

Modifying tracking-persistence-oracle.properties

In the directory <MMC_HOME>/WEB-INF/classes/META-INF/databases, locate the file tracking-persistence-oracle.properties, then open it for editing.

Modify the included settings as needed, according to the table below. In general, the only values that you should need to modify are mmc.tracking.db.username, mmc.tracking.db.password, mmc.tracking.db.host, mmc.tracking.db.port and mmc.tracking.db.servicename.

Parameter Description Default

mmc.tracking.db.platform

Type of database server to connect to

oracle(DriverVendor=oracle)

mmc.tracking.db.driver

Driver to use for connecting to the database

oracle.jdbc.driver.OracleDriver

mmc.tracking.db.host

Hostname or IP address where the database server is listening

localhost

mmc.tracking.db.port

Port where the database server is listening

1521

mmc.tracking.db.url

URL for connecting to the database

 
jdbc:oracle:thin:@${mmc.tracking.db.host}:
${mmc.tracking.db.port}
/${mmc.tracking.db.servicename}

mmc.tracking.db.username

Database user

mmc_tracking

mmc.tracking.db.password

Password for the database user

mmc123

mmc.tracking.db.servicename

Service name for connecting to the external database

XEXB

mmc.max.events.exception.details.length

Number of characters from a Business Events exception that will be stored in the tracking database. The maximum allowed is 261120.

8000

Save the file with your modifications, if any.

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store tracking data on the external Oracle database that you specified.

Troubleshooting Tips

Error message:

ORA-12519, TNS:no appropriate service handler found

If you get this error message, you will need to run the SQL command provided below, then restart the TNS listener.

As user SYS, run:

ALTER SYSTEM SET PROCESSES= 150 SCOPE=SPFILE;

To run the SQL command, you can use the sqlplus utility, as explained above.

To restart your TNS listener:

On Windows:

  1. Log in as the user who installed Oracle Database Express, then open a DOS terminal.

  2. Check the status of the TNS Listener by running the following command: LSNRCTL STATUS

  3. To stop the TNS Listener, run LSNRCTL STOP

  4. To start the TNS Listener, run LSNRCTL START

On Unix/Linux:

  1. Log in to the oracle system user, for example by running the command su - oracle.

  2. Set the appropriate environment variables by performing these steps:

    1. Navigate to the bin directory of the Oracle installation (typically, /u01/app/oracle/product/11.2.0/xe/bin).

    2. Run the command source oracle_env.sh.

  3. After setting environment variables, check the TNS listener status by running lsnrctl status

  4. To stop the TNS listener, run lsnrctl stop

  5. To start the TNS listener, run lsnrctl start

See Also