This discussion is archived
5 Replies Latest reply: Sep 8, 2012 4:34 AM by 758358 RSS

Propogation Not Working - Only after reboot

cleme1a Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points