Persisting Environment Data

Out of the box, the Mule console stores persistent state data (created alerts, deployments, dashboard portlets configuration, etc.) on an embedded database. If the server hosting the console experiences catastrophic failure, this persistent data is lost. This can be avoided by configuring the console host to use an external database to store persistent state data. Following a catastrophic failure of the console host, you can set up the console on another host, then configure it to use the same database as the previous instance.

The embedded database files for the Mule console reside at: <MMC installation path>/.mule/mmc/mmc-data/db After you configure MMC to use an external database to store its persistent data, do not delete the mmc-data folder. The db subfolder will not be used anymore, but the repository, version and workspaces subfolders will be in use by Java Content Repository (JCR) internal settings.

Do not confuse MMC state info with Business Events persistence. They are two independent systems, not related to each other. Refer to a detailed description of Business Events.

It is also possible to use an external database to store Mule event tracking information, which is normally stored using Business Events. Click here for details.

It is possible to use an Oracle or Postgres Database to persist Mule environment data.

Oracle Setup

Tools

• SQL Developer 3.0.04:
  • Download the latest version here
  • Consult the installation guides for Linux and Windows here
• Oracle Database Express Edition 11.x:
  • Download the latest version here
  • Consult the Installation guides for Linux and Windows here
  • The full documentation library is here

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

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.

Please 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 with SQLNET.AUTHENTICATION_SERVICES = (NONE).

Creating the Database User

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

1. Launch SQL Developer
2. Navigate to <sqldeveloper install>/sqldeveloper
   • 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 (or navigate to it if you are using a GUI). Typical Java paths are:

• Linux: /usr/java/jdk1.6.0_31/bin
• Windows: C:\Program Files\Java\jdk1.6.0_31\bin

1. Create a new database connection:
   1. Display the New / Select Database connection dialog box by clicking the Connections tab in the left-hand pane, right-clicking Connections, then selecting New Connection.
   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
2. Click Test to verify the connection. After testing, verify that Status: Success appears near the bottom of the dialog box.
3. Click Save to save the connection settings you have specified.
4. Click Connect, then expand the connection elements menu by clicking the plus sign next to the name of your connection on the Connections tab.
5. Right click Other Users, then select Create User to display the Create/Edit User dialog box.
6. 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.

7. Click Apply, then click Close.

Setting up the Database

Creating the Tables

On the first run, JCR automatically creates all the tables needed to store persistent Mule console 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:

1. Navigate to <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/classes/quartz
2. Locate tables_oracle.sql
3. Extract the file to a convenient location, such as your home directory or the /tmp directory on a Linux system.

4. Execute tables_oracle.sql on the target database, under user MMC_STATUS

    Click to view how to use the sqlplus utility to execute tables_oracle.sql

   1. Access the Oracle Express menu and complete one of the following steps, depending on your operating system:
      • From the Windows Start menu: To open the sqlplus command prompt, navigate to Programs (or All Programs) > Oracle Database Express 11g Edition > , Run SQL Command Line.
      • In Linux: Open the appropriate menu (Applications in Gnome, or the K menu in KDE), and select Oracle Database 11g Express Edition, then select 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: start <path to script>/tables_oracle.sql, which typically resolves to something like start /tmp/tables_oracle.sql

   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.

To avoid storing persistent information locally (on the mmc-data/db directory), find and delete the file jackrabbit-repo.xml. This can be found in the following directory: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/classes/META-INF/.

Setting up the Mule Console for Oracle

Drivers

You can obtain appropriate drivers from the following locations:

• ojdbc5.jar (JDBC drivers can be downloaded from here
• quartz-oracle-1.8.5.jar (download here)

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

Driver for MMC run as Mule app

Copy the Oracle drivers to the following directory: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/lib

Or make the drivers generally available by copying them to: <Mule install path>/lib/user

Driver for MMC run as Web app

Copy the Oracle drivers to the following directory: <Mule install path>/mmc/webapps/mmc/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

Configuration File: mmc.properties

Go to the following directory: <Mule install path>/mmc/webapps/mmc/WEB-INF/classes/META-INF

Locate the file called mmc.properties

Edit the file as follows:

The colons (":") on the connection string should always be escaped, i.e. preceded by a backslash ("\").

Make sure that the following line does not appear:

Configuration File: applicationContext.xml

In the same directory, locate the file called applicationContext.xml. Open it for editing, and look for the bean called internalDataSource. Edit it as follows:

Configuration File: jackrabbit-repo.xml

For safety, make sure to delete the following files (if listed) before editing jackrabbit-repo.xml: jackrabbit-repo-default.xml jackrabbit-repo-test.xml

In the same directory, locate the file called jackrabbit-repo.xml and open it for editing. Use the following parameters for the database connection:

• Role: MMC_STATUS
• Password: mmc123
• Schema: oracle

The schema should always be Oracle. It defines the database, not the actual schema to be used for storing persistent data. The Persistence Manager used is org.apache.jackrabbit.core.persistence.pool.OraclePersistenceManager. The File System used is org.apache.jackrabbit.core.fs.db.OracleFileSystem.

Important! After configuring the default workspace and the workspace name itself, make sure that either, both have the same *new* name (eg "myOracleWorkspace") or that you go to mmc.data\workspaces and delete all subfolders in it (in particular "default" subfolder), otherwise, all binary data will be persisted locally instead of on the database.

Configuration file: scheduler.properties

Locate the file called scheduler.properties. Edit it to look like this:

Postgres Setup

The following tutorial covers a simple setup for MMC to work with a Postgres database. The Postgres installation outlined here is basic: the database is installed on the local machine on the default port, with security deactivated (unsecured connection to the database, user with full admin rights).

Tools

• PostgreSQL 9.1.3 (download it here)
• pgAdmin III (select it when installing Postgres)
• Postgres JDBC driver: postgresql-9.1-901.jdbc4.jar. Select it at installation time or download it from our internal Drivers page

Installing PostgreSQL

Follow the installation steps that can be found here, then proceed to the next section of this document.

Creating the database user

We will create user MMC_STATUS, using the same permissions as postgres, the default user. User MMC_STATUS will have the following parameters:

• Role name: MMC_STATUS
• Password: mmc123

postgres is the super user. User MMC_STATUS can operate with fewer permissions than postgres, but the minimal set of permissions has not been determined.

Using pgAdmin III:

• Login to the database server as user postgres by using pgAdmin III’s object browser (located on the right-hand pane) to right-click server PostgreSQL on localhost, then select Connect.
• On the Object Browser, right-click Login Roles, then select New Login Role.
• At the New Login Role dialog box, type MMC_STATUS in the Role name field.
• Go to the Definition tab in the dialog box, and type the password mmc123 in both Password fields.
• In the Role Privileges tab, select all the checkboxes.
• Click OK to close the New Login Role dialog box.

Creating the new database

We will create a database called mmc_persistency_status, owned by user MMC_STATUS

Using pgAdmin III:

• Using the Object Browser, navigate to Databases > New Database.
• In the dialog box, type mmc_persistency_status in the Name field.
• In the Owner field, select MMC_STATUS
• In the Definition tab, ensure that Encoding is set to UTF8
• Click OK to close the New Database dialog box.

Verifying the new database

Use PostgreSQL’s psql command-line utility to log in to database mmc_persistency_status as user MMC_STATUS. To do this, open a terminal and run:

psql postgres –UMMC_STATUS

When you run this command, psql should prompt for the user’s password. After typing it, you should get a prompt similar to the following:

mmc_persistency_status=#

This indicates that you have successfully connected to the mmc_persistency_status database as user MMC_STATUS.

An example of the full login command and output:

To exit psql, type \q, then press Enter.

Creating the tables

On the first run, JCR will automatically create all the tables needed to store persistent MMC information. However, you have to manually create some tables that store Quartz job info; otherwise at some point the following error will occur:

To create and insert the tables:

• Navigate to the directory <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/classes/quartz
• If there is a file named talbes_postgres.sql, rename it to tables_postgres.sql
• Execute the tables_postgres.sql script on the target database mmc_persistency_status. One way to do this is by running the following command: psql –d mmc_persistency_status –UMMC_STATUS –f tables_postgres.sql (if necessary, specify <full path>/tables_postgres.sql)

At this point, the Postgres database should be completely defined:

To avoid storing persistent information locally (on the mmc-data/db directory), find and delete the file jackrabbit-repo.xml. This can be found in the following directory: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/classes/META-INF/

Setting up MMC for use with Postgres

Driver for MMC run as Mule app

Copy the Postgres jdbc driver, postgresql-9.1-901.jdbc3.jar, to the following directory: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/lib

Or make it generally available by copying it to: <Mule install path>/lib/user

Driver for MMC run as a Web app

Copy the Postgres jdbc driver, postgresql-9.1-901.jdbc3.jar, to the following directory: <Mule install path>/mmc/webapps/mmc/WEB-INF/lib

Configuration File: mmc.properties

Go to the following directory: <Mule install path>/mmc/webapps/mmc/WEB-INF/classes/META-INF

Locate the file called mmc.properties. Edit the file as follows:

The colons (":") on the connection string should always be escaped with a backslash ("\").

Make sure that the following line does not appear:

Configuration File: applicationContext.xml

In the same directory, locate the file called applicationContext.xml. Open it for editing, and look for the bean called internalDataSource. Edit it as follows:

Configuration File: jackrabbit-repo.xml

For safety, before editing this file delete the following files (if listed): jackrabbit-repo-default.xml and jackrabbit-repo-test.xml.

In the same directory, locate the file called jackrabbit-repo.xml and open it for editing. Use the following parameters for the database connection:

• Role: MMC_STATUS
• Password: mmc123
• Schema: postgresql
• IP: localhost
• Port: 5432 (default at installation)

The schema should be always postgresql. It defines the database, not the actual schema to be used for storing persistent data.

There is a newer version of org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManage, called org.apache.jackrabbit.core.persistence.pool.PostgreSQLPersistenceManage, but it currently has issues so it is not recommended yet.

Configuration File: scheduler.properties

Locate the file called scheduler.properties. Edit it to look like this:

MMC is now configured to store its persistent data in the specified Postgres database. Start Mule and login to the MMC normally.

It is possible that, when using the PostgreSQL database, attempting to connect to the MMC will result in a "503 - Service unavailable” error, even if the system is configured correctly. In this case, check the file <Mule installation path>/logs/mule-app-mmc.log. Look for a line that contains PSQLException: FATAL: role "<user>" does not exist, where <user> is the OS system user running Mule. If you find this line, use pgAdmin III to create a new role with the same name as the OS system user that runs Mule, and assign full admin privileges to this new role (you can follow the same steps as when creating the MMC_STATUS role). Restart Mule, and MMC should now start normally.

Disaster Recovery

Scenario

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, this file can be used to restore the database to its original state.

When you restore MMC to a previous state, please 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: 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. Server does not exist anymore. Unpair the server. Another server is using the same IP and port as the original one. Try to identify the original server’s current IP and port, then re-pair. Server is correctly connected, after the backup, deployed and/or undeployed apps are not shown or are shown incorrectly. Undeploy/Redeploy as needed to eliminate the unreconciled state.

Postgres

Scenario:

• Database server: PostgreSQL 9.1
• MMC is connected to Postgres
• A database is already created. For this example the following parameters will be used:
  • Role: MMC_STATUS (with same permissions as the "postgres" role)
  • Database name: mmc.test
    • Encoding: UTF8
    • Owner: MMC_STATUS
• Tool to access database: pgAdmin III

Backing up the Database

To backup the mmc.test database, complete the following steps:

• Login into pgAdmin III as admin (role postgres)
• On the object browser on the left-hand pane, go to Server Groups > PostgreSQL 9.1 > Databases > mmc.test
• Right click on mmc.test, then navigate to Backup > File Optionsand select:
  • Format: Tar
  • Encoding: UTF8
  • Rolename: MMC_STATUS
  • Filename: <Suitable name and folder>
• Click Backup to create a tar archive of the database at the location you specified.

Restoring the Database

To restore the mmc.test database, complete the following steps:

1. Go to the mmc-data folder (at <Mule install path>/.mule/mmc/mmc-data) and delete the following folders:
   • db (if it exists)
   • repository
   • tracking (This is necessary to avoid generating several stacktraces related to JCR).
   • you may need to also delete workspaces/<name of your workspace>/index
2. Login into pgAdmin III as admin (role postgres)
3. On the object browser, make sure that the database called mmc.test is defined
4. Make sure that all the tables that may be defined on the database are dropped
5. Right click on mmc.test, select Restore
6. On the File Optionstab, select:
   • Filename: <Database backup file>
   • Format: Custom or tar
   • Rolename: MMC_STATUS
7. Click Restore.

This process was tested when MMC was running, but without load.

Oracle

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 BLOBs 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: imp MMC_STATUS/mmc123 file=OracleMMCDB.dmp full=yes

The example assumes that the dump file is located in the same folder as the imp utility. Specify the full path to the .dmp file if necessary.

For help on imp command parameters run: imp help=yes