2 Single-Source Heterogeneous Replication Example

Simple Configuration for Sharing Data from a Single Database

Complete the following steps to specify the capture, propagation, and apply definitions using primarily the DBMS_STEAMS_ADM package.

1. Show Output and Spool Results

2. Configure Propagation at dbs1.example.com

3. Configure the Capture Process at dbs1.example.com

4. Set the Instantiation SCN for the Existing Tables at Other Databases

5. Instantiate the dbs1.example.com Tables at dbs3.example.com

6. Configure the Apply Process at dbs3.example.com

7. Specify hr as the Apply User for the Apply Process at dbs3.example.com

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

9. Start the Apply Process at dbs3.example.com

10. Configure Propagation at dbs2.example.com

11. Create the Custom Rule-Based Transformation for Row LCRs at dbs2.example.com

12. Configure the Apply Process for Local Apply at dbs2.example.com

13. Specify hr as the Apply User for the Apply Process at dbs2.example.com

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

15. Start the Apply Process at dbs2.example.com for Local Apply

16. Configure the Apply Process at dbs2.example.com for Apply at dbs4.example.com

17. Start the Apply Process at dbs2.example.com for Apply at dbs4.example.com

18. Start the Capture Process at dbs1.example.com

19. Check the Spool Results

/************************* 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_share_schema1.out

/*

Step 2   Configure Propagation at dbs1.example.com

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

*/
 
CONNECT strmadmin@dbs1.example.com

/*

Configure and schedule propagation of DML and DDL changes in the hr schema from the queue at dbs1.example.com to the queue at dbs2.example.com.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
    schema_name             => 'hr', 
    streams_name            => 'dbs1_to_dbs2', 
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@dbs2.example.com',
    include_dml             => TRUE,
    include_ddl             => TRUE,
    source_database         => 'dbs1.example.com',
    inclusion_rule          => TRUE,
    queue_to_queue          => TRUE);
END;
/

/*

Step 3   Configure the Capture Process at dbs1.example.com

Configure the capture process to capture changes to the entire hr schema at dbs1.example.com. This step specifies that changes to the tables in the specified schema are captured by the capture process and enqueued into the specified queue.

This step also prepares the hr schema for instantiation and enables supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the tables in this schema. Supplemental logging places additional information in the redo log for changes made to tables. The apply process needs this extra information to perform certain operations, such as unique row identification and conflict resolution. Because dbs1.example.com is the only database where changes are captured in this environment, it is the only database where you must specify supplemental logging for the tables in the hr schema.

See Also:

Oracle Streams Replication Administrator's Guide

*/

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

/*

Step 4   Set the Instantiation SCN for the Existing Tables at Other Databases

In this example, the hr.jobs table already exists at dbs2.example.com and dbs4.example.com. At dbs2.example.com, this table is named assignments, but it has the same shape and data as the jobs table at dbs1.example.com. Also, in this example, dbs4.example.com is a Sybase database. All of the other tables in the Oracle Streams environment are instantiated at the other destination databases using Data Pump import.

Because the hr.jobs table already exists at dbs2.example.com and dbs4.example.com, this example uses the GET_SYSTEM_CHANGE_NUMBER function in the DBMS_FLASHBACK package at dbs1.example.com to obtain the current SCN for the database. This SCN is used at dbs2.example.com to run the SET_TABLE_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package. Running this procedure twice sets the instantiation SCN for the hr.jobs table at dbs2.example.com and dbs4.example.com.

The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are ignored by an apply process and which LCRs for a table are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR.

In this example, both of the apply processes at dbs2.example.com will apply transactions to the hr.jobs table with SCNs that were committed after SCN obtained in this step.

Note:

This example assumes that the contents of the hr.jobs table at dbs1.example.com, dbs2.example.com (as hr.assignments), and dbs4.example.com are consistent when you complete this step. You might want to lock the table at each database while you complete this step to ensure consistency.

*/

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.EXAMPLE.COM(
    source_object_name    => 'hr.jobs',
    source_database_name  => 'dbs1.example.com',
    instantiation_scn     => iscn);
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.EXAMPLE.COM(
    source_object_name    => 'hr.jobs',
    source_database_name  => 'dbs1.example.com',
    instantiation_scn     => iscn,
    apply_database_link   => 'dbs4.example.com');
END;
/

/*

Step 5   Instantiate the dbs1.example.com Tables at dbs3.example.com

This example performs a network Data Pump import of the following tables:

• hr.countries

• hr.locations

• hr.regions

A network import means that Data Pump imports these tables from dbs1.example.com without using an export dump file.

See Also:

Oracle Database Utilities for information about performing an import

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

*/
 
CONNECT strmadmin@dbs3.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
  sscn      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 => 'DBS1.EXAMPLE.COM',
          job_name    => 'dp_sing1');
-- 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''');
-- A metadata filter is used to specify the tables that will be imported.
  DBMS_DATAPUMP.METADATA_FILTER(
    handle    => h1,
    name      => 'NAME_EXPR',
    value     => 'IN(''COUNTRIES'', ''REGIONS'', ''LOCATIONS'')');
-- 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 included in the import.
  sscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@dbs1.example.com(); 
  DBMS_DATAPUMP.SET_PARAMETER(
    handle => h1,
    name   => 'FLASHBACK_SCN',
    value  => sscn); 
-- 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: ' ||sscn);
  END;
END;
/

/*

Step 6   Configure the Apply Process at dbs3.example.com

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

*/
 
CONNECT strmadmin@dbs3.example.com

/*

Configure dbs3.example.com to apply changes to the countries table, locations table, and regions table.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.countries',
    streams_type    => 'apply', 
    streams_name    => 'apply',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'dbs1.example.com',
    inclusion_rule  => TRUE);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.locations',
    streams_type    => 'apply', 
    streams_name    => 'apply',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'dbs1.example.com',
    inclusion_rule  => TRUE);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.regions',
    streams_type    => 'apply', 
    streams_name    => 'apply',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => TRUE,
    include_ddl     => TRUE,
    source_database => 'dbs1.example.com',
    inclusion_rule  => TRUE);
END;
/

/*

Step 7   Specify hr as the Apply User for the Apply Process at dbs3.example.com

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 Oracle 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 Oracle Streams administrator as the apply user.

See Also:

Oracle Streams Replication Administrator's Guide for more information about configuring an Oracle Streams administrator

*/

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

/*

Step 8   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 the 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';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

/*

Step 9   Start the Apply Process at dbs3.example.com

Set the disable_on_error parameter to n so that the apply process will not be disabled if it encounters an error, and start the apply process at dbs3.example.com.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply', 
    parameter   => 'disable_on_error', 
    value       => 'N');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply');
END;
/

/*

Step 10   Configure Propagation at dbs2.example.com

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

*/
 
CONNECT strmadmin@dbs2.example.com

/*

Configure and schedule propagation from the queue at dbs2.example.com to the queue at dbs3.example.com. You must specify this propagation for each table that will apply changes at dbs3.example.com. This configuration is an example of directed networks because the changes at dbs2.example.com originated at dbs1.example.com.

*/
 
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name               => 'hr.countries',
    streams_name             => 'dbs2_to_dbs3',
    source_queue_name        => 'strmadmin.streams_queue',
    destination_queue_name   => 'strmadmin.streams_queue@dbs3.example.com', 
    include_dml              => TRUE,
    include_ddl              => TRUE,
    source_database          => 'dbs1.example.com',
    inclusion_rule           => TRUE,
    queue_to_queue           => TRUE);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.locations',
    streams_name            => 'dbs2_to_dbs3',
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@dbs3.example.com', 
    include_dml             => TRUE,
    include_ddl             => TRUE,
    source_database         => 'dbs1.example.com',
    inclusion_rule          => TRUE);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.regions',
    streams_name            => 'dbs2_to_dbs3',
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@dbs3.example.com', 
    include_dml             => TRUE,
    include_ddl             => TRUE,
    source_database         => 'dbs1.example.com',
    inclusion_rule          => TRUE);
END;
/

/*

Step 11   Create the Custom Rule-Based Transformation for Row LCRs at dbs2.example.com

Connect to dbs2.example.com as the hr user.

*/
 
CONNECT hr@dbs2.example.com

/*

Create the custom rule-based transformation function that transforms row changes resulting from DML statements to the jobs table from dbs1.example.com into row changes to the assignments table on dbs2.example.com.

The following function transforms every row LCR for the jobs table into a row LCR for the assignments table.

Note:

If DDL changes were also applied to the assignments table, then another transformation would be required for the DDL LCRs. This transformation would need to change the object name and the DDL text.

*/

CREATE OR REPLACE FUNCTION hr.to_assignments_trans_dml(
  p_in_data in ANYDATA) 
  RETURN ANYDATA IS out_data SYS.LCR$_ROW_RECORD;
  tc   pls_integer;
BEGIN
  -- Typecast AnyData to LCR$_ROW_RECORD
     tc := p_in_data.GetObject(out_data);
     IF out_data.GET_OBJECT_NAME() = 'JOBS'
     THEN
  -- Transform the in_data into the out_data
     out_data.SET_OBJECT_NAME('ASSIGNMENTS');
     END IF;
  -- Convert to AnyData
     RETURN ANYDATA.ConvertObject(out_data);
END;
/

/*

Step 12   Configure the Apply Process for Local Apply at dbs2.example.com

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

*/
 
CONNECT strmadmin@dbs2.example.com

/*

Configure dbs2.example.com to apply changes to the assignments table. Remember that the assignments table receives changes from the jobs table at dbs1.example.com.

*/

DECLARE
  to_assignments_rulename_dml   VARCHAR2(30);
  dummy_rule                    VARCHAR2(30);
BEGIN
--  DML changes to the jobs table from dbs1.example.com are applied 
--  to the assignments table. The to_assignments_rulename_dml variable 
--  is an out parameter in this call.
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.jobs', -- jobs, not assignments, specified
    streams_type    => 'apply', 
    streams_name    => 'apply_dbs2',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => TRUE,
    include_ddl     => FALSE,
    source_database => 'dbs1.example.com',
    dml_rule_name   => to_assignments_rulename_dml,
    ddl_rule_name   => dummy_rule,
    inclusion_rule  => TRUE); 
--  Modify the rule for the hr.jobs table to use the transformation function.
  DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
    rule_name          => to_assignments_rulename_dml,
    transform_function => 'hr.to_assignments_trans_dml');
END;
/

/*

Step 13   Specify hr as the Apply User for the Apply Process at dbs2.example.com

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 Oracle 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 Oracle Streams administrator as the apply user.

See Also:

Oracle Streams Replication Administrator's Guide for more information about configuring an Oracle Streams administrator

*/

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

/*

Step 14   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 the 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_DBS2';
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => rs_name,
    grantee     => 'hr');
END;
/

/*

Step 15   Start the Apply Process at dbs2.example.com for Local Apply

Set the disable_on_error parameter to n so that the apply process will not be disabled if it encounters an error, and start the apply process for local apply at dbs2.example.com.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_dbs2', 
    parameter   => 'disable_on_error', 
    value       => 'N');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_dbs2');
END;
/

/*

Step 16   Configure the Apply Process at dbs2.example.com for Apply at dbs4.example.com

Configure the apply process for dbs4.example.com, which is a Sybase database. The dbs2.example.com database is acting as a gateway to dbs4.example.com. Therefore, the apply process for dbs4.example.com must be configured at dbs2.example.com. The apply process cannot apply DDL changes to non-Oracle databases. Therefore, the include_ddl parameter is set to FALSE when the ADD_TABLE_RULES procedure is run.

*/

BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name          => 'strmadmin.streams_queue',
    apply_name          => 'apply_dbs4',
    apply_database_link => 'dbs4.example.com',
    apply_captured      => TRUE);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.jobs',
    streams_type    => 'apply', 
    streams_name    => 'apply_dbs4',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => TRUE,
    include_ddl     => FALSE,
    source_database => 'dbs1.example.com',
    inclusion_rule  => TRUE);
END;
/

/*

Step 17   Start the Apply Process at dbs2.example.com for Apply at dbs4.example.com

Set the disable_on_error parameter to n so that the apply process will not be disabled if it encounters an error, and start the remote apply for Sybase using database link dbs4.example.com.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_dbs4', 
    parameter   => 'disable_on_error', 
    value       => 'N');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_dbs4');
END;
/

/*

Step 18   Start the Capture Process at dbs1.example.com

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

*/
 
CONNECT strmadmin@dbs1.example.com

/*

Start the capture process at dbs1.example.com.

*/

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

/*

Step 19   Check the Spool Results

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

*/

SET ECHO OFF
SPOOL OFF

/*

You can now make DML and DDL changes to specific tables at dbs1.example.com and see these changes replicated to the other databases in the environment based on the rules you configured for the Oracle Streams processes and propagations in this environment.

See Also:

"Make DML and DDL Changes to Tables in the hr Schema" for examples of changes that are replicated in this environment

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

Flexible Configuration for Sharing Data from a Single Database

Complete the following steps to use a more flexible approach for specifying the capture, propagation, and apply definitions. This approach does not use the DBMS_STREAMS_ADM package. Instead, it uses the following packages:

• The DBMS_CAPTURE_ADM package to configure capture processes

• The DBMS_PROPAGATION_ADM package to configure propagations

• The DBMS_APPLY_ADM package to configure apply processes

• The DBMS_RULES_ADM package to specify capture process, propagation, and apply process rules and rule sets

  Note:

  Neither the ALL_STREAMS_TABLE_RULES nor the DBA_STREAMS_TABLE_RULES data dictionary view is populated by the rules created in this example. To view the rules created in this example, you can query the ALL_STREAMS_RULES or DBA_STREAMS_RULES data dictionary view.

This example includes the following steps:

1. Show Output and Spool Results

2. Configure Propagation at dbs1.example.com

3. Configure the Capture Process at dbs1.example.com

4. Prepare the hr Schema at dbs1.example.com for Instantiation

5. Set the Instantiation SCN for the Existing Tables at Other Databases

6. Instantiate the dbs1.example.com Tables at dbs3.example.com

7. Configure the Apply Process at dbs3.example.com

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

9. Start the Apply Process at dbs3.example.com

10. Configure Propagation at dbs2.example.com

11. Create the Custom Rule-Based Transformation for Row LCRs at dbs2.example.com

12. Configure the Apply Process for Local Apply at dbs2.example.com

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

14. Start the Apply Process at dbs2.example.com for Local Apply

15. Configure the Apply Process at dbs2.example.com for Apply at dbs4.example.com

16. Start the Apply Process at dbs2.example.com for Apply at dbs4.example.com

17. Start the Capture Process at dbs1.example.com

18. Check the Spool Results

/************************* 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_share_schema2.out

/*

Step 2   Configure Propagation at dbs1.example.com

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

*/
 
CONNECT strmadmin@dbs1.example.com

/*

Configure and schedule propagation from the queue at dbs1.example.com to the queue at dbs2.example.com. This configuration specifies that the propagation propagates all changes to the hr schema. You have the option of omitting the rule set specification, but then everything in the queue will be propagated, which might not be desired if, in the future, multiple capture processes will use the streams_queue.

*/

BEGIN
  -- Create the rule set
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name       => 'strmadmin.propagation_dbs1_rules',
    evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
  -- Create rules for all modifications to the hr schema
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name  => 'strmadmin.all_hr_dml',
    condition  => ' :dml.get_object_owner() = ''HR'' AND ' || 
                  ' :dml.is_null_tag() = ''Y'' AND ' ||
                  ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name  => 'strmadmin.all_hr_ddl',
    condition  => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                  ' :ddl.get_base_table_owner() =   ''HR'') AND ' || 
                  ' :ddl.is_null_tag() = ''Y'' AND ' ||
                  ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Add rules to rule set
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_hr_dml', 
    rule_set_name  => 'strmadmin.propagation_dbs1_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_hr_ddl', 
    rule_set_name  => 'strmadmin.propagation_dbs1_rules');
  -- Create a propagation that uses the rule set as its positive rule set
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name    => 'dbs1_to_dbs2',
    source_queue        => 'strmadmin.streams_queue',
    destination_queue   => 'strmadmin.streams_queue',
    destination_dblink  => 'dbs2.example.com',
    rule_set_name       => 'strmadmin.propagation_dbs1_rules');
END;
/

/*

Step 3   Configure the Capture Process at dbs1.example.com

Create a capture process and rules to capture the entire hr schema at dbs1.example.com.

*/

BEGIN
  -- Create the rule set
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name       => 'strmadmin.demo_rules',
    evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
  --   Create rules that specify the entire hr schema
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name  => 'strmadmin.schema_hr_dml',
    condition  => ' :dml.get_object_owner() = ''HR''  AND ' || 
                  ' :dml.is_null_tag() = ''Y'' AND ' ||
                  ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name  => 'strmadmin.schema_hr_ddl',
    condition  => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                  ' :ddl.get_base_table_owner() =   ''HR'') AND ' || 
                  ' :ddl.is_null_tag() = ''Y'' AND ' ||
                  ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  --  Add the rules to the rule set
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.schema_hr_dml', 
    rule_set_name  => 'strmadmin.demo_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.schema_hr_ddl', 
    rule_set_name  => 'strmadmin.demo_rules');
  --  Create a capture process that uses the rule set as its positive rule set
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name    => 'strmadmin.streams_queue',
    capture_name  => 'capture',
    rule_set_name => 'strmadmin.demo_rules');
END;
/

/*

Step 4   Prepare the hr Schema at dbs1.example.com for Instantiation

While still connected as the Oracle Streams administrator at dbs1.example.com, prepare the hr schema at dbs1.example.com for instantiation at dbs3.example.com. This step marks the lowest SCN of the tables in the schema for instantiation. SCNs subsequent to the lowest SCN can be used for instantiation.

This step also enables supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the tables in the hr schema. Supplemental logging places additional information in the redo log for changes made to tables. The apply process needs this extra information to perform certain operations, such as unique row identification and conflict resolution. Because dbs1.example.com is the only database where changes are captured in this environment, it is the only database where you must specify supplemental logging for the tables in the hr schema.

Note:

This step is not required in the "Simple Configuration for Sharing Data from a Single Database". In that example, when the ADD_SCHEMA_RULES procedure in the DBMS_STREAMS_ADM package is run in Step 3, the PREPARE_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package is run automatically for the hr schema.

See Also:

Oracle Streams Replication Administrator's Guide

*/

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
    schema_name          => 'hr',
    supplemental_logging => 'keys');
END;
/

/*

Step 5   Set the Instantiation SCN for the Existing Tables at Other Databases

In this example, the hr.jobs table already exists at dbs2.example.com and dbs4.example.com. At dbs2.example.com, this table is named assignments, but it has the same shape and data as the jobs table at dbs1.example.com. Also, in this example, dbs4.example.com is a Sybase database. All of the other tables in the Oracle Streams environment are instantiated at the other destination databases using Data Pump import.

Because the hr.jobs table already exists at dbs2.example.com and dbs4.example.com, this example uses the GET_SYSTEM_CHANGE_NUMBER function in the DBMS_FLASHBACK package at dbs1.example.com to obtain the current SCN for the database. This SCN is used at dbs2.example.com to run the SET_TABLE_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package. Running this procedure twice sets the instantiation SCN for the hr.jobs table at dbs2.example.com and dbs4.example.com.

The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are ignored by an apply process and which LCRs for a table are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR.

In this example, both of the apply processes at dbs2.example.com will apply transactions to the hr.jobs table with SCNs that were committed after SCN obtained in this step.

Note:

This example assumes that the contents of the hr.jobs table at dbs1.example.com, dbs2.example.com (as hr.assignments), and dbs4.example.com are consistent when you complete this step. You might want to lock the table at each database while you complete this step to ensure consistency.

*/

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.EXAMPLE.COM(
    source_object_name    => 'hr.jobs',
    source_database_name  => 'dbs1.example.com',
    instantiation_scn     => iscn);
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.EXAMPLE.COM(
    source_object_name    => 'hr.jobs',
    source_database_name  => 'dbs1.example.com',
    instantiation_scn     => iscn,
    apply_database_link   =>       'dbs4.example.com');
END;
/

/*

Step 6   Instantiate the dbs1.example.com Tables at dbs3.example.com

This example performs a network Data Pump import of the following tables:

• hr.countries

• hr.locations

• hr.regions

A network import means that Data Pump imports these tables from dbs1.example.com without using an export dump file.

See Also:

Oracle Database Utilities for information about performing an import

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

*/
 
CONNECT strmadmin@dbs3.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
  sscn      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 => 'DBS1.EXAMPLE.COM',
          job_name    => 'dp_sing2');
-- 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''');
-- A metadata filter is used to specify the tables that will be imported.
  DBMS_DATAPUMP.METADATA_FILTER(
    handle    => h1,
    name      => 'NAME_EXPR',
    value     => 'IN(''COUNTRIES'', ''REGIONS'', ''LOCATIONS'')');
-- 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 included in the import.
  sscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@dbs1.example.com();
  DBMS_DATAPUMP.SET_PARAMETER(
    handle => h1,
    name   => 'FLASHBACK_SCN',
    value  => sscn); 
-- 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: ' ||sscn);
  END;
END;
/

/*

Step 7   Configure the Apply Process at dbs3.example.com

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

*/
 
CONNECT strmadmin@dbs3.example.com

/*

Configure dbs3.example.com to apply DML and DDL changes to the countries table, locations table, and regions table.

*/

BEGIN
  -- Create the rule set
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name       => 'strmadmin.apply_rules',
    evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
  -- Rules for hr.countries
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name    => 'strmadmin.all_countries_dml',
    condition    => ' :dml.get_object_owner() = ''HR'' AND ' || 
                    ' :dml.get_object_name() = ''COUNTRIES''  AND ' || 
                    ' :dml.is_null_tag() = ''Y'' AND ' ||
                    ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name    => 'strmadmin.all_countries_ddl',
    condition    => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                    ' :ddl.get_base_table_owner() =         ''HR'') AND ' || 
                    ' :ddl.get_object_name() = ''COUNTRIES'' AND ' || 
                    ' :ddl.is_null_tag() = ''Y'' AND ' ||
                    ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Rules for hr.locations
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name    => 'strmadmin.all_locations_dml',
    condition    => ' :dml.get_object_owner() = ''HR'' AND ' ||
                    ' :dml.get_object_name() = ''LOCATIONS'' AND ' || 
                    ' :dml.is_null_tag() = ''Y'' AND ' ||
                    ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name    => 'strmadmin.all_locations_ddl',
    condition    => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                    ' :ddl.get_base_table_owner() =         ''HR'') AND ' ||
                    ' :ddl.get_object_name() = ''LOCATIONS'' AND ' || 
                    ' :ddl.is_null_tag() = ''Y'' AND ' ||
                    ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Rules for hr.regions
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name    => 'strmadmin.all_regions_dml',
    condition    => ' :dml.get_object_owner() = ''HR'' AND ' ||
                    ' :dml.get_object_name() = ''REGIONS'' AND ' || 
                    ' :dml.is_null_tag() = ''Y'' AND ' ||
                    ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name    => 'strmadmin.all_regions_ddl',
    condition    => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                    ' :ddl.get_base_table_owner() =         ''HR'') AND ' ||
                    ' :ddl.get_object_name() = ''REGIONS'' AND ' || 
                    ' :ddl.is_null_tag() = ''Y'' AND ' ||
                    ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Add rules to rule set
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_countries_dml', 
    rule_set_name  => 'strmadmin.apply_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_countries_ddl', 
    rule_set_name  => 'strmadmin.apply_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_locations_dml', 
    rule_set_name  => 'strmadmin.apply_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_locations_ddl', 
    rule_set_name  => 'strmadmin.apply_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_regions_dml', 
    rule_set_name  => 'strmadmin.apply_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_regions_ddl', 
    rule_set_name  => 'strmadmin.apply_rules');
  -- Create an apply process that uses the rule set as its positive rule set
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name      => 'strmadmin.streams_queue',
    apply_name      => 'apply',
    rule_set_name   => 'strmadmin.apply_rules',
    apply_user      => 'hr',
    apply_captured  => TRUE,
    source_database => 'dbs1.example.com');
END;
/

/*

Step 8   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 the apply process

*/

BEGIN
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => 'strmadmin.apply_rules',
    grantee     => 'hr');
END;
/

/*

Step 9   Start the Apply Process at dbs3.example.com

Set the disable_on_error parameter to n so that the apply process will not be disabled if it encounters an error, and start the apply process at dbs3.example.com.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply', 
    parameter   => 'disable_on_error', 
    value       => 'N');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply');
END;
/

/*

Step 10   Configure Propagation at dbs2.example.com

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

*/
 
CONNECT strmadmin@dbs2.example.com

/*

Configure and schedule propagation from the queue at dbs2.example.com to the queue at dbs3.example.com. This configuration is an example of directed networks because the changes at dbs2.example.com originated at dbs1.example.com.

*/

BEGIN
  -- Create the rule set
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name       => 'strmadmin.propagation_dbs3_rules',
    evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
  -- Create rules for all modifications to the countries table
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name  => 'strmadmin.all_countries_dml',
    condition  => ' :dml.get_object_owner() = ''HR'' AND ' || 
                  ' :dml.get_object_name() = ''COUNTRIES'' AND ' || 
                  ' :dml.is_null_tag() = ''Y'' AND ' ||
                  ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name  => 'strmadmin.all_countries_ddl',
    condition  => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                  ' :ddl.get_base_table_owner() =   ''HR'') AND ' || 
                  ' :ddl.get_object_name() = ''COUNTRIES'' AND ' || 
                  ' :ddl.is_null_tag() = ''Y'' AND ' ||
                  ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Create rules for all modifications to the locations table
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name   => 'strmadmin.all_locations_dml',
    condition  => ' :dml.get_object_owner() = ''HR'' AND ' ||
                  ' :dml.get_object_name() = ''LOCATIONS'' AND ' || 
                  ' :dml.is_null_tag() = ''Y'' AND ' ||
                  ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name   => 'strmadmin.all_locations_ddl',
    condition  => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                  ' :ddl.get_base_table_owner() =   ''HR'') AND ' ||
                  ' :ddl.get_object_name() = ''LOCATIONS'' AND ' || 
                  ' :ddl.is_null_tag() = ''Y'' AND ' ||
                  ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Create rules for all modifications to the regions table
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name   => 'strmadmin.all_regions_dml',
    condition  => ' :dml.get_object_owner() = ''HR'' AND ' ||
                  ' :dml.get_object_name() = ''REGIONS'' AND ' || 
                  ' :dml.is_null_tag() = ''Y'' AND ' ||
                  ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name   => 'strmadmin.all_regions_ddl',
    condition  => ' (:ddl.get_object_owner() = ''HR'' OR ' ||
                  ' :ddl.get_base_table_owner() =   ''HR'') AND ' ||
                  ' :ddl.get_object_name() = ''REGIONS'' AND ' || 
                  ' :ddl.is_null_tag() = ''Y'' AND ' ||
                  ' :ddl.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Add rules to rule set
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_countries_dml', 
    rule_set_name  => 'strmadmin.propagation_dbs3_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_countries_ddl', 
    rule_set_name  => 'strmadmin.propagation_dbs3_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_locations_dml',  
    rule_set_name  => 'strmadmin.propagation_dbs3_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_locations_ddl',  
    rule_set_name  => 'strmadmin.propagation_dbs3_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_regions_dml',  
    rule_set_name  => 'strmadmin.propagation_dbs3_rules');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_regions_ddl',  
    rule_set_name  => 'strmadmin.propagation_dbs3_rules');
  -- Create a propagation that uses the rule set as its positive rule set
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name    => 'dbs2_to_dbs3',
    source_queue        => 'strmadmin.streams_queue',
    destination_queue   => 'strmadmin.streams_queue',
    destination_dblink  => 'dbs3.example.com',
    rule_set_name       => 'strmadmin.propagation_dbs3_rules');
END;
/

/*

Step 11   Create the Custom Rule-Based Transformation for Row LCRs at dbs2.example.com

Connect to dbs2.example.com as the hr user.

*/
 
CONNECT hr@dbs2.example.com

/*

Create the custom rule-based transformation function that transforms row changes resulting from DML statements to the jobs table from dbs1.example.com into row changes to the assignments table on dbs2.example.com.

The following function transforms every row LCR for the jobs table into a row LCR for the assignments table.

Note:

If DDL changes were also applied to the assignments table, then another transformation would be required for the DDL LCRs. This transformation would need to change the object name and the DDL text.

*/

CREATE OR REPLACE FUNCTION hr.to_assignments_trans_dml(
  p_in_data in ANYDATA) 
  RETURN ANYDATA IS out_data SYS.LCR$_ROW_RECORD;
  tc   pls_integer;
BEGIN
  -- Typecast AnyData to LCR$_ROW_RECORD
     tc := p_in_data.GetObject(out_data);
     IF out_data.GET_OBJECT_NAME() = 'JOBS'
     THEN
  -- Transform the in_data into the out_data
     out_data.SET_OBJECT_NAME('ASSIGNMENTS');
     END IF;
  -- Convert to AnyData
     RETURN ANYDATA.ConvertObject(out_data);
END;
/

/*

Step 12   Configure the Apply Process for Local Apply at dbs2.example.com

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

*/
 
CONNECT strmadmin@dbs2.example.com

/*

Configure dbs2.example.com to apply changes to the local assignments table. Remember that the assignments table receives changes from the jobs table at dbs1.example.com. This step specifies a custom rule-based transformation without using the SET_RULE_TRANSFORM_FUNCTION procedure in the DBMS_STREAMS_ADM package. Instead, a name-value pair is added manually to the action context of the rule. The name-value pair specifies STREAMS$_TRANSFORM_FUNCTION for the name and hr.to_assignments_trans_dml for the value.

*/

DECLARE
  action_ctx_dml       SYS.RE$NV_LIST;
  action_ctx_ddl       SYS.RE$NV_LIST;
  ac_name              VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
BEGIN
  -- Specify the name-value pair in the action context
  action_ctx_dml := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY());
  action_ctx_dml.ADD_PAIR(
    ac_name, 
    ANYDATA.CONVERTVARCHAR2('hr.to_assignments_trans_dml'));
  --  Create the rule set strmadmin.apply_rules
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name       => 'strmadmin.apply_rules',
    evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
  --  Create a rule that transforms all DML changes to the jobs table into 
  --  DML changes for assignments table
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name       => 'strmadmin.all_jobs_dml',
    condition       => ' :dml.get_object_owner() = ''HR'' AND ' ||
                       ' :dml.get_object_name() = ''JOBS'' AND ' || 
                       ' :dml.is_null_tag() = ''Y'' AND ' ||
                       ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ',
    action_context  => action_ctx_dml);
  --  Add the rule to the rule set
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_jobs_dml', 
    rule_set_name  => 'strmadmin.apply_rules');
  -- Create an apply process that uses the rule set as its positive rule set
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name      => 'strmadmin.streams_queue',
    apply_name      => 'apply_dbs2',
    rule_set_name   => 'strmadmin.apply_rules',
    apply_user      => 'hr',
    apply_captured  => TRUE,
    source_database => 'dbs1.example.com');
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 the apply process

*/

BEGIN
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege   => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
    object_name => 'strmadmin.apply_rules',
    grantee     => 'hr');
END;
/

/*

Step 14   Start the Apply Process at dbs2.example.com for Local Apply

Set the disable_on_error parameter to n so that the apply process will not be disabled if it encounters an error, and start the apply process for local apply at dbs2.example.com.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_dbs2', 
    parameter   => 'disable_on_error', 
    value       => 'N');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_dbs2');
END;
/

/*

Step 15   Configure the Apply Process at dbs2.example.com for Apply at dbs4.example.com

Configure dbs2.example.com to apply DML changes to the jobs table at dbs4.example.com, which is a Sybase database. Remember that these changes originated at dbs1.example.com.

*/

BEGIN
  -- Create the rule set
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name      => 'strmadmin.apply_dbs4_rules',
    evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');
  -- Create rule strmadmin.all_jobs_remote for all modifications 
  -- to the jobs table
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name    => 'strmadmin.all_jobs_remote',
    condition    => ' :dml.get_object_owner() = ''HR'' AND ' ||
                    ' :dml.get_object_name() = ''JOBS'' AND ' || 
                    ' :dml.is_null_tag() = ''Y'' AND ' ||
                    ' :dml.get_source_database_name() = ''DBS1.EXAMPLE.COM'' ');
  -- Add the rule to the rule set
  DBMS_RULE_ADM.ADD_RULE(
    rule_name      => 'strmadmin.all_jobs_remote', 
    rule_set_name  => 'strmadmin.apply_dbs4_rules');
  -- Create an apply process that uses the rule set as its positive rule set
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name          => 'strmadmin.streams_queue',
    apply_name          => 'apply_dbs4',
    rule_set_name       => 'strmadmin.apply_dbs4_rules',
    apply_database_link => 'dbs4.example.com',
    apply_captured      => TRUE,
    source_database     => 'dbs1.example.com');
END;
/

/*

Step 16   Start the Apply Process at dbs2.example.com for Apply at dbs4.example.com

Set the disable_on_error parameter to n so that the apply process will not be disabled if it encounters an error, and start the remote apply for Sybase using database link dbs4.example.com.

*/

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

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

/*

Step 17   Start the Capture Process at dbs1.example.com

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

*/
 
CONNECT strmadmin@dbs1.example.com

/*

Start the capture process at dbs1.example.com.

*/

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

/*

Step 18   Check the Spool Results

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

*/

SET ECHO OFF
SPOOL OFF

/*

You can now make DML and DDL changes to specific tables at dbs1.example.com and see these changes replicated to the other databases in the environment based on the rules you configured for the Oracle Streams processes and propagations in this environment.

See Also:

"Make DML and DDL Changes to Tables in the hr Schema" for examples of changes that are replicated in this environment

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