MGW

Oracle Messaging Gateway

Check

Is MGW Running?

Is MGW Installed?

Agent User Configuration

COLUMN agent_database FORMAT a30

SELECT agent_user,

       agent_database,

       agent_name

  FROM mgw_gateway;

Links

SELECT link_name,

       link_type,

       agent_name

  FROM mgw_links;


SET LINESIZE 200 PAGESIZE 50

COLUMN queue_manager FORMAT a30

COLUMN channel FORMAT a40

SELECT link_name,

       queue_manager,

       channel,

       hostname,

       port,

       interface_type

  FROM mgw_mqseries_links;

Foreign queues

SET LINESIZE 200

SET PAGESIZE 50

COLUMN provider_queue FORMAT a50

SELECT name,

       link_name,

       provider_queue 

  FROM MGW_FOREIGN_QUEUES;

Propagation Jobs

SET LINESIZE 300

SET PAGESIZE 50

COLUMN destination FORMAT a40

SELECT job_name,

       propagation_type,

       source,

       destination,

       enabled,

       agent_name,

       link_name,

       status

  FROM mgw_jobs;

Check Role Grants

Who is Granted MGW_AGENT_ROLE/MGW_ADMINISTRATOR_ROLE?

SELECT grantee,

       granted_role

  FROM dba_role_privs

 WHERE granted_role LIKE 'MGW%';

Start

CONNECT admin_user

EXEC DBMS_MGWADM.STARTUP


Messaging Gateway uses a Scheduler job to start the Messaging Gateway agent. This job is created when procedure DBMS_MGWADM.STARTUP is called. When the job is run, it calls an external procedure that creates the Messaging Gateway agent in an external process. The job is removed after:
The agent shuts down because DBMS_MGWADM.SHUTDOWN was calledThe agent terminates because a non-restartable error occursThe Scheduler job used by the default agent is SYS.MGW_JOB_DEFAULT_AGENT.The Scheduler job used by a named agent is SYS.MGW_JOB_<agent_name>.
If the agent job encounters an error, then the error is classified as either a restartable error or non-restartable error. A restartable error indicates a problem that might go away if the agent job were to be restarted. A non-restartable error indicates a problem that is likely to persist and be encountered again if the agent job restarts. ORA-01089 (immediate shutdown in progress) and ORA-28576 (lost RPC connection to external procedure) are examples of restartable errors. ORA-06520 (error loading external library) is an example of a non-restartable error.
Messaging Gateway uses a database shutdown trigger (called MGW_SHUTDOWN_TRIG). If the Messaging Gateway agent is running on the instance being shut down, then the trigger notifies the agent of the shutdown, and upon receipt of the notification, the agent will terminate the current run. The job scheduler will automatically schedule the job to run again at a future time.
If a Messaging Gateway agent job instance ends because of a database malfunction or a restartable error detected by the agent job, then the job will not be removed and the job scheduler will automatically schedule the job to run again at a future time.
https://docs.oracle.com/cd/E11882_01/server.112/e11013/mg_work.htm#ADQUE3292 

Stop

CONNECT admin_user

EXEC DBMS_MGWADM.SHUTDOWN

Troubleshooting

When the Messaging Gateway agent fails to convert a message from the source format to the destination format, the agent moves the message from the source queue to an exception queue.

If the Messaging Gateway agent runs into failures when processing a propagation job, it retries up to sixteen times in an exponential backoff scheme (from two seconds up to thirty minutes) before it stops retrying.

Logging

The default location for the log file is (it can be overridden via a setting in MGW.ORA):

$ORACLE_HOME/mgw/log

The default logging level is set in the MGW.ORA file (in normal circumstances this should always be 0 in the MGW.ORA file). The logging level can be dynamically changed by calling DBMS_MGWADM.SET_LOG_LEVEL while the Messaging Gateway agent is running.

Check Invalid MGW Objects

SELECT owner,

       object_name,

       object_type 

  FROM dba_objects 

 WHERE status = 'INVALID'

   AND object_name like '%MGW%'; 

ORA-28575: unable to open RPC connection to external procedure agent

Are you sure the listener was started using the correct config files? Check TNS_ADMIN. Set correctly and restart Listener and MGW if necessary.

ORA-28576: lost RPC connection to external procedure agent

Possibly due to the setting of SQLNET.INBOUND_CONNECT_TIMEOUT.

See: 2773657.1, 1356971.1

Update SQLNET.ORA.
Recommended settings are: 0, 60, or remove the paramter altogether (default is 60).
Reload listener (lsnrctl reload)

ORA-32830: result code -101 returned by Messaging Gateway agent

Are you sure the listener was started using the correct config files? Check TNS_ADMIN. Set correctly and restart Listener and MGW if necessary.

ORA-32846: Messaging Gateway agent cannot be started; status is BROKEN

CONNECT admin_user/admin_password

EXEC DBMS_MGWADM.SHUTDOWN;

EXEC DBMS_MGWADM.CLEANUP_GATEWAY(DBMS_MGWADM.CLEAN_STARTUP_STATE);

Terminate any Messaging Gateway agent process that may still be running after a CLEAN_STARTUP_STATE action has been successfully performed. This should be done before calling DBMS_MGWADM.STARTUP to start Messaging Gateway. The process is usually named extprocmgwextproc

exec dbms_mgwadm.startup;

AGENT_STATUS = RUNNING / AGENT_PING = UNREACHABLE

CONNECT admin_user/admin_password

EXEC DBMS_MGWADM.SHUTDOWN(DBMS_MGWADM.FORCE);

EXEC DBMS_MGWADM.CLEANUP_GATEWAY(DBMS_MGWADM.CLEAN_STARTUP_STATE);

Terminate any Messaging Gateway agent process that may still be running after a CLEAN_STARTUP_STATE action has been successfully performed. This should be done before calling DBMS_MGWADM.STARTUP to start Messaging Gateway. The process is usually named extprocmgwextproc.

EXEC DBMS_MGWADM.STARTUP;

AGENT_STATUS stays at START_SCHEDULED

SHOW PARAMETER job_queue_processes

MGW needs a non zero value for JOB_QUEUE_PROCESSES

com.ibm.mq.MQException

Refrer to the Websphere MQ Troubleshooting section here.

[610]  Agent has been identified as a rogue and will be shut down.

oracle.mgw.admin.MgwAdminException: [610]  Agent has been identified as a rogue and will be shut down.

See 1244874.1. This suggests a timeout issue.

Comparison method violates its general contract!

If the Messaging Gateway starts successfully but crashes soon after, and you see this in the log file...

java.lang.IllegalArgumentException: Comparison method violates its general contract!

.. it is likely you are running into Sun Java Bug 7075600 .

You can workaround this bug by adding this to your mgw.ora file...

setJavaProp java.util.Arrays.useLegacyMergeSort=true

MOS Note 1507950.1 implies  that this only affects Java 7, but this workaround has worked for the IBM build of 1.8.0_231 on AIX

AMQ9208: Error on receive from host...

Check status of the host specified in the error message. If the host (or the MQ process on that host) is unreachable then it is likely to cause MGQ to fail.

[Linked-exception]com.ibm.mq.MQException: MQJE001: Completion Code '2', Reason '2009'.        at com.ibm.mq.MQQueueManager.backout(MQQueueManager.java:1713)        at oracle.mgw.drivers.mq.MQOPHandle.rollback(MQOPHandle.java:137)        at oracle.mgw.drivers.mq.MQOPHandle.close(MQOPHandle.java:162)        at oracle.mgw.drivers.OPHandlePool.destroyHandles(OPHandlePool.java:336)        at oracle.mgw.drivers.mq.MQLink.closeResetHandlesFactories(MQLink.java:1381)        at oracle.mgw.drivers.BaseLink.closeLink(BaseLink.java:693)        at oracle.mgw.drivers.BaseLink.close(BaseLink.java:650)        at oracle.mgw.drivers.mq.MQLink.close(MQLink.java:271)        at oracle.mgw.engine.MsgLinkMgr.shutdown(MsgLinkMgr.java:170)        at oracle.mgw.engine.Agent.shutdown(Agent.java:351)        at oracle.mgw.engine.Agent.start(Agent.java:172)        at oracle.mgw.engine.Agent.agentMain(Agent.java:767)Caused by: com.ibm.mq.jmqi.JmqiException: CC=2;RC=2009        at com.ibm.mq.jmqi.remote.internal.RemoteHconn.enterCall(RemoteHconn.java:481)        at com.ibm.mq.jmqi.remote.internal.RemoteHconn.enterCall(RemoteHconn.java:403)        at com.ibm.mq.jmqi.remote.internal.RemoteHconn.enterCall(RemoteHconn.java:378)        at com.ibm.mq.jmqi.remote.internal.RemoteFAP.MQBACK(RemoteFAP.java:9120)        at com.ibm.mq.MQSESSION.MQBACK(MQSESSION.java:737)        at com.ibm.mq.MQQueueManager.backout(MQQueueManager.java:1710)        ... 11 moreCaused by: com.ibm.mq.jmqi.JmqiException: CC=2;RC=2009;AMQ9208: Error on receive from host 'MYMQHOST/10.170.1.10:1451 (MYMQHOST)'. [1=-1,2=ffffffff,3=MYMQHOST/10.170.1.10:1451 (MYMQHOST),4=TCP]        at com.ibm.mq.jmqi.remote.internal.RemoteRcvThread.receiveBuffer(RemoteRcvThread.java:805)        at com.ibm.mq.jmqi.remote.internal.RemoteRcvThread.receiveOneTSH(RemoteRcvThread.java:745)        at com.ibm.mq.jmqi.remote.internal.RemoteRcvThread.run(RemoteRcvThread.java:157)        at java.lang.Thread.run(Thread.java:818)

MGW/MQ Certification Matrix

NOTE: Native Websphere MQ integration works with Queues (i.e. point-to-point) not Topics (i.e. publish/subscribe). If you need to use Topics you should consider using JMS rather than native integration.Info in following matrix taken from "On what Platforms and Versions of Oracle is the Oracle Messaging Gateway available? (Doc ID 219410.1)"
MGW-MQ-Matrix

Initial Setup

JOB _QUEUE_PROCESSES

The value of the JOB_QUEUE_PROCESEES paramter defines the maximum number of Scheduler jobs and job queue jobs that can run concurrently. To use the Messaging Gateway it must be non-zero.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=20 SCOPE=BOTH;

Install MGW objects

cd $ORACLE_HOME/mgw/admin

sqlplus / as sysdba

@catmgw.sql

LISTENER.ORA, TNSNAMES.ORA and MGW.ORA

See the example stanzas.

Create Users

Messaging Gateway Administrator User

CREATE USER &admin_user IDENTIFIED BY &admin_password;

GRANT CREATE SESSION to &&admin_user;

GRANT MGW_ADMINISTRATOR_ROLE to &&admin_user;

Messaging Gateway Agent User

Note: Perhaps counter-intuitively, the Agent User is in some ways more powerful than the Admin User

CREATE USER &agent_user IDENTIFIED BY &agent_password;

GRANT CREATE SESSION to &&agent_user;

GRANT MGW_AGENT_ROLE to &&agent_user;

MGW_ADMINISTRATOR_ROLE

The MGW_ADMINISTRATOR_ROLE gives the user:

This Role:

The ability to Execute:

The ability to Select from:

MGW_AGENT_ROLE

The MGW_AGENT_ROLE gives the user:

These Roles:

The ability to Execute:

The ability to Select and Update ANY TABLE

The ability to Select and Update these objects:

The ability to Select, Update and Delete from:

The ability to DEQUEUE, ENQUEUE, SELECT and UPDATE:

The ability to ENQUEUE and DEQUEUE:

The ability to Select on these objects:

Configure Agent User

set echo off

set verify off

connect admin_user

 

ACCEPT password CHAR PROMPT 'Enter the password for AGENT_NAME: ' HIDE

ACCEPT password CHAR PROMPT 'Enter the password for AGENT_USER: ' HIDE

ACCEPT password CHAR PROMPT 'Enter the password for AGENT_PASS: ' HIDE

ACCEPT password CHAR PROMPT 'Enter the password for AGENT_DB  : ' HIDE


EXEC DBMS_MGWADM.ALTER_AGENT(

      agent_name  => '&default_agent', 

      username    => '&agent_user', 

      password    => '&password', 

      database    => '&agent_database');

Recommended Settings

agent_name  => 'DEFAULT_AGENT'

This is the default agent name

username => 'AGENT_USER'

Created above

password => 'password'

Any complex password

database => 'MGW_AGENT'

As defined in TNSNAMES.ORA

Using the Messaging Gateway

Links

Configuring a WebSphere MQ Base Java Link

DECLARE

  v_options sys.mgw_properties;

  v_prop sys.mgw_mqseries_properties;

BEGIN

  v_prop := sys.mgw_mqseries_properties.construct();

  

  v_prop.interface_type := dbms_mgwadm.MQSERIES_BASE_JAVA_INTERFACE;

  v_prop.max_connections := 1;

  v_prop.username := 'mqm';

  v_prop.password := 'mqm';

  v_prop.hostname := 'myhost.mydomain';

  v_prop.port     := 1414;

  v_prop.channel  := 'mychannel';

  v_prop.queue_manager := 'my.queue.manager';

  v_prop.outbound_log_queue := 'mylogq';

  

  dbms_mgwadm.create_msgsystem_link(linkname   => 'mqlink',

                                    agent_name =>'default_agent',

                                    properties => v_prop,

                                    options    => v_options );

END;

Removing Links

BEGIN

  dbms_mgwadm.remove_msgsystem_link(linkname =>'mqlink');

END;

Foreign Queues

Register (Base Java Websphere MQ)

BEGIN

  DBMS_MGWADM.REGISTER_FOREIGN_QUEUE(

    name            => 'destq',

    linkname        => 'mqlink',

    provider_queue  => 'my_mq_queue',

    domain          => dbms_mgwadm.DOMAIN_QUEUE);

END;

The domain must be DBMS_MGWADM.DOMAIN_QUEUE or NULL, because only point-to-point queues are supported for WebSphere MQ.

De-register

BEGIN

  DBMS_MGWADM.UNREGISTER_FOREIGN_QUEUE(name =>'destq', linkname=>'mqlink');

END;

Propagation Jobs

Create (Outbound)

BEGIN

   DBMS_MGWADM.CREATE_JOB(

     job_name            =>  'job_aq2mq',

     propagation_type    =>   DBMS_MGWADM.OUTBOUND_PROPAGATION,

     source              =>  'mquser.srcq',

     destination         =>  'deqq@mqlink');

END;

Enable Job

By default a job is Enabled when created. If it has been Disabled you can re-enable using...

BEGIN

  DBMS_MGWADM.ENABLE_JOB(job_name => 'job_aq2mq');

END;

Disable Job

BEGIN

  DBMS_MGWADM.DISABLE_JOB(job_name => 'job_aq2mq');

END;

Reset Job

When a problem occurs with a propagation job, the Messaging Gateway agent retries the failed operation up to 16 times in an exponential backoff scheme before the propagation job stops. You can use DBMS_MGWADM.RESET_JOB to reset the failure count to zero to allow the agent to retry the failed operation immediately.

https://docs.oracle.com/database/121/ADQUE/mg_work.htm#ADQUE3333

BEGIN

  DBMS_MGWADM.RESET_JOB (job_name => 'job_aq2mq');

END;

Remove Job

BEGIN

    DBMS_MGWADM.REMOVE_JOB (job_name  =>  'job_aq2mq');

END;

For a full description of the what removing a job does, please see: https://docs.oracle.com/database/121/ADQUE/mg_work.htm#ADQUE3336

Remove Messaging Gateway

If you want to remove MGW from your database...

CONNECT admin_user

EXEC DBMS_MGWADM.SHUTDOWN

Remove any user-created queues whose payload is a Messaging Gateway canonical type (for example, SYS.MGW_BASIC_MSG_T).

sqlplus / as sysdba

@ORACLE_HOME/mgw/admin/catnomgw.sql

This drops the database objects used by Messaging Gateway, including roles, tables, views, packages, object types, and synonyms.

Remove entries for Messaging Gateway created in listener.ora and tnsnames.ora.

Concepts

IDAP = Internet Data Access Presentation which is the SOAP specification for Oracle Database AQ. IDAP defines the XML message structure for the body of the SOAP request. An IDAP-structured message is transmitted over the Internet using HTTP(S).

SOAP = Simple Object Access Protocol. A minimal set of conventions for invoking code using XML over HTTP defined by World Wide Web Consortium.

message = The smallest unit of information inserted into and retrieved from a queue. A message consists of control information (metadata) and payload (data).

queue = The abstract storage unit used by a messaging system to store messages.

Processes

QMNC

Queue monitor coordinator. It dynamically spawns slaves qXXX depending on the system load. The slaves do various background tasks.

QMNn

Queue monitor process.

JMS

Oracle Java Message Service (Oracle JMS) provides a Java API for Oracle Database Advanced Queuing based on the Java Message Service (JMS) standard. Oracle JMS supports the standard JMS interfaces and has extensions to support the Oracle Database Advanced Queuing administrative operations and other Oracle Database Advanced Queuing features that are not a part of the standard.

JMS is a messaging standard defined by Sun Microsystems, Oracle, IBM, and other vendors. JMS is a set of interfaces and associated semantics that define how a JMS client accesses the facilities of an enterprise messaging product.

Bibliography

https://prezi.com/xgkhmoutrguo/oracle-messaging-gateway/http://nadvi.blogspot.com/2011/11/integrating-oracle-database-with-ibm.html http://sadockobeth.blogspot.com/2014/03/how-to-configure-oracle-messaging.html

Oracle Documentation

My Oracle Supporthttps://support.oracle.comMessaging Gateway Will Not Shutdown (Doc ID 335523.1)SRDC - How to Collect Information for Troubleshooting Messaging Gateway Issues (Doc ID 1933998.1)  On what Platforms and Versions of Oracle is the Oracle Messaging Gateway available? (Doc ID 219410.1)Sun Defect 7075600 - "Comparison method violates its general contract!" errors - and Problems with the Work-around (Doc ID 1507950.1)Configuring Our Websphere Mq Link To Use Ssl (Doc ID 1293302.1)Extproc User Session Disconnects at SQLNET.INBOUND_CONNECT_TIMEOUT Value (Doc ID 1356971.1)Bug 12932097 : EXTPROC: USER SESSION IS DISCONNECTED IF SQLNET.INBOUND_CONNECT_TIMEOUT IS SEThttps://support.oracle.com/rs?type=doc&id=219410.1 (On what Platforms and Versions of Oracle is the Oracle Messaging Gateway available?)MGW Crashes With Message -- Agent Has Been Identified as a Rogue (Doc ID 1244874.1)
11.2https://docs.oracle.com/cd/E11882_01/server.112/e11013/mg_work.htm#ADQUE3292 https://docs.oracle.com/cd/E11882_01/server.112/e11013/mg_trble.htm#ADQUE2100 (Monitoring Oracle Messaging Gateway)https://docs.oracle.com/cd/E18283_01/network.112/e10836/advcfg.htm (Enabling Advanced Features of Oracle Net Services)https://docs.oracle.com/cd/E11882_01/server.112/e11013/mg_trble.htm (11.2 Streams Advanced Queuing User's Guide - Monitoring Oracle Messaging Gateway)https://docs.oracle.com/cd/E11882_01/server.112/e11013/mg_start.htm#ADQUE3231 (11.2 Oracle Streams Advanced Queuing User's Guide - Understanding the mgw.ora Initialization File)
12.1https://docs.oracle.com/database/121/ADQUE/mg_intro.htm (12.1.0.2 Database Advanced Queuing User's Guide)http://oradb-srv.wlv.ac.uk/E16655_01/server.121/e17945/mg_trble.htm Oracle® Database Advanced Queuing User's Guide 12c Release 1 (12.1) - 21 Monitoring Oracle Messaging Gatewayhttps://docs.oracle.com/database/121/ADQUE/glossary.htm#i432259https://docs.oracle.com/database/121/ADQUE/mg_work.htm (Working with Oracle Messaging Gateway)https://docs.oracle.com/database/121/ARPLS/d_mgwadm.htm#ARPLS66993 (CLEANUP_GATEWAY Procedures)
12.2https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adque/omg-getting-started.html https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adque/omg-monitoring.html 
19c PL/SQL Packages and Types Referencehttps://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_MGWADM.html
21 PL/SQL Packages and Types Referencehttps://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_MGWADM.html