Add an Active Table 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 Table 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 table into an existing bi-directional Streams replication environment while the table is active. A table is an active table if it is being modified by DML (Inserts/Updates/Deletes) . DDL can also be performed on the table except while the export of the table 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 table while the table is added into the replication configuration. This example illustrates bi-directional capture, propagation and application of the table DML and DDL change events between two different databases.

1)Two instances 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.
Capture CAP1 is running at db1 and apply APP1 is running at db2. At each database, the queue is named Q1.

Steps to add table T1 which exists at db1 to the Streams environment without stopping the existing apply/propagation/capture:

1. Create a new queue Q2 at db1 and db2.
2. Create new capture CAP2 at db1 and add T1 to the rules.
3. Create new apply APP2 at db2 and add T1 to the rules.
4. Add propagation rules for T1 to schedule propagation between Q2 at db1 and db2.
5. Export table T1 with object_consistent=Y at db1 and import into db2 with streams_instantiation=Y. Do not perform DML or DDL on T1 at db2 till replication from db2 to db1 is set up.
6. If bi-directional replication is going to be used:
   
   • Add T1 to the capture rules for the capture process at db2.
   • Add T1 to the apply rules for the apply process at db1.
   • Add T1 to the propagation rules from db1 to db2 for Q1.
   • Set the current scn at db2 as the instantiation scn for T1 at db1 to apply changes from db2.
7. Start up the new apply and capture engines at db1 and db2.
8. Add T1 to the capture rules for CAP1.
9. Add T1 to the propagation rules for Q1 from db1 to db2.
10. Get a shared lock on T1 at db1 and obtain the current scn. The shared lock is needed so that we can be sure there are no transactions in progress during the time we get the current scn. If not, we may end up losing some LCRs.
11. Set this current scn as the maximum_scn for the new capture CAP2 at db1 and for the old apply APP1 at db2.
12. Release shared lock on T1.
13. When APP1 is disabled on account of reaching the maximum_scn, add T1 to the apply rules for APP1.
14. Wait till APP2 has finished processing all the messages.
15. Drop the new capture/apply/queue CAP2/APP2/Q2.
16. Set the maximum_scn for the apply process APP1 to infinite.
17. Enable apply process APP1.

The above procedure should be used if the table is in active use within the database and none of the Streams processes (capture, propagation, apply) can be interrupted for an extended period of time.

This procedure is not required in the following cases:

• If the capture process can be stopped during the instantiation of the source table at the target database, use the following alternative steps to add the table.
• Stop the capture process at the source database.
• Add a rule(s) to include the table in the capture ruleset.  Add a rule(s) to include the table into the propagation and apply rulesets, if appropriate, using  the DBMS_STREAMS_ADM.ADD_TABLE_RULE procedure.
• Instantiate the table at the  destination database (export-import instantiation)

• Start the capture process at the source site

• If the apply process or propagation to the destination site can be stopped during the instantiation, use the following alternative steps to add the table.
• Stop the apply process or propagation to the destination site.
• Add a rule(s) to include the table in the capture ruleset.  Add a rule(s) to include the table into the propagation and apply rulesets, if appropriate, using  the DBMS_STREAMS_ADM.ADD_TABLE_RULE procedure
• Instantiate the table at the destination database (export-import instantiation).

• Start the apply process or propagation to the destination site.

• If all DML and DDL can be stopped on the table while the table is being instantiated, use the following alternative steps to add the table.
• Add a rule(s) to include the table in the capture, propagation, and apply rulesets, as appropriate, using  the DBMS_STREAMS_ADM.ADD_TABLE_RULE procedure
• Instantiate the table at the destination database (export-import instantiation).
• Allow DML and DDL activity on the table after the instantiation is complete.

This sample is provided as a zip file named AddActiveTableToStreams.zip . Extract the file using Winzip utility. This results in creation of AddActiveTableToStreams 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 table's DML and DDL changes between the two databases.
3. Add another table which already exists at one of the databasesto streams environment with minimal downtime on the table and minimum disruption to any of the existing capture, propagation or apply processes at the two databases.

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.

Note : This sample needs LOGMNR_MAX_PERSISTENT_SESSIONS initialization parameter to be set to 2 or more because two capture processes are being created at source database.

Setup the Streams

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

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 active table to existing Streams environment

The script AddTable.sql provided with the sample adds another active table to the existing two-way streams setup (created in Step 2) with minimal downtime on the table and stopping any existing capture/propagation/apply for the briefest possible amount of time. It creates a table named tabtwo in strmuser schema and adds it to the existing streams environment.

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

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

The AddTable.sql script hangs and wait for a message after executing step 10 in the procedure detailed in Steps to Add Table to existing Streams Environment .

At this point open a new command window and login to SQL * Plus as any user. Execute the script SetParams.sql as follows and provide the information asked by the script :

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

On completion this script sends a message to session in which AddTable.sql is running so that it can continue with further steps.

Check spool file add_table.log and setparams.log for any errors.

Note : The SetParams.sql has to be executed from different session because the SET_PARAMETER method used in script does an implicit commit which would release the lock (acquired in step 10) on the table being added to streams environment.

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

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

SQL>INSERT INTO tabtwo VALUES(26,'Adam');

SQL>COMMIT;

Allow some time for streams to capture, propagate and apply the changes to tabone table in database 2. Now login as strmuser/strmuser to database 2 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 database 2 and check if they are replicated at database 1.

Perform similar test for table tabtwo also.

You can also perform DDL operations on the tables at any database and check if the changes are replicated at other database.
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_DB1';

   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 ='DB1_TO_DB2_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 = 'DB1_TO_DB2_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 = 'DB1_TO_DB2_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_DB2';

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

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

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

Refer documentation for more details on troubleshooting the streams environment.

The directory structure of the AddActiveTableToStreams is as shown below :