32 Troubleshooting Propagation

The following topics describe identifying and resolving common propagation problems in an Oracle Streams environment:

Does the Propagation Use the Correct Source and Destination Queue?

If messages are not appearing in the destination queue for a propagation as expected, then the propagation might not be configured to propagate messages from the correct source queue to the correct destination queue.

For example, to check the source queue and destination queue for a propagation named dbs1_to_dbs2, run the following query:

COLUMN SOURCE_QUEUE HEADING 'Source Queue' FORMAT A35
COLUMN DESTINATION_QUEUE HEADING 'Destination Queue' FORMAT A35

SELECT 
  p.SOURCE_QUEUE_OWNER||'.'||
    p.SOURCE_QUEUE_NAME||'@'||
    g.GLOBAL_NAME SOURCE_QUEUE, 
  p.DESTINATION_QUEUE_OWNER||'.'||
    p.DESTINATION_QUEUE_NAME||'@'||
    p.DESTINATION_DBLINK DESTINATION_QUEUE 
  FROM DBA_PROPAGATION p, GLOBAL_NAME g
  WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2';

Your output looks similar to the following:

Source Queue                        Destination Queue
----------------------------------- -----------------------------------
STRMADMIN.QUEUE1@DBS1.EXAMPLE.COM   STRMADMIN.QUEUE2@DBS2.EXAMPLE.COM

If the propagation is not using the correct queues, then create a different propagation. You might need to remove the existing propagation if it is not appropriate for your environment.

See Also:

Is the Propagation Enabled?

For a propagation job to propagate messages, the propagation must be enabled. If messages are not being propagated by a propagation as expected, then the propagation might not be enabled.

You can find the following information about a propagation:

  • The database link used to propagate messages from the source queue to the destination queue

  • Whether the propagation is ENABLED, DISABLED, or ABORTED

  • The date of the last error, if there are any propagation errors

  • If there are any propagation errors, then the error number of the last error

  • The error message of the last error, if there are any propagation errors

For example, to check whether a propagation named streams_propagation is enabled, run the following query:

COLUMN DESTINATION_DBLINK HEADING 'Database|Link'      FORMAT A15
COLUMN STATUS             HEADING 'Status'             FORMAT A8
COLUMN ERROR_DATE         HEADING 'Error|Date'
COLUMN ERROR_MESSAGE      HEADING 'Error Message'      FORMAT A35
 
SELECT DESTINATION_DBLINK,
       STATUS,
       ERROR_DATE,
       ERROR_MESSAGE
  FROM DBA_PROPAGATION
  WHERE PROPAGATION_NAME = 'STREAMS_PROPAGATION';

If the propagation is disabled currently, then your output looks similar to the following:

Database                 Error
Link            Status   Date      Error Message
--------------- -------- --------- -----------------------------------
D2.EXAMPLE.COM  DISABLED 27-APR-05 ORA-25307: Enqueue rate too high, f
                                   low control enabled

If there is a problem, then try the following actions to correct it:

  • If a propagation is disabled, then you can enable it using the START_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package, if you have not done so already.

  • If the propagation is disabled or aborted, and the Error Date and Error Message fields are populated, then diagnose and correct the problem based on the error message.

  • If the propagation is disabled or aborted, then check the trace file for the propagation job process. The query in "Displaying Information About the Schedules for Propagation Jobs" displays the propagation job process.

  • If the propagation job is enabled, but is not propagating messages, then try stopping and restarting the propagation.

Is Security Configured Properly for the ANYDATA Queue?

ANYDATA queues are secure queues, and security must be configured properly for users to be able to perform operations on them. If you use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package to configure a secure ANYDATA queue, then an error is raised if the agent that SET_UP_QUEUE tries to create already exists and is associated with a user other than the user specified by queue_user in this procedure. In this case, rename or remove the existing agent using the ALTER_AQ_AGENT or DROP_AQ_AGENT procedure, respectively, in the DBMS_AQADM package. Next, retry SET_UP_QUEUE.

In addition, you might encounter one of the following errors if security is not configured properly for an ANYDATA queue:

See Also:

"Secure Queues"

ORA-24093 AQ Agent not granted privileges of database user

Secure queue access must be granted to an Oracle Database Advanced Queuing (AQ) agent explicitly for both enqueue and dequeue operations. You grant the agent these privileges using the ENABLE_DB_ACCESS procedure in the DBMS_AQADM package.

For example, to grant an agent named explicit_dq privileges of the database user oe, run the following procedure:

BEGIN
  DBMS_AQADM.ENABLE_DB_ACCESS(
    agent_name  => 'explicit_dq',
    db_username => 'oe');
END;
/

To check the privileges of the agents in a database, run the following query:

SELECT AGENT_NAME "Agent", DB_USERNAME "User" FROM DBA_AQ_AGENT_PRIVS;

Your output looks similar to the following:

Agent                          User
------------------------------ ------------------------------
EXPLICIT_ENQ                   OE
APPLY_OE                       OE
EXPLICIT_DQ                    OE

See Also:

"Enabling a User to Perform Operations on a Secure Queue" for a detailed example that grants privileges to an agent

ORA-25224 Sender name must be specified for enqueue into secure queues

To enqueue into a secure queue, the SENDER_ID must be set to an Oracle Database Advanced Queuing (AQ) agent with secure queue privileges for the queue in the message properties.

See Also:

Oracle Database Advanced Queuing User's Guide for an example that sets the SENDER_ID for enqueue