1 2 Previous Next 27 Replies Latest reply: Apr 19, 2010 2:12 PM by 757007 RSS

    Capture Queue Loop

    757007
      Hi.
      I have a Oracle Streams context defined between two servers. After a normal stop and start of the streams queues (capture, propagation and apply), the STATE column presents a sequence of the messages ENQUEUING MESSAGE and PAUSED FOR FLOW CONTROL, indefinitely. And no replication happen. Searching by solution, I discovered the problem is a corrupt archived log. My question is: can I reset the Streams Queues and start the Capture Queue today with no recreation of these queues ? How can I do this ?
      Please, some direction.
      Thanks.
        • 1. Re: Capture Queue Loop
          Anurag Tibrewal
          user4593962 wrote:
          Hi.
          I have a Oracle Streams context defined between two servers. After a normal stop and start of the streams queues (capture, propagation and apply), the STATE column presents a sequence of the messages ENQUEUING MESSAGE and PAUSED FOR FLOW CONTROL, indefinitely. And no replication happen. Searching by solution, I discovered the problem is a corrupt archived log. My question is: can I reset the Streams Queues and start the Capture Queue today with no recreation of these queues ? How can I do this ?
          Please, some direction.
          Thanks.
          Hi,

          Enqueing message means the capture process is in progress and is actively mining the archive files, capturing the data and enequing the messages in the queue and so you should not be worring about it.
          Paused for flow control means the apply/propagation process is not able to actively dequeue the message from the queue at the rate at which capture process is enqueing it. This arises generally when apply process come across a very big transaction which it is not able to apply at a faster rate.

          Even if you reset the queue or recreate the entire streams environmen you would be able to completely pass away with paused for flow control. The max you can do is check why the apply process /propagation process is not able to dequeue the message at which capture process is enqueuing it.

          Regards
          Anurag
          • 2. Re: Capture Queue Loop
            757007
            Hi.
            Ok. I agree with you. But how can I verify and resolve this problem? Have I to change or modify some parameter ?
            Thanks.
            • 3. Re: Capture Queue Loop
              Anurag Tibrewal
              Hi,

              I am not sure about the 4 digit Oracle rdbms version you are using.
              If it is 10g and above you can verify v$buffered_publishers to verify what the procesess are doing with respect to queue. And whether it is paused for unbrowsed messages or memory.

              You may like to increase the value at which streams process would paused for flow control. This is possible in 10.2.0.4 and above.
              If you are sure apply process is not able to keep up with capture process then you may also try to increase parallelism of the apply process.

              Regards
              Anurag.
              • 4. Re: Capture Queue Loop
                757007
                Hi.
                The result of SELECT * FROM v$buffered_publishers give me some high values:

                NUM_MSGS = 7038196
                CNUM_MSGS = 7038203
                LAST_ENQUEUED_MSG=7069761
                UNBROWSED_MSGS = 0
                OVERSPILLED_MSGS = 0
                MEMORY_USAGE = 98
                PUBLISHER_STATE = PUBLISHING MESSAGES

                Is this normal or not ?

                Oracle Software: 10gR2 10.2.0.4
                streams_pool_size = 112M

                Select * from v$streams_pool_advice giver me STREAMS_POOL_SIZE_FOR_ESTIMATE = 3584.

                By this statistic, I need to resize the STREAMS_POOL_SIZE parameter to 3G or more, right ?
                Or this generate no problem ?

                Thanks.
                Formiga

                Edited by: user4593962 on 15/04/2010 09:33

                Edited by: user4593962 on 15/04/2010 09:35
                • 5. Re: Capture Queue Loop
                  Anurag Tibrewal
                  Hi,

                  So currently it is publishing messages so i do not see any immediate concern.
                  Yes one concern is you have used nearly 98% of streams memory. So you may like to increase your streams_pool_size 112M is very small if you have huge stream environment. But am not sure whether your memory management is auto or manual.

                  You have 0 unbrowsed messages so its again good for you as of now.
                  Do you really see replication is taking place at this point?

                  Regards
                  Anurag
                  • 6. Re: Capture Queue Loop
                    757007
                    Hi.
                    No replication is taking place in this moment. Only changin the state PAUSED FOR FLOW CONTROL to ENQUEUING MESSAGE.
                    After a stop and start, it ask me about three archives. I locate the archives, it apply some archives and stay with the following Alert messages (alert.log) :

                    LOGMINER: session# = 181, reader process P000 started with pid=44 OS id=11125
                    LOGMINER: session# = 181, builder process P001 started with pid=54 OS id=11132
                    LOGMINER: session# = 181, preparer process P002 started with pid=106 OS id=11134
                    Thu Apr 15 14:01:27 2010
                    LOGMINER: Begin mining logfile for session 181 thread 1 sequence 42295, /oracle/archives/inmetro/inmetro_1_42295_616685316.arc
                    Thu Apr 15 14:01:27 2010
                    LOGMINER: Begin mining logfile for session 181 thread 2 sequence 10177, /oracle/archives/inmetro/inmetro_2_10177_616685316.arc
                    Thu Apr 15 14:01:54 2010
                    Trace dumping is performing id=[cdmp_20100415135841]
                    Thu Apr 15 14:02:48 2010
                    LOGMINER: End mining logfile: /oracle/archives/inmetro/inmetro_1_42295_616685316.arc
                    Thu Apr 15 14:04:23 2010
                    LOGMINER: Begin mining logfile for session 181 thread 1 sequence 42296, /oracle/archives/inmetro/inmetro_1_42296_616685316.arc
                    Thu Apr 15 14:04:23 2010
                    LOGMINER: End mining logfile: /oracle/archives/inmetro/inmetro_2_10177_616685316.arc
                    Thu Apr 15 14:05:02 2010
                    Trace dumping is performing id=[cdmp_20100415135909]
                    Thu Apr 15 14:05:02 2010
                    LOGMINER: Begin mining logfile for session 181 thread 2 sequence 10178, /oracle/archives/inmetro/inmetro_2_10178_616685316.arc
                    Thu Apr 15 14:05:48 2010
                    . . .
                    But don't terminate the application of this archives (/oracle/archives/inmetro/inmetro_2_10178_616685316.arc).
                    A believe that here is the root of my problem. And this problem is during last two days.
                    Thanks;
                    Formiga
                    • 7. Re: Capture Queue Loop
                      Anurag Tibrewal
                      Hi,
                      NUM_MSGS = 7038196
                      I am not sure why your num_msgs is so high,
                      Is propagation running?
                      Is apply process running?
                      select status, propagation_name from dba_propagation
                      
                      --- From target database
                      select status, apply_name from dba_apply                
                      select * from v$buffered_publishers
                      Regards
                      Anurag
                      • 8. Re: Capture Queue Loop
                        757007
                        Hi.

                        1)-Is propagation running? YES
                        2)-Is apply process running? YES

                        select status, propagation_name from dba_propagation
                        Result:
                        STATUS: ENABLED PROPAGATION_NAME: DO_RS_PARA_SP


                        --- From target database
                        select status, apply_name from dba_apply
                        Result:

                        STATUS : ENABLE APPLY_NAME: APPLY_STREAM

                        select * from v$buffered_publishers
                        Result:

                        NUM_MSGS: 7038196
                        QUEUE_NAME: STREAMS_QUEUE
                        SENDER_NAME: CAPTURE_STREAM
                        PUBLISHER_STATE: PUBLISHING MESSAGES

                        OK?
                        Thanks.
                        Formiga
                        • 9. Re: Capture Queue Loop
                          Anurag Tibrewal
                          Hi,

                          Do you see any change in applied_message_create_time, apply_time in dba_apply_progress. I highly doubt your apply process is able to dequeue any messages from the queue. You might have to look at the rule set for the apply process if apply process is in enabled state. Did this configuration ever worked in the past? Did you do any changes to this streams environment?

                          Below query should give you the exact latency between the capture and apply process
                          SELECT APPLY_NAME,
                          (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
                          TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') 
                          "Message Creation",
                          TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
                          APPLIED_MESSAGE_NUMBER 
                          FROM DBA_APPLY_PROGRESS;
                          
                          SELECT APPLY_NAME,
                          (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
                          TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') 
                          "Message Creation",
                          TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
                          HWM_MESSAGE_NUMBER 
                          FROM V$STREAMS_APPLY_COORDINATOR;
                          Regards
                          Anurag
                          • 10. Re: Capture Queue Loop
                            757007
                            Hi.

                            1)- Do you see any change in applied_message_create_time, apply_time in dba_apply_progress: NO
                            2)- Did this configuration ever worked in the past? NO in last three months;
                            3)- Did you do any changes to this streams environment? YES when I need add more tables to replicate;

                            Queries results.
                            SELECT APPLY_NAME,
                            (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
                            TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
                            "Message Creation",
                            TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
                            APPLIED_MESSAGE_NUMBER
                            FROM DBA_APPLY_PROGRESS;

                            Result:
                            APPLY_NAME: APPLY_STREAM
                            Latency in Seconds: 37,00000000000000000000000000000000000002
                            Message Creation : 17:36:11 04/12/10
                            Apply Time : 17:36:48 04/12/10
                            APPLIED_MESSAGE_NUMBER : 7785969471211

                            SELECT APPLY_NAME,
                            (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
                            TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
                            "Message Creation",
                            TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
                            HWM_MESSAGE_NUMBER
                            FROM V$STREAMS_APPLY_COORDINATOR;

                            Result:
                            APPLY_NAME: APPLY_STREAM
                            Latency in Seconds: Blanks
                            Message Creation: Blanks
                            Apply Time: Blanks
                            HWM_MESSAGE_NUMBER: 7785969471211

                            Ok?
                            Thanks.
                            Formiga
                            • 11. Re: Capture Queue Loop
                              Anurag Tibrewal
                              Hi,

                              You meant to say for past 3 months your stream process is not replicating?
                              Moreover your co-ordinator process is not showing any result i.e. i doubt it is actually doing anything.
                              Your dba_apply_progress is showing a lag of less than 1 minitue. This raises a doubt that your ruleset are not properly configured. I am sure you would be like to check your ruleset at the apply and capture side.

                              Regards
                              Anurag
                              • 12. Re: Capture Queue Loop
                                757007
                                Hi.

                                1)-You meant to say for past 3 months your stream process is not replicating? No. the replication stopped after a database restart two days ago in my source server (capture, propagation). The destination server (apply) is ok. No restarted.
                                During last three months all replication was good. No problem.
                                Thanks.
                                Formiga
                                • 13. Re: Capture Queue Loop
                                  Anurag Tibrewal
                                  Hi,

                                  You need to stop the apply process, before stopping the capture process if there is any maintainance in the database.
                                  You can now stop and restart the apply process to see if apply process has started applying the changes.

                                  You can query below views to check the progress of apply process.
                                  v$streams_apply_server
                                  v$streams_apply_reader
                                  v$streams_apply_coordinator
                                  Moreover you can query v$buffered_queue, v$buffered_publisher to make sure num_rows are decreasing and cnum_rows are increasing.

                                  Regards
                                  Anurag
                                  • 14. Re: Capture Queue Loop
                                    757007
                                    Hi.

                                    1)-You need to stop the apply process, before stopping the capture process - OK
                                    2)-You can now stop and restart the apply process to see if apply process has started applying the changes - OK
                                    3)-See v$streams_apply_server , v$streams_apply_reader, v$streams_apply_coordinator - OK, No changes. No changes happened. This mean than NO DATA (replication) is going out to the Server destination (apply). Right ?
                                    4)- You can query v$buffered_queue, v$buffered_publisher to make sure num_rows are decreasing and cnum_rows are increasing - OK. But nothing is decreasing.

                                    I have no idea about what is happening.
                                    Again: perhaps the data is not going out of Source Database (capture, propagation).
                                    But, where to discover or find informations ?

                                    Thanks.
                                    Formiga

                                    Edited by: user4593962 on 15/04/2010 13:08
                                    1 2 Previous Next