Troubleshooting WebSphere MQ when connecting through Oracle Database gateway

oracle

Many times, you have the infrastructure team Install and setup WebSphere MQ in your servers and then as an Architect when you try to move data across servers through these MQs you face a lot of issues.

Here I am not going to talk about troubleshooting any specific errors, however I will tell you the steps to do generic troubleshooting for a WebSphere MQ and Oracle connection.

This article assumes that you access WebSphere MQ through the gateway.

MainĀ items:

  1. Check if the DB Link in the DB is pointing to the tns correctly
  2. Check the tnsnames.ora
  3. Check the listener.ora – 2 entries described below
  4. Check if the init<SID> has the correct entries
  5. Check if Oracle user on the server has access to the MQM group.

You must use the same SID value in the tnsnames.ora file, the listener.ora file, and the GATEWAY_SID environment variable in the gateway initialization file for each gateway instance being configured.

  1. Check if your DBLINK is configured to the correct tns_name_entry.

CREATE [PUBLIC] DATABASE LINK dblink [CONNECT TO userid IDENTIFIED
BY password] USING ‘tns_name_entry’;

You can look in the tnsnames.ora to verify if we are using the same name or not.

  1. The tnsnames.ora should have a entry like this.

tns_name_entry=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=host_name)

(PORT=port_number)

)

(CONNECT_DATA=

(SID=gateway_sid)

)

(HS=OK)

)

The gateway has some of the same components as an Oracle database which includes

>>directory where the gateway software is installed

>>System identifier (SID)

>>and an Initialization file similar to the Oracle database initialization parameter file

 

The default SID values

are:

— dg4mqs

This is the default SID that is used when the gateway resides on the same system

as the WebSphere MQ software.

— dg4mqc

This is the default SID that is used when the gateway resides on a different system

than the WebSphere MQ software. In this case, the gateway functions as a remote

WebSphere MQ client.

The gateway and the DB talk to each other using Oracle Net. You will need to configure both, the gateway and Oracle

database to have Oracle Net communication enabled, by configuring the files tnsnames.ora and listener.ora

The gateway initialization file would be like (init<sid>.ora). The initialization file must be available when the gateway is started.

During installation, a default initialization file is created in

$ORACLE_HOME/dg4mq/admin/init<sid>.ora, on UNIX based systems where sid is the default SID of dg4mqs or dg4mqc.

3. Two entries must be added to the listener.ora file:

  • A list of Oracle Net addresses for the Oracle Net Listener to listen on
  • The gateway process that the Oracle Net Listener should start in response to incoming connection requests

If you are using Oracle Net and the TCP/IP protocol adapter, then the syntax of an

entry in the listener.ora file is:

LISTENER=

(ADDRESS_LIST=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=host_name)

(PORT=port_number)

)

 

Entry for the Gateway in listener.ora

 

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=gateway_sid)

(ORACLE_HOME=gateway_directory)

(PROGRAM=driver)

)

)

4. The following entries needs to be in the initialization file (init<SID>.ora)

LOG_DESTINATION=log_file

QUEUE_MANAGER=manager_name

AUTHORIZATION_MODEL=auth_model

TRANSACTION_MODEL=tx_model

TRANSACTION_LOG_QUEUE=tx_queue_name

TRANSACTION_RECOVERY_USER=rec_user

TRANSACTION_RECOVERY_PASSWORD=rec_password

TRACE_LEVEL=0

MQSERVER=channel

MQCCSID=character_set