Start

CONNECT admin_user

EXEC DBMS_MGWADM.STARTUP

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.

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

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

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

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

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.

MGW/MQ Certification 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

CREATE USER &agent_user IDENTIFIED BY &agent_password;

GRANT CREATE SESSION to &&agent_user;

GRANT MGW_AGENT_ROLE to &&agent_user;

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;

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.

BEGIN

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

END;

Remove Job

BEGIN

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

END;

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

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

Oracle Documentation