5 Replies Latest reply: Sep 8, 2012 6:34 AM by 758358 RSS

    Propogation Not Working - Only after reboot

    cleme1a
      Hi,

      I migrated an AQ solution which sends messages between three instances from a development environment to an UAT environment and my propogation from one instance to another is not working as per development.

      When I enqueue a message I note that the enq_time is one hour behind the actual sysdate.

      All the messages are been stored in the queue table and are propogated through only when the instance is restarted.

      I think it is something to do with timezones, but cannot find any solution.

      FYI - the datbase is 11i Express.

      The progration job is:

      dbms_aqadm.schedule_propagation(queue_name => 'ITEMS',
      destination => 'DIBS_MSG_DIBS_EXT'
      ,start_time => sysdate
      ,next_time => 'sysdate + 100/86400'
      ,duration => 60
      ,latency => 3000
      ,destination_queue => 'DIBS_STREAMS.ITEMS');

      Any help greatly appreciated.

      Thanks.
        • 1. Re: Propogation Not Working - Only after reboot
          758358
          Hi,

          Could you post some additional information?
          select SCHEMA, QNAME, DESTINATION, SCHEDULE_DISABLED, PROCESS_NAME, LAST_RUN_DATE, NEXT_RUN_DATE, FAILURES, LAST_ERROR_MSG from DBA_QUEUE_SCHEDULES;
          
          select p.SPID, p.PROGRAM, j.JOB_NAME, s.EVENT
          from v$PROCESS p, DBA_SCHEDULER_RUNNING_JOBS jr, V$SESSION s, DBA_SCHEDULER_JOBS j
          where s.SID=jr.SESSION_ID
          and s.PADDR=p.ADDR
          and jr.JOB_NAME=j.JOB_NAME
          and j.JOB_NAME like '%AQ_JOB$_%';
          Are any messages being posted to the database alert log or job queue trace files?

          When you say the enq_time is 1 hour in front of sysdate, this seems odd if this issue persists after an instance restart, I have seen behavior like this through daylight savings changes if a process which has been running prior to the time change is not restarted but not repeated. What process enqueues the messages on to this queue?

          Thanks,
          Paul
          • 2. Re: Propogation Not Working - Only after reboot
            cleme1a
            Hi,

            Thanks for taking the time to have a look at this.

            I have posted the results from your queries below.

            Best Regards,

            Andrew.



            SCHEMA     DIBS_STREAMS
            QNAME     ITEMS
            DESTINATION     DIBS_STREAMS"".""ITEMS""@DIBS_MSG_DIBS_EXT
            SCHEDULE_DISABLED     N
            PROCESS_NAME     
            LAST_RUN_DATE     
            NEXT_RUN_DATE     07-SEP-12 17.48.43.000000000 EUROPE/LONDON
            FAILURES     0
            LAST_ERROR_MSG     
                 


            SPID     PROGRAM     JOB_NAME     EVENT
            1000     ORACLE.EXE (J000)     AQ_JOB$_65     Streams AQ: waiting for messages in the queue
            3076     ORACLE.EXE (J003)     AQ_JOB$_64     Streams AQ: waiting for messages in the queue
            3652     ORACLE.EXE (J005)     AQ_JOB$_63     Streams AQ: waiting for messages in the queue
            4032     ORACLE.EXE (J004)     AQ_JOB$_61     Streams AQ: waiting for messages in the queue
            • 3. Re: Propogation Not Working - Only after reboot
              cleme1a
              The following PL/SQL procedure enqueues the message

              procedure ITEM_2_MSG_EQ -- scheduled job, maybe combine into single scheduled call
              is
              CURSOR cIte
              IS
              SELECT MESSAGE FROM aq_items_message_t WHERE status = 'AWAITING PROCESS'
              FOR UPDATE;

              BEGIN
              -- this procedure gets a basket information stores it into an datatype
              -- and the create a AQ message with the basket information stored as
              -- the data type
              -- open a cursor for the package header
              FOR rcIte IN cIte
              LOOP
              --
              -- submit the message to the queue
              DECLARE
              rc binary_integer;
              nq_opt dbms_aq.enqueue_options_t;
              nq_pro dbms_aq.message_properties_t;
              msgid raw(16);
              lErrm VARCHAR2(80);
              BEGIN
              nq_opt.visibility := dbms_aq.immediate;
              nq_pro.expiration := dbms_aq.never;


              dbms_aq.enqueue('ITEMS',nq_opt,nq_pro,rcIte.message,msgid);

              UPDATE aq_items_message_t
              SET status = 'QUEUED'
              , enqueued_date = SYSDATE
              WHERE CURRENT OF cIte;



              EXCEPTION

              WHEN OTHERS THEN

              lErrm := SUBSTR(SQLERRM, 1, 80);
              dbms_output.put_line(lErrm);
              END;

              END LOOP;

              COMMIT;


              END ITEM_2_MSG_EQ;


              It just seems very odd that:

              a: it works fine in another environment
              b: all the messages propogate after a restart which is stand DBMS_JOB functionality when next date is < sysdate
              • 4. Re: Propogation Not Working - Only after reboot
                cleme1a
                Hi,

                Thanks for reading.

                I reimported the schemas and purged all the table, and restarted the props.

                This seemed to sort the issue

                I don't know for sure, but I think the orphan data that came across when imported the schema must have been corrputing things.

                Lesson learnt - AQ is a good tool, but you have to be 'bang on'.

                If you are going to import AQ schemas, make sure your queue tables are purged.

                However, hands up - my fault this one.
                • 5. Re: Propogation Not Working - Only after reboot
                  758358
                  Hi,

                  Yes if there is data in the underlying queue objects that doesn't properly hang together (i.e. no corresponding entry in the IOT's for instance) then this can break propagation - you should usually see errors in the job queue trace files when this sort of thing is a problem.

                  Glad you got to the bottom of it.

                  thanks
                  Paul