Bitmap Subsetting for Update Anywhere in Hub and Spoke Configuration

Table Of Contents 

This document is organized into following sub-sections :

• Overview of the Sample Application
• Required Software
• Extracting the source code
• Installing and Configuring the Sample
• Testing the Streams Setup
• Troubleshooting
• Description of Sample Files

Oracle Streams provides for sharing and management of data and events in a stream. The stream can propagate this information within a database or from one database to another. Oracle Streams enable you to have a complete control on what information goes into the stream, how it is propagated to the destinations and how it is applied at the destinations. It also enables you to transform the data in streams at any stage as per the requirement.

Streams can capture, stage, and manage events in the database automatically, including data manipulation language (DML) changes and data definition language (DDL) changes. You can also put user-defined events into a stream. These events are automatically captured, propagated and applied at the specified destinations.

This sample demonstrates routing LCRs (Logical Control Records) in a hub/spoke configuration. The hub and spoke configuration is as shown below :

Any changes made at hub are applied to spoke S1 and S2. Similarly any changes made at S1 are applied at hub and S2. Same is the case for changes at S2. To avoid cyclic propagation of changes, Bitmap Subsetting is used to determine whether or not to replicate the changes based on the values of the bitmap. The RAW tag associated with every redo generated in the database is used as the bitmap for apply and queue forwarding. Based on the value of tag bit, a site determines whether or not to capture/propagate/apply the LCR.

Introduction to tags

Every redo entry in the redo log has a tag associated with it. The datatype of the tag is RAW. By default, when a user or an application generates redo entries, the values of the tag is NULL for each redo entry, and a NULL tag consumes no space in the redo entry. The size limit for a tag value is 2000 bytes.

One can configure the way the tag values are interpreted. For example, a tag can be used to determine whether an LCR contains a change that originated in the local database or at a different database, so that you can avoid change cycling ( sending an LCR back to the database where it originated). This concept is used in the sample as detailed below.

Using Redo tags with Streams

The sample uses a hub and spoke configuration as shown above. Any changes made to strmuser schema on any node should be propagated and replicated at all other nodes using a central hub. All the other nodes form the spokes of the configuration. Tags are used as follows to avoid propagation of LCRs back to the node where it was generated :

A NULL tag LCR at each node denotes local DML changes. A set (non NULL) tag represents changes from a remote node. A set bit at hub identifies the spoke where the changes were generated while at spoke it represents non local changes.

Each spoke sends redo with NULL tag to hub. The apply process for the spoke at hub applies the LCRs to hub and sets the tag to a bit that identifies the spoke. The LCRs are then propagated to all the spokes except the spoke where they were generated, using the bit value. Any redo generated at hub is sent to spokes with the NULL tag. The apply process at spoke applies the LCRs irrespective of the bit values since only the remote LCRs are propagated from hub to any spoke. Following example explains propagation logic :

Assume that the tag 'T' can assume following values :

NULL : If the changes were originated at the HUB H.

1 : If the changes originated at spoke S1 and corresponding LCR was applied at HUB H by the apply process.

2 : If the changes originated at spoke S2 and corresponding LCR was applied at HUB H by the apply process.

Propagation Logic :

Apply Logic at Hub :

After applying the LCRs propagated from S1 at hub, the apply process at hub for S1 changes the value of LCR's tag to 1 so that it is not propagated back to S1. Similarly after applying the LCRs propagated from S2 at hub, the apply process for S2 at hub changes the value of LCR's tag to 2 to prevent it from being propagated to S2.

1) Oracle9i 9.2.0.2 or later database. Download Oracle9i 9.2.0.1 here and apply patch no. 2632931 for Oracle9i 9.2.0.2

This sample is provided as a zip file named BitmapSubsetting.zip . Extract the file using Winzip utility. This results in creation of BitmapSubsetting directory containing sample files.

This sample assumes that no streams set up has been done on the databases being used. It does the streams configuration in following steps :

1. Create and configure the Streams Administrator.
2. Setup the streams for update anywhere in Hub and Spoke Configuration.

Configure the Streams Environment

The script StreamsAdminConfig.sql creates a streams administrator and assigns necessary privileges to it. Login to SQL * Plus as any user and execute this script as follows:

SQL>@<SAMPLE_HOME>/scripts/StreamsAdminConfig.sql

Execute this script on each database that will be a part of Streams environment. Check spool file named streams_config.log for any errors. Some initialization parameter need to be set for the operation, reliability and performance of Streams environment. Refer documentation for setting up these parameters.

Setup the Streams for update anywhere in Hub and Spoke configuration

The script StreamsSetup.sql provided with the sample sets up streams for replication of a table in a hub and spoke configuration. It uses three databases of which one is hub and the other two are spokes. The script creates a schema named strmuser with a table named tabone in at all the nodes in configuration and creates necessary capture, propagation and apply processes in at all the nodes to set up replication for table tabone. It uses Bitmap Subsetting to prevent the changes from being propagated back to source database.

Login to SQL* Plus as any user and execute the script StreamsSetup.sql as follows and provide the information asked by the script :

SQL>@<SAMPLE_HOME>/scripts/StreamsSetup.sql

Check streams_bitmap.log for any errors.

Login as strmuser/strmuser to hub database and insert a row in tables tabone as follows :

SQL>INSERT INTO tabone VALUES (51,'Chan');

Allow some time for streams to capture, propagate and apply the changes to tabone table in spoke1 and spoke2. Now login as strmuser/strmuser to spoke1 and spoke2 database and perform following query :

SQL>select * from tabone;

If the streams setup was done correctly, you would see the above values in results of the query. Similarly insert rows in tabone at spoke1 and spoke2 and check if they are replicated at all other nodes.

You can also perform DDL operations on the table tabone at any node and check if the changes are replicated at other nodes.
Example :

SQL> ALTER TABLE TABONE ADD (address varchar2(100));

If the replication from source database to destination database is not taking place, follow the steps below to check what is going wrong :

1. Check for the status of capture and apply processes at source and destination database as follows :

    SELECT CAPTURE_NAME, STATUS FROM DBA_CAPTURE;

   If the capture process is disabled, start it and try again. If capture process is aborted, check for the errors in the alert_SID.log file located at <ORACLE_HOME>/admin/sid/bdump/ directory of your Oracle installation.

2. If the capture process is enable check if it is capturing the changes as follows :

   SELECT TOTAL_MESSAGES_CAPTURED,TOTAL_MESSAGES_ENQUEUED 
   FROM V$STREAMS_CAPTURE WHERE CAPTURE_NAME='CAPTURE_HUB';

   If the results of query are non-zero, it means the changes are being captured. If results show zero values, check the alert_SID.log and trace files for details.

3. If the capture is happening properly, check if the propagation is set up correctly and the captured changes are being propagated to destination database as follows :

   Check if source and destination queues are specified correctly for the propagation process using following query :

   SELECT p.SOURCE_QUEUE_OWNER||'.'|| p.SOURCE_QUEUE_NAME||'@'||g.GLOBAL_NAME
    SOURCE_QUEUE, 
   p.DESTINATION_QUEUE_OWNER||'.'|| p.DESTINATION_QUEUE_NAME||'@'||
   p.DESTINATION_DBLINK DESTINATION_QUEUE 
   FROM DBA_PROPAGATION p, GLOBAL_NAME g 
   WHERE p.PROPAGATION_NAME ='HUB_TO_SPOKE1_PROP'

   If the source and destination queues are not correct, drop the propagation process and create a new one with correct source and destination queues.

   Check if the events and bytes are being propagated by the propagation process using following query :

   SELECT s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES
   FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
   WHERE p.PROPAGATION_NAME =  'HUB_TO_SPOKE1_PROP'
   AND p.DESTINATION_DBLINK = s.DESTINATION 
   AND s.SCHEMA=p.SOURCE_QUEUE_OWNER  AND s.QNAME = p.SOURCE_QUEUE_NAME;

   You will get non-zero values if bytes are being propagated, else check if the propagation job is enabled and if there are any propagation errors using following query

   SELECT p.DESTINATION_DBLINK,
   DECODE(s.SCHEDULE_DISABLED,'Y', 'Disabled','N', 'Enabled') SCHEDULE_DISABLED,
   s.PROCESS_NAME, s.FAILURES, s.LAST_ERROR_TIME, s.LAST_ERROR_MSG 
   FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
   WHERE p.PROPAGATION_NAME = 'HUB_TO_SPOKE1_PROP'
   AND p.DESTINATION_DBLINK = s.DESTINATION  
   
   AND s.SCHEMA = p.SOURCE_QUEUE_OWNER 
   AND s.QNAME = p.SOURCE_QUEUE_NAME;

4. If capture and propagation process are performing as expected, check the apply process at destination database. First check the status of apply process as follows :

   SELECT APPLY_NAME, STATUS FROM DBA_APPLY;

   If the apply process is disabled, start it. If it is aborted, check errors in alert_SID.log file and trace files. You can also check if any errors occurred when applying the changes as follows :

   SELECT ERROR_MESSAGE FROM DBA_APPLY_ERROR WHERE APPLY_NAME ='APPLY_SPOKE1';

   Correct any errors and start the apply process.
   For example if you get an ORA-26687 Instantiation SCN Not Set error, it means the instantiation SCN is not set on an object for which an apply process is attempting to apply changes. User SET_TABLE_INSTANTIATION_SCN procedure in DBMS_APPLY_ADM package to set instantiation SCN for corresponding table. Refer documentation for correcting other common errors.

   After correcting the error execute following procedure to re-execute the transactions associated with errors :

   EXECUTE DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(apply_name => 'APPLY_SPOKE1');

5. If there are no errors in step 4 , check if messages are being dequeued properly at destination database as follows :

   SELECT TOTAL_MESSAGES_DEQUEUED FROM V$STREAMS_APPLY_READER 
   
   WHERE APPLY_NAME ='APPLY_SPOKE1';

   Check the total messages assigned versus total messages applied by the apply process as follows :

   SELECT TOTAL_RECEIVED, TOTAL_APPLIED, TOTAL_ERRORS 
   FROM V$STREAMS_APPLY_COORDINATOR 
   WHERE APPLY_NAME= 'APPLY_SPOKE1';

Refer documentation for more details on troubleshooting the streams environment.

The directory structure of the BitmapSubsetting sample is as shown follows :