This is a digression from the usual entry but it took me a while to install and configure the gateway and I think it may be useful to anyone trying it in the future. The documentation in the main is excellent but there are a few instructions that my installation required that were not documented.

Oracle Database Gateway for MQ (64-bit Linux installation)

I installed the Gateway on a separate server from the database. The WebSphere MQ was installed on it's own server also.

Basic Requirements

· X-Windows server (e.g. Cygwin, Xming)

· Ssh client software (e.g. putty)

· linux.x64_11gR2_gateways.zip file.

· mqc6_6.0.2.10_linuxx86-64.tar

· Oracle 11GR2 database installed

Installation documents

Gateway:

http://download.oracle.com/docs/cd/E11882_01/gateways.112/e12417/intro.htm#WSMQG104

IBM MQ Client

http://publib.boulder.ibm.com/infocenter/wmqv6/v6r0/index.jsp?topic=/com.ibm.mq.amq1ac.doc/lq10120_.htm

Installation Steps

· Install MQ Client

· Install Procedural Gateway

· Configure Gateway

Install MQ Client

N.B. Deploy the samples or we can’t test the Queue

Procedure

Log in as root.
Create the directories to hold the MQ code and working data.

o The WebSphere MQ is installed in /opt/mqm.
The working data uses /var/mqm

· Create a user ID and group ID mqm.

o Set the users home directory to /var/mqm

Change into the unzipped MQ product directory

Run the mqlicense.sh script.

./mqlicense.sh -accept

You must accept the license agreement before you can proceed with the installation.

Use the rpm -ivh command to install each component that you require.

o rpm -ivh MQSeriesRuntime-6.0.2-10.x86_64.rpm MQSeriesClient-6.0.2-10.x86_64.rpm MQSeriesSamples-6.0.2-10.x86_64.rpm

Verify

Go to /opt/mqm/samp/bin

· CHANNEL1 = the server-connection channel defined on the MQ server

· TCP = the communications protocol.

· server-hostname = the TCP/IP host name of the server.

· port =port MQ is listening on (Default 1414)

set MQSERVER=‘CHANNEL1/TCP/server-hostname(port)’ –Use quotes in Linux

Browse the queue

amqsgbrc <<Queue Name>> <<Queue Manager>>

Output (Successful)

Sample AMQSGBR0 (browse) start

<<Queue Manager>>

Messages for <<Queue Name>> (ctrl+C to stop or just let it browse the msgs)

1 <RFH >

2 <RFH >

Install Procedural Gateway

Log-in as the user intended to own the gateway software

Unzip the linux.x64_11gR2_gateways.zip file.

Pre-installation

Determine where the WebSphere MQ queue manager runs.
Local system Oracle SID = dg4mqs
Remote system Oracle SID = dg4mqc
We require the

1. MQ Queue Manager

2. MQ Channel

3. MQ server-host IP

4. MQ Port

Installation

Navigate to the unzipped folder and ./runInstaller to run the GUI installer

For installation instructions use section 5 of the Oracle Installation guide

http://download.oracle.com/docs/cd/E11882_01/gateways.112/e12417/intro.htm#WSMQG104

Configure Oracle Procedural Gateway

The gateway is installed and preconfigured using default values for the gateway SID, directory names, file names, and gateway parameter settings.
The default SID values are:

· dg4mqs

o The default SID that is used when the gateway resides on the same system as the WebSphere MQ software.

· dg4mqc

o The default SID that is used when the gateway resides on a different system than the WebSphere MQ software.

A basic gateway initialization file is also installed, and values in this file are set based on the information entered during the installation phase.

Listener.ora

We want to make 2 changes to the Listener File.

1. We want to an add an EXTPROC entry to listen for the external library

a. This will allow the database to connect to the correct library

2. We want to add the LD_LIBRARY_PATH to the dg4mqc entry

a. This will allow us to use the correct 64-bit library and eliminate the error

(ADDRESS=(PROTOCOL=tcp)(HOST=172.30.10.48)(PORT=10400)) * establish * dg4mqc * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Example Listener

Oracle_Home = /home/oracle/product/11.2.0/tg_1

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC=

(SID_NAME= PLSExtProc)

(ORACLE_HOME=/home/oracle/product/11.2.0/tg_1)

(PROGRAM= extproc)

(ENVS="EXTPROC_DLLS=ANY:/home/oracle/product/11.2.0/tg_1/bin/libdg4mqc.so")

)

(SID_DESC=

(SID_NAME=dg4mqc)

(ORACLE_HOME=/home/oracle/product/11.2.0/tg_1)

(PROGRAM=dg4mqc)

(ENVS="LD_LIBRARY_PATH=/opt/mqm/lib64")

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

DIRECT_HANDOFF_TTC_LISTENER=OFF

ADR_BASE_LISTENER = /home/oracle/product/11.2.0/tg_1

TRACE_LEVEL_LISTENER=ADMIN

TRACE_DIRECTORY_LISTENER=/home/oracle/product/11.2.0/tg_1/network/trace

TRACE_FILE_LISTENER=listener

LOG_DIRECTORY_LISTENER=/home/oracle/product/11.2.0/tg_1/network/log

LOG_FILE_LISTENER=listener

To test the dg4mqc programme is pointing to the correct library at the command line run

$ dg4mqc

Set the LD_LIBRARY_PATH and rerun

Tnsnames.ora

The database server needs an entry in the tnsnames.ora to connect to the gateway (via database link)

DG4MQC =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dg4mqc)

(SERVER=DEDICATED)

)

Database Link

A connection to the gateway is established through a database link when it is first used in an Oracle session.

CREATE PUBLIC DATABASE LINK dblink USING 'DG4MQC';

The AUTHORIZATION_MODEL parameter is set to RELAXED and as such no username or password are required.

Alias Library

Create the Oracle Database Gateway for WebSphere MQ alias library, libdg4mq, using the Oracle Database Gateway for WebSphere MQ deployment scripts. During installation, the appropriate shared library name is defined ORACLE_HOME/dg4mq/admin/deploy/pgmobj.sql on UNIX
For a remote model, the libdg4mqc.so shared library is used.
For example:

CREATE OR REPLACE LIBRARY libdg4mq AS 'ORACLE_HOME/lib/libdg4mqc.so' TRANSACTIONAL;

Preparing the Production Oracle Database

Before you can compile MIPs on a production Oracle database, the following PL/SQL packages must be present on the production Oracle database:

· DBMS_PIPE, DBMS_OUTPUT, and UTL_RAW

o Installed as part of a typical database creation

· PGM, PGM_BQM, PGM_SUP, and UTL_PG

o These packages are shipped with your Oracle Database Gateway for WebSphere MQ and are present in ORACLE_HOME/dg4mq/admin/deploy

Copy the scripts in this directory to the database server OR configure a connection to the database server
To install the packages we use the script

pgmdeploy.sql. The pgmdeploy script connects as scott/tiger so we need to edit the script and change this to a valid user, include the database connection script here also.  Change the line connect SYS/&INPASS2 as sysdba to include the database connection string connect SYS/&INPASS2@oracl as sysdba.

Set the Oracle_SID

         sqlplus /nolog @pgmdeploy.sql

The PQM_UTL8 procedure relies on the Visual workbench Repository so it will be invalid.

Testing the MQ connection

Reload the listener on the Gateway server

$ lsnrctl reload

Use the test.sql script from the ORACLE_HOME/dg4mq/sample directory on the gateway.

Edit the file to change the lines

objdesc.objectname := '<<Your Queue name>>';

objdesc.dblinkname := '<<Your DB Link>>';

From SQL prompt execute the script SQL> @test

Notes

No trace files will be written for MQ unless there has been a valid attempt to connect to MQ

The 3 Issues I encountered were

1. Port not open on the firewall -No Trace file written

2. LD_LIBRARY_PATH not set/pointed to correct library - No Trace file written

3. Library not declared in an exproc entry in the listener -Trace file written

My Jdeveloper Hints

Monday, February 28, 2011

Install & Configure Oracle Database Gateway for MQ