Persisting MMC Data to Oracle

This document will cover how to persist environment and transaction data to Oracle databases. Follow the instructions to install the Oracle database, then follow the specific instructions for Persisting Environment Data or 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:

Persisting Environment Data
Persisting Transaction Data to Oracle

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:

Mule Enterprise 3.4.1 or later. Download it from the customer portal if you need to upgrade to this version.
MMC Version 3.4.1. 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 using the trial version, this is <Mule install path>/mmc/webapps/mmc, for example /opt/mule/mule-enterprise-3.4.1/mmc/webapps/mmc. If using an application server such as Tomcat, this should be similar to <Tomcat root>/webapps/mmc-console-3.4.1, for example /var/lib/tomcat6/webapps/mmc-console-3.4.1.

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

SQL Developer 3.0.04:
- Download the latest version
- Consult the installation guides for Linux and Windows
Oracle Database Express Edition 11.x:
- Download the latest version
- Consult the installation guides for Linux and Windows
- Refer to the full documentation library

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.

listener.ora listing

# listener.ora Network Configuration File:
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
             (SID_NAME = PLSExtProc)
             (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
             (PROGRAM = extproc)
         )
    (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
        (PROGRAM = extproc)
    )
    (SID_DESC =
        (SID_NAME = XE)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
    )
)

LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
        )
    )

DEFAULT_SERVICE_LISTENER = (XE)

listing of tnsnames.ora

# tnsnames.ora Network Configuration File:

XE =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
        )
    )

EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
    (CONNECT_DATA =
        (SID = PLSExtProc)
        (PRESENTATION = RO)
    )
)

ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
        (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     )
    (CONNECT_DATA =
        (SID = CLRExtProc)
        (PRESENTATION = RO)
    )
)

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:

Create the database user and connection
Set up the database tables
Set up MMC

The embedded database files for the Mule Management 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.

Creating the Database User and Connection

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

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
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.
Verify the connection by clicking Test. After testing, verify that the message Status: Success appears near the bottom of the dialog box.
Click Save to save the connection settings you have specified.
Click Connect, then expand the connection elements menu by clicking the plus sign next to the name of your connection on the Connections tab.
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:

Navigate to <MMC_HOME>/WEB-INF/classes/quartz.
Locate the SQL script tables_oracle.sql.
Execute tables_oracle.sql on the target database as user MMC_STATUS.
How to use the sqlplus utility to execute tables_oracle.sql:
1. Run tables_oracle.sql using the sqlplus utility, and 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 opens 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:
  start <path to script>/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.

Setting Up MMC to Use Oracle for Persisting Environment Data

First, install the appropriate database driver.
Then, follow the instructions below for editing the following configuration files:
- mmc.properties * applicationContext.xml
- link:/mule-management-console/v/3.4/persisting-mmc-data-to-oracle#configuration-file-code-jackrabbit-repo-xml-code
- link:/mule-management-console/v/3.4/persisting-mmc-data-to-oracle#configuration-file-code-scheduler-properties-code

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

Configuration File: `mmc.properties`

(Directory: <MMC_HOME>/WEB-INF/classes/META-INF.)

In the directory listed above, locate the file called mmc.properties.
Edit the file as per the following.

script=oracle
datasource.username=MMC_STATUS
datasource.password=mmc123
datasource.url=jdbc\:oracle\:thin\:MMC_STATUS/mmc123@localhost\:1521\:xe
datasource.driver=oracle.jdbc.driver.OracleDriver
# Existing entries
plugin.directory=WEB-INF/plugins
console.inactivityTimeout=60
mmc.data=./mmc-data

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:

datasource.dbName=${galaxy.data}/db from mmc.properties

This line would conflict with the parameters for accessing the correct database, resulting in a connection error. If the line is present in the mmc.properties file, delete it.

Configuration File: `applicationContext.xml`

(Directory: Same as previous file (<MMC_HOME>/WEB-INF/classes/META-INF).)

Locate the file called applicationContext.xml.
Open the file for editing, and look for the bean called internalDataSource.
Edit the bean as per the following.

<bean name="internalDataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
    </bean>

Configuration File: `jackrabbit-repo.xml`

(Directory: Same as previous file (`<MMC_HOME>/`WEB-INF/classes/META-INF).)

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

jackrabbit-repo-default.xml
jackrabbit-repo-test.xml

Locate the file jackrabbit-repo.xml, then open it for editing.
Replace the appropiate parameters to use Oracle instead of the default Derby database. A full listing of the modified file is provided below.

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!

When configuring the default workspace and the workspace name itself, ensure that you comply with one of the following conditions:

assign the same new name to both workspaces (for example myOracleWorkspace)

or
delete all subfolders in mmc-data/workspaces, specifically the default subfolder. You will find the mmc-data folder on the root folder of your Web application server installation (for example, /var/lib/tomcat6/mmc-data).

If neither of the above conditions are met, all binary data will be persisted locally instead of on the external database.

Example of jackrabbit-repo.xml

<?xml version="1.0"?>
<!DOCTYPE Repository PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit
     1.2 //EN"  "http://jackrabbit.apache.org/dtd/repository-1.2.dtd">
<Repository>
  <FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
    <param name="schemaObjectPrefix" value="repo_"/>
    <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
    <param name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
    <param name="user" value="MMC_STATUS"/>
    <param name="password" value="mmc123"/>
    <param name="schema" value="oracle"/>
    <param name="schemaCheckEnabled" value="true"/>

  </FileSystem>
  <Security appName="Jackrabbit">
    <AccessManager class="org.apache.jackrabbit.core.security.SimpleAccessManager"/>
    <LoginModule class="org.apache.jackrabbit.core.security.SimpleLoginModule">
      <param value="anonymous" name="anonymousId"/>
    </LoginModule>
  </Security>
  <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
  <Workspace name="default">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
      <param name="schemaObjectPrefix" value="workspace_"/>
      <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
      <param name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
      <param name="user" value="MMC_STATUS"/>
      <param name="password" value="mmc123"/>
      <param name="schema" value="oracle"/>
      <param name="schemaCheckEnabled" value="true"/>

    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.OraclePersistenceManager">
      <param name="schemaObjectPrefix" value="Jackrabbit Core_" />
      <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
      <param name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
      <param name="user" value="MMC_STATUS"/>
      <param name="password" value="mmc123"/>
      <param name="schema" value="oracle"/>
      <param name="externalBLOBs" value="false"/>
      <param name="schemaCheckEnabled" value="true"/>

    </PersistenceManager>
    <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
      <param value="${wsp.home}/index" name="path"/>
      <param value="org.apache.jackrabbit.core.query.lucene.TextPlainTextFilter,
          org.apache.jackrabbit.extractor.MsExcelTextExtractor,
          org.apache.jackrabbit.extractor.MsPowerPointTextExtractor,
          org.apache.jackrabbit.extractor.MsWordTextExtractor,
          org.apache.jackrabbit.extractor.PdfTextExtractor,
          org.apache.jackrabbit.extractor.HTMLTextExtractor,
          org.apache.jackrabbit.extractor.XMLTextExtractor,
          org.apache.jackrabbit.extractor.RTFTextExtractor,
          org.apache.jackrabbit.extractor.OpenOfficeTextExtractor"
          name="textFilterClasses"/>
      <FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
        <param name="schemaObjectPrefix" value="search_index_w_"/>
        <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
        <param name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
        <param name="user" value="MMC_STATUS"/>
        <param name="password" value="mmc123"/>
        <param name="schema" value="oracle"/>
        <param name="schemaCheckEnabled" value="true"/>

      </FileSystem>
    </SearchIndex>
  </Workspace>
  <Versioning rootPath="${rep.home}/version">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
      <param name="schemaObjectPrefix" value="filesystem_version_"/>
      <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
      <param name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
      <param name="user" value="MMC_STATUS"/>
      <param name="password" value="mmc123"/>
      <param name="schema" value="oracle"/>
      <param name="schemaCheckEnabled" value="true"/>

    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.OraclePersistenceManager">
      <param name="schemaObjectPrefix" value="version_"/>
      <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
      <param name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
      <param name="user" value="MMC_STATUS"/>
      <param name="password" value="mmc123"/>
      <param name="schema" value="oracle"/>
      <param name="externalBLOBs" value="false"/>
      <param name="schemaCheckEnabled" value="true"/>

    </PersistenceManager>
  </Versioning>
  <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
    <param value="${rep.home}/repository/index" name="path"/>
    <FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
      <param name="schemaObjectPrefix" value="search_index_"/>
      <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
      <param name="url" value="jdbc:oracle:thin:MMC_STATUS/mmc123@localhost:1521:xe"/>
      <param name="user" value="MMC_STATUS"/>
      <param name="password" value="mmc123"/>
      <param name="schema" value="oracle"/>
      <param name="schemaCheckEnabled" value="true"/>

    </FileSystem>
  </SearchIndex>
</Repository>

Configuration file: `scheduler.properties`

(Directory: Same as previous files (<MMC_HOME>/WEB-INF/classes/META-INF).)

Locate the file called scheduler.properties.
Edit it as per the following.

org.quartz.jobStore.isClustered=false
    org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
    org.quartz.jobStore.useProperties=true
    org.quartz.scheduler.instanceId=AUTO

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

listing of imp output

C:\QA\oracle.xe\app\oracle\product\11.2.0\server\bin>imp MMC_STATUS/mmc123 file=someOracleFile.dmp full=yes

Import: Release 11.2.0.2.0 - Production on Thu Mar 29 11:03:29 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing MMC_STATUS's objects into MMC_STATUS
. . importing table "FILESYSTEM_VERSION_FSENTRY" 1 rows imported
. . importing table "JR_CORE_BINVAL" 0 rows imported
. . importing table "JR_CORE_BUNDLE" 187 rows imported
. . importing table "JR_CORE_NAMES" 0 rows imported
. . importing table "JR_CORE_REFS" 5 rows imported
. . importing table "QRTZ_BLOB_TRIGGERS" 0 rows imported
. . importing table "QRTZ_CALENDARS" 0 rows imported
. . importing table "QRTZ_CRON_TRIGGERS" 2 rows imported
. . importing table "QRTZ_FIRED_TRIGGERS" 0 rows imported
. . importing table "QRTZ_JOB_DETAILS" 2 rows imported
. . importing table "QRTZ_JOB_LISTENERS" 0 rows imported
. . importing table "QRTZ_LOCKS" 5 rows imported
. . importing table "QRTZ_PAUSED_TRIGGER_GRPS" 0 rows imported
. . importing table "QRTZ_SCHEDULER_STATE" 0 rows imported
. . importing table "QRTZ_SIMPLE_TRIGGERS" 0 rows imported
. . importing table "QRTZ_TRIGGERS" 2 rows imported
. . importing table "QRTZ_TRIGGER_LISTENERS" 0 rows imported
. . importing table "REPO_FSENTRY" 8 rows imported
. . importing table "SEARCH_INDEX_FSENTRY" 1 rows imported
. . importing table "SEARCH_INDEX_W_FSENTRY" 1 rows imported
. . importing table "VERSION_BINVAL" 0 rows imported
. . importing table "VERSION_BUNDLE" 3 rows imported
. . importing table "VERSION_NAMES" 0 rows imported
. . importing table "VERSION_REFS" 0 rows imported
. . importing table "WS1_FSENTRY" 1 rows imported
Import terminated successfully without warnings.

listener.ora listing

C:\QA\oracle.xe\app\oracle\product\11.2.0\server\bin>imp MMC_STATUS/mmc123 file=
someOracleFile.dmp full=yes

Import: Release 11.2.0.2.0 - Production on Thu Mar 29 11:03:29 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing MMC_STATUS's objects into MMC_STATUS
. . importing table   "FILESYSTEM_VERSION_FSENTRY"          1 rows imported
. . importing table               "JR_CORE_BINVAL"          0 rows imported
. . importing table               "JR_CORE_BUNDLE"        187 rows imported
. . importing table                "JR_CORE_NAMES"          0 rows imported
. . importing table                 "JR_CORE_REFS"          5 rows imported
. . importing table           "QRTZ_BLOB_TRIGGERS"          0 rows imported
. . importing table               "QRTZ_CALENDARS"          0 rows imported
. . importing table           "QRTZ_CRON_TRIGGERS"          2 rows imported
. . importing table          "QRTZ_FIRED_TRIGGERS"          0 rows imported
. . importing table             "QRTZ_JOB_DETAILS"          2 rows imported
. . importing table           "QRTZ_JOB_LISTENERS"          0 rows imported
. . importing table                   "QRTZ_LOCKS"          5 rows imported
. . importing table     "QRTZ_PAUSED_TRIGGER_GRPS"          0 rows imported
. . importing table         "QRTZ_SCHEDULER_STATE"          0 rows imported
. . importing table         "QRTZ_SIMPLE_TRIGGERS"          0 rows imported
. . importing table                "QRTZ_TRIGGERS"          2 rows imported
. . importing table       "QRTZ_TRIGGER_LISTENERS"          0 rows imported
. . importing table                 "REPO_FSENTRY"          8 rows imported
. . importing table         "SEARCH_INDEX_FSENTRY"          1 rows imported
. . importing table       "SEARCH_INDEX_W_FSENTRY"          1 rows imported
. . importing table               "VERSION_BINVAL"          0 rows imported
. . importing table               "VERSION_BUNDLE"          3 rows imported
. . importing table                "VERSION_NAMES"          0 rows imported
. . importing table                 "VERSION_REFS"          0 rows imported
. . importing table                  "WS1_FSENTRY"          1 rows imported
Import terminated successfully without warnings.

Persisting Transaction Data

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

Create the database user and connection
Determine the database quota
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 Creating the Database User and Connection 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

Click Test to verify the connection. After testing, verify that the message Status: Success appears near the bottom of the dialog box.
Click Save to save the connection settings you have specified.

Configure the Connection

Click Connect, then expand the connection elements menu by clicking the plus sign next to the name of your connection on the Connections tab.
Right-click Other Users, then select Create User to display the Create/Edit User dialog box.
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 CONTEXT, CREATE ANY TABLE, CREATE ANY SEQUENCE
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 more details below]) and run the following command:

select sum(bytes) from user_segments;

details on using the sqlplus utility to run commands

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.
After the sqlplus command prompt opens, type: connect TRACKER/tracker@XE (in this example, TRACKER is the user and tracker is the password)
Run the command select sum(bytes) from user segments;
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/.

Database-Specific Configuration

Configuration File: `persistence.xml`

(Directory: <MMC_HOME>/WEB-INF/classes/META-INF/.)

In the directory listed above, locate the file persistence.xml.
Open the file for editing, and find the following line:

<property name= "openjpa.jdbc.DBDictionary" value= "batchLimit=-1" />

Replace the line with the following:

<property name= "openjpa.jdbc.DBDictionary" value= "oracle(DriverVendor=oracle)}" />

Configuration File: `applicationContext-tracking.xml`

(Directory: <MMC_HOME>/WEB-INF/classes/META-INF/.)

In the directory listed above, locate the file applicationContext-tracking.xml, then open it for editing.
Redefine the beans dataSource, jpavendorAdapter and entityManagerFactory, as listed below:

<bean id= "dataSource" class = "org.springframework.jdbc.datasource.DriverManagerDataSource" >
     <property name= "driverClassName" value= "oracle.jdbc.driver.OracleDriver" />
     <property name= "url" value= "jdbc:oracle:thin:@127.0.0.1:1521:xe" />
     <property name= "username" value= "TRACKER" />
     <property name= "password" value= "tracker" />
</bean>

<tx:annotation-driven transaction-manager= "transactionManager" />

<bean id= "transactionManager" class = "org.springframework.orm.jpa.JpaTransactionManager" >
     <property name= "entityManagerFactory" ref= "entityManagerFactory" />
</bean>

<bean id= "entityManagerFactory" class = "org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" >
     <property name= "dataSource" ref= "dataSource" />
     <property name= "persistenceUnitName" value= "tracking-persistence-unit" />

     <property name= "jpaVendorAdapter" >
         <bean id= "jpaAdapter" class = "org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter" >
             <property name= "database" value= "ORACLE" />
             <property name= "showSql" value= "true" />
         </bean>
     </property>

     <property name= "loadTimeWeaver" >
         <bean  class = "com.mulesoft.mmc.tracking.repository.jpa.OpenJPALoadTimeWeaver" />
     </property>
</bean>

In the output above, notice that the configuration specifies a database listening on localhost (IP 127.0.0.1), on the default port for Oracle (1521). This is defined in the line:

<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe" />

The value parameter takes the form <database driver specification>@<database server listening host>:<port>:<SID>. The SID, in this case xe, and the password (defined in the password property, in this example tracker) must correspond to the values you specified when creating the database connection and user.

Known Limitations

On persistence.xml the following property is set:

<property name= "openjpa.jdbc.SynchronizeMappings" value= "buildSchema(SchemaAction=add)" />

This specifies that new tables will be added as needed, but that if an essential change is performed, the older tables will not be removed, which may be cause for conflicts.

Using SchemaAction=refresh instead of SchemaAction=add in the above line will solve this problem, but will produce ORA-01031 Not enough privileges exceptions. This is caused by the system trying to drop everything that it does not directly use, including sequences on the XDB and SYS schemas, which are reserved by Oracle.

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:

Log in as the user who installed Oracle Database Express, then open a DOS terminal.
Check the status of the TNS Listener by running the following command: LSNRCTL STATUS
To stop the TNS Listener, run LSNRCTL STOP
To start the TNS Listener, run LSNRCTL START

On Unix/Linux:

Log in to the oracle system user, for example by running the command su - oracle.
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.
After setting environment variables, check the TNS listener status by running lsnrctl status
To stop the TNS listener, run lsnrctl stop
To start the TNS listener, run lsnrctl start

Persisting MMC Data to Oracle

Assumptions

Installing the Oracle Database

Tools

Installing SQL Developer

Installing Oracle Express

Configuration Files

Persisting Environment Data

Creating the Database User and Connection

Setting Up the Database Tables

Setting Up MMC to Use Oracle for Persisting Environment Data

Drivers

General Setup

Configuration File: mmc.properties

Configuration File: applicationContext.xml

Configuration File: jackrabbit-repo.xml

Configuration file: scheduler.properties

Disaster Recovery of Environment Data

Database Backup Procedure

Database Restore Procedure

Persisting Transaction Data

Creating the Database User and Connection

Verify and Save the Connection

Configure the Connection

Determining Database Quota

Setting Up MMC to Use Oracle for Persisting Transaction Data

Installing the Database Driver

Database-Specific Configuration

Configuration File: persistence.xml

Configuration File: applicationContext-tracking.xml

Known Limitations

Troubleshooting Tips

See Also

Configuration File: `mmc.properties`

Configuration File: `applicationContext.xml`

Configuration File: `jackrabbit-repo.xml`

Configuration file: `scheduler.properties`

Configuration File: `persistence.xml`

Configuration File: `applicationContext-tracking.xml`