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.
Download Location:
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
CREATE USER admin_user IDENTIFIED BY admin_password;
GRANT CREATE SESSION to admin_user;
GRANT MGW_ADMINISTRATOR_ROLE to admin_user;
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/
CREATE USER agent_user IDENTIFIED BY agent_password;
GRANT CREATE SESSION to agent_user;
GRANT MGW_AGENT_ROLE to agent_user;
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.
EXEC DBMS_MGWADM.ALTER_AGENT( agent_name => 'default_agent', username => 'agent_user', password => 'agent_password', database => 'TESTDB');
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.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTHOST)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)))
# Add a SID_DESC
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC =
(SID_NAME= mgwextproc)
(ENVS="LD_LIBRARY_PATH=/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/sparcv9/server:/app/oracle/oradbms/eedb/11.2/lib")
(ORACLE_HOME=/app/oracle/oradbms/eedb/11.2)
(PROGRAM = extproc))
)
8. Modifying tnsnames.ora for the External Procedure
For the external procedure, configure a net service name
MGW_AGENT in tnsnames.ora whose connect descriptor matches the information configured in listener.oraThe net service name must be
MGW_AGENT (this value is fixed). The KEY value must match the KEY value specified for the IPC protocol in listener.ora. The SID value must match the value specified for SID_NAME of the SID_DESC entry in listener.ora.Here is an example:
MGW_AGENT =
(DESCRIPTION=
(ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC)))
(CONNECT_DATA= (SID=mgwextproc) (PRESENTATION=RO)))
AGENT_SERVICE =
(DESCRIPTION =
(ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC)))
(CONNECT_DATA= (SID=TESTDB)(PRESENTATION=RO)))
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.
# If a value is not specified, the default will be used
log_directory=/app/oracle/oradbms/eedb/11.2/mgw/log
# Set logging level to logging only. Note: whitespace is OK
log_level = 3
# set the LD_LIBRARY_PATH we need for the MGW Agent (UNIX only)
set LD_LIBRARY_PATH=/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/sparc:/app/oracle/oradbms/eedb/11.2/rdbms/lib:/app/oracle/oradbms/eedb/11.2/lib:/opt/mqm/java/lib:/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/sparc/
# MGW_PRE_PATH will be prepended to the PATH inherited
# by the MGW external process
# WINDOWS_NT only: This variable must be set to indicate where the library
# jvm.dll may be found.
#set MGW_PRE_PATH=\myOracleHome\jdk\jre\bin\classic
# set the CLASSPATH we need for the MGW Agent
set CLASSPATH=/app/oracle/oradbms/eedb/11.2/jdbc/lib/ojdbc5.jar:/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/i18n.jar:/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/rt.jar:/app/oracle/oradbms/eedb/11.2/sqlj/lib/runtime12.jar:/app/oracle/oradbms/eedb/11.2/sqlj/lib/translator.jar:/app/oracle/oradbms/eedb/11.2/jlib/orai18n.jar:/app/oracle/oradbms/eedb/11.2/jlib/jta.jar:/app/oracle/oradbms/eedb/11.2/rdbms/jlib/jmscommon.jar:/app/oracle/oradbms/eedb/11.2/rdbms/jlib/aqapi.jar:/opt/mqm/java/lib/com.ibm.mq.jar:/opt/mqm/java/lib/com.ibm.mqjms.jar:/opt/mqm/java/lib/connector.jar:/opt/mqm/java/lib/jms.jar:/opt/mqm/java/lib/jndi.jar:/opt/mqm/java/lib/jta.jar:/opt/mqm/java/lib/connector.jar:/opt/mqm/java/lib:/opt/mqm/java/lib/com.ibm.mqbind.jar:/opt/mqm/java/lib/com.ibm.jms.jar
# ensure that ORACLE_SID is set in case we don't want to specify a service
# name
set ORACLE_SID=TESTDB
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.
sqlplus agent_user/agent_password@agent_database.
If it is successful, then the Messaging Gateway agent is able to connect to the database.
Run sqlplus agent_user/agent_password@MGW_AGENT.
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.
exec dbms_mgwadm.shutdown
exec dbms_mgwadm.startup
select agent_status,agent_ping from mgw_gateway;
AGENT_STATUS | AGENT_PING |
RUNNING | REACHABLE |
No comments:
Post a Comment