6 Replies Latest reply on Jun 7, 2007 3:39 PM by 572407

    Messages remain in OAI_HUB_QUEUE after delivery

    34849
      Hi,

      The subject says it all. I have a very basic integration scenario. Two applications, one publishes, one subscribes. Only Object Copy transformations.

      But as far as I can see, all messages remain in the OAI_HUB_QUEUE table which, as a result, grows to millions of rows.

      Why?

      Arjan
        • 1. Re: Messages remain in OAI_HUB_QUEUE after delivery
          572407
          Arjan,
          There could be any number of reasons for this. I had this issue when the developer used the wrong message type for the subscribing side - the hub was looking for an adapter named 'X' which would handle message type 'Y', but couldn't find one, so the message wouldn't get dequeued and subsequent messages piled on. Fortunately this was in a development instance and I could recover and fix the issue.

          What was the last change that you made? Were things working ok prior to that?

          If you've got a few such "orphan" messages and you tried to "Sync Adapters" in iStudio you may have inadvertently created a lock on the OAI_HUB_QUEUE. If this is a dev instance, stop the repository and adapters, bounce the database to remove the locks on the queue, recreate the OAI_HUB_QUEUE ( by executing create_oai_hub_queue('oai_hub_queue'); in SQL ) , restart the repository, restart all but the "problematic" adapters and give it a go.

          Hope this helps.
          Sunder
          • 2. Re: Messages remain in OAI_HUB_QUEUE after delivery
            34849
            Thanks for your quick response

            Messages pass IConnect ok. All data we expect to arrive at the receiving end arrives. So the messages are being delivered.

            I know IConnect uses multiconsumer queues, so there has to be some sort of 'garbage collection' going on to empty the queue after the last subscribed adapter has received (dequeued) the message. But that never seems to happen.

            The only solution I know of is to recreate the queues with the script 'create_queues.sql', which is provided with ICOnnect. If OAI_HUB_QUEUE is very large this takes too long and I truncate it prior to running the create_queues script.

            To answer your questions; message types appear to be ok, as all messages travel through IConnect. It has never worked oke. We've always had to empty the hub queue.

            Btw. tried to use ICManager to empty the hub queue, but that tool seems seriously flawed.

            All this is on 10.1.2 IConnect.

            Arjan
            • 3. Re: Messages remain in OAI_HUB_QUEUE after delivery
              572407
              I recall seeing something on Metalink - Note 413106.1. Not sure if it is relevant

              "Database initialization parameter AQ_TM_PROCESSES is set to 0.

              If AQ_TM_PROCESSES is set to 0, this means no queue monitor processes will be created to
              management message queues. Therefore, queue tables will grow and not shrink.

              "
              • 4. Re: Messages remain in OAI_HUB_QUEUE after delivery
                572407
                I'm curious to know what errors/flaws you came across when using ICManager to clear the hub queue. I've used it with success, but the number of messages has always been less than 20.
                • 5. Re: Messages remain in OAI_HUB_QUEUE after delivery
                  34849
                  I'm curious to know what errors/flaws you came across
                  when using ICManager to clear the hub queue. I've
                  used it with success, but the number of messages has
                  always been less than 20.
                  I've mostly just been unable to delete messages from the Queue. Besides the fact that it doesn't appear to work, it's just a dreadful tool. They should have integrated it into the Enterprise manager or something. But with Oracle pushing ESB, I guess there's no room for IConnect.
                  I recall seeing something on Metalink - Note
                  413106.1. Not sure if it is relevant

                  "Database initialization parameter AQ_TM_PROCESSES is
                  set to 0.

                  If AQ_TM_PROCESSES is set to 0, this means no queue
                  monitor processes will be created to
                  management message queues. Therefore, queue tables
                  will grow and not shrink.
                  Thanks! Preliminary tests appear to show that this was indeed the problem. The parameter was set to 0. Thanks a lot!

                  Arjan
                  • 6. Re: Messages remain in OAI_HUB_QUEUE after delivery
                    572407
                    Another FYI on AQ_TM_PROCESSES.

                    For Oracle database versions up to and including 9.2 , aq_tm_processes must be explicitly set to a non-zero positive value.

                    10.1 onwards you are recommended not to specify the AQ_TM_PROCESSES parameter and let the database autotune the number of queue monitor processes. If this parameter has been specified, you will need to unset it and bounce the database OR explicitly alter system and set it to a positive value.