17.1 Best Practices for Propagation Configuration
This following sections describe best practices for configuring propagations:
17.1.1 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 before Oracle Database 10g Release 2 are queue-to-dblink propagations. Also, any propagation that includes a queue in a database before 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:
- Stop the propagation.
- Ensure that the source queue is empty.
- Ensure that the destination queue is empty and has no unapplied, spilled messages before you drop the propagation.
- Re-create the propagation with the
queue_to_queueparameter set to
TRUEin the creation procedure.
If you automate the configuration, as described in "Automate the Oracle Streams Replication Configuration", then each propagation uses queue-to-queue propagation automatically.
"Follow the Best Practices for Configuring and Managing Propagations" for information about propagations in an Oracle Real Application Clusters (Oracle RAC) environment
17.1.2 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
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.example.com, log in as the Oracle Streams administrator, and run the following procedure:
BEGIN DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE( queue_name => 'strmadmin.q1', destination => 'dbs2.example.com', latency => 5, destination_queue => 'strmadmin.q2'); END; /
latency parameter is not specified, then propagation latency is set to the default value of 60.
17.1.3 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
RECV_BUF_SIZE parameters in the
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.
17.2 Best Practices for Propagation Operation
The following section describes best practices for operating existing propagations.
17.2.1 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.