Add an Active Site to an Existing Streams Environment

Table Of Contents 

This document is organized into following sub-sections :

• Overview of the Sample Application
• Required Software
• Steps to Add Site to existing Streams Environment
• 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 adding a site ( a database node) to an existing two-way Streams replication environment while the site is active. A site is an active site if the tables in its schema to be replicated, are being modified by DML (Inserts/Updates/Deletes) . DDL can also be performed on the tables except while the export of the tables is performed for instantiation. Using the procedure detailed below, the existing processes in the streams environment continue to perform their assigned tasks and users continue their activity on the site while the site is added into the replication configuration. This example illustrates two-way capture, propagation and application of the schema DML and DDL change events between three different databases. The sampe first configures two databases for bi-directional replication of a sample schema and then adds a third database to streams environment.

1)Three installations of 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

Assume that two databases db1 and db2 have Streams setup and are replicating HR schema.

Steps to add a new site (db3) to the Streams environment without stopping the existing apply/propagation/capture setup at two databases:

1. Setup Streams admin user at db3 and grant it necessary privileges.
2. Setup queue at db3.
3. At db3, create database links to db1 and db2 for the Streams propagation user.
4. Schedule propagation from db1 to db3 and from db2 to db3.
5. Schedule propagation from db3 to db1 and db2.
6. Drop all tables in the HR schema being replicated at db3 if schema already exists.
7. Configure db1 and db2 to apply changes from db3. Apply should be left in the DISABLED state.
8. Configure db3 to apply changes from db1 and db2. Apply should be left in the DISABLED state.
9. Configure capture at db3. Capture should be left in the DISABLED state.
10. If db1 has been chosen as the database for exporting schema to db3, at db1, ensure the apply engine for applying changes from db2 at db1 terminated cleanly without any outstanding entries in sys.streams$_apply_progress table (this might require some code changes in apply engine, but should not be very difficult).The apply engine must be left in the disabled state until the export has started.
11. Use the value of dba_apply_progress.applied_message_number for db2 apply engine at db1 as the instantiation_scn at db3 for the HR tables from db2.
12. Take an export of the HR schema at db1 with object_consistent=Y or with the flashback_scn set to the current scn.
13. Do an import of the schema at db3 with streams_instantiation=Y. This sets the instantiation scn at db3 for db1.
14. Before starting any operations on the HR schema at db3, get the current scn and set it as the instantiation scn for HR tables from db3 at db1 and db2.
15. Startup capture/apply engines at db3.
16. Startup the apply engines at db1 and db2 for applying changes from db3. Also start the apply engine for applying changes from db2 at db1.

This sample is provided as a zip file named AddActiveSiteToStreams.zip . Extract the file using Winzip utility. This results in creation of AddActiveSiteToStreams 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 to bi-directionally replicate a schema's DML and DDL changes between the two databases.
3. Add another site to the above streams environment for n-way replication of the changes in schema at this site.

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

The script SetupStreams.sql provided with the sample sets up bi-directional replication of a schema between two databases using streams. It creates a schema named strmuser with tables named tableone and tabletwo in both databases and creates necessary capture, propagation and apply processes in both the databases to set up bi-directional replication for strmuser schema.

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

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

Check spool file streams_setup.log for any errors.

Add another Site to existing Streams environment

The script AddSite.sql provided with the sample adds another active site to the existing two-way streams setup (created in Step 2) without stopping any capture/propagation/apply process in the existing streams environment. The script follows the steps detailed above to add the site to existing streams environment.

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

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

Check spool file add_site.log for any errors.

Login as strmuser/strmuser to database 1 and insert a row in tables tableone and tabletwo as follows :

SQL>INSERT INTO tableone VALUES(5,'Chan');

SQL>INSERT INTO tabletwo VALUES(6,'Adam');
SQL>COMMIT;

Allow some time for streams to capture, propagate and apply the changes to tableone table in database 2 and database 3. Now login as strmuser/strmuser to database 2 or database 3 and perform following query :

SQL>SELECT * FROM tableone;
SQL>SELECT * FROM tabletwo;

If the streams setup was done correctly, you would see the above values in results of the query. Similarly insert rows in strmuser schema tables at database 2 and database 3 and check if they are replicated at other two nodes.

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

SQL> ALTER TABLE tableone ADD (job 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_SITE1';

   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 ='SITE1_TO_SITE2'

   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 =  'SITE1_TO_SITE2' 
   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 = 'SITE1_TO_SITE2'
   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_SITE2';

   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_SITE2');

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_SITE2';

   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_SITE2';

Refer documentation for more details on troubleshooting the streams environment.

The directory structure of the AddActiveSiteToStreams is as shown below :