Demonstration of configuring Oracle Database 11gR2 with IBM Websphere MQ series v7
1. Install IBM Websphere MQ client on DB server. We installed recent version 7.
2. Loading Database Objects into the Database
Using SQL*Plus, run ORACLE_HOME/mgw/admin/catmgw.sql as user SYS as SYSDBA. This script loads the database objects necessary for Messaging Gateway, including roles, tables, views, object types, and PL/SQL packages. It creates public synonyms for Messaging Gateway PL/SQL packages. It creates two roles, MGW_ADMINISTRATOR_ROLE and MGW_AGENT_ROLE, with certain privileges granted. All objects are owned by SYS.
3. Setting Up for WebSphere MQ Base Java or JMS
The WebSphere MQ client and WebSphere MQ classes for Java and JMS must be installed on the computer where the Messaging Gateway agent runs. In this section MQ_HOME refers to the location of the installed client. On the Linux operating system, this location is always /opt/mqm.
For our case, Websphere MQSeries V7 has built in MQ base java & JMS classes. So we didn’t need to worry about it. For later versions, these two options needs to be installed seperatedly.
4. Creating an Oracle Messaging Gateway Administration User
To perform Messaging Gateway administration work, a database user must be created with MGW_ADMINISTRATOR_ROLE privileges
5. Creating an Oracle Messaging Gateway Agent User
To establish the Messaging Gateway agent connection back to the database, a database user with MGW_AGENT_ROLE privileges must be created/
6. Configuring Oracle Messaging Gateway Connection Information
After the Messaging Gateway agent user is created, the administration user uses DBMS_MGWADM.ALTER_AGENT to configure Messaging Gateway with the username, password, and database connect string used by the Messaging Gateway agent to connect back to the database. Use the Messaging Gateway username and password that you created. The database connect string parameter can be set to either a net service name in tnsnames.ora (with IPC protocol for better performance) or NULL. If NULL, then the oracle_sid parameter must be set in mgw.ora.
For this release, always specify a not NULL value for the database connect string parameter when calling DBMS_MGWADM.ALTER_AGENT.
7. Modifying listener.ora for the External Procedure
You must modify listener.ora so that the Messaging Gateway PL/SQL packages can call the external procedure.
8. Modifying tnsnames.ora for the External Procedure
For the external procedure, configure a net service name
tnsnames.orawhose connect descriptor matches the information configured in
The net service name must be
MGW_AGENT(this value is fixed). The
KEYvalue must match the
KEYvalue specified for the IPC protocol in
listener.ora. The SID value must match the value specified for
Here is an example:
9. Setting Up a mgw.ora Initialization File
The Messaging Gateway default initialization file ORACLE_HOME/mgw/admin/mgw.ora is a text file. The Messaging Gateway external procedure uses it to get initialization parameters to start the Messaging Gateway agent. Copy ORACLE_HOME/mgw/admin/sample_mgw.ora to mgw.ora and modify it according to your situation.
10. Verifying the Oracle Messaging Gateway Setup
The following procedure verifies the setup and includes a simple startup and shutdown of the Messaging Gateway agent:
a. Start the database listeners.
b. Start the listener for the external procedure and other listeners for the regular database connection.
c. Test the database connect string for the Messaging Gateway agent user.
If it is successful, then the Messaging Gateway agent is able to connect to the database.
This should fail with "ORA-28547: connection to server failed, probable Oracle Net admin error". Any other error indicates that the tnsnames.ora, listener.ora, or both are not correct.
d. Connect as admin_user and call DBMS_MGWADM.STARTUP to start the Messaging Gateway agent.