6 Troubleshooting XStream

An OCI Client Application Cannot Attach to the Outbound Server

An XStream client application cannot attach to an outbound server using the Oracle Call Interface (OCI) OCIXStreamOutAttach() function.

The following sections describe possible problems and their solutions.

Problem 1: Client Application Not Connected as Connect User

The client application is not connected as the outbound server's connect user to the outbound server's database. The client application connected to the database as a different user.

Solution 1

To correct problem 1: 

• Modify the client application to connect to the database as the connect user before attaching to the outbound server.

Problem 2: Client Application Not Passing Service Handle

The client application is not passing a service handle to the outbound server.

Solution 2

To correct problem 2: 

• Modify the client application so that it passes a service handle using OCISvcCtx and not OCIServer.

Changes Are Failing to Reach the Client Application in XStream Out

In an XStream Out configuration, database changes that should be captured and streamed to the XStream client application are not reaching the client application.

The following sections describe possible problems and their solutions.

Problem 1: Capture Process Has Fallen Behind

The capture process has fallen behind.

To determine whether the capture process has fallen behind: 

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
   COLUMN CREATE_MESSAGE HEADING 'Last LCR|Create Time'
   COLUMN ENQUEUE_MESSAGE HEADING 'Last|Enqueue Time'
   
   SELECT CAPTURE_NAME,
          TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_MESSAGE,
          TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_MESSAGE
     FROM V$STREAMS_CAPTURE;
   

   This query displays the time when the capture process last created a logical change record (LCR) and the time when the capture process last enqueued an LCR. If the times returned are before the time when the database changes were made, then the capture process must catch up and capture the changes.

Solution 1

No action is required. Normally, the capture process will catch up on its own without the need for intervention.

Problem 2: Rules or Rule-Based Transformation Excluding Changes

Rules or rule-based transformations are excluding the changes that should be captured.

Rules determine which LCRs are captured by a capture process, sent from a source queue to a destination queue by a propagation, and sent to an XStream client application by an outbound server. If the rules are not configured properly, then the client application might not receive the LCRs it should receive. The client application might also receive LCRs that it should not receive.

Rule-based transformations modify the contents of LCRs. Therefore, if the expected change data is not reaching the client application, it might be because a rule-based transformation modified the data or deleted the data. For example, a DELETE_COLUMN declarative rule-based transformation removes a column from an LCR.

Solution 2

To correct problem 2: 

• Check the rules and rule-based transformations that are configured for each component in the stream from the capture process to the client application, and correct any problems.

Problem 3: LCRs Blocked in the Stream

If the capture process has not fallen behind, and there are no problems with rules or rule-based transformations, then LCRs might be blocked in the stream for some other reason. For example, a propagation or outbound server might be disabled, a database link might be broken, or there might be another problem.

You can track an LCR through a stream using one of the following methods:

• Setting the message_tracking_frequency capture process parameter to 1 or another relatively low value

• Running the SET_MESSAGE_TRACKING procedure in the DBMS_STREAMS_ADM package

After using one of these methods, use the V$STREAMS_MESSAGE_TRACKING view to monitor the progress of LCRs through a stream. By tracking an LCR through the stream, you can determine where the LCR is blocked.

Solution 3

To correct problem 3: 

• Take the appropriate action based on the reason that the LCR is blocked. For example, if a propagation is disabled, then enable it.

LCRs Streaming from an Outbound Server Are Missing Extra Attributes

LCRs streaming from an outbound server are expected to include extra attributes, but these attributes are not included in the LCRs.

LCRs can contain the following extra attributes related to database changes:

• row_id

• serial#

• session#

• thread#

• tx_name

• username

By default, a capture process does not capture these extra attributes. If you want extra attributes to be included in LCRs streamed from an outbound server to an XStream client application, but the LCRs do not contain values for extra attributes, then make sure the capture process that captures changes for the outbound server is configured to capture values for the extra attributes.

The following sections describe the possible problem and its solution.

Problem: Capture Process Not Configured to Capture Extra Attributes

The capture process is not configured to capture the required extra attributes.

To display the extra attributes currently being captured by the capture processes in a database: 

1. Connect to the database running the capture process as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN CAPTURE_NAME HEADING 'Capture Process' FORMAT A30
   COLUMN ATTRIBUTE_NAME HEADING 'Attribute Name' FORMAT A30
    
   SELECT CAPTURE_NAME, ATTRIBUTE_NAME
     FROM DBA_CAPTURE_EXTRA_ATTRIBUTES
     WHERE INCLUDE = 'YES'
     ORDER BY CAPTURE_NAME;
   

   If an extra attribute is not displayed by this query, then it is not being captured.

Solution

To solve the problem, configure the capture process to capture the required extra attributes: 

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package.

BEGIN
  DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
    capture_name   => 'xcapture',
    attribute_name => 'tx_name',
    include        => TRUE);
END;
/

The XStream Out Client Application Is Unresponsive

The XStream client application in an XStream Out configuration is unresponsive.

The following sections describe the possible problem and its solution.

Problem 1: Streams Pool Size Is Too Small

The Streams pool size might be too small.

To determine whether the Streams pool size is too small: 

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following queries at the database that contains the outbound server:

   • Query the V$PROPAGATION_RECEIVER view.:

     SELECT STATE FROM V$PROPAGATION_RECEIVER;
     

     If the state is WAITING FOR MEMORY, then consider increasing the Streams pool size.

   • Query the V$STREAMS_POOL_STATISTICS view.:

     SELECT TOTAL_MEMORY_ALLOCATED/CURRENT_SIZE FROM V$STREAMS_POOL_STATISTICS;
     

     If the value returned is .90 or greater, then consider increasing the Streams pool size.

   • If the outbound server receives changes from a capture process that is running on the same database, then query the V$STREAMS_CAPTURE view.:

     SELECT STATE FROM V$STREAMS_CAPTURE;
     

     If the state is WAITING FOR BUFFER QUEUE TO SHRINK, then increase the Streams pool size.

Solution 1

To correct problem 1: 

• Increase the Streams pool size by modifying the STREAMS_POOL_SIZE initialization parameter or by modifying other initialization parameters related to memory.

Problem 2: Programming Errors

If there is enough memory in the Streams pool, then check your client application for programming errors.

Solution 2

To correct problem 2: 

• Correct the programming errors.

XStream In Cannot Identify an Inbound Server

If an XStream In configuration cannot identify an inbound server, then the following error is returned:

ORA-26840: STREAMS unable to identify an apply for the source database "%s"

The following sections describe the possible problem and its solution.

Problem: Multiple Subscribers to the Inbound Server's Queue

The ORA-26840 error indicates that there are multiple subscribers to the queue used by the inbound server. Subscribers can include inbound servers, outbound servers, apply processes, and propagations.

To determine whether there are multiple subscribers to the inbound server's queue: 

1. Connect to the inbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   SELECT APPLY_NAME SUBSCRIBER, QUEUE_NAME FROM DBA_APPLY UNION
     SELECT PROPAGATION_NAME, SOURCE_QUEUE_NAME
     FROM DBA_PROPAGATION
     ORDER BY QUEUE_NAME;
   

   You can add a WHERE clause to the query to limit the output to the inbound server's queue.

Solution

To correct the problem: 

• If the query returns multiple subscribers to the inbound server's queue, then reconfigure the subscribers so that the inbound server is the only subscriber.