4 Replies Latest reply on Apr 19, 2013 12:32 PM by Balazs Papp

    Oracle Messages are stuck and can't transfer


      I hace oracle AQ which transfer messages to the Mainframe system.
      Something has happend and now they stuck in queue and can't be transfered .

      I think something has happened in Mainframe servers and they have problems in recieving messages .

      I'm a begginer in AQ and i don't know how to discover where the problem lies??

      I really appreciate if somebody can help me!

      / Hesam
        • 1. Re: Oracle Messages are stuck and can't transfer
          Balazs Papp
          Something has happend
          And that something is .... ?

          In which phase are the messages stuck?
          Are they stuck in the source site queue table?
          Does the propagation work properly?
          Do they appear in the destination site queue table?
          • 2. Re: Oracle Messages are stuck and can't transfer
            That's exactly my problem, I'm pretty sure that we haven't changed anything in the database so i just suspect that the reciever server (a Mainframe) has a problem reciving them.
            But how can i verify that?
            Yes, they are stuck in the source site table squeue.Now thet are in READY state which i see them in Queue table.
            I don't know if the propagation works properly, how can i check that?
            No, they don't appear in the destination site table because they are saying why the AQ doesn't work?????

            Thanks again for the help!
            • 3. Re: Oracle Messages are stuck and can't transfer
              Since you mentioned the propagation, i checked it and found out there is no records in DBA_QUEUE_SCHEDULES? i used the below instruction.
              What can i do now?

              *1.Check that the propagation schedule has been created and that a job queue process has been assigned.*

              Look for the entry in the DBA_QUEUE_SCHEDULES view and make sure that the status of the schedule is enabled. SCHEDULE_DISABLED must be set to 'N'. Check that it has a nonzero entry for JOBNO in table AQ$_SCHEDULES, and that there is an entry in table JOB$ with that JOBNO._

              To check if propagation is occurring, monitor the DBA_QUEUE_SCHEDULES view for the number of messages propagated (TOTAL_NUMBER)._

              If propagation is not occurring, check the view for any errors. Also check the NEXT_RUN_DATE and NEXT_RUN_TIME in DBA_QUEUE_SCHEDULES to see if propagation is scheduled for a later time, perhaps due to errors or the way it is set up._
              • 4. Re: Oracle Messages are stuck and can't transfer
                Balazs Papp
                Queue tables can have a retention time specified (check DBA_QUEUE_TABLES), so they will store messages for a fix amount of time even after they were processed.
                But as the messages doesnt appear at all on the destination site, I think the propagation is not working as expected.
                You can check the propagation properties in the DBA_PROPAGATION and DBA_QUEUE_SCHEDULES views. The second one should show that there is a J00x process running as propagation process.
                Also you can trace the propagation process by setting the following event:

                alter system set events '24040 trace name context forever, level 10';

                turn off tracing:

                alter system set events '24040 trace name context off';

                All the above should be done on the source database.

                I met a similar situation a few months ago, messages were stuck at source queue table, propagation seemed to be running, but it didnt work.
                In the propagation process trace I found entries telling that the propagation process cant find any messages in the queue to process, while there were thousands of them.
                In that case, the queue tables had different versions (IIRC 8.0.3 and 10.0, on RAC databases) that prevented the propagation job to process the messages.