21 Monitoring Oracle Messaging Gateway

21.1 Oracle Messaging Gateway Log Files

Messaging Gateway agent status, history, and errors are recorded in Messaging Gateway log files. A different log file is created each time the Messaging Gateway agent is started. You should monitor the log file because any errors, configuration information read at startup time, or dynamic configuration information is written to the log. The format of the log file name is:

oramgw-hostname-timestamp-processid.log

By default the Messaging Gateway log file is in ORACLE_HOME/mgw/log. This location can be overridden by parameter log_directory in mgw.ora.

This section contains these topics:

• Sample Oracle Messaging Gateway Log File

• Interpreting Exception Messages in an Oracle Messaging Gateway Log File

>>2003-07-22 15:04:49  MGW  C-Bootstrap  0 LOG  process-id=11080
Bootstrap program starting
>>2003-07-22 15:04:50  MGW  C-Bootstrap  0 LOG  process-id=11080
JVM created -- heapsize = 64
>>2003-07-22 15:04:53  MGW  Engine  0  200  main
MGW Agent version: 10.1.0.2
>>2003-07-22 15:04:53  MGW  AdminMgr  0  LOG  main
Connecting to database using connect string = jdbc:oracle:oci8:@INST1
>>2003-07-22 15:05:00  MGW  Engine  0  200  main
MGW Component version: 10.1.0.2.0
>>2003-07-22 15:05:01  MGW  Engine  0  200  main
MGW job number: 125, MGW job sid: 10, MGW database instance: 1
>>2003-07-22 15:05:09  MGW  Engine  0  1  main
Agent is initializing.
>>2003-07-22 15:05:09  MGW  Engine  0  23  main
The number of worker threads is set to 1.
>>2003-07-22 15:05:09  MGW  Engine  0  22  main
The default polling interval is set to 5000ms.
>>2003-07-22 15:05:09  MGW  MQD  0  LOG  main
Creating MQSeries messaging link:
  link          : MQLINK
  link type     : Base Java interface
  queue manager : my.queue.manager
  channel       : channel1
  host          : my.machine
  port          : 1414
  user          : 
  connections   : 1
  inbound logQ  : logq1
  outbound logQ : logq2
>>2003-07-22 15:05:09  MGW  Engine  0  4  main
Link MQLINK has been added.
>>2003-07-22 15:05:09  MGW  Engine  0  7  main
Queue DESTQ@MQLINK has been registered; provider queue: MGWUSER.MYQUEUE.
>>2003-07-22 15:05:09  MGW  Engine  0  9  main
Propagation Schedule SCH_AQ2MQ (MGWUSER.MGW_BASIC_SRC --> DESTQ@MQLINK) has been added.
>>2003-07-22 15:05:09  MGW  AQN  0  LOG  main
Creating AQ messaging link:
  link            : oracleMgwAq
  link type       : native
  database        : INST1
  user            : MGWAGENT
  connection type : JDBC OCI
  connections     : 1
  inbound logQ    : sys.mgw_recv_log
  outbound logQ   : sys.mgw_send_log
>>2003-07-22 15:05:10  MGW  Engine  0  19  main
MGW subscriber SUB_AQ2MQ has been activated.
>>2003-07-22 15:05:10  MGW  Engine  0  14  main
MGW subscriber SUB_AQ2MQ (MGWUSER.MGW_BASIC_SRC --> DESTQ@MQLINK) has been added.
>>2003-07-22 15:05:11  MGW  Engine  0  2  main
Agent is up and running.

>>2003-07-22 15:27:26  MGW  AdminMgr  0  LOG  main
Connecting to database using connect string = jdbc:oracle:oci8:@BAD_SERVICE_NAME
>>2003-07-22 15:27:29  MGW  Engine  0  EXCEPTION  main
oracle.mgw.admin.MgwAdminException: [241]  Failed to connect to database. SQL
error: 12154, connect string: jdbc:oracle:oci8:@BAD_SERVICE_NAME
[ …Java stack trace here…]
[Linked-exception]
java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier
specified
[ …Java stack trace here…]
>>2003-07-22 15:27:29  MGW  Engine  0  25  main
Agent is shutting down.

21.2 Monitoring the Oracle Messaging Gateway Agent Status

This section contains these topics:

• MGW_GATEWAY View

• Oracle Messaging Gateway Irrecoverable Error Messages

• Other Oracle Messaging Gateway Error Conditions

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = number;

select g.agent_job, j.failures, j.broken from MGW_GATEWAY g, DBA_JOBS j where j.job = g.agent_job;

21.3 Monitoring Oracle Messaging Gateway Propagation

Messaging Gateway propagation can be monitored using the MGW_SUBSCRIBERS view and the Messaging Gateway log file. The view provides information on propagated messages and errors that may have occurred during propagation attempts. The log file can be used to determine the cause of the errors.

Besides showing configuration information, the MGW_SUBSCRIBERS view also has dynamic information that can be used to monitor message propagation. Applicable fields include STATUS, PROPAGATED_MSGS, EXCEPTIONQ_MSGS, FAILURES, LAST_ERROR_MSG, LAST_ERROR_DATE, and LAST_ERROR_TIME.

STATUS can be either ENABLED or DELETE_PENDING. DELETE_PENDING means subscriber removal is pending, usually because DBMS_MGWADM.REMOVE_SUBSCRIBER has been called but certain cleanup tasks pertaining to this subscriber are still outstanding. Otherwise the subscriber is considered ENABLED.

The PROPAGATED_MSGS field of the MGW_SUBSCRIBERS view indicates how many messages have been successfully propagated. This field is reset to zero when the Messaging Gateway agent is started.

If a Messaging Gateway subscriber has been configured with an exception queue, then the Messaging Gateway agent will move messages to that exception queue the first time the Messaging Gateway agent encounters a propagation failure caused by a message conversion failure. A message conversion failure is indicated by oracle.mgw.common.MessageException in the Messaging Gateway log file. The EXCEPTIONQ_MSGS field indicates how many messages have been moved to the exception queue. This field is reset to zero when the Messaging Gateway agent is started.

If an error occurs during message propagation for a subscriber, a count is incremented in the FAILURES field. This field indicates the number of failures encountered since the last successful propagation of messages. Each time a failure occurs, an error message and the time it occurred will be shown by LAST_ERROR_MSG, LAST_ERROR_DATE, and LAST_ERROR_TIME. When the number of failures reaches sixteen, Messaging Gateway halts propagation attempts for this subscriber. To resume propagation attempts you must call DBMS_MGWADM.RESET_SUBSCRIBER for the subscriber in question.

If an error occurs, then examine the Messaging Gateway log file for further information.

21.4 Oracle Messaging Gateway Agent Error Messages

This section lists some of the most commonly occurring errors that are shown in the LAST_ERROR_MSG column of the MGW_SUBSCRIBERS view and logged to the Messaging Gateway agent log file. Also shown are some errors that require special action. When you notice that a failure has occurred, look at the linked exceptions in the log file to determine the root cause of the problem.

Two primary types of errors are logged to the Messaging Gateway agent log file:

• oracle.mgw.common.MessageException

  This error type is logged when a message conversion failure occurs. The Messaging Gateway agent probably cannot propagate the message causing the failure, and the propagation job will eventually be stopped.

• oracle.mgw.common.GatewayException

  This error type is logged when some failure other than message conversion occurs. Depending on the cause, the problem may fix itself or require user action.

[221] Failed to access <messaging_system> queue: <queue>

An error occurred while trying to access either an Oracle Streams AQ queue or a non-Oracle queue. Check the linked exception error code and message in the log file.

[241] Failed to connect to database. SQL error: <error>, connect string: <connect_string>

This is probably caused by incorrect entries in DBMS_MGWADM.DB_CONNECT_INFO. Either the Messaging Gateway agent user or password has not been entered correctly, or the database parameter is incorrect or NULL.

If the database parameter is NULL, then check the Messaging Gateway log file for the following Oracle linked errors:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

These two errors together indicate that the Messaging Gateway agent is attempting to connect to the database using a local IPC connection, but the ORACLE_SID value is not correct.

A local connection is used when DBMS_MGWADM.DB_CONNECT_INFO is called with a NULL value for the database parameter. If a local connection is desired, the correct ORACLE_SID value must be set in the Messaging Gateway agent process. This can be done by adding the following line to mgw.ora:

set ORACLE_SID = sid_value

ORACLE_SID need not be set if DBMS_MGWADM.DB_CONNECT_INFO is called with a not NULL value for the database parameter. In this case the value should specify a net service name from tnsnames.ora.

If setting ORACLE_SID in mgw.ora does not work, then the database parameter of DBMS_MGWADM.DB_CONNECT_INFO must be set to a value that is not NULL.

[415] Missing messages from source queue of subscriber <subscriber_id>

Possible causes include:

• The agent partially processed persistent messages that were dequeued by someone other than the Messaging Gateway agent.

• The propagation source queue was purged or re-created.

• A message was moved to the Oracle Streams AQ exception queue.

If this error occurs, then call procedure CLEANUP_GATEWAY in the DBMS_MGWADM package:

DBMS_MGWADM.CLEANUP_GATEWAY (
       action => DBMS_MGWADM.RESET_SUB_MISSING_MESSAGE, 
       sarg => <subscriber_id>);

The call takes effect only if the subscriber has encountered the missing message problem and the agent is running. The agent treats the missing messages as nonpersistent messages and continues processing the subscriber.

[416] Missing log records in receiving log queue for subscriber <subscriber_id>

Possible causes include:

• Log records were dequeued from the log queues by someone other than the Messaging Gateway agent.

• The log queues were purged or re-created.

If this error occurs, then call procedure CLEANUP_GATEWAY in the DBMS_MGWADM package:

DBMS_MGWADM.CLEANUP_GATEWAY (
       action => DBMS_MGWADM.RESET_SUB_MISSING_LOG_REC,  
       sarg => <subscriber_id>);

The call takes effect only if the subscriber has encountered the missing log records problem and the agent is running.

[417] Missing log records in sending log queue for subscriber <subscriber_id>

See previous error.

[421] WARNING: Unable to get connections to recover subscriber <subscriber_id>

This message is a warning message indicating that the Messaging Gateway agent failed to get a connection to recover the propagation job, because other propagation jobs are using them all. The agent will keep trying to get a connection until it succeeds.

If this message is repeated many times for a WebSphere MQ link, then increase the maximum number of connections used by the Messaging Gateway link associated with the subscriber.

[434] Failed to access queue <queue>; provider queue <queue>

This message indicates that a messaging system native queue cannot be accessed. The queue may have been registered by DBMS_MGWADM.REGISTER_FOREIGN_QUEUE, or it may be an Oracle Streams AQ queue. The linked exceptions should give more information.

Possible causes include:

• The foreign queue was registered incorrectly, or the Messaging Gateway link was configured incorrectly.

  Verify configuration information. If possible, use the same configuration information to run a sample application of the non-Oracle messaging system.

• The non-Oracle messaging system is not accessible.

  Check that the non-Oracle messaging system is running and can be accessed using the information supplied in the Messaging Gateway link.

• The Oracle Streams AQ queue does not exist. Perhaps the queue was removed after the Messaging Gateway subscriber was created.

  Check that the Oracle Streams AQ queue still exists.

[436] LOW MEMORY WARNING: total memory = < >, free_mem = < >

The Messaging Gateway agent JVM is running low on memory. Java garbage collection will be invoked, but this may represent a JVM heap size that is too small. Use the max_memory parameter of DBMS_MGWADM.ALTER_AGENT to increase the JVM heap size. If the Messaging Gateway agent is running, then it must be restarted for this change to take effect.

[703] Failed to retrieve information for transformation <transformation_id>

The Messaging Gateway agent could not obtain all the information it needs about the transformation. The transformation parameter of DBMS_MGWADM.ADD_SUBSCRIBER must specify the name of the registered transformation and not the name of the transformation function.

Possible causes include:

• The transformation does not exist. Verify that the transformation has been created. You can see this from the following query performed as user SYS:

  SELECT TRANSFORMATION_ID, OWNER FROM DBA_TRANSFORMATIONS;
  
  

• The wrong transformation is registered with Messaging Gateway. Verify that the transformation registered is the one intended.

• The Messaging Gateway agent user does not have EXECUTE privilege on the object type used for the from_type or the to_type of the transformation indicated in the exception.

  It is not sufficient to grant EXECUTE to MGW_AGENT_ROLE and then grant MGW_AGENT_ROLE to the agent user. You must grant EXECUTE privilege on the object type directly to the agent user or to PUBLIC.

  Example 21-3 shows such a case for the from_type. It also shows the use of linked exceptions for determining the precise cause of the error.

  Example 21-3 No EXECUTE Privilege on Object Type

  Errors occurred during processing of subscriber SUB_AQ2MQ_2
  oracle.mgw.common.GatewayException: [703] Failed to retrieve information for
  transformation mgwuser.SAMPLEADT_TO_MGW_BASIC_MSG
  […Java stack trace here…]
  [Linked-exception]
  java.sql.SQLException: "from_type" is null
  […Java stack trace here…]
  

[720] AQ payload type <type> not supported; queue: <queue>

The payload type of the Oracle Streams AQ queue used by a Messaging Gateway subscriber is not directly supported by Messaging Gateway. For non-JMS propagation, Messaging Gateway directly supports the payload types RAW, SYS.MGW_BASIC_MSG_T and SYS.MGW_TIBRV_MSG_T.

Possible actions include:

• Configure the Messaging Gateway subscriber to use a transformation that converts the queue payload type to a supported type.

• Remove the Messaging Gateway subscriber and create a new subscriber that uses an Oracle Streams AQ queue with a supported payload type.

  For Java Message Service (JMS) propagation, the Messaging Gateway subscriber must be removed and a new subscriber added whose Oracle Streams AQ payload type is supported by Oracle Java Message Service (OJMS). Transformations are not supported for JMS propagation.

[721] Transformation type <type> not supported; queue: <queue_name>, transform: <transformation>

A Messaging Gateway subscriber was configured with a transformation that uses an object type that is not one of the Messaging Gateway canonical types.

For an outbound subscriber, the transformation from_type must be the Oracle Streams AQ payload type, and the to_type must be a Messaging Gateway canonical type. For an inbound subscriber, the transformation from_type must be a Messaging Gateway canonical type and the to_type must be the Oracle Streams AQ payload type.

[722] Message transformation failed; queue: <queue_name>, transform: <transformation>

An error occurred while attempting execution of the transformation. ORA-25229 is typically thrown by Oracle Streams AQ when the transformation function raises a PL/SQL exception or some other Oracle error occurs when attempting to use the transformation.

Possible causes include:

• The Messaging Gateway agent user does not have EXECUTE privilege on the transformation function. This is illustrated in Example 21-4.

  It is not sufficient to grant EXECUTE to MGW_AGENT_ROLE and then grant MGW_AGENT_ROLE to the Messaging Gateway agent user. You must grant EXECUTE privilege on the transformation function directly to the Messaging Gateway agent user or to PUBLIC.

  Example 21-4 No EXECUTE Privilege on Transformation Function

  Errors occurred during processing of subscriber SUB_MQ2AQ_2
  oracle.mgw.common.GatewayException: [722] Message transformation failed queue:
  MGWUSER.DESTQ_SIMPLEADT, transform: MGWUSER.MGW_BASIC_MSG_TO_SIMPLEADT
  […Java stack trace here…]
  [Linked-exception]
  oracle.mgw.common.MessageException: [722] Message transformation failed;
  queue: MGWUSER.DESTQ_SIMPLEADT, transform: 
  MGWUSER.MGW_BASIC_MSG_TO_SIMPLEADT
  […Java stack trace here…]
  [Linked-exception]
  java.sql.SQLException: ORA-25229: error on transformation of message msgid:
  9749DB80C85B0BD4E03408002086745E
  ORA-00604: error occurred at recursive SQL level 1
  ORA-00904: invalid column name
  […Java stack trace here…]
  
  

• The transformation function does not exist, even though the registered transformation does. If the transformation function does not exist, it must be re-created.

• The Messaging Gateway agent user does not have EXECUTE privilege on the payload object type for the queue indicated in the exception.

  It is not sufficient to grant EXECUTE to MGW_AGENT_ROLE and then grant MGW_AGENT_ROLE to the Messaging Gateway agent user. You must grant EXECUTE privilege on the object type directly to the Messaging Gateway agent user or to PUBLIC.

• The transformation function raised the error. Verify that the transformation function can handle all messages it receives.

[724] Message conversion not supported; to AQ payload type: <type>, from type: <type>

A Messaging Gateway subscriber is configured for inbound propagation where the canonical message type generated by the non-Oracle messaging system link is not compatible with the Oracle Streams AQ queue payload type. For example, propagation from a TIB/Rendezvous messaging system to an Oracle Streams AQ queue with a SYS.MGW_BASIC_MSG_T payload type, or propagation from WebSphere MQ to an Oracle Streams AQ queue with a SYS.MGW_TIBRV_MSG_T payload type.

Possible actions include:

• Configure the Messaging Gateway subscriber with a transformation that maps the canonical message type generated by the non-Oracle messaging link to the Oracle Streams AQ payload type.

• Remove the Messaging Gateway subscriber and create a new subscriber whose Oracle Streams AQ queue payload type matches the canonical message type generated by the non-Oracle link.

[725] Text message not supported for RAW payload

A Messaging Gateway subscriber is configured for inbound propagation to an Oracle Streams AQ destination having a RAW payload type. A text message was received from the source (non-Oracle) queue resulting in a message conversion failure.

If support for text data is required, remove the Messaging Gateway subscriber and create a new subscriber to an Oracle Streams AQ destination whose payload type supports text data.

[726] Message size <size> too large for RAW payload; maximum size is <size>

A Messaging Gateway subscriber is configured for inbound propagation to an Oracle Streams AQ destination having a RAW payload type. A message conversion failure occurred when a message containing a large RAW value was received from the source (non-Oracle) queue.

If large data support is required, remove the Messaging Gateway subscriber and create a new subscriber to an Oracle Streams AQ destination whose payload type supports large data, usually in the form of an object type with a BLOB attribute.

[728] Message contains too many large (BLOB) fields

The source message contains too many fields that must be stored in BLOB types. SYS.MGW_TIBRV_MSG_T is limited to three BLOB fields. Reduce the number of large fields in the message, perhaps by breaking them into smaller fields or combining them into fewer large fields.

[729] Message contains too many large (CLOB) fields

The source message contains too many fields that contain a large text value that must be stored in a CLOB. SYS.MGW_TIBRV_MSG_T is limited to three CLOB fields. Reduce the number of large fields in the message, perhaps by breaking them into smaller fields or combining them into fewer large fields.

[805] MQSeries Message error while enqueuing to queue: <queue>

WebSphere MQ returned an error when an attempt was made to put a message in a WebSphere MQ queue. Check the linked exception error code and message in the log file. Consult WebSphere MQ documentation.