[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2210
  • Last Modified:

In Oracle AQ (Advanced Queueing), why are the messages not dequeuing and stuck in the ready state?

Messages are successfully enqueueing but not dequeueing and are stuck in ready state (STATE = 0).  The ENQ_TIME is 5 hours ahead of system time.  In one environment, AQ is working (10g 10.2.0.4.0).  In the other environment, it is not working (11g 11.2.0.3.0).
0
Chad Hilton
Asked:
Chad Hilton
  • 9
  • 3
  • 2
  • +1
1 Solution
 
Wasim Akram ShaikCommented:
Check for the subscriber which dequeues the message.. the subscriber may not be in ready state.. if you are using plsql program to dequeue it, then you may have to search for the errors.. it may be throwing end of all messages or no messages.. then we may take an appropriate action depending on the error..
0
 
Mark GeerlingsDatabase AdministratorCommented:
We just had this happen in our 11.2.0.3 database, and the problem here was exactly what Wasim suggested: we had no subscriber active to dequeue the messages.  In our case, we ended up dropping the queue and purging the messages, since we determined that we actually didn't need them.
0
 
Chad HiltonComputer ProgrammerAuthor Commented:
We don't explicitly declare any subscribers - we use package procedures to enqueue and dequeue.  Both environments mentioned in my previous post have the same code in the package procedures.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Chad HiltonComputer ProgrammerAuthor Commented:
I just did the following:

1. Purged queue table
2. Stopped queues
3. Dropped queues
4. Dropped queue table
5. Created queue table
6. Created queues
7. Started queues

I tested once and a record was inserted in the queue table:

MSGID      <msgid>
CORRID      
PRIORITY      1
STATE      0
DELAY      
EXPIRATION      
TIME_MANAGER_INFO      
LOCAL_ORDER_NO      0
CHAIN_NO      0
CSCN      0
DSCN      0
ENQ_TIME      12/23/2014 4:33:43.338902 PM
ENQ_UID      <enq_uid>
ENQ_TID      <enq_tid>
DEQ_TIME      
DEQ_UID      
DEQ_TID      
RETRY_COUNT      0
EXCEPTION_QSCHEMA      
EXCEPTION_QUEUE      
STEP_NO      0
RECIPIENT_KEY      0
DEQUEUE_MSGID      
SENDER_NAME      
SENDER_ADDRESS      
SENDER_PROTOCOL      
USER_DATA      <user_data>
USER_PROP      

Notice the RETRY_COUNT is 0.  The ENQ_TIME is 5 hours ahead.
0
 
Wasim Akram ShaikCommented:
That's what I have asked for plsql you don't need to specify the subscription explicitly. However I want to know what error have you encountered. Run an anonymous block and in exception when others section capture sqlerrm...
0
 
Chad HiltonComputer ProgrammerAuthor Commented:
In the procedures to enqueue and dequeue, there are no errors.  Following is the plsql to dequeue:

CREATE OR REPLACE PACKAGE BODY
pkg_1
AS  

    PROCEDURE create_thing ( context IN RAW,
                                reginfo IN sys.aq$_reg_info,
                                descr IN sys.aq$_descriptor,
                                payload IN RAW,
                                payloadl IN NUMBER )
    IS  
        dequeue_options dbms_aq.dequeue_options_t;
        message_properties dbms_aq.message_properties_t;
        message_handle RAW(16);
        message msg_type;
    BEGIN

        dequeue_options.msgid := descr.msg_id;
        dequeue_options.consumer_name := descr.consumer_name;
        DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
                        dequeue_options => dequeue_options,
                        message_properties => message_properties,
                        payload => message,
                        msgid => message_handle);

        pkg_2.create_thing( p_thing_id => message.thing_id );
  
        UPDATE table t
           SET creation_complete = 1
         WHERE id = message.thing_id;

        COMMIT;

    EXCEPTION
        WHEN OTHERS
        THEN

            ROLLBACK;
   
            plog.error(SQLERRM);
            plog.full_call_stack;
                    
    END create_thing;
    
    
    PROCEDURE delete_thing ( context IN RAW,
                                reginfo IN sys.aq$_reg_info,
                                descr IN sys.aq$_descriptor,
                                payload IN RAW,
                                payloadl IN NUMBER )
    IS  
        dequeue_options dbms_aq.dequeue_options_t;
        message_properties dbms_aq.message_properties_t;
        message_handle RAW(16);
        message msg_type;
    BEGIN

        dequeue_options.msgid := descr.msg_id;
        dequeue_options.consumer_name := descr.consumer_name;
        DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
                        dequeue_options => dequeue_options,
                        message_properties => message_properties,
                        payload => message,
                        msgid => message_handle);

        pkg_2.delete_thing( p_thing_id => message.thing_id );
  
        COMMIT;

    EXCEPTION
        WHEN OTHERS
        THEN

            ROLLBACK;
    
            plog.error(SQLERRM);
            plog.full_call_stack;
                    
    END delete_thing;    

END pkg_1;

Open in new window

0
 
Chad HiltonComputer ProgrammerAuthor Commented:
Following is the plsql to enqueue:

CREATE OR REPLACE PACKAGE BODY
pkg_2
AS
    FUNCTION queue_create_thing ( <parameters> )
        RETURN NUMBER
    IS
        enqueue_options     dbms_aq.enqueue_options_t;
        message_properties  dbms_aq.message_properties_t;
        message_handle      RAW(16);
        v_message           msg_type;
        v_thing_id          things.id%TYPE;
    BEGIN
	
        v_message := msg_type( <parameters> );
        dbms_aq.enqueue(queue_name => '<queue name>',
                        enqueue_options => enqueue_options,
                        message_properties => message_properties,
                        payload => v_message,
                        msgid => message_handle);

        RETURN v_thing_id;

    EXCEPTION

        WHEN OTHERS
        THEN

           errpkg.record_and_stop (SQLCODE);

    END queue_create_thing;
	
    PROCEDURE queue_delete_thing( <parameters> )
    IS
        enqueue_options     dbms_aq.enqueue_options_t;
        message_properties  dbms_aq.message_properties_t;
        message_handle      RAW(16);
        v_message           msg_type;
    BEGIN

        v_message := msg_type( <parameters> );
        dbms_aq.enqueue(queue_name => '<queue name>',
                        enqueue_options => enqueue_options,
                        message_properties => message_properties,
                        payload => v_message,
                        msgid => message_handle);

    END;
END pkg_2;

Open in new window

0
 
gheistCommented:
Are you sure you ever dequeue? The purpose of queue is to make sure transactions are processed in integral way.
0
 
Chad HiltonComputer ProgrammerAuthor Commented:
I'm sure messages aren't dequeueing because they remain in the queue table with a ready state (STATE = 0).
0
 
gheistCommented:
Yes, they should be dequeued after processing. Since thay are not processed thay are queued up for processing until dequeued.
0
 
Chad HiltonComputer ProgrammerAuthor Commented:
I returned from vacation.  The problem persists.  Are there any suggestions?  Thanks.
0
 
gheistCommented:
Yes - dequeue the messages?
0
 
Chad HiltonComputer ProgrammerAuthor Commented:
Thanks for the response.  I purged the queue table, stopped and dropped the queue, purged the recycle bin, created and started the queue, and added the following code to the dequeue call and am now seeing correct ENQ_TIMEs in the view, but the queue table ENQ_TIME is still 5 hours ahead:

 enqueue_options.deq_condition := 'FROM_TZ(enq_time,''-05:00'') < sysdate';

Open in new window


The messages remain in the ready state and are not dequeued.
0
 
Mark GeerlingsDatabase AdministratorCommented:
They are not dequeued at all, or they only get dequeued five hours later than you expect?  This looks to me like you may have a time zone setting either on the database server or possibly on an application server that is set incorrectly, or set differently that you expect.  On the database server, this could be set in any of at least these three places:
1. environment variable for the O/S user that runs the Oracle database
2. in the database parameter file (spfile or init*.ora)
3. in an after-startup or after-logon database trigger
0
 
Chad HiltonComputer ProgrammerAuthor Commented:
In working and non-working environments within the same time zone, the following are consistent.  In table SYSTEM.AQ$_QUEUE_TABLES, TIMEZONE is -04:00.  SYSDATE is 5 hours ahead of current real time.  OS: date = current real time; date %Z = EST; date %z = -0500; clock ZONE="America/New_York"; clock UTC=true; clock ARC=false.  The database parameter, set in init.ora, is default.
0
 
Chad HiltonComputer ProgrammerAuthor Commented:
This was resolved by adding DBMS_AQ.NO_WAIT to dequeue_options.  Thanks for everyone's help.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 9
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now