PART I: Setup

Reference

Oracle Procedural Gateway or Messaging Gateway?

Iinstall the Messaging Gateway

How Oracle Structure its Link from AQ to MQAgent

Install MQ client java binaries.

MGW Agent configuration file

Start the MGW Agent

Connecting the streams to MQ series is barely a configuration game. However, in version 10gR2 you will have to deal with quite a few traps, mainly due to inadequate documentations.

References

It is mandatory to read the section of "Advanced Queuing User's Guide and Reference, Part V" which deals with External links to AQ. It spans from chapter 17 to chapters 22

Note:212587.1 Configuring Oracle Messaging Gateway on Unix Platforms

Note:188833.1 Configuring Oracle Messaging Gateway on Windows Platforms

Note:198523.1 External Procedure Calls and ORA-28595 In Versions 9.2 through 10gR2

Note:252816.1 Use of ENVS, LD_LIBRARY_PATH, EXTPROC_DLLS=ANY in external procedures

The note 18833.1on windows contains also information you need need for Unix (mainly the section about the tnsnames.ora)

Oracle Procedural Gateway or Messaging Gateway?

So, you will go PGM or MGW ?

Read the Note:412116.1 Differences Between Oracle Procedural Gateway For IBM Websphere MQ (MQSeries) and Oracle Messaging Gateway
it will probably answer all questions you may have. In short, if you have already licensed Advance Queuing (it is included in EE) , MGW is the way to go, as Oracle Messaging Gateway is already included in the license of AQ. If you don't have AQ then you may opt add PGM license to your Standard Edition. For the rest of this part we will deal only with the messaging gateway as AQ is included into Streams and you need Enterprise Edition to run Streams.

Install the Messaging Gateway

When you follow the messaging gateway install procedure, you find In the Directory ORACLE_HOME/mgw/admin the master installation gateway script : 'catmgw.sql'. (To remove the installation run 'catnomgw.sql')

Connect into SYS as sysdba and run this script.

@catmgw.sql

Create a Messaging Gateway Administrator User

create user MQADMIN identified by MQADMIN ;
grant create session to mqadmin;
grant connect, resource to mqadmin;
grant mgw_administrator_role to mqadmin;

Create a Messaging Gateway Agent User

create user MQAGENT identified by MQAGENT ;
grant create session to mqagent;
grant connect, resource to mqagent;
grant mgw_agent_role to mqagent;

Create a Messaging Gateway User

Create user MQUSER identified by MQUSER ;
grant create session to mquser;
grant connect, resource to mquser;
grant execute on dbms_aq to mquser;
grant execute on dbms_aqadm to mquser;

Configure Oracle Messaging Gateway Connection Information

Connect mqadmin/mqadmin
exec dbms_mgwadm.db_connect_info('MQAGENT','MQAGENT','WLINT2');

Now you need to setup the listener.ora and the tnsnames.ora. to complete the chain that links the library reference into Oracle to the physical binaries and libraries

Listener.ora

(replace [oracle_home] by your full path of oracle_home)

SID_LIST_LISTENER_DWLPDB02 =
(SID_LIST =
   (SID_DESC =
     (SID_NAME = mgwextproc)
     (ORACLE_HOME = /oraapp01/app/oracle/product/RDBMS/102030/RAC)
     (PROGRAM = extproc32)
     (ENVS="EXTPROC_DLLS=ANY,LD_LIBRARY_PATH =[oracle_home]/jre/1.4.2/lib/i386:
                                                                                            [oracle_home]/jre/1.4.2/lib/i386/server:
                                              [oracle_home]/lib32")
    )
)

LISTENER_DWLPDB02 =
   (DESCRIPTION_LIST =
       (DESCRIPTION =
       (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
       (ADDRESS = (PROTOCOL = TCP)(HOST = dwlpdb02-vip.unix.banksys.be)(PORT = 1531)(IP = FIRST))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1532)(IP = FIRST))
       (ADDRESS = (PROTOCOL = TCP)(HOST = dwlpdb02.unix.banksys.be)(PORT = 1531)(IP = FIRST))
   )
)
)

At this stage, if you are in Linux 64 bit, you must differ in 2 points for the official install instruction

Since Messaging gateway, as of this time of writing, exists only as 32 bit binaries on Linux. So you must replace '(PROGRAM = extproc)' with '(PROGRAM = extproc32)'. Note how the install procedure sets in LD_LIBRARY_PATH lib32 instead of lib

The documentation state to set ENVS parameter to (ENVS=LD_LIBRARY_PATH....) . However it turns out that Oracle cannot load a library outside of ORACLE_HOME/lib unless you disable the name validation check. You do this using the parameters (ENVS=EXTPROC_DLLS=ANY,LD_LIBRARY_PATH=....")

Beside these 2 points, the important thing to note in this listener.ora is the (KEY=EXTPROC), we will come back to it later

Tnsnames.ora

Add the following entry into your tnsnames.ora

MGW_AGENT , MGW_AGENT.WLINT2.UNIX.BANKSYS.BE =
   (DESCRIPTION=
   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY= EXTPROC)))
   (CONNECT_DATA= (SID=mgwextproc)(PRESENTATION=RO))
)

The name 'MGW_AGENT' is not negotiable as it is the Agent of the library 'libmgwagent.so'. Hum.... I hear from here your question : what is it an agent of a library

How Oracle Structure its Link from AQ to MQAgent

Sending Messages outside AQ requires a external binary that will makes the links between Oracle and the vendor software. This external library is referenced into DBA_LIBRARY together with a TNS entry name which is not visible into this view! This TNS entry is called 'Agent' (probably to better confuse the enemy). Here is how you declare a reference to an external library in Oracle

    Create or replace library 'my_ful_path/library_name.so' AGENT 'a_tns_entry' ;

If you omit the clause AGENT, the default Agent/TNS entry name is EXTPROC_CONNECT_DATA and point toward the default KEY value EXTPROC. It is often skipped as the sole purpose of this mechanism is to find the proper entry of the proper listener and if you use the default you inherit the default KEY value which is EXTPROC. At the end of this puzzle Oracle just want some additional information's on the wrapper that will load the external library and this wrapper location is defined in the listener.ora.

Do not mix the agent of the library with the external procedure agent:

     The agent of the library is just a TNS entry where you find the string KEY=[a name] and the default is KEY=EXTPROC, the same entry you find in the listener.ora. It is the name after the clause AGENT of create library

     The external procedure agent is spawned by the listener.ora and its name is given by the clause PROGRAM= in the listener.ora. It receives the name of the shared library, the name of the function to invoke, performs the required conversions from PL/SQL to the shared library language datatypes, and finally receive the actual arguments. The external procedure agent loads the shared library, invokes the appropriate function, converts any returned data from another language datatypes to the corresponding PL/SQL types, and sends the data back to the database.

In messaging Gateway for MQ series we do not use these DEFAULT, so let's follow the path as it is to better understand how Oracle makes the links to the external binaries. From within Oracle AQ there is a call to the external library. The agent of the library is MGW_AGENT. The name MGW_AGENT comes from ORACLE_HOME/mgw/admin/mgwlib.sql, an sql file called by 'catmgw.sql'. Open it and you will find the sql for the creation of the library libmgwagent.so.:

v_statement := 'create or replace library dbms_mgwadm_agent_lib as ' ||
                           '''${ORACLE_HOME}/lib32/libmgwagent.so'' agent ''MGW_AGENT''';
Which you produce the following

create or replace library bms_mgwadm_agent_lib as '${ORACLE_HOME}/lib32/libmgwagent.so' agent 'MGW_AGENT';

Now that we have this name 'MGW_AGENT' let's look again to the corresponding entry in tnsnames.ora :

MGW_AGENT , MGW_AGENT.WLINT2.UNIX.BANKSYS.BE =
    (DESCRIPTION=
    (ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY= EXTPROC)))
    (CONNECT_DATA= (SID=mgwextproc)(PRESENTATION=RO))
)

The TNS entry as 2 key value in it :

         (KEY= EXTPROC)            <---- locate the right LISTENER section
    (SID=mgwextproc)          <---- locate the right SID_DESC in the LISTENER_SID_LIST of the right LISTENER

These values EXTPROC will point to the right listener. If you have 2 listener defined with twice the entry

             (PROTOCOL = IPC)(KEY = EXTPROC)

You may fool one of the two depending the order they appear in your file listener.ora.

LISTENER_DWLPDB02 =
     (DESCRIPTION_LIST =
         (DESCRIPTION =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1532)(IP = FIRST))
         )
    )
)

So using the agent library, Oracle found the TNS entry, picked the KEY value, matched the right listener. Now having fetches the LISTENER_SID_LIST and uses again the SID_NAME value it will find the right SID_DESC section :

SID_LIST_LISTENER_DWLPDB02 =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = mgwextproc)
         (ORACLE_HOME = /oraapp01/app/oracle/product/RDBMS/102030/RAC)
         (PROGRAM = extproc32)
         (ENVS="EXTPROC_DLLS=ANY,LD_LIBRARY_PATH =[oracle_home]/jre/1.4.2/lib/i386:
                                                                                            [oracle_home]/jre/1.4.2/lib/i386/server:
                                              [oracle_home]/lib32")
        )
    )
)

Everything here is relevant :

SID_NAME points to the right SID_DESC, its value was originally found in the TNS entry pointed to by the agent library

PROGRAM point to the wrapper that will load t the external library and pass the parameters and output the results

ORACLE_HOME is the root path where the PROGRAM is to be found

ENVS point to additional directories where other libraries declared into the initial external library (the one declared into DBA_LIBRARY.FILE_SPEC are to be found)
Figure showing the construct:

More about all this in Note:70638.1 External Procedures - Troubleshooting ORA-28575 Errors

Install MQ client java binaries.

All you need is to install a set of MQ jar file: here is the list that install client as put into my /opt/mqm/java/lib directory

[dwlpdb02:WLINT21]/opt/mqm/java/lib> ls -l
total 5996
-r--r--r-- 1 mqm mqm 13143 Oct 21 2005 CL3Export.jar
-r--r--r-- 1 mqm mqm 33166 Oct 21 2005 CL3Nonexport.jar
-r--r--r-- 1 mqm mqm 426532 Oct 21 2005 com.ibm.mq.jar
-r--r--r-- 1 mqm mqm 1234881 Oct 21 2005 com.ibm.mqjms.jar
-r--r--r-- 1 mqm mqm 18901 Oct 21 2005 com.ibm.mq.jms.Nojndi.jar
-r--r--r-- 1 mqm mqm 134453 Oct 21 2005 com.ibm.mq.soap.jar
-r-xr-xr-x 1 mqm mqm 275436 Oct 21 2005 commonservices.jar
-r--r--r-- 1 mqm mqm 17978 Oct 21 2005 connector.jar
-r--r--r-- 1 mqm mqm 2101623 Oct 21 2005 dhbcore.jar
-r--r--r-- 1 mqm mqm 22769 Oct 21 2005 fscontext.jar
-r--r--r-- 1 mqm mqm 25998 Oct 21 2005 jms.jar
-r--r--r-- 1 mqm mqm 98496 Oct 21 2005 jndi.jar
-r--r--r-- 1 mqm mqm 8809 Oct 21 2005 jta.jar
-r--r--r-- 1 mqm mqm 123717 Oct 21 2005 ldap.jar
-r-xr-xr-x 1 mqm mqm 28494 Oct 21 2005 libmqjexitstub01.so
-r--r--r-- 1 mqm mqm 445504 Oct 21 2005 postcard.jar
-r--r--r-- 1 mqm mqm 77116 Oct 21 2005 providerutil.jar
-r--r--r-- 1 mqm mqm 887237 Oct 21 2005 rmm.jar
dr-xr-xr-x 2 mqm mqm 4096 Apr 21 11:35 soap

MGW Agent configuration file

Mgw has a small configuration file, in $ORACLE_HOME/mgw/admin/mgw.ora. Normally there is only one variable that needs to be set and this is the CLASSPATH:

set CLASSPATH= [oracle_home]/jre/1.4.2/lib/rt.jar:[oracle_home]/jdbc/lib/ojdbc14.jar:[oracle_home]/jlib/orai18n.jar: [oracle_home]/sqlj/lib/runtime12.jar:[oracle_home]/rdbms/jlib/jmscommon.jar:[oracle_home]/rdbms/jlib/aqapi13.jar: [oracle_home]/jlib/jta.jar:/opt/mqm/java/lib/com.ibm.mqjms.jar:/opt/mqm/java/lib/com.ibm.mq.jar
:opt/mqm/java/lib:/opt/mqm/java/lib/connector.jar

So beside adding some jar file from Oracle Home, then we add only 3 jar plus the java/lib from MQ client :

/opt/mqm/java/lib/com.ibm.mqjms.jar
/opt/mqm/java/lib/com.ibm.mq.jar
/opt/mqm/java/lib
/opt/mqm/java/lib/connector.jar

Start the MGW Agent:

Now that the Agent is configured, you need to start if : In RAC environment the agent is started only from one node, so you add the indice of the Instance that run it To have it run on instance number 1

          exec dbms_mgwadm.startup(1) ;

Your agent looks like

[dwlpdb02:WLINT21]> ps -ef |grep mgw
oracle 8403 1 0 12:04 ? 00:00:04 extproc32mgwextproc (LOCAL=NO)