ERROR: relation "qrtz_locks" does not exist.
Persisting MMC Data to Oracle
Mule Management Console (MMC) was deprecated in December 2015. Its End of Life is July 15, 2019. For more information see the MMC Migrator Tool or contact your Customer Success Manager to determine how you can migrate to Anypoint Runtime Manager. |
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.8.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, 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 If you are using a production installation in an application server such as Tomcat, the path should be similar to |
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:
-
Oracle Database Express Edition 11.x:
-
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
.
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:
|
Creating the Database User and Connection
To use the SQL Developer GUI to create the database user, complete the following steps:
-
Launch SQL Developer:
-
Navigate to
<sqldeveloper install>/sqldeveloper
-
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:
-
Display the New/Select Database connection dialog box. To do so, follow these steps:
-
Click the Connections tab in the left-hand pane.
-
Right-click Connections.
-
Select New Connec tion.
-
-
In the connection Name field, enter:
mmc_persistency_status
. -
In the Username field, enter:
SYSTEM
. -
In the Password field, enter the SYSTEM password that you issued during the Oracle Express installation process.
-
In the Hostname field, ensure that the hostname is correct (it will be
localhost
, if Oracle is installed on the local machine). -
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:
-
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:
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:
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 userMMC_STATUS
.
How to use the sqlplus utility to execute tables_oracle.sql
|
At this point, the Oracle database is completely defined.
Setting Up MMC to Use Oracle for Persisting Environment Data
-
First, install the appropriate Drivers.
-
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
, port1521
, 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
-
In the directory
<MMC_HOME>/WEB-INF
, locate the fileweb.xml
, then open it for editing. -
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>
-
Delete the string
env-derby
, then replace it withenv-oracle
, as shown below.
<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-oracle</param-value>
</context-param>
-
If you are also planning to Persisting Transaction Data to Oracle, delete the string
tracking-h2
and replace it withtracking-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 |
---|---|---|
|
Driver to use for connecting to the database |
|
|
Script to use for creating the tables in the target database |
|
|
Database user |
|
|
Password for the database user |
|
|
Hostname or IP address where the database server is listening |
|
|
Port where the database server is listening |
|
|
URL for connecting to the database |
|
|
Service name for connecting to the external database |
|
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:
|
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:
-
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 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
-
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 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 below) and run the following command:
select sum(bytes) from user_segments;
Details on using the sqlplus utility to run commands
|
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
-
In the directory
<MMC_HOME>/WEB-INF
, locate the fileweb.xml
, then open it for editing. -
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>
-
Delete the string
tracking-h2
, then replace it withtracking-oracle
, as shown below.
<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-oracle,env-derby</param-value>
</context-param>
-
If you are also planning to Persisting Environment Data to Oracle, delete the string
env-derby
and replace it withenv-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 |
---|---|---|
|
Type of database server to connect to |
|
|
Driver to use for connecting to the database |
|
|
Hostname or IP address where the database server is listening |
|
|
Port where the database server is listening |
|
|
URL for connecting to the database |
jdbc:oracle:thin:@${mmc.tracking.db.host}: ${mmc.tracking.db.port} /${mmc.tracking.db.servicename} |
|
Database user |
|
|
Password for the database user |
|
|
Service name for connecting to the external database |
|
|
Number of characters from a Business Events exception that will be stored in the tracking database. The maximum allowed is 261120. |
|
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:
-
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 commandsu - oracle
. -
Set the appropriate environment variables by performing these steps:
-
Navigate to the
bin
directory of the Oracle installation (typically,/u01/app/oracle/product/11.2.0/xe/bin
). -
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
See Also
-
Read more about MMC setup.
-
Review the Architecture of the Mule Management Console.