1. Extended Examples
  2. N-Way Replication Example

3 N-Way Replication Example

This chapter illustrates an example of an n-way replication environment that can be constructed using Oracle Streams.

This chapter contains these topics:

3.1 Overview of the N-Way Replication Example

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

This type of environment is called an n-way replication environment. An n-way replication environment is a type of multiple-source replication environment because more than one source database captures and replicates changes.

Figure 3-1 provides an overview of the environment.

Figure 3-1 Sample N-Way Replication Environment

Description of Figure 3-1 follows
Description of "Figure 3-1 Sample N-Way Replication Environment"

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

In this example, Oracle 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 hrmult 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 hrmult 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:

3.2 Prerequisites

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

  • Set the following initialization parameters to the values indicated at each database in the Oracle Streams environment:

    • GLOBAL_NAMES: This parameter must be set to TRUE. Ensure that the global names of the databases are mult1.example.com, mult2.example.com, and mult3.example.com.

    • COMPATIBLE: This parameter must be set to 10.2.0 or higher.

    • Ensure that the PROCESSES and SESSIONS initialization parameters are set high enough for all of the Oracle Streams clients used in this example. This example configures one capture process, two propagations, and two apply processes at each database.

    • STREAMS_POOL_SIZE: Optionally set this parameter to an appropriate value for each database in the environment. This parameter specifies the size of the Oracle Streams pool. The Oracle Streams pool stores messages in a buffered queue and is used for internal communications during parallel capture and apply. When the MEMORY_TARGET, MEMORY_MAX_TARGET, or SGA_TARGET initialization parameter is set to a nonzero value, the Oracle Streams pool size is managed automatically.

      Note:

      You might need to modify other initialization parameter settings for this example to run properly.

    See Also:

    Oracle Streams Replication Administrator's Guide for information about other initialization parameters that are important in an Oracle Streams environment

  • Any database producing changes that will be captured must be running in ARCHIVELOG mode. In this example, all databases are capturing changes, and so all databases must be running in ARCHIVELOG mode.

    See Also:

    Oracle Database Administrator's Guide for information about running a database in ARCHIVELOG mode

  • Configure your network and Oracle Net so that all three databases can communicate with each other.

    See Also:

    Oracle Database Net Services Administrator's Guide

  • Create an Oracle Streams administrator at each database in the replication environment. In this example, the databases are mult1.example.com, mult2.example.com, and mult3.example.com. This example assumes that the user name of the Oracle Streams administrator is strmadmin.

    See Also:

    Oracle Streams Replication Administrator's Guide for instructions about creating an Oracle Streams administrator

3.3 Create the hrmult Schema at the mult1.example.com Database

For the purposes of this example, create a new schema named hrmult at the mult1.example.com database. The n-way environment will replicate this new schema.

Complete the following steps to use Data Pump export/import to create an hrmult schema that is a copy of the hr schema:

  1. In SQL*Plus, connect to the mult1.example.com database as an administrative user.

    See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

  2. Create a directory object to hold the export dump file and export log file. The directory object can point to any accessible directory on the computer system. For example, the following statement creates a directory object named dp_hrmult_dir that points to the /usr/tmp directory:
    CREATE DIRECTORY dp_hrmult_dir AS '/usr/tmp';

    Substitute an appropriate directory on your computer system.

  3. Determine the current system change number (SCN) of the source database:
    SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

    The SCN value returned by this query is specified for the FLASHBACK_SCN Data Pump export parameter in Step 5. Because the hr schema includes foreign key constraints between tables, the FLASHBACK_SCN export parameter, or a similar export parameter, must be specified during export.

  4. Exit SQL*Plus.
  5. On a command line at the mult1.example.com database site, use Data Pump to export the hr schema at the mult1.example.com database. Ensure that you specify the SCN value returned in Step 3 for the FLASHBACK_SCN parameter:
    expdp system SCHEMAS=hr DIRECTORY=dp_hrmult_dir
DUMPFILE=hrmult_schema.dmp FLASHBACK_SCN=flashback_scn_value
  6. On a command line at the mult1.example.com database site, use Data Pump to import the import dump file hrmult_schema.dmp:
    impdp system SCHEMAS=hr DIRECTORY=dp_hrmult_dir
DUMPFILE=hrmult_schema.dmp REMAP_SCHEMA=hr:hrmult
  7. In SQL*Plus, connect to the mult1.example.com database as an administrative user.
  8. Assign a password to the new hrmult user at the mult1.example.com database using the ALTER USER statement.

    Remember the password that you assign to the hrmult user so that you can log in as the user in the future.

3.4 Create Queues and Database Links

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

Complete the following steps to create the queues and database links at all of the databases.

  1. "Show Output and Spool Results"
  2. "Create the ANYDATA Queue at mult1.example.com"
  3. "Create the Database Links at mult1.example.com"
  4. "Prepare the Tables at mult1.example.com for Latest Time Conflict Resolution"
  5. "Create the ANYDATA Queue at mult2.example.com"
  6. "Create the Database Links at mult2.example.com"
  7. "Create the ANYDATA Queue at mult3.example.com"
  8. "Create the Database Links at mult3.example.com"
  9. "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 ******************************
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

/*
Create the ANYDATA Queue at mult1.example.com

Connect as the Oracle Streams administrator at mult1.example.com. 

*/

CONNECT strmadmin@mult1.example.com

/*

Run the SET_UP_QUEUE procedure to create the following queues:

  • The captured_mult1 queue to hold changes captured at the mult1.example.com database and propagated to other databases.

  • The from_mult2 queue to hold changes captured at the mult2.example.com database and propagated to the mult1.example.com database to be applied.

  • The from_mult3 queue to hold changes captured at the mult3.example.com database and propagated to the mult1.example.com database to be applied.

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

  • Creates a queue table that is owned by the Oracle Streams administrator (strmadmin) and that uses the default storage of this user.

  • Creates an ANYDATA queue that is owned by the Oracle Streams administrator (strmadmin).

  • Starts the 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;
/

/*
Create the Database Links at mult1.example.com

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

*/

ACCEPT password PROMPT 'Enter password for user: ' HIDE

CREATE DATABASE LINK mult2.example.com CONNECT TO strmadmin 
   IDENTIFIED BY &password USING 'mult2.example.com';

CREATE DATABASE LINK mult3.example.com CONNECT TO strmadmin 
   IDENTIFIED BY &password USING 'mult3.example.com';

/*
Prepare the Tables at mult1.example.com for Latest Time Conflict Resolution

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

Connect to mult1.example.com as the hrmult user. 

*/
 
CONNECT hrmult@mult1.example.com

/*

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

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

/*

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

*/

CREATE OR REPLACE TRIGGER hrmult.insert_time_countries
BEFORE
  INSERT OR UPDATE ON hrmult.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 hrmult.insert_time_departments
BEFORE
  INSERT OR UPDATE ON hrmult.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 hrmult.insert_time_employees
BEFORE
  INSERT OR UPDATE ON hrmult.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 hrmult.insert_time_job_history
BEFORE
  INSERT OR UPDATE ON hrmult.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 hrmult.insert_time_jobs
BEFORE
  INSERT OR UPDATE ON hrmult.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 hrmult.insert_time_locations
BEFORE
  INSERT OR UPDATE ON hrmult.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 hrmult.insert_time_regions
BEFORE
  INSERT OR UPDATE ON hrmult.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;
/

/*
Create the ANYDATA Queue at mult2.example.com

Connect as the Oracle Streams administrator at mult2.example.com. 

*/

CONNECT strmadmin@mult2.example.com

/*

Run the SET_UP_QUEUE procedure to create the following queues:

  • The captured_mult2 queue to hold changes captured at the mult2.example.com database and propagated to other databases.

  • The from_mult1 queue to hold changes captured at the mult1.example.com database and propagated to the mult2.example.com database to be applied.

  • The from_mult3 queue to hold changes captured at the mult3.example.com database and propagated to the mult2.example.com database to be applied.

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

  • Creates a queue table that is owned by the Oracle Streams administrator (strmadmin) and that uses the default storage of this user.

  • Creates an ANYDATA queue that is owned by the Oracle Streams administrator (strmadmin).

  • Starts the 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;
/

/*
Create the Database Links at mult2.example.com

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

*/

CREATE DATABASE LINK mult1.example.com CONNECT TO strmadmin 
   IDENTIFIED BY &password USING 'mult1.example.com';

CREATE DATABASE LINK mult3.example.com CONNECT TO strmadmin 
   IDENTIFIED BY &password USING 'mult3.example.com';

/*
Create the ANYDATA Queue at mult3.example.com

Connect as the Oracle Streams administrator at mult3.example.com. 

*/

CONNECT strmadmin@mult3.example.com

/*

Run the SET_UP_QUEUE procedure to create the following queues:

  • The captured_mult3 queue to hold changes captured at the mult3.example.com database and propagated to other databases.

  • The from_mult1 queue to hold changes captured at the mult1.example.com database and propagated to the mult3.example.com database to be applied.

  • The from_mult2 queue to hold changes captured at the mult2.example.com database and propagated to the mult3.example.com database to be applied.

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

  • Creates a queue table that is owned by the Oracle Streams administrator (strmadmin) and that uses the default storage of this user.

  • Creates an ANYDATA queue that is owned by the Oracle Streams administrator (strmadmin).

  • Starts the 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;
/

/*
Create the Database Links at mult3.example.com

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

*/

CREATE DATABASE LINK mult1.example.com CONNECT TO strmadmin 
   IDENTIFIED BY &password USING 'mult1.example.com';

CREATE DATABASE LINK mult2.example.com CONNECT TO strmadmin 
   IDENTIFIED BY &password USING 'mult2.example.com';

/*
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 ******************************/

3.5 Example Script for Configuring N-Way Replication

Complete the following steps to configure an Oracle Streams n-way replication environment.

  1. "Show Output and Spool Results"
  2. "Specify Supplemental Logging at mult1.example.com"
  3. "Create the Capture Process at mult1.example.com"
  4. "Create One Apply Process at mult1.example.com for Each Source Database"
  5. "Configure Latest Time Conflict Resolution at mult1.example.com"
  6. "Configure Propagation at mult1.example.com"
  7. "Create the Capture Process at mult2.example.com."
  8. "Set the Instantiation SCN for mult2.example.com at the Other Databases"
  9. "Create One Apply Process at mult2.example.com for Each Source Database"
  10. "Configure Propagation at mult2.example.com"
  11. "Create the Capture Process at mult3.example.com"
  12. "Set the Instantiation SCN for mult3.example.com at the Other Databases"
  13. "Create One Apply Process at mult3.example.com for Each Source Database"
  14. "Configure Propagation at mult3.example.com"
  15. "Instantiate the hrmult Schema at mult2.example.com"
  16. "Instantiate the hrmult Schema at mult3.example.com"
  17. "Configure Latest Time Conflict Resolution at mult2.example.com"
  18. "Start the Apply Processes at mult2.example.com"
  19. "Configure Latest Time Conflict Resolution at mult3.example.com"
  20. "Start the Apply Processes at mult3.example.com"
  21. "Start the Apply Processes at mult1.example.com"
  22. "Start the Capture Process at mult1.example.com"
  23. "Start the Capture Process at mult2.example.com"
  24. "Start the Capture Process at mult3.example.com"
  25. "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 ******************************
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

/*
Specify Supplemental Logging at mult1.example.com

Connect to mult1.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult1.example.com

/*

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.example.com"". 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.example.com and mult3.example.com in this example. When you use Data Pump to instantiate the tables in the hrmult schema at these databases later in this example, the supplemental logging specifications at mult1.example.com are retained at mult2.example.com and mult3.example.com.

See Also:

Oracle Streams Replication Administrator's Guide

*/

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

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

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

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

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

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

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

/*
Create the Capture Process at mult1.example.com

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

*/

BEGIN   
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name    => 'hrmult',
    streams_type   => 'capture',
    streams_name   => 'capture_hrmult', 
    queue_name     => 'strmadmin.captured_mult1',
    include_dml    => TRUE,
    include_ddl    => TRUE,
    inclusion_rule => TRUE);
END;
/

/*
Create One Apply Process at mult1.example.com for Each Source Database

Configure mult1.example.com to apply changes to the hrmult schema at mult2.example.com. 

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hrmult',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult2',
    queue_name      => 'strmadmin.from_mult2',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'mult2.example.com',
    inclusion_rule  => TRUE);
END;
/

/*

Configure mult1.example.com to apply changes to the hrmult schema at mult3.example.com. 

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hrmult',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult3',
    queue_name      => 'strmadmin.from_mult3',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'mult3.example.com',
    inclusion_rule  => TRUE);
END;
/

/*
Configure Latest Time Conflict Resolution at mult1.example.com

Specify an update conflict handler for each table in the hrmult 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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.regions',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

/*
Configure Propagation at mult1.example.com

Configure and schedule propagation of DML and DDL changes in the hrmult schema from the queue at mult1.example.com to the queue at mult2.example.com. 

*/

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

/*

Configure and schedule propagation of DML and DDL changes in the hrmult schema from the queue at mult1.example.com to the queue at mult3.example.com. 

*/

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

/*
Create the Capture Process at mult2.example.com.

Connect to mult2.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult2.example.com

/*

Create the capture process to capture changes to the entire hrmult schema at mult2.example.com. This step also prepares the hrmult schema at mult2.example.com for instantiation. 

*/

BEGIN   
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name    => 'hrmult',
    streams_type   => 'capture',
    streams_name   => 'capture_hrmult', 
    queue_name     => 'strmadmin.captured_mult2',
    include_dml    => TRUE,
    include_ddl    => TRUE,
    inclusion_rule => TRUE);
END;
/

/*
Set the Instantiation SCN for mult2.example.com at the Other Databases

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

However, the instantiation SCNs for mult2.example.com and mult3.example.com are not set automatically at the other sites in the environment. This step sets the schema instantiation SCN for mult2.example.com manually at mult1.example.com and mult3.example.com. The current SCN at mult2.example.com is obtained by using the GET_SYSTEM_CHANGE_NUMBER function in the DBMS_FLASHBACK package at mult2.example.com. This SCN is used at mult1.example.com and mult3.example.com 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.example.com, 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.example.com and mult3.example.com will apply transactions to the tables in the hrmult 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.EXAMPLE.COM(
    source_schema_name    => 'hrmult',
    source_database_name  => 'mult2.example.com',
    instantiation_scn     => iscn);
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT3.EXAMPLE.COM(
    source_schema_name    => 'hrmult',
    source_database_name  => 'mult2.example.com',
    instantiation_scn     => iscn);
END;
/

/*

Create One Apply Process at mult2.example.com for Each Source Database

Configure mult2.example.com to apply changes to the hrmult schema at mult1.example.com. 

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hrmult',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult1',
    queue_name      => 'strmadmin.from_mult1',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'mult1.example.com',
    inclusion_rule  => TRUE);
END;
/

/*

Configure mult2.example.com to apply changes to the hrmult schema at mult3.example.com. 

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hrmult',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult3',
    queue_name      => 'strmadmin.from_mult3',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'mult3.example.com',
    inclusion_rule  => TRUE);
END;
/

/*
Configure Propagation at mult2.example.com

Configure and schedule propagation of DML and DDL changes in the hrmult schema from the queue at mult2.example.com to the queue at mult1.example.com. 

*/

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

/*

Configure and schedule propagation of DML and DDL changes in the hrmult schema from the queue at mult2.example.com to the queue at mult3.example.com. 

*/

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

/*
Create the Capture Process at mult3.example.com

Connect to mult3.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult3.example.com

/*

Create the capture process to capture changes to the entire hrmult schema at mult3.example.com. This step also prepares the hrmult schema at mult3.example.com for instantiation. 

*/

BEGIN   
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name    => 'hrmult',
    streams_type   => 'capture',
    streams_name   => 'capture_hrmult', 
    queue_name     => 'strmadmin.captured_mult3',
    include_dml    => TRUE,
    include_ddl    => TRUE,
    inclusion_rule => TRUE);
END;
/

/*
Set the Instantiation SCN for mult3.example.com at the Other Databases

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

However, the instantiation SCNs for mult2.example.com and mult3.example.com are not set automatically at the other sites in the environment. This step sets the schema instantiation SCN for mult3.example.com manually at mult1.example.com and mult2.example.com. The current SCN at mult3.example.com is obtained by using the GET_SYSTEM_CHANGE_NUMBER function in the DBMS_FLASHBACK package at mult3.example.com. This SCN is used at mult1.example.com and mult2.example.com 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.example.com, 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.example.com and mult2.example.com will apply transactions to the tables in the hrmult 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.EXAMPLE.COM(
    source_schema_name    => 'hrmult',
    source_database_name  => 'mult3.example.com',
    instantiation_scn     => iscn);
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT2.EXAMPLE.COM(
    source_schema_name    => 'hrmult',
    source_database_name  => 'mult3.example.com',
    instantiation_scn     => iscn);
END;
/

/*
Create One Apply Process at mult3.example.com for Each Source Database

Configure mult3.example.com to apply changes to the hrmult schema at mult1.example.com. 

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hrmult',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult1',
    queue_name      => 'strmadmin.from_mult1',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'mult1.example.com',
    inclusion_rule  => TRUE);
END;
/

/*

Configure mult3.example.com to apply changes to the hrmult schema at mult2.example.com. 

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name     => 'hrmult',   
    streams_type    => 'apply',
    streams_name    => 'apply_from_mult2',
    queue_name      => 'strmadmin.from_mult2',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'mult2.example.com',
    inclusion_rule  => TRUE);
END;
/

/*
Configure Propagation at mult3.example.com

Configure and schedule propagation of DML and DDL changes in the hrmult schema from the queue at mult3.example.com to the queue at mult1.example.com. 

*/

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

/*

Configure and schedule propagation of DML and DDL changes in the hrmult schema from the queue at mult3.example.com to the queue at mult2.example.com. 

*/

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

/*
Instantiate the hrmult Schema at mult2.example.com

This example performs a network Data Pump import of the hrmult schema from mult1.example.com to mult2.example.com. A network import means that Data Pump imports the database objects in the schema from mult1.example.com without using an export dump file.

See Also:

Oracle Database Utilities for information about performing an import

Connect to mult2.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult2.example.com

/*

This example will do a schema-level 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 schema-level import.
  h1 := DBMS_DATAPUMP.OPEN(
          operation   => 'IMPORT',
          job_mode    => 'SCHEMA',
          remote_link => 'MULT1.EXAMPLE.COM',
          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     => '=''HRMULT''');
-- 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.example.com();  
  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;
/

/*
Instantiate the hrmult Schema at mult3.example.com

This example performs a network Data Pump import of the hrmult schema from mult1.example.com to mult3.example.com. A network import means that Data Pump imports the database objects in the schema from mult1.example.com without using an export dump file.

See Also:

Oracle Database Utilities for information about performing an import

Connect to mult3.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult3.example.com

/*

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 schema-level import.
  h1 := DBMS_DATAPUMP.OPEN(
          operation   => 'IMPORT',
          job_mode    => 'SCHEMA',
          remote_link => 'MULT1.EXAMPLE.COM',
          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     => '=''HRMULT''');
-- 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.example.com();
  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;
/

/*
Configure Latest Time Conflict Resolution at mult2.example.com

Connect to mult2.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult2.example.com

/*

Specify an update conflict handler for each table in the hrmult 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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.regions',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

/*
Start the Apply Processes at mult2.example.com

Start both of the apply processes at mult2.example.com. 

*/

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

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

/*
Configure Latest Time Conflict Resolution at mult3.example.com

Connect to mult3.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult3.example.com

/*

Specify an update conflict handler for each table in the hrmult 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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.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           =>  'hrmult.regions',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'time',
    column_list           =>  cols);
END;
/

/*
Start the Apply Processes at mult3.example.com

Start both of the apply processes at mult3.example.com. 

*/

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

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

/*
Start the Apply Processes at mult1.example.com

Connect to mult1.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult1.example.com

/*

Start both of the apply processes at mult1.example.com. 

*/

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

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

/*
Start the Capture Process at mult1.example.com

Start the capture process at mult1.example.com. 

*/

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

/*
Start the Capture Process at mult2.example.com

Connect to mult2.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult2.example.com

/*

Start the capture process at mult2.example.com. 

*/

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

/*
Start the Capture Process at mult3.example.com

Connect to mult3.example.com as the strmadmin user. 

*/
 
CONNECT strmadmin@mult3.example.com

/*

Start the capture process at mult3.example.com. 

*/

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

SET ECHO OFF

/*
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 ******************************/

See Also:

Oracle Streams Replication Administrator's Guide for general instructions that explain how to add database objects or databases to the replication environment

3.6 Make DML and DDL Changes to Tables in the hrmult Schema

You can make DML and DDL changes to the tables in the hrmult 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 hrmult.employees table at mult1.example.com and mult2.example.com. 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 hrmult.jobs table at mult3.example.com and then confirm that the change was captured at mult3.example.com, propagated to the other databases in the environment, and applied at these databases.

Make a DML Change to hrmult.employees at mult.example.com and mult2.example.com

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

CONNECT hrmult@mult1.example.com
Enter password: password

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

CONNECT hrmult@mult2.example.com
Enter password: password

UPDATE hrmult.employees SET salary=10000 WHERE employee_id=206;
COMMIT;
Alter the hrmult.jobs Table at mult3.example.com

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

CONNECT hrmult@mult3.example.com
Enter password: password

ALTER TABLE hrmult.jobs RENAME COLUMN job_title TO job_name;
Query the hrmult.employees Table at Each Database

After some time passes to allow for capture, propagation, and apply of the changes performed in Step "Make a DML Change to hrmult.employees at mult.example.com and mult2.example.com", run the following query to confirm that the UPDATE changes have been applied at each database. 

CONNECT hrmult@mult1.example.com
Enter password: password

SELECT salary FROM hrmult.employees WHERE employee_id=206;

CONNECT hrmult@mult2.example.com
Enter password: password

SELECT salary FROM hrmult.employees WHERE employee_id=206;

CONNECT hrmult@mult3.example.com
Enter password: password

SELECT salary FROM hrmult.employees WHERE employee_id=206;

All of the queries should show 10000 for the value of the salary. The update conflict handler at each database has resolved the conflict by using the latest change to the row. In this case, the latest change to the row was made at the mult2.example.com database in Step "Make a DML Change to hrmult.employees at mult.example.com and mult2.example.com".

Describe the hrmult.jobs Table at Each Database

After some time passes to allow for capture, propagation, and apply of the change performed in Step "Alter the hrmult.jobs Table at mult3.example.com", describe the hrmult.jobs table at each database to confirm that the ALTER TABLE change was propagated and applied correctly. 

CONNECT hrmult@mult1.example.com
Enter password: password

DESC hrmult.jobs

CONNECT hrmult@mult2.example.com
Enter password: password

DESC hrmult.jobs

CONNECT hrmult@mult3.example.com
Enter password: password

DESC hrmult.jobs

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