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.
- 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.
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
This is the default SID that is used when the gateway resides on the same system
as the WebSphere MQ software.
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:
Entry for the Gateway in listener.ora
4. The following entries needs to be in the initialization file (init<SID>.ora)