Troubleshooting Streams Capture when status is Paused For Flow Control (Doc ID 746247.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.

Symptoms

For DBA and Support to diagnose and resolve Streams Capture is stuck with Paused for Flow Control

Definition

This warning message typically occurs when the target site is unable to keep up with the rate of messages flowing from the source site.In 10g Flow control is automatically enabled to reduce the rate at which events are being enqueued into the staging area. But this message also indicate other problems which will be covered in this note.

If "Streams AQ: enqueue blocked due to flow control" occurs,it simply means that we cannot queue messages until the components that had fallen behind catch up .As long as you see CAPTURE getting in and out of FLOWCONTROl status,there should not be any problem.


Implement Flow Control (9.2 source site only):

When the threshold for memory of the buffer queue is exceeded, Streams will write the messages to disk.This is sometimes referred to as "spillover". When this occurs, Streams can no longer take advantage of the in-memory queue optimization. One technique to minimize this spillover is to implement a form of flow control.
The manual scripts of <Note 259609.1> Script to Prevent Excessive Spill of Message from the Streams Buffer Queue to disk should only be used on Oracle 9.2 Streams source databases (ie, databases running 9.2 streams capture processes).

In 9i ,implement a "heartbeat" table. To ensure that the applied_scn of the DBA_CAPTURE view is updated periodically, implement a "heart beat" table. Implementing a heartbeat table ensures that the metadata is updated frequently. Additionally, the heartbeat table provides quick feedback as to the health of the streams replication.
In Oracle10g source and target databases, Streams flow control is automatic. However, if Streams is used in a 9iR2/10g combination with 9iR2 as the source site, it is imperative that the manual 9iR2 flow control scripts be used at the source site.

Changes

Where and which view to check the symptoms

a) The healthcheck script will show capture status enabled and capture statistics as PAUSED FOR FLOW
    CONTROL . Example from SOURCE Healthcheck Script

Capture statistics:

C001 CAPUSR_CAP TURE 03:32:25 09/13/08 193 404 PAUSED FOR FLOW CONTROL

or from

SELECT CAPTURE_NAME, STATE FROM V$STREAMS_CAPTURE;

CAPTURE_NAME STATE
-------------- ------------------------
STREAM_CAPTURE PAUSED FOR FLOW CONTROL



b) V$BUFFERED_SUBSCRIBERS may show TOO MANY UNBROWSED MESSAGES
     Example from SOURCE Healthcheck Script(HC)

++ BUFFERED PUBLISHERS ++

Queue Name: STREAMS_QUEUE
SENDER_NAME: STREAM_CAPTURE
Current Number of Msgs in Queue: 15521
MEMORY_USAGE: 15
UNBROWSED_MSGS: 5001
PUBLISHER_STATE: IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES
or from Flow Control Query:

COLUMN queue_schema FORMAT A12
COLUMN queue_name FORMAT A20
SELECT queue_schema, queue_name, unbrowsed_msgs, overspilled_msgs, memory_usage, publisher_state
FROM V$BUFFERED_PUBLISHERS;

NOTE: The Flow Control query will show:

* the queue owner and name
* the number of messages that have been enqueued, but not browsed
* the number of messages that have been spilled, but not browsed
* the percentage of the streams pool that is being used (or 0 if there is no streams pool)
* the state of the publisher. Possible values are:

- PUBLISHING MESSAGES (normal)
- IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES
- IN FLOW CONTROL: OVERSPILLED MESSAGES
- IN FLOW CONTROL: INSUFFICIENT MEMORY AND UNBROWSED MESSAGES

This last column will really help to determine why flow control is being enabled for a system and what can
be done to resolve the matter.


c) Propagation schedule may throw Ora-25307 error   
    Example from SOURCE Healthcheck Script(HC)

++ SCHEDULE FOR EACH PROPAGATION++
Propagation Name: STREAM_PROPAGATE
Status : Enabled
Error Message: ORA-25307: Enqueue rate too high, flow control enabled

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms