Skip Headers
Oracle® Streams Replication Administrator's Guide
10g Release 2 (10.2)

Part Number B14228-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

20 Multiple-Source Replication Example

This chapter illustrates an example of a multiple-source replication environment that can be constructed using Streams.

This chapter contains these topics:

Overview of the Multiple Source Databases Example

This example illustrates using Streams to replicate data for a schema among three Oracle databases. DML and DDL changes made to tables in the hr schema are captured at all databases in the environment and propagated to each of the other databases in the environment.

Figure 20-1 provides an overview of the environment.

Figure 20-1 Sample Environment that Shares Data from Multiple Databases

Description of Figure 20-1 follows
Description of "Figure 20-1 Sample Environment that Shares Data from Multiple Databases"

As illustrated in Figure 20-1, all of the databases will contain the hr schema when the example is complete. However, at the beginning of the example, the hr schema exists only at mult1.net. During the example, you instantiate the hr schema at mult2.net and mult3.net.

In this example, Streams is used to perform the following series of actions:

  1. After instantiation, the capture process at each database captures DML and DDL changes for all of the tables in the hr schema and enqueues them into a local queue.

  2. Propagations at each database propagate these changes to all of the other databases in the environment.

  3. The apply processes at each database apply changes in the hr schema received from the other databases in the environment.

This example avoids sending changes back to their source database by using the default apply tag for the apply processes. When you create an apply process, the changes applied by the apply process have redo entries with a tag of '00' (double zero) by default. These changes are not recaptured because, by default, rules created by the DBMS_STREAMS_ADM package have an is_null_tag()='Y' condition by default, and this condition ensures that each capture process captures a change in a redo entry only if the tag for the redo entry is NULL.

See Also:

Chapter 4, "Streams Tags" for more information about tags

Prerequisites

The following prerequisites must be completed before you begin the example in this chapter.

Set Up Users and Create Queues and Database Links

This section illustrates how to set up users and create queues and database links for a Streams replication environment that includes three Oracle databases. The remaining parts of this example depend on the users and queues that you configure in this section.

Complete the following steps to set up the users and to create the queues at all of the databases.

  1. Show Output and Spool Results

  2. Set Up Users at mult1.net

  3. Create the ANYDATA Queue at mult1.net

  4. Create the Database Links at mult1.net

  5. Prepare the Tables at mult1.net for Latest Time Conflict Resolution

  6. Set Up Users at mult2.net

  7. Create the ANYDATA Queue at mult2.net

  8. Create the Database Links at mult2.net

  9. Drop All of the Tables in the hr Schema at mult2.net

  10. Set Up Users at mult3.net

  11. Create the ANYDATA Queue at mult3.net

  12. Create the Database Links at mult3.net

  13. Drop All of the Tables in the hr Schema at mult3.net

  14. Check the Spool Results

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.
/************************* BEGINNING OF SCRIPT ******************************

Step 1   Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL streams_setup_mult.out

/*

Step 2   Set Up Users at mult1.net

Connect to mult1.net as SYSTEM user.

*/
 
CONNECT SYSTEM/MANAGER@mult1.net

/*

Create the Streams administrator named strmadmin and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You can choose a different name for this user.

Note:

  • For security purposes, use a password other than strmadminpw for the Streams administrator.

  • The ACCEPT command must appear on a single line in the script.

See Also:

Oracle Streams Concepts and Administration for more information about configuring a Streams administrator
*/

GRANT DBA TO strmadmin IDENTIFIED BY strmadminpw;

ACCEPT streams_tbs PROMPT 'Enter Streams admin tablespace on mult1.net: '

ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs
                     QUOTA UNLIMITED ON &streams_tbs;

/*

Step 3   Create the ANYDATA Queue at mult1.net

Connect as the Streams administrator at mult1.net.

*/

CONNECT strmadmin/strmadminpw@mult1.net

/*

Run the SET_UP_QUEUE procedure to create the following queues:

Running the SET_UP_QUEUE procedure performs the following actions for each queue:

*/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.captured_mult1_table',
    queue_name   => 'strmadmin.captured_mult1');
END;
/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.from_mult2_table',
    queue_name   => 'strmadmin.from_mult2');
END;
/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.from_mult3_table',
    queue_name   => 'strmadmin.from_mult3');
END;
/

/*

Step 4   Create the Database Links at mult1.net

Create database links from the current database to the other databases in the environment.

*/

CREATE DATABASE LINK mult2.net CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'mult2.net';

CREATE DATABASE LINK mult3.net CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'mult3.net';

/*

Step 5   Prepare the Tables at mult1.net for Latest Time Conflict Resolution

This example will configure the tables in the hr schema for conflict resolution based on the latest time for a transaction.

Connect to mult1.net as the hr user.

*/
 
CONNECT hr/hr@mult1.net

/*

Add a time column to each table in the hr schema.

*/
 
ALTER TABLE hr.countries ADD (time TIMESTAMP WITH TIME ZONE);
ALTER TABLE hr.departments ADD (time TIMESTAMP WITH TIME ZONE);
ALTER TABLE hr.employees ADD (time TIMESTAMP WITH TIME ZONE);
ALTER TABLE hr.job_history ADD (time TIMESTAMP WITH TIME ZONE);
ALTER TABLE hr.jobs ADD (time TIMESTAMP WITH TIME ZONE);
ALTER TABLE hr.locations ADD (time TIMESTAMP WITH TIME ZONE);
ALTER TABLE hr.regions ADD (time TIMESTAMP WITH TIME ZONE);

/*

Create a trigger for each table in the hr schema to insert the time of a transaction for each row inserted or updated by the transaction.

*/

CREATE OR REPLACE TRIGGER hr.insert_time_countries
BEFORE
  INSERT OR UPDATE ON hr.countries FOR EACH ROW
BEGIN
   -- Consider time synchronization problems. The previous update to this 
   -- row might have originated from a site with a clock time ahead of the 
   -- local clock time.
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

CREATE OR REPLACE TRIGGER hr.insert_time_departments
BEFORE
  INSERT OR UPDATE ON hr.departments FOR EACH ROW
BEGIN
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

CREATE OR REPLACE TRIGGER hr.insert_time_employees
BEFORE
  INSERT OR UPDATE ON hr.employees FOR EACH ROW
BEGIN
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

CREATE OR REPLACE TRIGGER hr.insert_time_job_history
BEFORE
  INSERT OR UPDATE ON hr.job_history FOR EACH ROW
BEGIN
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

CREATE OR REPLACE TRIGGER hr.insert_time_jobs
BEFORE
  INSERT OR UPDATE ON hr.jobs FOR EACH ROW
BEGIN
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

CREATE OR REPLACE TRIGGER hr.insert_time_locations
BEFORE
  INSERT OR UPDATE ON hr.locations FOR EACH ROW
BEGIN
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

CREATE OR REPLACE TRIGGER hr.insert_time_regions
BEFORE
  INSERT OR UPDATE ON hr.regions FOR EACH ROW
BEGIN
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

/*

Step 6   Set Up Users at mult2.net

Connect to mult2.net as SYSTEM user.

*/
 
CONNECT SYSTEM/MANAGER@mult2.net

/*

Create the Streams administrator named strmadmin and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You can choose a different name for this user.

Note:

  • For security purposes, use a password other than strmadminpw for the Streams administrator.

  • The ACCEPT command must appear on a single line in the script.

See Also:

Oracle Streams Concepts and Administration for more information about configuring a Streams administrator
*/

GRANT DBA TO strmadmin IDENTIFIED BY strmadminpw;

ACCEPT streams_tbs PROMPT 'Enter Streams admin tablespace on mult2.net: '

ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs
                     QUOTA UNLIMITED ON &streams_tbs;

/*

Step 7   Create the ANYDATA Queue at mult2.net

Connect as the Streams administrator at mult2.net.

*/

CONNECT strmadmin/strmadminpw@mult2.net

/*

Run the SET_UP_QUEUE procedure to create the following queues:

Running the SET_UP_QUEUE procedure performs the following actions for each queue:

*/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.captured_mult2_table',
    queue_name   => 'strmadmin.captured_mult2');
END;
/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.from_mult1_table',
    queue_name   => 'strmadmin.from_mult1');
END;
/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.from_mult3_table',
    queue_name   => 'strmadmin.from_mult3');
END;
/

/*

Step 8   Create the Database Links at mult2.net

Create database links from the current database to the other databases in the environment.

*/

CREATE DATABASE LINK mult1.net CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'mult1.net';

CREATE DATABASE LINK mult3.net CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'mult3.net';

/*

Step 9   Drop All of the Tables in the hr Schema at mult2.net

This example illustrates instantiating the tables in the hr schema at mult2.net by importing these tables from mult1.net into mult2.net. You must drop the tables in the hr schema at mult2.net for the instantiation portion of this example to work properly.

Attention:

If you complete the following steps and drop the tables in the hr schema at mult2.net, then you should complete the remaining steps of this example to reinstantiate the hr schema. If the hr schema does not exist in an Oracle database, then some examples in the Oracle documentation set can fail.

Connect as hr at mult2.net.

*/

CONNECT hr/hr@mult2.net

/*

Drop all tables in the hr schema in the mult2.net database.

*/

DROP TABLE hr.countries CASCADE CONSTRAINTS;
DROP TABLE hr.departments CASCADE CONSTRAINTS;
DROP TABLE hr.employees CASCADE CONSTRAINTS;
DROP TABLE hr.job_history CASCADE CONSTRAINTS;
DROP TABLE hr.jobs CASCADE CONSTRAINTS;
DROP TABLE hr.locations CASCADE CONSTRAINTS;
DROP TABLE hr.regions CASCADE CONSTRAINTS;

/*

Step 10   Set Up Users at mult3.net

Connect to mult3.net as SYSTEM user.

*/
 
CONNECT SYSTEM/MANAGER@mult3.net

/*

Create the Streams administrator named strmadmin and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You can choose a different name for this user.

Note:

  • For security purposes, use a password other than strmadminpw for the Streams administrator.

  • The ACCEPT command must appear on a single line in the script.

See Also:

Oracle Streams Concepts and Administration for more information about configuring a Streams administrator
*/

GRANT DBA TO strmadmin IDENTIFIED BY strmadminpw;

ACCEPT streams_tbs PROMPT 'Enter Streams admin tablespace on mult3.net: '

ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs
                     QUOTA UNLIMITED ON &streams_tbs;

/*

Step 11   Create the ANYDATA Queue at mult3.net

Connect as the Streams administrator at mult3.net.

*/

CONNECT strmadmin/strmadminpw@mult3.net

/*

Run the SET_UP_QUEUE procedure to create the following queues:

Running the SET_UP_QUEUE procedure performs the following actions for each queue:

*/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.captured_mult3_table',
    queue_name   => 'strmadmin.captured_mult3');
END;
/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.from_mult1_table',
    queue_name   => 'strmadmin.from_mult1');
END;
/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'strmadmin.from_mult2_table',
    queue_name   => 'strmadmin.from_mult2');
END;
/

/*

Step 12   Create the Database Links at mult3.net

Create database links from the current database to the other databases in the environment.

*/

CREATE DATABASE LINK mult1.net CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'mult1.net';

CREATE DATABASE LINK mult2.net CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'mult2.net';

/*

Step 13   Drop All of the Tables in the hr Schema at mult3.net

This example illustrates instantiating the tables in the hr schema at mult3.net by importing these tables from mult1.net into mult3.net. You must drop the tables in the hr schema at mult3.net for the instantiation portion of this example to work properly.

Attention:

If you complete the following steps and drop the tables in the hr schema at mult3.net, then you should complete the remaining steps of this example to reinstantiate the hr schema. If the hr schema does not exist in an Oracle database, then some examples in the Oracle documentation set can fail.

Connect as hr at mult3.net.

*/

CONNECT hr/hr@mult3.net

/*

Drop all tables in the hr schema in the mult3.net database.

*/

DROP TABLE hr.countries CASCADE CONSTRAINTS;
DROP TABLE hr.departments CASCADE CONSTRAINTS;
DROP TABLE hr.employees CASCADE CONSTRAINTS;
DROP TABLE hr.job_history CASCADE CONSTRAINTS;
DROP TABLE hr.jobs CASCADE CONSTRAINTS;
DROP TABLE hr.locations CASCADE CONSTRAINTS;
DROP TABLE hr.regions CASCADE CONSTRAINTS;

/*

Step 14   Check the Spool Results

Check the streams_setup_mult.out spool file to ensure that all actions finished successfully after this script is completed.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Example Script for Sharing Data from Multiple Databases

Complete the following steps to configure a Streams environment that shares information from multiple databases.

  1. Show Output and Spool Results

  2. Specify Supplemental Logging at mult1.net

  3. Create the Capture Process at mult1.net

  4. Create One Apply Process at mult1.net for Each Source Database

  5. Specify hr as the Apply User for Each Apply Process at mult1.net

  6. Grant the hr User Execute Privilege on the Apply Process Rule Set

  7. Configure Latest Time Conflict Resolution at mult1.net

  8. Configure Propagation at mult1.net

  9. Create the Capture Process at mult2.net.

  10. Set the Instantiation SCN for mult2.net at the Other Databases

  11. Create One Apply Process at mult2.net for Each Source Database

  12. Specify hr as the Apply User for Each Apply Process at mult2.net

  13. Grant the hr User Execute Privilege on the Apply Process Rule Set

  14. Configure Propagation at mult2.net

  15. Create the Capture Process at mult3.net

  16. Set the Instantiation SCN for mult3.net at the Other Databases

  17. Create One Apply Process at mult3.net for Each Source Database

  18. Specify hr as the Apply User for Each Apply Process at mult3.net

  19. Grant the hr User Execute Privilege on the Apply Process Rule Set

  20. Configure Propagation at mult3.net

  21. Instantiate the hr Schema at mult2.net

  22. Instantiate the hr Schema at mult3.net

  23. Configure Latest Time Conflict Resolution at mult2.net

  24. Start the Apply Processes at mult2.net

  25. Configure Latest Time Conflict Resolution at mult3.net

  26. Start the Apply Processes at mult3.net

  27. Start the Apply Processes at mult1.net

  28. Start the Capture Process at mult1.net

  29. Start the Capture Process at mult2.net

  30. Start the Capture Process at mult3.net

  31. Check the Spool Results

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.
/************************* BEGINNING OF SCRIPT ******************************

Step 1   Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL streams_mult.out

/*

Step 2   Specify Supplemental Logging at mult1.net

Connect to mult1.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult1.net

/*

Specify an unconditional supplemental log group that includes the primary key for each table and the column list for each table, as specified in "Configure Latest Time Conflict Resolution at mult1.net". Because the column list for each table includes all of the columns of each table except for its primary key, this step creates a supplemental log group for each table that includes all of the columns in the table.

Note:

  • For convenience, this example includes the primary key column(s) for each table and the columns used for update conflict resolution in a single unconditional log group. You can choose to place the primary key column(s) for each table in an unconditional log group and the columns used for update conflict resolution in a conditional log group.

  • You do not need to specify supplemental logging explicitly at mult2.net and mult3.net in this example. When you use Data Pump to instantiate the tables in the hr schema at these databases later in this example, the supplemental logging specifications at mult1.net are retained at mult2.net and mult3.net.

*/

ALTER TABLE hr.countries ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE hr.job_history ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE hr.locations ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

/*

Step 3   Create the Capture Process at mult1.net

Create the capture process to capture changes to the entire hr schema at mult1.net. This step also prepares the hr schema at mult1.net for instantiation. After this step is complete, users can modify tables in the hr schema at mult1.net.

*/

BEGIN   
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name    => 'hr',
    streams_type   => 'capture',
    streams_name   => 'capture_hr', 
    queue_name     => 'strmadmin.captured_mult1',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/

/*

Step 4   Create One Apply Process at mult1.net for Each Source Database

Configure mult1.net to apply changes to the hr schema at mult2.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hr',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult2',
    queue_name      => 'strmadmin.from_mult2',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'mult2.net',
    inclusion_rule  => true);
END;
/

/*

Configure mult1.net to apply changes to the hr schema at mult3.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hr',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult3',
    queue_name      => 'strmadmin.from_mult3',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'mult3.net',
    inclusion_rule  => true);
END;
/

/*

Step 5   Specify hr as the Apply User for Each Apply Process at mult1.net

In this example, the hr user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr already has the necessary privileges to change these database objects, and it is convenient to make hr the apply user.

When the apply process was created in the previous step, the Streams administrator strmadmin was specified as the apply user by default, because strmadmin ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin as the apply user, but then you must grant strmadmin privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it might be more convenient to use the Streams administrator as the apply user.

See Also:

Oracle Streams Concepts and Administration for more information about configuring a Streams administrator
*/

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'apply_from_mult2',
    apply_user => 'hr');
END;
/

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'apply_from_mult3',
    apply_user => 'hr');
END;
/

/*

Step 6   Grant the hr User Execute Privilege on the Apply Process Rule Set

Because the hr user was specified as the apply user in the previous step, the hr user requires EXECUTE privilege on the positive rule set used by each apply process

*/

DECLARE
   rs_name  VARCHAR2(64);   -- Variable to hold rule set name
BEGIN
  SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME 
    INTO rs_name 
    FROM DBA_APPLY 
    WHERE APPLY_NAME='APPLY_FROM_MULT2';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

DECLARE
   rs_name  VARCHAR2(64);   -- Variable to hold rule set name
BEGIN
  SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME 
    INTO rs_name 
    FROM DBA_APPLY 
    WHERE APPLY_NAME='APPLY_FROM_MULT3';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

/*

Step 7   Configure Latest Time Conflict Resolution at mult1.net

Specify an update conflict handler for each table in the hr schema. For each table, designate the time column as the resolution column for a MAXIMUM conflict handler. When an update conflict occurs, such an update conflict handler applies the transaction with the latest (or greater) time and discards the transaction with the earlier (or lesser) time. The column lists include all columns for each table, except for the primary key, because this example assumes that primary key values are never updated.

*/
 
DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'country_name';
  cols(2) := 'region_id';
  cols(3) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.countries',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'department_name';
  cols(2) := 'manager_id';
  cols(3) := 'location_id';
  cols(4) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.departments',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1)  := 'first_name';
  cols(2)  := 'last_name';
  cols(3)  := 'email';
  cols(4)  := 'phone_number';
  cols(5)  := 'hire_date';
  cols(6)  := 'job_id';
  cols(7)  := 'salary';
  cols(8)  := 'commission_pct';
  cols(9)  := 'manager_id';
  cols(10) := 'department_id';
  cols(11) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.employees',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'job_title';
  cols(2) := 'min_salary';
  cols(3) := 'max_salary';
  cols(4) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.jobs',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'employee_id';
  cols(2) := 'start_date';
  cols(3) := 'end_date';
  cols(4) := 'job_id';
  cols(5) := 'department_id';
  cols(6) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.job_history',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'street_address';
  cols(2) := 'postal_code';
  cols(3) := 'city';
  cols(4) := 'state_province';
  cols(5) := 'country_id';
  cols(6) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.locations',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'region_name';
  cols(2) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.regions',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

/*

Step 8   Configure Propagation at mult1.net

Configure and schedule propagation of DML and DDL changes in the hr schema from the queue at mult1.net to the queue at mult2.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
    schema_name               => 'hr', 
    streams_name              => 'mult1_to_mult2',    
    source_queue_name         => 'strmadmin.captured_mult1',
    destination_queue_name    => 'strmadmin.from_mult1@mult2.net',
    include_dml               => true,
    include_ddl               => true,
    source_database           => 'mult1.net',
    inclusion_rule            => true,
    queue_to_queue            => true);
END;
/

/*

Configure and schedule propagation of DML and DDL changes in the hr schema from the queue at mult1.net to the queue at mult3.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
    schema_name               => 'hr', 
    streams_name              => 'mult1_to_mult3',    
    source_queue_name         => 'strmadmin.captured_mult1',
    destination_queue_name    => 'strmadmin.from_mult1@mult3.net',
    include_dml               => true,
    include_ddl               => true,
    source_database           => 'mult1.net',
    inclusion_rule            => true,
    queue_to_queue            => true);
END;
/

/*

Step 9   Create the Capture Process at mult2.net.

Connect to mult2.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult2.net

/*

Create the capture process to capture changes to the entire hr schema at mult2.net. This step also prepares the hr schema at mult2.net for instantiation.

*/

BEGIN   
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name    => 'hr',
    streams_type   => 'capture',
    streams_name   => 'capture_hr', 
    queue_name     => 'strmadmin.captured_mult2',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/

/*

Step 10   Set the Instantiation SCN for mult2.net at the Other Databases

In this example, the hr schema already exists at all of the databases. The tables in the schema exist only at mult1.net until they are instantiated at mult2.net and mult3.net in Step 22. The instantiation is done using an import of the tables from mult1.net. These import operations set the schema instantiation SCNs for mult1.net at mult2.net and mult3.net automatically.

However, the instantiation SCNs for mult2.net and mult3.net are not set automatically at the other sites in the environment. This step sets the schema instantiation SCN for mult2.net manually at mult1.net and mult3.net. The current SCN at mult2.net is obtained by using the GET_SYSTEM_CHANGE_NUMBER function in the DBMS_FLASHBACK package at mult2.net. This SCN is used at mult1.net and mult3.net to run the SET_SCHEMA_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package.

The SET_SCHEMA_INSTANTIATION_SCN procedure controls which DDL LCRs for a schema are ignored by an apply process and which DDL LCRs for a schema are applied by an apply process. If the commit SCN of a DDL LCR for a database object in a schema from a source database is less than or equal to the instantiation SCN for that database object at some destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

Because you are running the SET_SCHEMA_INSTANTIATION_SCN procedure before the tables are instantiated at mult2.net, and because the local capture process is configured already, you do not need to run the SET_TABLE_INSTANTIATION_SCN for each table after the instantiation. In this example, an apply process at both mult1.net and mult3.net will apply transactions to the tables in the hr schema with SCNs that were committed after the SCN obtained in this step.

Note:

  • In a case where you are instantiating a schema that does not exist, you can set the global instantiation SCN instead of the schema instantiation SCN.

  • In a case where the tables are instantiated before you set the instantiation SCN, you must set the schema instantiation SCN and the instantiation SCN for each table in the schema.

*/

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT1.NET(
    source_schema_name    => 'hr',
    source_database_name  => 'mult2.net',
    instantiation_scn     => iscn);
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT3.NET(
    source_schema_name    => 'hr',
    source_database_name  => 'mult2.net',
    instantiation_scn     => iscn);
END;
/

/*

Step 11   Create One Apply Process at mult2.net for Each Source Database

Configure mult2.net to apply changes to the hr schema at mult1.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hr',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult1',
    queue_name      => 'strmadmin.from_mult1',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'mult1.net',
    inclusion_rule  => true);
END;
/

/*

Configure mult2.net to apply changes to the hr schema at mult3.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hr',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult3',
    queue_name      => 'strmadmin.from_mult3',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'mult3.net',
    inclusion_rule  => true);
END;
/

/*

Step 12   Specify hr as the Apply User for Each Apply Process at mult2.net

In this example, the hr user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr already has the necessary privileges to change these database objects, and it is convenient to make hr the apply user.

When the apply process was created in the previous step, the Streams administrator strmadmin was specified as the apply user by default, because strmadmin ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin as the apply user, but then you must grant strmadmin privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it might be more convenient to use the Streams administrator as the apply user.

See Also:

Oracle Streams Concepts and Administration for more information about configuring a Streams administrator
*/

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'apply_from_mult1',
    apply_user => 'hr');
END;
/

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'apply_from_mult3',
    apply_user => 'hr');
END;
/

/*

Step 13   Grant the hr User Execute Privilege on the Apply Process Rule Set

Because the hr user was specified as the apply user in the previous step, the hr user requires EXECUTE privilege on the positive rule set used by each apply process

*/

DECLARE
   rs_name  VARCHAR2(64);   -- Variable to hold rule set name
BEGIN
  SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME 
    INTO rs_name 
    FROM DBA_APPLY 
    WHERE APPLY_NAME='APPLY_FROM_MULT1';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

DECLARE
   rs_name  VARCHAR2(64);   -- Variable to hold rule set name
BEGIN
  SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME 
    INTO rs_name 
    FROM DBA_APPLY 
    WHERE APPLY_NAME='APPLY_FROM_MULT3';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

/*

Step 14   Configure Propagation at mult2.net

Configure and schedule propagation of DML and DDL changes in the hr schema from the queue at mult2.net to the queue at mult1.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
    schema_name             => 'hr',
    streams_name            => 'mult2_to_mult1',
    source_queue_name       => 'strmadmin.captured_mult2',
    destination_queue_name  => 'strmadmin.from_mult2@mult1.net',
    include_dml             => true,
    include_ddl             => true, 
    source_database         => 'mult2.net',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/

/*

Configure and schedule propagation of DML and DDL changes in the hr schema from the queue at mult2.net to the queue at mult3.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
    schema_name             => 'hr',
    streams_name            => 'mult2_to_mult3',
    source_queue_name       => 'strmadmin.captured_mult2',
    destination_queue_name  => 'strmadmin.from_mult2@mult3.net', 
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'mult2.net',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/

/*

Step 15   Create the Capture Process at mult3.net

Connect to mult3.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult3.net

/*

Create the capture process to capture changes to the entire hr schema at mult3.net. This step also prepares the hr schema at mult3.net for instantiation.

*/

BEGIN   
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name    => 'hr',
    streams_type   => 'capture',
    streams_name   => 'capture_hr', 
    queue_name     => 'strmadmin.captured_mult3',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/

/*

Step 16   Set the Instantiation SCN for mult3.net at the Other Databases

In this example, the hr schema already exists at all of the databases. The tables in the schema exist only at mult1.net until they are instantiated at mult2.net and mult3.net in Step 22. The instantiation is done using an import of the tables from mult1.net. These import operations set the schema instantiation SCNs for mult1.net at mult2.net and mult3.net automatically.

However, the instantiation SCNs for mult2.net and mult3.net are not set automatically at the other sites in the environment. This step sets the schema instantiation SCN for mult3.net manually at mult1.net and mult2.net. The current SCN at mult3.net is obtained by using the GET_SYSTEM_CHANGE_NUMBER function in the DBMS_FLASHBACK package at mult3.net. This SCN is used at mult1.net and mult2.net to run the SET_SCHEMA_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package.

The SET_SCHEMA_INSTANTIATION_SCN procedure controls which DDL LCRs for a schema are ignored by an apply process and which DDL LCRs for a schema are applied by an apply process. If the commit SCN of a DDL LCR for a database object in a schema from a source database is less than or equal to the instantiation SCN for that database object at some destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

Because you are running the SET_SCHEMA_INSTANTIATION_SCN procedure before the tables are instantiated at mult3.net, and because the local capture process is configured already, you do not need to run the SET_TABLE_INSTANTIATION_SCN for each table after the instantiation. In this example, an apply process at both mult1.net and mult2.net will apply transactions to the tables in the hr schema with SCNs that were committed after the SCN obtained in this step.

Note:

  • In a case where you are instantiating a schema that does not exist, you can set the global instantiation SCN instead of the schema instantiation SCN.

  • In a case where the tables are instantiated before you set the instantiation SCN, you must set the schema instantiation SCN and the instantiation SCN for each table in the schema.

*/

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT1.NET(
    source_schema_name    => 'hr',
    source_database_name  => 'mult3.net',
    instantiation_scn     => iscn);
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT2.NET(
    source_schema_name    => 'hr',
    source_database_name  => 'mult3.net',
    instantiation_scn     => iscn);
END;
/

/*

Step 17   Create One Apply Process at mult3.net for Each Source Database

Configure mult3.net to apply changes to the hr schema at mult1.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hr',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult1',
    queue_name      => 'strmadmin.from_mult1',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'mult1.net',
    inclusion_rule  => true);
END;
/

/*

Configure mult3.net to apply changes to the hr schema at mult2.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hr',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult2',
    queue_name      => 'strmadmin.from_mult2',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'mult2.net',
    inclusion_rule  => true);
END;
/

/*

Step 18   Specify hr as the Apply User for Each Apply Process at mult3.net

In this example, the hr user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr already has the necessary privileges to change these database objects, and it is convenient to make hr the apply user.

When the apply process was created in the previous step, the Streams administrator strmadmin was specified as the apply user by default, because strmadmin ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin as the apply user, but then you must grant strmadmin privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it might be more convenient to use the Streams administrator as the apply user.

See Also:

Oracle Streams Concepts and Administration for more information about configuring a Streams administrator
*/

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'apply_from_mult1',
    apply_user => 'hr');
END;
/

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'apply_from_mult2',
    apply_user => 'hr');
END;
/

/*

Step 19   Grant the hr User Execute Privilege on the Apply Process Rule Set

Because the hr user was specified as the apply user in the previous step, the hr user requires EXECUTE privilege on the positive rule set used by each apply process

*/

DECLARE
   rs_name  VARCHAR2(64);   -- Variable to hold rule set name
BEGIN
  SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME 
    INTO rs_name 
    FROM DBA_APPLY 
    WHERE APPLY_NAME='APPLY_FROM_MULT1';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

DECLARE
   rs_name  VARCHAR2(64);   -- Variable to hold rule set name
BEGIN
  SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME 
    INTO rs_name 
    FROM DBA_APPLY 
    WHERE APPLY_NAME='APPLY_FROM_MULT2';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

/*

Step 20   Configure Propagation at mult3.net

Configure and schedule propagation of DML and DDL changes in the hr schema from the queue at mult3.net to the queue at mult1.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
    schema_name             => 'hr',
    streams_name            => 'mult3_to_mult1',
    source_queue_name       => 'strmadmin.captured_mult3',
    destination_queue_name  => 'strmadmin.from_mult3@mult1.net', 
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'mult3.net',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/

/*

Configure and schedule propagation of DML and DDL changes in the hr schema from the queue at mult3.net to the queue at mult2.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
    schema_name             => 'hr',
    streams_name            => 'mult3_to_mult2',
    source_queue_name       => 'strmadmin.captured_mult3',
    destination_queue_name  => 'strmadmin.from_mult3@mult2.net', 
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'mult3.net',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/

/*

Step 21   Instantiate the hr Schema at mult2.net

This example performs a network Data Pump import of the following tables from mult1.net to mult2.net:

A network import means that Data Pump imports these tables from mult1.net without using an export dump file.

See Also:

Oracle Database Utilities for information about performing an import

Connect to mult2.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult2.net

/*

This example will do a table import using the DBMS_DATAPUMP package. For simplicity, exceptions from any of the API calls will not be trapped. However, Oracle recommends that you define exception handlers and call GET_STATUS to retrieve more detailed error information if a failure occurs. If you want to monitor the import, then query the DBA_DATAPUMP_JOBS data dictionary view at the import database.

*/

SET SERVEROUTPUT ON
DECLARE
  h1                 NUMBER;         -- Data Pump job handle
  mult2_instantscn   NUMBER;         -- Variable to hold current source SCN
  job_state          VARCHAR2(30);   -- To keep track of job state
  js                 ku$_JobStatus;  -- The job status from GET_STATUS
  sts                ku$_Status;     -- The status object returned by GET_STATUS
  job_not_exist    exception;
  pragma exception_init(job_not_exist, -31626);
BEGIN
-- Create a (user-named) Data Pump job to do a table-level import.
  h1 := DBMS_DATAPUMP.OPEN(
          operation   => 'IMPORT',
          job_mode    => 'TABLE',
          remote_link => 'MULT1.NET',
          job_name    => 'dp_mult2');
-- A metadata filter is used to specify the schema that owns the tables 
-- that will be imported.
  DBMS_DATAPUMP.METADATA_FILTER(
    handle    => h1,
    name      => 'SCHEMA_EXPR',
    value     => '=''HR''');
-- Get the current SCN of the source database, and set the FLASHBACK_SCN 
-- parameter to this value to ensure consistency between all of the 
-- objects in the schema.
  mult2_instantscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@mult1.net();  
  DBMS_DATAPUMP.SET_PARAMETER(
    handle => h1,
    name   => 'FLASHBACK_SCN',
    value  => mult2_instantscn); 
-- Start the job. 
  DBMS_DATAPUMP.START_JOB(h1);
-- The import job should be running. In the following loop, the job
-- is monitored until it completes.
  job_state := 'UNDEFINED';
  BEGIN
    WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
      sts:=DBMS_DATAPUMP.GET_STATUS(
             handle  => h1,
             mask    => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR +
                        DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS +
                        DBMS_DATAPUMP.KU$_STATUS_WIP,
             timeout => -1);
      js := sts.job_status;
      DBMS_LOCK.SLEEP(10);
      job_state := js.state;
    END LOOP;
  -- Gets an exception when job no longer exists
    EXCEPTION WHEN job_not_exist THEN
      DBMS_OUTPUT.PUT_LINE('Data Pump job has completed');
      DBMS_OUTPUT.PUT_LINE('Instantiation SCN: ' ||mult2_instantscn);
  END;
END;
/

/*

Step 22   Instantiate the hr Schema at mult3.net

This example performs a network Data Pump import of the following tables from mult1.net to mult3.net:

A network import means that Data Pump imports these tables from mult1.net without using an export dump file.

See Also:

Oracle Database Utilities for information about performing an import

Connect to mult3.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult3.net

/*

This example will do a table import using the DBMS_DATAPUMP package. For simplicity, exceptions from any of the API calls will not be trapped. However, Oracle recommends that you define exception handlers and call GET_STATUS to retrieve more detailed error information if a failure occurs. If you want to monitor the import, then query the DBA_DATAPUMP_JOBS data dictionary view at the import database.

*/

SET SERVEROUTPUT ON
DECLARE
  h1                 NUMBER;         -- Data Pump job handle
  mult3_instantscn   NUMBER;         -- Variable to hold current source SCN
  job_state          VARCHAR2(30);   -- To keep track of job state
  js                 ku$_JobStatus;  -- The job status from GET_STATUS
  sts                ku$_Status;     -- The status object returned by GET_STATUS
  job_not_exist    exception;
  pragma exception_init(job_not_exist, -31626);
BEGIN
-- Create a (user-named) Data Pump job to do a table-level import.
  h1 := DBMS_DATAPUMP.OPEN(
          operation   => 'IMPORT',
          job_mode    => 'TABLE',
          remote_link => 'MULT1.NET',
          job_name    => 'dp_mult3');
-- A metadata filter is used to specify the schema that owns the tables 
-- that will be imported.
  DBMS_DATAPUMP.METADATA_FILTER(
    handle    => h1,
    name      => 'SCHEMA_EXPR',
    value     => '=''HR''');
-- Get the current SCN of the source database, and set the FLASHBACK_SCN 
-- parameter to this value to ensure consistency between all of the 
-- objects in the schema.
  mult3_instantscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@mult1.net();
  DBMS_DATAPUMP.SET_PARAMETER(
    handle => h1,
    name   => 'FLASHBACK_SCN',
    value  => mult3_instantscn); 
-- Start the job. 
  DBMS_DATAPUMP.START_JOB(h1);
-- The import job should be running. In the following loop, the job
-- is monitored until it completes.
  job_state := 'UNDEFINED';
  BEGIN
    WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
      sts:=DBMS_DATAPUMP.GET_STATUS(
             handle  => h1,
             mask    => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR +
                        DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS +
                        DBMS_DATAPUMP.KU$_STATUS_WIP,
             timeout => -1);
      js := sts.job_status;
      DBMS_LOCK.SLEEP(10);
      job_state := js.state;
    END LOOP;
  -- Gets an exception when job no longer exists
    EXCEPTION WHEN job_not_exist THEN
      DBMS_OUTPUT.PUT_LINE('Data Pump job has completed');
      DBMS_OUTPUT.PUT_LINE('Instantiation SCN: ' ||mult3_instantscn);
  END;
END;
/

/*

Step 23   Configure Latest Time Conflict Resolution at mult2.net

Connect to mult2.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult2.net

/*

Specify an update conflict handler for each table in the hr schema. For each table, designate the time column as the resolution column for a MAXIMUM conflict handler. When an update conflict occurs, such an update conflict handler applies the transaction with the latest (or greater) time and discards the transaction with the earlier (or lesser) time.

*/
 
DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'country_name';
  cols(2) := 'region_id';
  cols(3) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.countries',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'department_name';
  cols(2) := 'manager_id';
  cols(3) := 'location_id';
  cols(4) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.departments',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1)  := 'first_name';
  cols(2)  := 'last_name';
  cols(3)  := 'email';
  cols(4)  := 'phone_number';
  cols(5)  := 'hire_date';
  cols(6)  := 'job_id';
  cols(7)  := 'salary';
  cols(8)  := 'commission_pct';
  cols(9)  := 'manager_id';
  cols(10) := 'department_id';
  cols(11) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.employees',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'job_title';
  cols(2) := 'min_salary';
  cols(3) := 'max_salary';
  cols(4) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.jobs',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'employee_id';
  cols(2) := 'start_date';
  cols(3) := 'end_date';
  cols(4) := 'job_id';
  cols(5) := 'department_id';
  cols(6) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.job_history',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'street_address';
  cols(2) := 'postal_code';
  cols(3) := 'city';
  cols(4) := 'state_province';
  cols(5) := 'country_id';
  cols(6) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.locations',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'region_name';
  cols(2) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.regions',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

/*

Step 24   Start the Apply Processes at mult2.net

Set the disable_on_error parameter to n for both apply processes so that they will not be not disabled if they encounter an error, and start both of the apply processes at mult2.net.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_from_mult1', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_from_mult1');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_from_mult3', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_from_mult3');
END;
/

/*

Step 25   Configure Latest Time Conflict Resolution at mult3.net

Connect to mult3.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult3.net

/*

Specify an update conflict handler for each table in the hr schema. For each table, designate the time column as the resolution column for a MAXIMUM conflict handler. When an update conflict occurs, such an update conflict handler applies the transaction with the latest (or greater) time and discards the transaction with the earlier (or lesser) time.

*/
 
DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'country_name';
  cols(2) := 'region_id';
  cols(3) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.countries',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'department_name';
  cols(2) := 'manager_id';
  cols(3) := 'location_id';
  cols(4) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.departments',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1)  := 'first_name';
  cols(2)  := 'last_name';
  cols(3)  := 'email';
  cols(4)  := 'phone_number';
  cols(5)  := 'hire_date';
  cols(6)  := 'job_id';
  cols(7)  := 'salary';
  cols(8)  := 'commission_pct';
  cols(9)  := 'manager_id';
  cols(10) := 'department_id';
  cols(11) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.employees',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'job_title';
  cols(2) := 'min_salary';
  cols(3) := 'max_salary';
  cols(4) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.jobs',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'employee_id';
  cols(2) := 'start_date';
  cols(3) := 'end_date';
  cols(4) := 'job_id';
  cols(5) := 'department_id';
  cols(6) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.job_history',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'street_address';
  cols(2) := 'postal_code';
  cols(3) := 'city';
  cols(4) := 'state_province';
  cols(5) := 'country_id';
  cols(6) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.locations',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'region_name';
  cols(2) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.regions',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

/*

Step 26   Start the Apply Processes at mult3.net

Set the disable_on_error parameter to n for both apply processes so that they will not be disabled if they encounter an error, and start both of the apply processes at mult3.net.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_from_mult1', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_from_mult1');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_from_mult2', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_from_mult2');
END;
/

/*

Step 27   Start the Apply Processes at mult1.net

Connect to mult1.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult1.net

/*

Set the disable_on_error parameter to n for both apply processes so that they will not be disabled if they encounter an error, and start both of the apply processes at mult1.net.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_from_mult2', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_from_mult2');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_from_mult3', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_from_mult3');
END;
/

/*

Step 28   Start the Capture Process at mult1.net

Start the capture process at mult1.net.

*/

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_hr');
END;
/

/*

Step 29   Start the Capture Process at mult2.net

Connect to mult2.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult2.net

/*

Start the capture process at mult2.net.

*/

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_hr');
END;
/

/*

Step 30   Start the Capture Process at mult3.net

Connect to mult3.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@mult3.net

/*

Start the capture process at mult3.net.

*/

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_hr');
END;
/

SET ECHO OFF

/*

Step 31   Check the Spool Results

Check the streams_mult.out spool file to ensure that all actions finished successfully after this script is completed.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Make DML and DDL Changes to Tables in the hr Schema

You can make DML and DDL changes to the tables in the hr schema at any of the databases in the environment. These changes will be replicated to the other databases in the environment, and you can run queries to view the replicated data.

For example, complete the following steps to make DML changes to the hr.employees table at mult1.net and mult2.net. To see the update conflict handler you configured earlier resolve an update conflict, you can make a change to the same row in these two databases and commit the changes at nearly the same time. You can query the changed row at each database in the environment to confirm that the changes were captured, propagated, and applied correctly.

You can also make a DDL change to the hr.jobs table at mult3.net and then confirm that the change was captured at mult3.net, propagated to the other databases in the environment, and applied at these databases.


Step 1   Make a DML Change to hr.employees at mult.net and mult2.net

Make the following changes. To make the update conflict handler at each database resolve a conflict, try to commit them at nearly the same time, but commit the change at mult2.net after you commit the change at mult1.net.

CONNECT hr/hr@mult1.net

UPDATE hr.employees SET salary=9000 WHERE employee_id=206;
COMMIT;

CONNECT hr/hr@mult2.net

UPDATE hr.employees SET salary=10000 WHERE employee_id=206;
COMMIT;

Step 2   Alter the hr.jobs Table at mult3.net

Alter the hr.jobs table by renaming the job_title column to job_name:

CONNECT hr/hr@mult3.net

ALTER TABLE hr.jobs RENAME COLUMN job_title TO job_name;

Step 3   Query the hr.employees Table at Each Database

After some time passes to allow for capture, propagation, and apply of the changes performed in Step 1, run the following query to confirm that the UPDATE changes have been applied at each database.

CONNECT hr/hr@mult1.net

SELECT salary FROM hr.employees WHERE employee_id=206;

CONNECT hr/hr@mult2.net

SELECT salary FROM hr.employees WHERE employee_id=206;

CONNECT hr/hr@mult3.net

SELECT salary FROM hr.employees WHERE employee_id=206;

All of the queries should show 10000 for the value of the salary.

Step 4   Describe the hr.jobs Table at Each Database

After some time passes to allow for capture, propagation, and apply of the change performed in Step 2, describe the hr.jobs table at each database to confirm that the ALTER TABLE change was propagated and applied correctly.

CONNECT hr/hr@mult1.net

DESC hr.jobs

CONNECT hr/hr@mult2.net

DESC hr.jobs

CONNECT hr/hr@mult3.net

DESC hr.jobs

Each database should show job_name as the second column in the table.