5 Replies Latest reply on May 3, 2007 3:22 PM by 436342

    OAI queue volume, re-publishing, error alerts and other gotchas

      Hi interConnect Gurus,
      Last week there was a power outage at my client site which resulted in a lot of questions being asked about how OAI can be/should be configured to handle abnormal data loss due to unexpected calamities. I have read through the various forum posts that address lost messages and realize that there may be a few options available to handle lost messages, but I have a few fundamental questions.

      1) If there is a power outage and the subscribing adapter is in the process of dequeuing a message, is the message still available to dequeue when the adapter is brought back up? Or, put differently, since the adapter was not shut down gracefully, what happens to messages that were being processed?
      2) Let's say a message cannot be processed by the subscribing adapter because of tablespace issues or invalid objects or whatever. If the adapter is restarted, I have noticed that the message is no longer available. Is there a setting that I should be looking at, so that unprocessed messages are available after a restart?
      3) If a message cannot be processed, other than looking at the log file, is there any other alert capability that comes out of the box with OAI so that the DBAs or some authorized administrator knows that there is an issue?
      4)What are the best practices when it comes to message recovery?

      Thanks in advance for any suggestion.

        • 1. Re: OAI queue volume, re-publishing, error alerts and other gotchas
          5) What are the best practices for naming applications to simplify the migration process from DEV to QA to PROD?
          • 2. Re: OAI queue volume, re-publishing, error alerts and other gotchas
            Chris Slattery
            1,2,4 best ask for definitive answers from Oracle via SR

            Since your worried about basic OAI features they should help you. since it's all based on AQ multi subscriber queues you may have specific Adapter problems so again RAISE an SR

            As for 3 - yes there is message alerting check out the capabilities as per the manual - monitor the error queues and so forth.

            As for 5 - no idea.
            • 3. Re: OAI queue volume, re-publishing, error alerts and other gotchas
              Thanks Chris.

              I found some answers in the InterConnect User's Guide (chapter 10).

              "if the message could not be delivered to the target application by the adapter,
              then the message is moved to the oai_agent_error table. But if the adapter is
              down, then the message will be persisted in the queue, until the adapter is up and

              As far as monitoring is concerned, I am looking at using OEM to generate alerts.
              • 4. Re: OAI queue volume, re-publishing, error alerts and other gotchas
                Also found note 392616.1 on Metalink:

                "The adapter will write the error'd-out message into the oai_agent_error table and then drop the
                message so that waiting messages can get processed. The OEM based management console provides
                capabilities to see all messages in the oai_agent_error table, edit those and resent them.

                Sending/receiving adapters are both populated during runtime. A sending adapter enqueues messages
                into the oai_hub_qeue and a receiving adapter dequeues messages from it. If an error encounters,
                both sending and receiving adapters populate the oai_agent_error table. These messages can then
                be resubmitted via ICManager. Messages reside either in oai_hub_queue or in error table to
                guarantee delivery.
                • 5. Re: OAI queue volume, re-publishing, error alerts and other gotchas
                  If you have things like tablespace issue on the subscribing side then the messages should reside in the Hub queue and once the error is resolved then it should be delivered.

                  Any message that is written to the adapter log file is also written to a database table oai_agent_error (i think, from memeory) therefore you could query this table for new records to check if errors are being generated.

                  Also you could set the adapter log level to 0, so that nothing is written to the log unless an error occurs, therefore you could keep checking the size of the file and if it is greater than 0 then something has gone wrong.

                  There are someother options if you want to create a message warehouse so that any message can be resent, although you may want to check with Oracle that this approach is supported.

                  You can set AQ retention on the Hub queue (which is just a standard AQ) so that all messages, ones processed, expired or unprocessed reside on the Hub queue for a period of times. Then copy all messages off into a table where one of the columns is based on same datatype as the AQ datatype.

                  Then you have the ability to enqueue these messages for a specific adapter with some simple PL/SQL..