1 Reply Latest reply: Oct 12, 2012 10:17 AM by 733411 RSS

    Oracle Messaging Gateway (to MQ) Configuration Issue

    johnb-Oracle
      All,

      Pardons for the long post. I wanted to include all/most relevant steps.

      I am attempting to implement a link between Oracle and MQ Series via the
      Message Gateway.

      Here is a summary of the error messages:

      Caused by: com.ibm.mq.jmqi.JmqiException: CC=2;RC=2540;AMQ9204: Connection to host '10.241.97.27(1414)'
      rejected. [1=com.ibm.mq.jmqi.JmqiException[CC=2;RC=2540;AMQ9520:
      Channel not defined remotely. [3=channel1]],3=10.241.97.27(1414),5=RemoteConnection.analyseErrorSegment]
      ...

      More details follow:
      -----------------------------------------------------------------------------------

      I have to install and configure all parts. And that is where the trouble
      lies.

      I have the following installed on one linux x86-64 server:

      a. Oracle DB v11.2.0.3
      b. MQ Series Server 7.5
      c. MQ Series Client 7.5

      I have followed the MQ Series install notes found at this link:

      http://publib.boulder.ibm.com/infocenter/wmqv7/v7r0/index.jsp?topic=%2Fcom.ibm.mq.amq1ac.doc%2Flq10120_.htm

      These are all the packages I installed:

      MQSeriesRuntime-7.5.0-0.x86_64.rpm
      MQSeriesServer-7.5.0-0.x86_64.rpm
      MQSeriesClient-7.5.0-0.x86_64.rpm
      MQSeriesJava-7.5.0-0.x86_64.rpm
      MQSeriesJRE-7.5.0-0.x86_64.rpm
      MQSeriesSamples-7.5.0-0.x86_64.rpm


      Likewise, I have followed the Oracle documentation here:

      http://docs.oracle.com/cd/E11882_01/server.112/e11013/mg_start.htm


      There is a point in the Server/Client installation where I tested, successfully, the ability of
      the pair to enqueue/dequeue messages.

      Basically, these are the steps I performed:

      a. Create a queue manager

      crtmqm -q saturn.queue.manager

      Directory '/var/mqm/qmgrs/saturn!queue!manager' created.
      The queue manager is associated with installation 'Installation1'.
      Creating or replacing default objects for queue manager 'saturn.queue.manager'.
      Default objects statistics : 74 created. 0 replaced. 0 failed.
      Completing setup.
      Setup completed.

      b. Start queue manager

      strmqm

      WebSphere MQ queue manager 'saturn.queue.manager' starting.
      The queue manager is associated with installation 'Installation1'.
      5 log records accessed on queue manager 'saturn.queue.manager' during the log replay phase.
      Log replay for queue manager 'saturn.queue.manager' complete.
      Transaction manager state recovered for queue manager 'saturn.queue.manager'.
      WebSphere MQ queue manager 'saturn.queue.manager' started using V7.5.0.0.

      c. Execute commands with MQSC utility

      runmqsc

      5724-H72 (C) Copyright IBM Corp. 1994, 2011. ALL RIGHTS RESERVED.
      Starting MQSC for queue manager saturn.queue.manager.

      d. Create a local queue (QUEUE1)

      define qlocal (queue1)
      1 : define qlocal (queue1)
      AMQ8006: WebSphere MQ queue created.

      e. Create a channel (CHANNEL1)

      define channel (channel1) chltype (svrconn) trptype (tcp) mcauser ('mqm')
      2 : define channel (channel1) chltype (svrconn) trptype (tcp) mcauser ('mqm')
      AMQ8014: WebSphere MQ channel created.

      f. Create a listener (LISTENER1)

      define listener (listener1) trptype (tcp) control (qmgr) port (1414)
      3 : define listener (listener1) trptype (tcp) control (qmgr) port (1414)
      AMQ8626: WebSphere MQ listener created.

      g. Start listener

      start listener (listener1)
      4 : start listener (listener1)
      AMQ8021: Request to start WebSphere MQ listener accepted.

      h. end

      4 MQSC commands read.
      No commands have a syntax error.
      All valid MQSC commands were processed.


      -----------------------------------------------------

      Now after this, I attempted to follow a provided sample and got the following error:

      /opt/mqm/samp/bin/amqsputc QUEUE1 saturn.queue.manager

      Sample AMQSPUT0 start
      MQCONN ended with reason code 2035
      [mqm@slc02kfc bin]$


      I searched for preferred solutions and found none I could implement, so referring to the
      following document addressing above 2035 issue, I implemented the following, somewhat
      unadvised workaround.

      http://www.mqseries.net/phpBB2/viewtopic.php?t=61380&sid=ca932c7ce88b9273a6d4868468a6a248


      Resolved by unadvisable (runmqsc) command:

      alter qmgr chlauth (disabled)

      Note, this is unadvisable in a secure environment. At this point in time, I have no need
      for a secure environment. Ultimately, I will not be configuring the MQ Server.
      But I do not understand if other there are other ramifications.

      Once the 2035 error was resolved, I could demonstrate the ability to enqueue/dequeue:

      [mqm@slc02kfc bin]$ ./amqsputc QUEUE1 saturn.queue.manager
      Sample AMQSPUT0 start
      target queue is QUEUE1
      Hello World

      Sample AMQSPUT0 end
      [mqm@slc02kfc bin]$ ./amqsgetc QUEUE1 saturn.queue.manager
      Sample AMQSGET0 start
      message <Hello World>


      no more messages
      Sample AMQSGET0 end
      [mqm@slc02kfc bin]$


      So, now I think I have the following resources configured on MQ:

      Queue Manager : saturn.queue.manager
      Channel : channel1
      Queue : queue1
      Listener : listener1
      Local port : 1414


      On the Oracle side, following the docs, I've configured :

      a. environment variables

      LD_LIBRARY_PATH=${ORACLE_HOME}/jdk/jre/lib/amd64:${ORACLE_HOME}/jdk/jre/lib/amd64/server:${ORACLE_HOME}/lib
      CLASSPATH=${ORACLE_HOME}/jdk/jre/lib/rt.jar:${ORACLE_HOME}/jdbc/lib/ojdbc5.jar:${ORACLE_HOME}/jlib/orai18n.jar:${ORACLE_HOME}/sqlj/lib/runtime12.jar:${ORACLE_HOME}/rdbms/jlib/jmscommon.jar:${ORACLE_HOME}/rdbms/jlib/aqapi.jar:${ORACLE_HOME}/jlib/jta.jar

      b. Listener entries

      # modification
      # (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

      # addition
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/1123)
      (ENVS= "LD_LIBRARY_PATH=/u01/app/oracle/product/1123/jdk/jre/lib/amd64:/u01/app/oracle/product/1123/jdk/jre/lib/amd64/server:/u01/app/oracle/product/1123/lib")
      (SID_NAME = mgwextproc)
      (PROGRAM = extproc)
      )
      )

      c. tnsnames.ora

      MGW_AGENT = (DESCRIPTION=
      (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC)))
      (CONNECT_DATA= (SID=mgwextproc)))

      d. mgw.ora

      -- mgw.ora : /u01/app/oracle/product/1123/mgw/admin

      log_directory=/u01/app/oracle/diag/mgw
      set CLASSPATH=/u01/app/oracle/product/1123/jdk/jre/lib/rt.jar:/u01/app/oracle/product/1123/jdbc/lib/ojdbc5.jar:/u01/app/oracle/product/1123/jlib/orai18n.jar:/u01/app/oracle/product/1123/sqlj/lib/runtime12.jar:/u01/app/oracle/product/1123/rdbms/jlib/jmscommon.jar:/u01/app/oracle/product/1123/rdbms/jlib/aqapi.jar:/u01/app/oracle/product:/1123/jlib/jta.jar:/opt/mqm/java/lib/java/lib/com.ibm.mqjms.jar:/opt/mqm/java/lib/java/lib/com.ibm.mq.jar:/opt/mqm/java/lib/java/lib/connector.jar


      e. DB Additions

      i. Users
      CREATE USER mgw_admin IDENTIFIED BY mgw_admin;
      GRANT CREATE SESSION to mgw_admin;
      GRANT MGW_ADMINISTRATOR_ROLE to mgw_admin;

      CREATE USER mgw_agent IDENTIFIED BY mgw_agent;
      GRANT CREATE SESSION to mgw_agent;
      GRANT MGW_AGENT_ROLE to mgw_agent;

      ii. mgw agent (run as mgw_admin)

      BEGIN
      /* example */
      DBMS_MGWADM.ALTER_AGENT(
      agent_name => 'default_agent',
      username => 'mgw_agent',
      password => 'mgw_agent',
      database => 'OTMDX14');

      DBMS_MGWADM.STARTUP
      end;
      /
      PL/SQL procedure successfully completed.

      Verifying agent status:
      select * from mgw_gateway
      AGENT_STATUS : RUNNING
      AGENT_PING : REACHABLE
      AGENT_JOB :
      AGENT_USER : MGW_AGENT
      AGENT_DATABASE : OTMDX14
      LAST_ERROR_DATE :
      LAST_ERROR_TIME :
      LAST_ERROR_MSG :
      MAX_CONNECTIONS : 1
      MAX_MEMORY : 64
      MAX_THREADS : 1
      AGENT_INSTANCE : 1
      AGENT_START_TIME : 25-SEP-12 09.34.44.738640 AM
      CONNTYPE : JDBC_OCI
      AGENT_NAME : DEFAULT_AGENT
      SERVICE :
      INITFILE :
      COMMENTS :
      -----------------

      iii. Configuring a WebSphere MQ Base Java Link

      DECLARE
      v_options sys.mgw_properties;
      v_prop sys.mgw_mqseries_properties;
      BEGIN
      v_prop := sys.mgw_mqseries_properties.construct();
      v_prop.interface_type := dbms_mgwadm.MQSERIES_BASE_JAVA_INTERFACE;
      v_prop.max_connections := 1;
      v_prop.username := 'mqm';
      v_prop.password := 'otmmqm';
      v_prop.hostname := '10.241.97.27'; --- 10.241.97.27
      v_prop.port := 1414;
      v_prop.channel := 'channel1';
      v_prop.queue_manager := 'saturn.queue.manager';
      v_prop.outbound_log_queue := 'queue1';

      dbms_mgwadm.create_msgsystem_link(
      linkname => 'otm_mqlink'
      , agent_name =>'default_agent'
      , properties => v_prop
      , options => v_options );
      END;
      /

      Dictionary queries:
      select link_name, link_type from MGW_LINKS;

      LINK_NAME LINK_TYP
      ------------------------------ --------
      OTM_MQLINK MQSERIES

      select link_name, queue_manager, channel, hostname from mgw_mqseries_links')

      LINK_NAME : OTM_MQLINK
      QUEUE_MANAGER : saturn.queue.manager
      CHANNEL : channel1
      HOSTNAME : 10.241.97.27

      iV. Created a queue

      Begin
      DBMS_AQADM.CREATE_QUEUE (
      queue_name => 'OUTBOUND_MQ'
      , queue_table => 'INTG_QUEUE' );
      End;
      /

      v. registered foreign queue (WebSphere MQ Base Java Queue)

      BEGIN
      DBMS_MGWADM.REGISTER_FOREIGN_QUEUE(
      name => 'OTM_TARGET_Q',
      linkname => 'OTM_MQLINK',
      provider_queue => 'QUEUE1',
      domain => dbms_mgwadm.DOMAIN_QUEUE);
      END;

      vi. created a transformation (not shown) and created, enabled job


      BEGIN
      DBMS_MGWADM.CREATE_JOB(
      job_name => 'job_OTM_2_MQ'
      , propagation_type => DBMS_MGWADM.OUTBOUND_PROPAGATION
      , source => 'GLOGOWNER.OUTBOUND_MQ'
      , destination => 'OTM_TARGET_Q@OTM_MQLINK'
      , transformation => 'GLOGOWNER.trans_sampleadt_to_mgw_basic'
      );

      DBMS_MGWADM.ENABLE_JOB(job_name => 'job_OTM_2_MQ');
      END;

      vi. I enqueue a message, not shown

      vii. dictionary query

      select JOB_NAME , PROPAGATION_TYPE , SOURCE , DESTINATION , RULE , TRANSFORMATION ,
      EXCEPTION_QUEUE , POLL_INTERVAL , ENABLED , AGENT_NAME , LINK_NAME , PROP_STYLE , STATUS ,
      PROPAGATED_MSGS , EXCEPTIONQ_MSGS , FAILURES , LAST_ERROR_MSG , LAST_ERROR_DATE , LAST_ERROR_TIME , COMMENTS
      from mgw_jobs ;

      JOB_NAME : JOB_OTM_2_MQ
      PROPAGATION_TYPE : OUTBOUND
      SOURCE : GLOGOWNER.OUTBOUND_MQ
      DESTINATION : OTM_TARGET_Q@OTM_MQLINK
      RULE :
      TRANSFORMATION : GLOGOWNER.trans_sampleadt_to_mgw_basic
      EXCEPTION_QUEUE :
      POLL_INTERVAL :
      ENABLED : TRUE
      AGENT_NAME : DEFAULT_AGENT
      LINK_NAME : OTM_MQLINK
      PROP_STYLE : NATIVE
      STATUS : RETRY
      PROPAGATED_MSGS : 0
      EXCEPTIONQ_MSGS : 0
      FAILURES : 7
      LAST_ERROR_MSG : Initialization failures for JOB_OTM_2_MQ errorcode = 435
      LAST_ERROR_DATE : 27-sep-2012 12:11:22
      LAST_ERROR_TIME : 12:11:22
      COMMENTS :


      Finally, I get the following errors in the mgw log file:



      [Linked-exception]
      com.ibm.mq.MQException: MQJE001: Completion Code '2', Reason '2540'.
      at com.ibm.mq.MQManagedConnectionJ11.<init>(MQManagedConnectionJ11.java:246)
      at com.ibm.mq.MQClientManagedConnectionFactoryJ11._createManagedConnection(MQClientManagedConnectionFactoryJ11.java:588)
      at com.ibm.mq.MQClientManagedConnectionFactoryJ11.createManagedConnection(MQClientManagedConnectionFactoryJ11.java:630)
      at com.ibm.mq.StoredManagedConnection.<init>(StoredManagedConnection.java:106)
      at com.ibm.mq.MQSimpleConnectionManager.allocateConnection(MQSimpleConnectionManager.java:205)
      at com.ibm.mq.MQQueueManagerFactory.obtainBaseMQQueueManager(MQQueueManagerFactory.java:911)
      at com.ibm.mq.MQQueueManagerFactory.procure(MQQueueManagerFactory.java:799)
      at com.ibm.mq.MQQueueManagerFactory.constructQueueManager(MQQueueManagerFactory.java:750)
      at com.ibm.mq.MQQueueManagerFactory.createQueueManager(MQQueueManagerFactory.java:157)
      at com.ibm.mq.MQQueueManager.<init>(MQQueueManager.java:757)
      at oracle.mgw.drivers.mq.MQOPHandle.<init>(MQOPHandle.java:92)
      at oracle.mgw.drivers.mq.MQOPHandleFactory.createOPHandle(MQOPHandleFactory.java:58)
      at oracle.mgw.drivers.OPHandlePool.getHandle(OPHandlePool.java:133)
      at oracle.mgw.drivers.mq.MQLink.registerDestination(MQLink.java:384)
      at oracle.mgw.engine.DestMgr.getForeignDest(DestMgr.java:54)
      at oracle.mgw.engine.JobData.init(JobData.java:233)
      at oracle.mgw.engine.Worker.recoverJob(Worker.java:1086)
      at oracle.mgw.engine.Worker.myRun(Worker.java:98)
      at oracle.mgw.engine.ManagedThr.run(ManagedThr.java:58)
      Caused by: com.ibm.mq.jmqi.JmqiException: CC=2;RC=2540;AMQ9204: Connection to host '10.241.97.27(1414)' rejected. [1=com.ibm.mq.jmqi.JmqiException[CC=2;RC=2540;AMQ9520: Channel not defined remotely. [3=channel1]],3=10.241.97.27(1414),5=RemoteConnection.analyseErrorSegment]
      at com.ibm.mq.jmqi.remote.api.RemoteFAP.jmqiConnect(RemoteFAP.java:2022)
      at com.ibm.mq.jmqi.remote.api.RemoteFAP.jmqiConnect(RemoteFAP.java:1208)
      at com.ibm.mq.ese.jmqi.InterceptedJmqiImpl.jmqiConnect(InterceptedJmqiImpl.java:351)
      at com.ibm.mq.ese.jmqi.ESEJMQI.jmqiConnect(ESEJMQI.java:337)
      at com.ibm.mq.MQSESSION.MQCONNX_j(MQSESSION.java:923)
      at com.ibm.mq.MQManagedConnectionJ11.<init>(MQManagedConnectionJ11.java:235)
      ... 18 more
      Caused by: com.ibm.mq.jmqi.JmqiException: CC=2;RC=2540;AMQ9520: Channel not defined remotely. [3=channel1]
      at com.ibm.mq.jmqi.remote.impl.RemoteConnection.analyseErrorSegment(RemoteConnection.java:3752)
      at com.ibm.mq.jmqi.remote.impl.RemoteConnection.receiveTSH(RemoteConnection.java:2659)
      at com.ibm.mq.jmqi.remote.impl.RemoteConnection.initSess(RemoteConnection.java:1020)
      at com.ibm.mq.jmqi.remote.impl.RemoteConnection.connect(RemoteConnection.java:713)
      at com.ibm.mq.jmqi.remote.impl.RemoteConnectionSpecification.getSessionFromNewConnection(RemoteConnectionSpecification.java:400)
      at com.ibm.mq.jmqi.remote.impl.RemoteConnectionSpecification.getSession(RemoteConnectionSpecification.java:299)
      at com.ibm.mq.jmqi.remote.impl.RemoteConnectionPool.getSession(RemoteConnectionPool.java:164)
      at com.ibm.mq.jmqi.remote.api.RemoteFAP.jmqiConnect(RemoteFAP.java:1578)
      ... 23 more
      2012-09-27 12:09:11
      My guess is that the message link is incorrectly configured. When looking at the properties,
      these appear to be the ones of most uncertainty:


      v_prop.channel := 'channel1';
      v_prop.queue_manager := 'saturn.queue.manager';
      v_prop.outbound_log_queue := 'queue1';

      Can anyone add any insight?

      Thanks in advance.

      John
        • 1. Re: Oracle Messaging Gateway (to MQ) Configuration Issue
          733411
          Hi John,

          MQ reason code pointing to something wrong with channel name ?

          2540 (09EC) (RC2540): MQRC_UNKNOWN_CHANNEL_NAME
          Explanation
          An MQCONN call was issued from a client to connect to a queue manager but the attempt to establish communication failed because the queue manager did not recognise the channel name.


          so make sure listener is up and channel is active ?

          James