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:
- Check if the DB Link in the DB is pointing to the tns correctly
- Check the tnsnames.ora
- Check the listener.ora – 2 entries described below
- Check if the init<SID> has the correct entries
- 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.
- 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.
- 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
Abhishek Joseph 