Oracle Messaging Gateway - MGW - Install and Setup

Install and setup Oracle Messaging Gateway - MGW

Messaging Gateway(MGW) is a feature in Oracle Database Enterprise Edition.  Messaging Gateway enables communication between applications based on non-Oracle messaging systems such as Java Message Service (Oracle JMS) ,WebSphere MQ Java Message Service (WebSphere MQ JMS),TIB/Rendezvous messages and Oracle Streams Advanced Queuing.

Here I will provide the steps I used to load and setup MGW in a existing Oracle Database.

Enviroment

OS: Oracle Enterprise Linux 7.2

DB: Oracle 12c Enterprise Edn.

Pre-Requisites

Check value of the JOB_QUEUE_PROCESSES database initialization parameter. It must be 1 or high.

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 required for Messaging Gateway. It also creates two roles, MGW_ADMINISTRATOR_ROLE and MGW_AGENT_ROLE, with certain privileges granted.

Modifying listener.ora for the External Procedure

LISTENER=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome_1)
      (SID_NAME = DB1)
    )
    (SID_DESC =
     (SID_NAME= mgwextproc)
    (ENVS="LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/jre/lib/amd64:/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/jre/lib/amd64/server:/u01/app/oracle/product/12.1.0.2/dbhome_1/lib")
     (ORACLE_HOM= /u01/app/oracle/product/12.1.0.2/dbhome_1)
     (PROGRAM= extproc)
    )
  )

Note: Set up the LD_LIBRARY_PATH environment needed for the external procedure to run. The LD_LIBRARY_PATH must contain the following paths:
JRE_HOME/lib/PLATFORM_TYPE
JRE_HOME/lib/PLATFORM_TYPE/server
ORACLE_HOME/lib

Modifying tnsnames.ora for the External Procedure

The 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.

MGW_AGENT=
(DESCRIPTION=
   (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))
   )
   (CONNECT_DATA=(SID=mgwextproc))
)

In sqlnet.ora set SQLNET.INBOUND_CONNECT_TIMEOUT =0 and restart the listener

Setting Up an mgw.ora Initialization 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 environment.

Creating an Oracle Messaging Gateway Administrator User

CREATE USER mgwadmin IDENTIFIED BY admin123;
GRANT CREATE SESSION to mgwadmin ;
GRANT MGW_ADMINISTRATOR_ROLE to mgwadmin ;

Creating an Oracle Messaging Gateway Agent User

CREATE USER mgwagent IDENTIFIED BY agent123;
GRANT CREATE SESSION to mgwagent ;
GRANT MGW_AGENT_ROLE to mgwagent ;

Configuring Oracle Messaging Gateway Connection Information

sqlplus /nolog

SQL> set echo off
SQL> set verify off
SQL> connect mgwadmin
Enter password:
Connected.

SQL> EXEC DBMS_MGWADM.ALTER_AGENT(agent_name => 'default_agent',username => 'mgwagent',password => 'agent123',database => 'DB1')

PL/SQL procedure successfully completed.

SQL>

Verifying the Oracle Messaging Gateway Setup

The following procedure verifies the setup and includes a simple startup and
shutdown of the Messaging Gateway agent:
1. Start the database listeners.
Start the listener for the external procedure and other listeners for the regular database connection.
2. Test the database connect string for the Messaging Gateway agent user.

Run sqlplus mgwagent/agent123@DB1.

If it is successful, then the Messaging Gateway agent can connect to the database.
3. Linux Operating System Only: Test the net service entry used to call the external
procedure.

Run sqlplus mgwagent/agent123@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.

4. Connect as admin_user and call DBMS_MGWADM.STARTUP to start the Messaging
Gateway agent.

sqlplus mgwadmin/admin123@DB1

SQL> exec DBMS_MGWADM.STARTUP

PL/SQL procedure successfully completed.

wait for AGENT_STATUS to change to RUNNING and AGENT_PING to change to REACHABLE
SQL> select agent_status , agent_ping from MGW_GATEWAY;