10 Oracle Streams Tags

N-Way Replication Environments

An n-way replication environment is one in which each database is a source database for every other database, and each database is a destination database of every other database. Each database communicates directly with every other database.

For example, consider an environment that replicates the database objects and data in the hrmult schema between three Oracle databases: mult1.example.com, mult2.example.com, and mult3.example.com. DML and DDL changes made to tables in the hrmult schema are captured at all three databases in the environment and propagated to each of the other databases in the environment, where changes are applied. Figure 10-1 illustrates a sample n-way replication environment.

Figure 10-1 Each Database Is a Source and Destination Database

Description of "Figure 10-1 Each Database Is a Source and Destination Database"

You can avoid change cycles by configuring such an environment in the following way:

• Configure one apply process at each database to generate non-NULL redo tags for changes from each source database. If you use a procedure in the DBMS_STREAMS_ADM package to create an apply process, then the apply process generates non-NULL tags with a value of '00' in the redo log by default. In this case, no further action is required for the apply process to generate non-NULL tags.

  If you use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package to create an apply process, then do not set the apply_tag parameter. Again, the apply process generates non-NULL tags with a value of '00' in the redo log by default, and no further action is required.

• Configure the capture process at each database to capture changes only if the tag in the redo entry for the change is NULL. You do this by ensuring that each DML rule in the positive rule set used by the capture process has the following condition:

  :dml.is_null_tag()='Y'
  

  Each DDL rule should have the following condition:

  :ddl.is_null_tag()='Y'
  

  These rule conditions indicate that the capture process captures a change only if the tag for the change is NULL. If you use the DBMS_STREAMS_ADM package to generate rules, then each rule has such a condition by default.

This configuration prevents change cycling because all of the changes applied by the apply processes are never recaptured (they were captured originally at the source databases). Each database sends all of its changes to the hrmult schema to every other database. So, in this environment, no changes are lost, and all databases are synchronized. Figure 10-2 illustrates how tags can be used in a database in an n-way replication environment.

Figure 10-2 Tag Use When Each Database Is a Source and Destination Database

Description of "Figure 10-2 Tag Use When Each Database Is a Source and Destination Database"

Hub-and-Spoke Replication Environments

A hub-and-spoke replication environment is one in which a primary database, or hub, communicates with secondary databases, or spokes. The spokes do not communicate directly with each other. In a hub-and-spoke replication environment, the spokes might or might not allow changes to the replicated database objects.

If the spokes do not allow changes to the replicated database objects, then the primary database captures local changes to the shared data and propagates these changes to all secondary databases, where these changes are applied at each secondary database locally. Change cycling is not possible when none of the secondary databases allow changes to the replicated database objects because changes to the replicated database objects are captured in only one location.

If the spokes allow changes to the replicated database objects, then changes are captured, propagated, and applied in the following way:

• The primary database captures local changes to the shared data and propagates these changes to all secondary databases, where these changes are applied at each secondary database locally.

• Each secondary database captures local changes to the shared data and propagates these changes to the primary database only, where these changes are applied at the primary database locally.

• The primary database applies changes from each secondary database locally. Next, these changes are captured at the primary database and propagated to all secondary databases, except for the one at which the change originated. Each secondary database applies the changes from the other secondary databases locally, after they have gone through the primary database. This configuration is an example of apply forwarding.

  An alternate scenario might use queue forwarding. If this environment used queue forwarding, then changes from secondary databases that are applied at the primary database are not captured at the primary database. Instead, these changes are forwarded from the queue at the primary database to all secondary databases, except for the one at which the change originated.

For example, consider an environment that replicates the database objects and data in the hr schema between one primary database named ps1.example.com and three secondary databases named ps2.example.com, ps3.example.com, and ps4.example.com. DML and DDL changes made to tables in the hr schema are captured at the primary database and at the three secondary databases in the environment. Next, these changes are propagated and applied as described previously. The environment uses apply forwarding, not queue forwarding, to share data between the secondary databases through the primary database. Figure 10-3 illustrates a sample environment which has one primary database and multiple secondary databases.

Figure 10-3 Primary Database Sharing Data with Several Secondary Databases

Description of "Figure 10-3 Primary Database Sharing Data with Several Secondary Databases"

You can avoid change cycles by configuring the environment in the following way:

• Configure each apply process at the primary database ps1.example.com to generate non-NULL redo tags that indicate the site from which it is receiving changes. In this environment, the primary database has at least one apply process for each secondary database from which it receives changes. For example, if an apply process at the primary database receives changes from the ps2.example.com secondary database, then this apply process can generate a raw value that is equivalent to the hexadecimal value '2' for all changes it applies. You do this by setting the apply_tag parameter in the CREATE_APPLY or ALTER_APPLY procedure in the DBMS_APPLY_ADM package to the non-NULL value.

  For example, run the following procedure to create an apply process that generates redo entries with tags that are equivalent to the hexadecimal value '2':

  BEGIN
    DBMS_APPLY_ADM.CREATE_APPLY(
      queue_name      => 'strmadmin.streams_queue',
      apply_name      => 'apply_ps2',
      rule_set_name   => 'strmadmin.apply_rules_ps2',
      apply_tag       => HEXTORAW('2'),
      apply_captured  => TRUE);
  END;
  /
  

• Configure the apply process at each secondary database to generate non-NULL redo tags. The exact value of the tags is irrelevant if it is non-NULL. In this environment, each secondary database has one apply process that applies changes from the primary database.

  If you use a procedure in the DBMS_STREAMS_ADM package to create an apply process, then the apply process generates non-NULL tags with a value of '00' in the redo log by default. In this case, no further action is required for the apply process to generate non-NULL tags.

  For example, assuming no apply processes exist at the secondary databases, run the ADD_SCHEMA_RULES procedure in the DBMS_STREAMS_ADM package at each secondary database to create an apply process that generates non-NULL redo entries with tags that are equivalent to the hexadecimal value '00':

  BEGIN
    DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
      schema_name     => 'hr',   
      streams_type    => 'apply',
      streams_name    => 'apply',
      queue_name      => 'strmadmin.streams_queue',
      include_dml     => TRUE,
      include_ddl     => TRUE,
      source_database => 'ps1.example.com',
      inclusion_rule  => TRUE);
  END;
  /
  

• Configure the capture process at the primary database to capture changes to the shared data regardless of the tags. You do this by setting the include_tagged_lcr parameter to TRUE when you run one of the procedures that generate capture process rules in the DBMS_STREAMS_ADM package. If you use the DBMS_RULE_ADM package to create rules for the capture process at the primary database, then ensure that the rules do not contain is_null_tag conditions, because these conditions involve tags in the redo log.

  For example, run the following procedure at the primary database to produce one DML capture process rule and one DDL capture process rule that each have a condition that evaluates to TRUE for changes in the hr schema, regardless of the tag for the change:

  BEGIN
    DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
      schema_name         => 'hr',   
      streams_type        => 'capture',
      streams_name        => 'capture',
      queue_name          => 'strmadmin.streams_queue',
      include_tagged_lcr  => TRUE, -- Note parameter setting
      include_dml         => TRUE,
      include_ddl         => TRUE,
      inclusion_rule      => TRUE);
  END;
  /
  

• Configure the capture process at each secondary database to capture changes only if the tag in the redo entry for the change is NULL. You do this by ensuring that each DML rule in the positive rule set used by the capture process at the secondary database has the following condition:

  :dml.is_null_tag()='Y'
  

  DDL rules should have the following condition:

  :ddl.is_null_tag()='Y'
  

  These rules indicate that the capture process captures a change only if the tag for the change is NULL. If you use the DBMS_STREAMS_ADM package to generate rules, then each rule has one of these conditions by default. If you use the DBMS_RULE_ADM package to create rules for the capture process at a secondary database, then ensure that each rule contains one of these conditions.

• Configure one propagation from the queue at the primary database to the queue at each secondary database. Each propagation should use a positive rule set with rules that instruct the propagation to propagate all LCRs in the queue at the primary database to the queue at the secondary database, except for changes that originated at the secondary database.

  For example, if a propagation propagates changes to the secondary database ps2.example.com, whose tags are equivalent to the hexadecimal value '2', then the rules for the propagation should propagate all LCRs relating to the hr schema to the secondary database, except for LCRs with a tag of '2'. For row LCRs, such rules should include the following condition:

  :dml.get_tag() IS NULL OR :dml.get_tag()!=HEXTORAW('2')
  

  For DDL LCRs, such rules should include the following condition:

  :ddl.get_tag() IS NULL OR :ddl.get_tag()!=HEXTORAW('2')
  

  Alternatively, you can add rules to the negative rule set for the propagation so that the propagation discards LCRs with the tag value. For row LCRs, such rules should include the following condition:

  :dml.get_tag()=HEXTORAW('2')
  

  For DDL LCRs, such rules should include the following condition:

  :ddl.get_tag()=HEXTORAW('2')
  

  You can use the and_condition parameter in a procedure in the DBMS_STREAMS_ADM package to add these conditions to system-created rules, or you can use the CREATE_RULE procedure in the DBMS_RULE_ADM package to create rules with these conditions. When you specify the condition in the and_condition parameter, specify :lcr instead of :dml or :ddl. See Oracle Streams Concepts and Administration for more information about the and_condition parameter.

• Configure one propagation from the queue at each secondary database to the queue at the primary database. A queue at one of the secondary databases contains only local changes made by user sessions and applications at the secondary database, not changes made by an apply process. Therefore, no further configuration is necessary for these propagations.

This configuration prevents change cycling in the following way:

• Changes that originated at a secondary database are never propagated back to that secondary database.

• Changes that originated at the primary database are never propagated back to the primary database.

• All changes made to the shared data at any database in the environment are propagated to every other database in the environment.

So, in this environment, no changes are lost, and all databases are synchronized.

Figure 10-4 illustrates how tags are used at the primary database ps1.example.com.

Figure 10-4 Tags Used at the Primary Database

Description of "Figure 10-4 Tags Used at the Primary Database"

Figure 10-5 illustrates how tags are used at one of the secondary databases (ps2.example.com).

Figure 10-5 Tags Used at a Secondary Database

Description of "Figure 10-5 Tags Used at a Secondary Database"

Hub-and-Spoke Replication Environment with Several Extended Secondary Databases

In this environment, one primary database shares data with several secondary databases, but the secondary databases have other secondary databases connected to them, which will be called remote secondary databases. This environment is an extension of the environment described in "Hub-and-Spoke Replication Environments".

If a remote secondary database allows changes to the replicated database objects, then the remote secondary database does not share data directly with the primary database. Instead, it shares data indirectly with the primary database through a secondary database. So, the shared data exists at the primary database, at each secondary database, and at each remote secondary database. Changes made at any of these databases can be captured and propagated to all of the other databases. Figure 10-6 illustrates an environment with one primary database and multiple extended secondary databases.

Figure 10-6 Primary Database and Several Extended Secondary Databases

Description of "Figure 10-6 Primary Database and Several Extended Secondary Databases"

In such an environment, you can avoid change cycling in the following way:

• Configure the primary database in the same way that it is configured in the example described in "Hub-and-Spoke Replication Environments".

• Configure each remote secondary database similar to the way that each secondary database is configured in the example described in "Hub-and-Spoke Replication Environments". The only difference is that the remote secondary databases share data directly with secondary databases, not the primary database.

• At each secondary database, configure one apply process to apply changes from the primary database with a redo tag value that is equivalent to the hexadecimal value '00'. This value is the default tag value for an apply process.

• At each secondary database, configure one apply process to apply changes from each of its remote secondary databases with a redo tag value that is unique for the remote secondary database.

• Configure the capture process at each secondary database to capture all changes to the shared data in the redo log, regardless of the tag value for the changes.

• Configure one propagation from the queue at each secondary database to the queue at the primary database. The propagation should use a positive rule set with rules that instruct the propagation to propagate all LCRs in the queue at the secondary database to the queue at the primary database, except for changes that originated at the primary database. You do this by adding a condition to the rules that evaluates to TRUE only if the tag in the LCR does not equal '00'. For example, enter a condition similar to the following for row LCRs:

  :dml.get_tag() IS NULL OR :dml.get_tag()!=HEXTORAW('00')
  

  You can use the and_condition parameter in a procedure in the DBMS_STREAMS_ADM package to add this condition to system-created rules, or you can use the CREATE_RULE procedure in the DBMS_RULE_ADM package to create rules with this condition. When you specify the condition in the and_condition parameter, specify :lcr instead of :dml or :ddl. See Oracle Streams Concepts and Administration for more information about the and_condition parameter.

• Configure one propagation from the queue at each secondary database to the queue at each remote secondary database. Each propagation should use a positive rule set with rules that instruct the propagation to propagate all LCRs in the queue at the secondary database to the queue at the remote secondary database, except for changes that originated at the remote secondary database. You do this by adding a condition to the rules that evaluates to TRUE only if the tag in the LCR does not equal the tag value for the remote secondary database.

  For example, if the tag value of a remote secondary database is equivalent to the hexadecimal value '19', then enter a condition similar to the following for row LCRs:

  :dml.get_tag() IS NULL OR :dml.get_tag()!=HEXTORAW('19')
  

  You can use the and_condition parameter in a procedure in the DBMS_STREAMS_ADM package to add this condition to system-created rules, or you can use the CREATE_RULE procedure in the DBMS_RULE_ADM package to create rules with this condition. When you specify the condition in the and_condition parameter, specify :lcr instead of :dml or :ddl. See Oracle Streams Concepts and Administration for more information about the and_condition parameter.

By configuring the environment in this way, you prevent change cycling, and no changes originating at any database are lost.

Managing Oracle Streams Tags for the Current Session

This section contains instructions for setting and getting the tag for the current session.

BEGIN
   DBMS_STREAMS.SET_TAG(
      tag  =>  HEXTORAW('1D'));
END;
/

SET SERVEROUTPUT ON
DECLARE
   raw_tag RAW(2048);
BEGIN
   raw_tag := DBMS_STREAMS.GET_TAG();
   DBMS_OUTPUT.PUT_LINE('Tag Value = ' || RAWTOHEX(raw_tag));
END;
/

SELECT DBMS_STREAMS.GET_TAG FROM DUAL;

Managing Oracle Streams Tags for an Apply Process

This section contains instructions for setting and removing the tag for an apply process.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
     apply_name  =>  'strep01_apply',
     apply_tag   =>  HEXTORAW('7'));
END;
/

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name       => 'strep01_apply',
    remove_apply_tag => TRUE);
END;
/

Displaying the Tag Value for the Current Session

You can display the tag value generated in all redo entries for the current session by querying the DUAL view:

SELECT DBMS_STREAMS.GET_TAG FROM DUAL;

Your output looks similar to the following:

GET_TAG
--------------------------------------------------------------------------------
1D

You can also determine the tag for a session by calling the DBMS_STREAMS.GET_TAG function.

Displaying the Default Tag Value for Each Apply Process

You can get the default tag for all redo entries generated by each apply process by querying for the APPLY_TAG value in the DBA_APPLY data dictionary view. For example, to get the hexadecimal value of the default tag generated in the redo entries by each apply process, run the following query:

COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30
COLUMN APPLY_TAG HEADING 'Tag Value' FORMAT A30

SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY;

Your output looks similar to the following:

Apply Process Name             Tag Value
------------------------------ ------------------------------
APPLY_FROM_MULT2               00
APPLY_FROM_MULT3               00

A handler or custom rule-based transformation function associated with an apply process can get the tag by calling the DBMS_STREAMS.GET_TAG function.