Solved

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

Posted on 2014-12-22
18
526 Views
Last Modified: 2016-06-17
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
Comment
Question by:Chad Hilton
  • 9
  • 3
  • 2
  • +1
18 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40514480
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40514912
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
 

Author Comment

by:Chad Hilton
ID: 40514946
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
 

Author Comment

by:Chad Hilton
ID: 40515069
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40515070
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
 

Author Comment

by:Chad Hilton
ID: 40515308
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
 

Author Comment

by:Chad Hilton
ID: 40515338
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
 
LVL 61

Expert Comment

by:gheist
ID: 40523353
Are you sure you ever dequeue? The purpose of queue is to make sure transactions are processed in integral way.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Chad Hilton
ID: 40523545
I'm sure messages aren't dequeueing because they remain in the queue table with a ready state (STATE = 0).
0
 
LVL 61

Expert Comment

by:gheist
ID: 40523572
Yes, they should be dequeued after processing. Since thay are not processed thay are queued up for processing until dequeued.
0
 

Author Comment

by:Chad Hilton
ID: 40547229
I returned from vacation.  The problem persists.  Are there any suggestions?  Thanks.
0
 
LVL 61

Expert Comment

by:gheist
ID: 40547450
Yes - dequeue the messages?
0
 

Author Comment

by:Chad Hilton
ID: 40558530
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40558653
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
 

Author Comment

by:Chad Hilton
ID: 40560136
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
 

Accepted Solution

by:
Chad Hilton earned 0 total points
ID: 40711190
This was resolved by adding DBMS_AQ.NO_WAIT to dequeue_options.  Thanks for everyone's help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now