MGW

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.

0 for basic logging; equivalent to DBMS_MGWADM.BASIC_LOGGING
1 for light tracing; equivalent to DBMS_MGWADM.TRACE_LITE_LOGGING
2 for high tracing; equivalent to DBMS_MGWADM.TRACE_HIGH_LOGGING
3 for debug tracing; equivalent to DBMS_MGWADM.TRACE_DEBUG_LOGGING
4 for high debug tracing; equivalent to DBMS_MGWADM.TRACE_DEBUG_HIGH_LOGGING

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.

Websphere MQ

[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)

Websphere MQ

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.

LISTENER.ORA

TNSNAMES.ORA

MGW.ORA

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:

AQ_ADMINISTRATOR_ROLE

The ability to Execute:

DBMS_MGWADM
MGW_MQSERIES_PROPERTIES
MGW_TIBRV_PROPERTIES
MGW_MSMQ_PROPERTIES
MGW_PROPERTIES
MGW_PROPERTY

The ability to Select from:

MGW_AGENT_OPTIONS
MGW_FOREIGN_QUEUES
MGW_GATEWAY
MGW_JOBS
MGW_LINKS
MGW_MQSERIES_LINKS
MGW_MSMQ_LINKS
MGW_SCHEDULES
MGW_SUBSCRIBERS
MGW_TIBRV_LINKS

MGW_AGENT_ROLE

The MGW_AGENT_ROLE gives the user:

These Roles:

AQ_ADMINISTRATOR_ROLE
MGW_ADMINISTRATOR_ROLE

The ability to Execute:

DBMS_PIPE
MGWI_ADMIN
MGWI_CRYPTO
MGWI_FOREIGNQUEUE
MGWI_MSGLINK
MGWI_SCHEDULE
MGWI_SUBSCRIBER
MGW_AQDRIVER
MGW_AQ_DEQUEUE_OPTIONS_T
MGW_AQ_ENQUEUE_OPTIONS_T
MGW_AQ_MESSAGE_PROPS_T
MGW_NOTIFY
MGW_NOTIF_ELEMENT
MGW_NOTIF_ELEMENT_LIST
MGW_NOTIF_MSG

The ability to Select and Update ANY TABLE

The ability to Select and Update these objects:

MGW$_AGENT_OPTIONS
MGW$_CONFIG
MGW$_FOREIGN_QUEUES
MGW$_GATEWAY
MGW$_LINKS
MGW$_MQSERIES_LINKS
MGW$_MSMQ_LINKS
MGW$_SCHEDULES
MGW$_TIBRV_LINKS

The ability to Select, Update and Delete from:

MGW$_SUBSCRIBERS

The ability to DEQUEUE, ENQUEUE, SELECT and UPDATE:

MGW_JMS_RECV_LOG
MGW_JMS_SEND_LOG

The ability to ENQUEUE and DEQUEUE:

MGW_RECV_LOG
MGW_SEND_LOG

The ability to Select on these objects:

DBA_TRANSFORMATIONS

AQ

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