Prior to Oracle 11g, maintaining a single capture queue for multiple destinations was not highly recommended due to performance impacts to the overall Streaming, and as a result, substandard propagation performance from the source to one or more destinations. As for Oracle 11g, Propagation Split and Merge not only allows the DBA to separate a sub-performant destination from a single capture queue on the fly, but also automates the remerging of the destination to the original capture queue, if and when the destination propagation performance reaches an acceptable level. Chapter 1 covers the theory of the Propagation Split and Merge feature, here we will review how to implement the feature.

The Propagation Split and Merge is managed using the following procedures:

specifically the auto_merge_threshold parameter to automate the remerge of the Propagation process once propagation performance to the destination site reaches an acceptable level.

For our example, we have a Single-Source one Hub and two (2) Spoke environment (One master database, H1, and two secondary databases, S1 and S2). The Master Hub has a single capture queue and process, and two Propagation processes; one to each Spoke Site. Each Spoke has an Apply queue and process for changes received from H1.

If propagation to S2 becomes degraded, the DBA can Split the propagation from the capture queue using the DBMS_STREAMS_ADM.SPLIT_STREAMS procedure as such:

declare
sched_name varchar2(50);
mergejob_name varchar2(50);
begin
DBMS_STREAMS_ADM.SPLIT_STREAMS(
propagation_name =>'PROP_S2',
cloned_propagation_name =>'CLONE_PROP_S2',
cloned_queue_name =>'CLONE_CAPT_S2_Q',
cloned_capture_name =>'CLONE_CAPT_S2',
perform_actions => TRUE,
script_name => 'Split_S2_Stream.sql',
script_directory_object => 'SCRIPT_DIR',
auto_merge_threshold => 6,
schedule_name =>sched_name,
merge_job_name =>mergejob_name);
end;
/

The result of this procedure is the creation of a new capture queue, CLONE_CAPT_S2_Q, and process CLONE_CAPT_S2; and a new Propagation process CLONE_PROP_S2 from the CLONE_CAPT_S2_Q to the APPLY_H1_Q at the S2 site. Also, the original Propagation process PROP_S2 is removed all together.

Notice we specified six seconds for the auto_merge_threshold. This means that a scheduler job will be created to monitor the latency of the CLONE_CAPT_S2 Capture process. We can access the scheduler name and merge job name via out parameters. You can also view the job information in the DBMS_SCHEDULER_JOB view. The scheduler job runs the DBMS_STREAMS_ADM.MERGE_STREAMS_JOB procedure based on its schedule.

The DBMS_STREAMS_ADM.MERGE_STREAMS_JOB procedure is actually responsible for comparing the latency of the cloned capture with the orignal capture and merging the cloned processes back into the original processes if the difference falls at or below the specified auto_merge_threshold.

If the latency for the new CLONE_CAPT_S2 process falls below a difference of six seconds from the latency of the original CAPT_ALL Capture process, the job will merge the cloned stream back into the original stream.

This latency is determined by comparing the CAPTURE_MESSAGE_CREATE_TIME for the original Capture process and the cloned Capture process in the GV$STREAMS_CAPTURE view.

If we had specified NULL or zero (0) for the auto_merge_threshold, a schedule job would not be created to automate the merge. We would need to accomplish this manually using the DBMS_STREAM_ADM.MERGE_STREAM procedure.

begin
DBMS_STREAMS_ADM.MERGE_STREAMS(
cloned_propagation_name => 'Clone_PROP_S2',
propagation_name => 'PROP_S2',
queue_name => 'CAPT_ALL_Q',
perform_actions => TRUE,
script_name => 'Merge_S2_Stream.sql',
script_directory_object => 'SCRIPT_DIR'),
end;
/

The result of the merge is the deletion of the cloned queue, CLONE_CAPT_S2_Q, the cloned Capture process, CLONE_CAPT_S2, and the cloned Propagation process,CLONE_PROP_S2. A new Propagation process, PROP_S2 is created from the original CAPT_ALL_Q at H1 to the APPLY_H1_Q at S2. Essentially, returning the Stream back to its original configuration.

You can view information concerning Split/Merge operation in the DBA_STREAMS_SPLIT_MERGE view.