17 Best Practices for Propagation

Use Queue-to-Queue Propagations

A propagation can be queue-to-queue or queue-to-database link (queue-to-dblink). Use queue-to-queue propagations whenever possible. A queue-to-queue propagation always has its own exclusive propagation job to propagate messages from the source queue to the destination queue. Because each propagation job has its own propagation schedule, the propagation schedule of each queue-to-queue propagation can be managed separately. Even when multiple queue-to-queue propagations use the same database link, you can enable, disable, or set the propagation schedule for each queue-to-queue propagation separately.

Propagations configured prior to Oracle Database 10g Release 2 are queue-to-dblink propagations. Also, any propagation that includes a queue in a database prior to Oracle Database 10g Release 2 must be a queue-to-dblink propagation. When queue-to-dblink propagations are used, propagation will not succeed if the database link no longer connects to the owning instance of the destination queue.

If you have queue-to-dblink propagations created in a prior release of Oracle Database, you can re-create these propagation during a maintenance window to use queue-to-queue propagation. To re-create a propagation, complete the following steps:

1. Stop the propagation.

2. Ensure that the source queue is empty.

3. Ensure that the destination queue is empty and has no unapplied, spilled messages before you drop the propagation.

4. Re-create the propagation with the queue_to_queue parameter set to TRUE in the creation procedure.

Set the Propagation Latency for Each Propagation

Propagation latency is the maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued. Set the propagation latency to an appropriate value for each propagation in your Oracle Streams replication environment. The default propagation latency value is 60.

The following scenarios describe how a propagation will behave given different propagation latency values:

• If latency is set to 60, and there are no messages enqueued during the propagation window, then the queue will not be checked for 60 seconds for messages to be propagated to the specified destination. That is, messages enqueued into the queue for the propagation destination will not be propagated for at least 60 more seconds.

• If the latency is set to 600, and there are no messages enqueued during the propagation window, then the queue will not be checked for 10 minutes for messages to be propagated to the specified destination. That is, messages enqueued into the queue for the propagation destination will not be propagated for at least 10 more minutes.

• If the latency is set to 0, then a job slave will be waiting for messages to be enqueued for the destination and, as soon as a message is enqueued, it will be propagated. Setting latency to 0 is not recommended because it might affect the ability of the database to shut down in NORMAL mode.

You can set propagation parameters using the ALTER_PROPAGATION_SCHEDULE procedure from the DBMS_AQADM package. For example, to set the latency of a propagation from the q1 source queue owned by strmadmin to the destination queue q2 at the database with a global name of dbs2.net, log in as the Oracle Streams administrator, and run the following procedure:

BEGIN
  DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
   queue_name        => 'strmadmin.q1',
   destination       => 'dbs2.net',
   latency           => 1,
   destination_queue => 'strmadmin.q2'); 
END;
/

Increase the SDU in a Wide Area Network for Better Network Performance

When using Oracle Streams propagation in a Wide Area Network (WAN), increase the session data unit (SDU) to improve the propagation performance. The maximum value for SDU is 32K (32767). The SDU value for network transmission is negotiated between the sender and receiver sides of the connection, and the minimum SDU value of the two endpoints is used for any individual connection. To take advantage of an increased SDU for Oracle Streams propagation, the receiving side sqlnet.ora file must include the DEFAULT_SDU_SIZE parameter. The receiving side listener.ora file must indicate the SDU change for the system identifier (SID). The sending side tnsnames.ora file connect string must also include the SDU modification for the particular service.

In addition, the SEND_BUF_SIZE and RECV_BUF_SIZE parameters in the listener.ora and tnsnames.ora files increase the performance of propagation on your system. These parameters increase the size of the buffer used to send or receive the propagated messages. These parameters should only be increased after careful analysis of their overall impact on system performance.

Restart Broken Propagations

Sometimes, the propagation job for a propagation might become "broken" or fail to start after it encounters an error or after a database restart. Typically, stopping and restarting the propagation solves the problem. For example, to stop and restart a propagation named prop1, run the following procedures:

BEGIN
  DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
    propagation_name => 'prop1');
END;
/

BEGIN
  DBMS_PROPAGATION_ADM.START_PROPAGATION(
    propagation_name => 'prop1');
END;
/

If running these procedures does not correct the problem, then run the STOP_PROPAGATION procedure with the force parameter set to TRUE, and restart propagation. For example:

BEGIN
  DBMS_PROPAGATION_ADM.STOP_PROPAGATION(
    propagation_name => 'prop1',
    force            => TRUE);
END;
/

BEGIN
  DBMS_PROPAGATION_ADM.START_PROPAGATION(
    propagation_name => 'prop1');
END;
/

When you stop a propagation with the force parameter set to TRUE, the statistics for the propagation are cleared.