For eons, mankind has used a wonderful, yet simplistic method to mark, differentiate, and/or identify objects. What is this method? They put a tag on it. We put tags on retail merchandise to mark a price, size, inventory id, and so on. We put little tags on maps to show where something of significance has happened. We put tags on presents. We even put tags on toes (morbid, but true). Whatever the reason may be, we tag it. These tags make it easier for us to identify something "special" about the object. Oracle Streams is no different. Streams uses tags on LCRs to help the various Streams processes identify changes that need "special" handling. This tag is of a raw data type and can store a value up to 2000 bytes. If you are familiar with the use of the DBMS_REPUTIL.FROM_REMOTE procedure in advanced replication triggers, you are already familiar with the default usage for a Streams tag.
Every entry in the redo logs has a tag associated with it. The default value of the tag is set to null when a change is recorded in the redo. When the Capture process evaluates a change it has retrieved from the redo, by default it looks for a null value tag. If the tag is null, the Capture process builds an LCR from the change. When the Capture process builds an LCR from a redo transaction, it includes the null value of the tag. When the LCR is propagated and applied at the receiving site, the Apply process does something special to set the value of the tag that will be included with the redo log entry that resulted from the Apply process of the change at the target database. The Apply process sets the value of the redo tag to the hexadecimal equivalent of "00" — double zero (how you ask? the dbms_streams.set_tag procedure might be a clue here —more on this in the next section). Now, if there is a Capture process at the Apply site, the change is ignored because it has a non-null tag value. Thus Streams gives us a default mechanism that keeps the change from cycling back to the source from whence it came. Just like the DBMS_REPUTIL.FROM_REMOTE procedure call in the advanced replication trigger.
You can set the value of a redo entry tag either when the original change is recorded in the redo log at the source database, or when the Apply process applies the change at the destination database. You can also configure your capture, propagate, and Apply processes to evaluate the value of the tag to determine if the process should or should not process the LCR at that stage of the replication. You can also reference these tag values in rules and apply handlers. This allows a great amount of flexibility with how you can manipulate an LCR throughout the replication process based on that tag value. To capture or not to capture; where to propagate; what to apply and how. The combinations are limited only by your imagination (ok, that and the statistical limit of values that can be set, but hey there's a lot you can do in 2000 bytes).
You can set the value of a tag at the time the change is made at the source database by first calling the DBMS_STREAMS.SET_TAG function for the session, and then making the change.
Begin
DBMS_STREAMS.SET_TAG(HEXTORAW('22'));
Insert into oe.promotions values (3,'Local Sale'),
Commit;
End;
/
You can also set the value of the tag for the change at the apply site by specifying a value for the APPLY_TAG with the DBMS_APPLY_ADM.CREATE_APPLY or DBMS_APPLY_ADM.ALTER_APPLY procedures:
DBMS_APPLY_ADM.CREATE_APPLY( queue_name IN VARCHAR2, apply_name IN VARCHAR2, rule_set_name IN VARCHAR2 DEFAULT NULL, message_handler IN VARCHAR2 DEFAULT NULL, ddl_handler IN VARCHAR2 DEFAULT NULL, apply_user IN VARCHAR2 DEFAULT NULL, apply_database_link IN VARCHAR2 DEFAULT NULL, apply_tag IN RAW DEFAULT '00', apply_captured IN BOOLEAN DEFAULT FALSE, precommit_handler IN VARCHAR2 DEFAULT NULL, negative_rule_set_name IN VARCHAR2 DEFAULT NULL, source_database IN VARCHAR2 DEFAULT NULL);
Notice that the default for the apply_tag parameter is '00' for the CREATE_APPLY procedure, as we discussed earlier in this chapter:
DBMS_APPLY_ADM.ALTER_APPLY( apply_name IN VARCHAR2, apply_tag IN RAW DEFAULT NULL);
Notice that the default for the apply_tag parameter is null for the ALTER_APPLY procedure. This means that if you do not explicitly supply a value, the parameter value will not be changed. It does not mean that the value will be changed to null. This being the case, what if you really want the value of the tag to be null? This is where the remove_apply_tag parameter steps up:
DBMS_APPLY_ADM.ALTER_APPLY( apply_name IN VARCHAR2, remove_apply_tag IN BOOLEAN DEFAULT FALSE);
If the remove_apply_tag is set to true, then the Apply process sets the value of the tag to null when it generates redo. This comes in very handy when you want to configure an "apply forward" directed network streamed configuration.
If the remove_apply_tag is set to false (the default), then the apply sets the tag to the value specified for the apply_tag parameter.
When you configure capture, propagation, and Apply processes, you are actually adding rule conditions. These rules tell the process of how to handle an LCR. As mentioned earlier, if created with one of the DBMS_STREAMS_ADM.ADD_*_RULE, by default, the capture, propagate, and Apply processes only handle LCR's with null tags, and the Apply processes sets the tag to '00' when it applies a change. First, you want to tell the rule that you want non-null tagged LCRs considered as well as null tags (not ignored as is the default). You do this with the INCLUDE_TAGGED_LCR. Next, you want to make sure the rule knows not to let the non-null tag override the expected behavior of the rule set evaluation. You do this with the INCLUSION_RULE parameter. Finally, you let the rule know what the evaluation on the non-null tag should be. This is set by the AND_CONDITION parameter. Let's dig into this a little deeper.
This parameter tells the process whether or not to consider non-null tagged LCR's for replication.
If the value is set to FALSE, the rule includes a condition for the process to only look for LCRs with a null tag. If the tag is not null, then ignore the LCR.
If the value is set to TRUE, the rule is for the process to consider null and non-null tags. In actuality, it removes the piece of the rule that evaluates if the tag is set, thus making all tags a candidate for additional rule evaluation.
Make sure associated Capture, Propagate, and Apply processes have the same INCLUDE_TAGGED_LCR value. If these values are not synchronized between the associated processes, non-null tagged LCR's may be ignored when expected to be replicated, leading to unexpected behavior from one process to the next.
If this parameter is set to TRUE, this rule becomes part of the positive rule set. This means that if the LCR evaluates to true for this rule, it is processed. If it evaluates to false, it is ignored.
If this parameter is set to false, this rule becomes part of the negative rule set. This means that if the LCR evaluates to true for this rule, it is ignored. If it evaluates to false, it is processed. This is just the opposite from the LCR evaluating true for a positive rule.
A good rule of thumb here is that if this parameter is set to FALSE, meaning it is a negative rule, then set the INCLUDE_TAGGED_LCR = TRUE. The reason is that if the negative rule evaluates to TRUE, we would expect the LCR to be ignored. However, if the INCLUDE_TAGGED_LCR is set to false, and the LCR tag is null, the overall rule will replicate the LCR. By setting INCLUDE_TAGGED_LCR=TRUE, you can use the value of the tag as a "tie-breaker".
This parameter is a string that is appended to the rule conditions defined by the other parameters. Think of these rule conditions as where clauses. This last one allows you to add additional conditions to the where clause with the AND operator. This condition uses the :lcr object reference. When entering this value, remember it is a string. If you have single quotes within the string, make sure you escape them as you would with any PL/SQL string; for example:
and_condition => ':lcr.get_tag = HEXTORAW(''22'')'
A word of warning! Make sure your AND_CONDITION corresponds with the INCLUSION_RULE setting. Here is why:
dbms_streams_adm.add_schema_rules(
inclusion_rule => FALSE,
and_condition => ':lcr.get_tag = HEXTORAW(''22'')'),
The above rule tells the process that if the LCR tag is equal to 22, don't process the LCR. This is because the INCLUSION_RULE is set to FALSE, which means that the rule is a negative rule, and will NOT process the change if the tag value equal to 22 evaluates to TRUE (we have a positive negative).
dbms_streams_adm.add_schema_rules(
inclusion_rule => TRUE,
and_condition => ':lcr.get_tag = HEXTORAW(''22'')'),
Setting the INCLUSION_RULE = TRUE in the rule tells the process that if the LCR tag is equal to 22 then process the LCR. This means that the rule is a positive rule, and will process the change if the tag value equal to 22 evaluates to TRUE (we have a positive positive).
Below is a list of LCR subprograms common to both DDL and DML that might be used in the AND_CONDITION. For a full list, please refer to Oracle's PL/SQL Packages and Types Reference manual:
|
Subprogram/Member Functions |
Description |
|---|---|
|
|
Returns the command type of the LCR |
|
|
Returns the LCR commit system change number (SCN) at the time it is Applied (or erred) at the destination database. |
|
|
Returns the minimal database version compatibility required to support the LCR |
|
|
Returns the value for the specified extra attribute in the LCR |
|
|
Returns the name of the object that is changed by the LCR |
|
|
Returns the owner of the object that is changed by the LCR |
|
|
Returns the system change number (SCN) of the LCR when it was committed at the source database. |
|
|
Returns the source database name. |
|
|
Returns the time when the LCR's change was generated in the redo log of the source database, or the time when a persistent LCR was created. |
|
|
Returns the LCR tag value |
|
|
Returns the LCR transaction identifier |
|
|
Returns LCR tag status. 'Y' if the tag is NULL, 'N' if the tag is not NULL |
Avoid change cycling. We've seen how the default use of tags helps Capture processes determine if a change should be captured and how an Apply process avoids having its change captured. This method works well in a bi-directional, master-to-master configuration. The understanding here is that propagation only makes one "hop"; from the source to the destination, and then stops. We like to call this the "one hop prop" (when said with a rap rhythm, it's kinda catchy)!
We can take this logic one step further to a hub and spoke replicated environment. By setting the tag to a value specific to a site, we can tell a propagation job to send all changes from the master hub to a destination for all tag values except the tag value for the destination site. For instance: say you have a configuration with one "hub" (H1) and 3 (S1, S2, and S3) "spoke" masters. Changes can be made at any of the four master sites. H1 is responsible for receiving a change from one spoke master and passing it on to the other spokes.
When the H1 master passes the change to the spokes, we want to avoid sending the change back to the spoke that originated it. This can be done using tags set to unique identifiers indicating where the change originated from (this is where those prior design activities we discussed in Chapter 2 come in really—handy you should already know how many unique tag values you will need, and which site will be assigned which value).
One way to accomplish this is to have the Apply process at the hub set the apply_tag value indicating where the change originated from and then have the Propagation process to each site check the tag value, sending only those that do not match the id assigned to the destination for that Propagation process.
In this case, we will assign the tag values of "A1", "A2", and "A3" to the changes originating from the spoke sites S1, S2, and S3 respectively. We will let the changes that originate at the Hub default to the null tag value.
Note
Note: The following steps assume that the OE schema has already been instantiated in all 4 databases, the appropriate database links have been created, and the SCN has been set for each apply site at each site using the DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN for the OE schema, and DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN for each table in the OE scheme (both levels must be done). At this point no changes should be made to any of the objects at any of the sites until the Streams setup is completed.
Prior to Oracle 11g and the introduction of Propagation Split and Merge, using the same capture queue for multiple destinations has the potential of impacting the performance of the overall Streaming if the connectivity to one or more destination sites is degraded. Thus, prior to 11g, we would want to create a separate outbound queue and Capture process for each spoke. This way, if the propagation to one spoke is slow or down, it will not impact propagation or queue management for the other spokes. In Oracle 11g, the Propagation Split and Merge feature can be implemented with a single capture queue to mitigate these potential issues. In our example, we are going to go ahead and use the "Separate Capture Queue Per Destination" to allow us to focus on Tag usage.
The following code creates the capture queues and processes, and can be found in the TAG_HubCapture.sql code file.
First we create the capture queues; one for each destination.
Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.S1_CAPT_QT', storage_clause => NULL, queue_name => 'strm_admin.S1_CAPT_Q', queue_user => 'strm_admin'), End; / Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.S2_CAPT_QT', storage_clause => NULL, queue_name => 'strm_admin.S2_CAPT_Q', queue_user => 'strm_admin'), End; / Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.S3_CAPT_QT', storage_clause => NULL, queue_name => 'strm_admin.S3_CAPT_Q', queue_user => 'strm_admin'), End; /
We then create Capture processes for each queue, making sure to include non-null tags. We want the AND_CONDITION to be NULL so we get all of the changes regardless of the tag value.
Begin dbms_streams_adm.add_schema_rules( schema_name => 'OE', streams_type => 'CAPTURE', streams_name => 'OE_CAPT_S1', queue_name => 'S1_CAPT_Q', include_dml => TRUE, include_ddl => TRUE, include_tagged_lcr => TRUE, ---capture null and non-null tags source_database => 'H1.oracle.com', inclusion_rule => TRUE, and_condition => NULL); End; / Begin dbms_streams_adm.add_schema_rules( schema_name => 'OE', streams_type => 'CAPTURE', streams_name => 'OE_CAPT_S2', queue_name => 'S2_CAPT_Q', include_dml => TRUE, include_ddl => TRUE, include_tagged_lcr => TRUE, ---capture null and non-null tags source_database => 'H1.oracle.com', inclusion_rule => TRUE, and_condition => NULL); End; Begin dbms_streams_adm.add_schema_rules( schema_name => 'OE', streams_type => 'CAPTURE', streams_name => 'OE_CAPT_S3', queue_name => 'S3_CAPT_Q', include_dml => TRUE, include_ddl => TRUE, include_tagged_lcr => TRUE, ---capture null and non-null tags source_database => 'H1.oracle.com', inclusion_rule => TRUE, and_condition => NULL); End; /
At each spoke, we want to set up the basic capture and propagation to the Hub processes. We also want to create an Apply process that applies all changes sent to the spoke regardless of the tag value.
The following code creates the Capture and Apply queues, and the necessary Capture, Propagation, and Apply processes at each spoke. This code can be found in the TAG_SpokeSQL.sql code file.
First, we create the Capture and Apply queues.
Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.H1_CAPT_QT', storage_clause => NULL, queue_name => 'strm_admin.H1_CAPT_Q', queue_user => 'strm_admin'), End; / Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.H1_APPLY_QT', storage_clause => NULL, queue_name => 'strm_admin.H1_APPLY_Q', queue_user => 'strm_admin'), End; /
Next, create the Capture rule, making sure to set the SOURCE_DATABASE with the appropriate spoke name, and only capture null tag changes. We do this because the spoke is only sending to one site, so we don't need to worry about tags here.
Begin dbms_streams_adm.add_schema_rules( schema_name => 'OE', streams_type => 'CAPTURE', streams_name => 'OE_CAPT_H1', queue_name => 'H1_CAPT_Q', include_dml => TRUE, include_ddl => TRUE, include_tagged_lcr => False, ---capture just null tags - changes made at this site source_database => 'S1.oracle.com',---substitute S2, S3 here ---for the other two spokes inclusion_rule => TRUE, and_condition => NULL); End; /
Create the Apply Rule to include non-null tags. We do this because we may be receiving changes from the hub that are forwarded from another spoke. These changes will have a non-null tag. If the change originated at the hub, the tag will be null. We do not need to evaluate the tag value past null or non-null. This would have already been done by the Propagation process for this destination at the hub. Setting the AND_CONDITION to NULL and the INCLUDE_TAGGED_LCR to TRUE, instructs the Apply process to apply any null and non-null tagged LCR's it receives.
BEGIN dbms_streams_adm.add_schema_rules( schema_name => 'OE', streams_type => 'APPLY', streams_name => 'OE_APPLY_H1', queue_name => 'strm_admin.H1_APPLY_Q', include_dml => TRUE, include_ddl => TRUE, include_tagged_lcr => TRUE, --check null and non-null tags source_database => 'H1.oracle.com', inclusion_rule => TRUE, and_condition => NULL); END; /
Now create the propagation from the spoke to the Hub. As we want to send all of the changes originating at the spoke to the Hub, we don't need to worry about tags. We just need a basic null tag LCR propagation rule. Run the following at each spoke, substituting S1 with S2 and S3 for the respective spoke site. Because propagation is enabled when it is created, we will disable it immediately after creation to allow us to complete the configuration setup.
BEGIN dbms_streams_adm.add_schema_propagation_rules( schema_name => 'OE', streams_name => 'OE_PROP_H1', source_queue_name => 'H1_CAPT_Q', destination_queue_name => '[email protected]', include_dml => TRUE, include_ddl => TRUE, include_tagged_lcr => FALSE, --send only null tags source_database => 'S1.oracle.com', inclusion_rule => TRUE, and_condition => NULL, queue_to_queue => TRUE); --disable propagation until we are finished BEGIN dbms_aqadm.disable_propagation_schedule( queue_name => 'H1_CAPT_Q', destination => ' H1.oracle.com ', destination_queue => '[email protected]' ); EXCEPTION WHEN OTHERS THEN IF sqlcode = -24065 THEN NULL; -- propagation already disabled ELSE RAISE; END IF; END; END;
We now want to create an inbound queue and Apply process for each spoke at the Hub. This way, if one queue is slow or has errors, it will not hold up changes from the other spokes. This also helps with maintenance. Should you need to drop and recreate the queue, you are only impacting synchronization between the Hub and the one spoke. You can use a single apply queue if you wish.
The following code creates the Apply queues and Apply processes at the Hub. This code can be found in the TAG_SpokeSQL.sql code file.
First we create the Apply queues; one for each source site.
Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.S1_APPLY_QT', storage_clause => NULL, queue_name => 'strm_admin.S1_APPLY_Q', queue_user => 'strm_admin'), End; / Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.S2_APPLY_QT', storage_clause => NULL, queue_name => 'strm_admin.S2__APPLY_Q', queue_user => 'strm_admin'), End; / Begin dbms_streams_adm.set_up_queue( queue_table => 'strm_admin.S3_APPLY_QT', storage_clause => NULL, queue_name => 'strm_admin.S3__APPLY_Q', queue_user => 'strm_admin'), End; /
We then configure an Apply process on H1 for each of the spoke sites, and set the apply_tag with that site's tag value.
Begin
dbms_streams_adm.add_schema_rules(
schema_name => 'OE',
streams_type => 'APPLY',
streams_name => 'OE_APPLY_S1',
queue_name => 'S1_APPLY_Q',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'S1.oracle.com',
inclusion_rule => TRUE,
and_condition => NULL);
dbms_apply_adm.alter_apply(
apply_name => 'OE_APPLY_S1',
apply_tag => HEXTORAW('A1') ---this is the key
);
END;
/
Begin
dbms_streams_adm.add_schema_rules(
schema_name => 'OE',
streams_type => 'APPLY',
streams_name => 'OE_APPLY_S2',
queue_name => 'S2_APPLY_Q',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'S2.oracle.com',
inclusion_rule => TRUE,
and_condition => NULL);
dbms_apply_adm.alter_apply(
apply_name => 'OE_APPLY_S2',
apply_tag => HEXTORAW('A2') ---this is the key
);
END;
/
Begin
dbms_streams_adm.add_schema_rules(
schema_name => 'OE',
streams_type => 'APPLY',
streams_name => 'OE_APPLY_S3',
queue_name => 'S3_APPLY_Q',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE,
source_database => 'S3.oracle.com',
inclusion_rule => TRUE,
and_condition => NULL);
dbms_apply_adm.alter_apply(
apply_name => 'OE_APPLY_S3',
apply_tag => HEXTORAW('A3') ---this is the key
);
END;
/
Now we want to set up propagation from the Hub to each spoke. It is here where we evaluate the tag using the AND_CONDITION. As we want to send all changes, expect those originating from the destination spoke, it is easier to create a negative rule. Because propagation is enabled when it is created, we will disable it immediately after creation. As mentioned earlier, the AND_CONDITION is a string that is appended to system conditions created by the rule. Make sure to escape single quotes using PL/SQL syntax. Run the following at the hub for each spoke site, substituting S1 with S2 and S3 respectively, and A1 with A2 and A3 respectively.
The following code creates the Propagation processes at the Hub. This code can be found in the TAG_HUBProp.sql code file.
BEGIN dbms_streams_adm.add_schema_propagation_rules( schema_name => 'OE', streams_name => 'OE_PROP_S1', source_queue_name => 'S1_CAPT_Q', destination_queue_name => '[email protected]', include_dml => TRUE, include_ddl => TRUE, include_tagged_lcr => TRUE, --check null and non-null tags source_database => 'H1.oracle.com', inclusion_rule => FALSE, --if condition is true, don't send and_condition => ':lcr.get_tag = HEXTORAW(''A1'')', queue_to_queue => TRUE); --disable propagation until we are finished BEGIN dbms_aqadm.disable_propagation_schedule( queue_name => 'S1_CAPT_Q', destination => ' S1.oracle.com ', destination_queue => '[email protected]' ); EXCEPTION WHEN OTHERS THEN IF sqlcode = -24065 THEN NULL; -- propagation already disabled ELSE RAISE; END IF; END; END; /
Summary of what we have just done:
- We have created a Capture process at the Hub that looks for null and non-null tagged LCRs
- We have created a Capture process at each spoke that looks for null tag LCRs only
- We have created an Apply process at the hub, for each spoke site, that sets the LCR tag to a unique non-null value identifying where the change originated from
- We have created an Apply process at each spoke that applies all null and non-null tagged LCRs sent to it
- We have created a Propagation process from the hub, to each spoke site, that evaluates the LCR tag value, and sends all LCRs whose tag value is not equal to the destination spoke assigned identifier
- We have created a Propagation process from each spoke to the hub that sends any null tagged LCRs
At this point you will need to set the Apply Instantiation SCN using the DBMS_APPLY_ADM.SET_SCHEMA_INSTANITIATION_SCN and then start each of the Apply processes using the DBMS_APPLY_ADM.START_APPLY procedure (see Chapters 4 and 5 for specifics). Enable the Propagation processes using the DBMS_AQADM.ENABLE_PROPAGATION_SCHEUDLE procedure. Then finally, start the Capture processes using the DBMS_CAPTURE_ADM.START_CAPTURE procedure.
Tags can also be used by Rules, such as Error handling, and Rule based transformations. These rules are configured to run specified PL/SQL packages in which the LCR properties can be evaluated and modified. In these cases, you would reference the :dml, or :ddl object type methods (similar to the :lcr object type referenced in the AND_CONDITION) to read or set the tag value as needed.