A Migrating Advanced Replication to Oracle Streams

Overview of the Migration Process

The following sections provide a conceptual overview of the migration process:

• Migration Script Generation and Use

• Modification of the Migration Script

• Actions Performed by the Generated Script

• Migration Script Errors

• Manual Migration of Updatable Materialized Views

• Advanced Replication Elements that Cannot Be Migrated to Oracle Streams

Preparing to Generate the Migration Script

Before generating the migration script, ensure that all the following conditions are met:

• All the replication groups must have the same master site(s).

• The master site that generates the migration script must be running Oracle Database 10g or later.

• The other master sites that run the script, but do not generate the script, must be running Oracle9i Database Release 2 (9.2) or later.

Generating and Modifying the Migration Script

To generate the migration script, use the procedure DBMS_REPCAT.STREAMS_MIGRATION in the DBMS_REPCAT package. The syntax for this procedure is as follows:

DBMS_REPCAT.STREAMS_MIGRATION ( 
     gnames              IN   DBMS_UTILITY.NAME_ARRAY, 
     file_location       IN   VARCHAR2, 
     filename            IN   VARCHAR2);

Parameters for the DBMS_REPCAT.STREAMS_MIGRATION procedure include the following:

• gnames: List of replication groups to migrate to Oracle Streams. The replication groups listed must all contain the same master sites. An error is raised if the replication groups have different masters.

• file_location: Directory location of the migration script.

• filename: Name of the migration script.

This procedure generates a script for setting up an Oracle Streams environment for the given replication groups. The script can be customized and run at each master site.

Example Advanced Replication Environment to be Migrated to Oracle Streams

Figure A-1 shows the Advanced Replication environment that will be migrated to Oracle Streams in this example.

Figure A-1 Advanced Replication Environment to be Migrated to Oracle Streams

Description of "Figure A-1 Advanced Replication Environment to be Migrated to Oracle Streams"

This Advanced Replication environment has the following characteristics:

• The orc1.example.com database is the master definition site for a three-way master configuration that also includes orc2.example.com and orc3.example.com.

• The orc1.example.com database is the master site for the mv1.example.com materialized view site.

• The environment replicates changes to the database objects in the hr schema between the three master sites and between the master site and the materialized view site. A single replication group named hr_repg contains the replicated objects.

• Conflict resolution is configured for the hr.countries table in the multimaster environment. The latest time stamp conflict resolution method resolves conflicts on this table.

• The materialized views at the mv1.example.com site are updatable.

You can configure this Advanced Replication environment by completing the tasks described in the following sections of the Oracle Database Advanced Replication Management API Reference:

• Set up the three master sites.

• Set up the materialized view sites (to set up mv1.example.com only).

• Create the hr_repg master group for the three master sites with orc1.example.com as the master definition site.

• Configure time stamp conflict resolution for the hr.countries table.

• Create the materialized view group at mv1.example.com based on the hr_repg master group at orc1.example.com.

To generate the migration script for this Advanced Replication environment, complete the following steps:

1. Create the Oracle Streams Administrator at All Master Sites

2. Make a Directory Location Accessible

3. Generate the Migration Script

4. Verify the Generated Migration Script Creation and Modify Script

Step 1   Create the Oracle Streams Administrator at All Master Sites

Complete the following steps to create the Oracle Streams administrator at each master site for the replication groups being migrated to Oracle Streams. For the sample environment described in "Example Advanced Replication Environment to be Migrated to Oracle Streams", complete these steps at orc1.example.com, orc2.example.com, and orc3.example.com:

1. Connect as an administrative user who can create users, grant privileges, and create tablespaces.

2. Either create a tablespace for the Oracle Streams administrator or use an existing tablespace. For example, the following statement creates a new tablespace for the Oracle Streams administrator:

   CREATE TABLESPACE streams_tbs DATAFILE '/usr/oracle/dbs/streams_tbs.dbf' 
     SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   

3. Create a new user to act as the Oracle Streams administrator or use an existing user. For example, to create a user named strmadmin and specify that this user uses the streams_tbs tablespace, run the following statement:

   CREATE USER strmadmin IDENTIFIED BY password
      DEFAULT TABLESPACE streams_tbs
      QUOTA UNLIMITED ON streams_tbs;
   
   GRANT DBA TO strmadmin;
   

   Note:

   • The migration script assumes that the user name of the Oracle Streams administrator is strmadmin. If your Oracle Streams administrator has a different user name, then edit the migration script to replace all instances of strmadmin with the user name of your Oracle Streams administrator.

   • Ensure that you grant DBA role to the Oracle Streams administrator.

4. Grant any additional privileges required by the Oracle Streams administrator at each master site. The necessary privileges depend on your specific Oracle Streams environment.

   See Also:

   "Configuring an Oracle Streams Administrator on All Databases" for information about addition privileges that might be required for an Oracle Streams administrator

Step 2   Make a Directory Location Accessible

The directory specified by the file_location parameter in the DBMS_REPCAT.STREAMS_MIGRATION procedure must be accessible to PL/SQL. If you do not have directory object that is accessible to the Oracle Streams administrator at the master definition site currently, then connect as the Oracle Streams administrator, and create a directory object using the SQL statement CREATE DIRECTORY.

A directory object is similar to an alias for the directory. For example, to create a directory object called MIG2STR_DIR for the /usr/scripts directory on your computer system, run the following procedure:

CONNECT strmadmin@orc1.example.com
Enter password: password

CREATE DIRECTORY MIG2STR_DIR AS '/usr/scripts';

See Also:

Oracle Database SQL Language Reference for more information about the CREATE DIRECTORY statement

Step 3   Generate the Migration Script

To generate the migration script, run the DBMS_REPCAT.STREAMS_MIGRATION procedure at the master definition site and specify the appropriate parameters. For example, the following procedure generates a script that migrates an Advanced Replication environment with one replication group named hr_repg. The script name is rep2streams.sql, and it is generated into the /usr/scripts directory on the local computer system. This directory is represented by the directory object MIG2STR_DIR.

CONNECT strmadmin@orc1.example.com
Enter password: password

DECLARE
  rep_groups DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    rep_groups(1) := 'HR_REPG';
    DBMS_REPCAT.STREAMS_MIGRATION(
      gnames         =>  rep_groups,
      file_location  =>  'MIG2STR_DIR',
      filename       =>  'rep2streams.sql');
END;
/

See Also:

"Example Advanced Replication to Oracle Streams Migration Script" to view the script generated in this example

Step 4   Verify the Generated Migration Script Creation and Modify Script

After generating the migration script, verify that the script was created viewing the script in the specified directory. If necessary, you can modify it to support the following:

• If your environment requires conflict resolution that used the additive, average, priority group, or site priority Advanced Replication conflict resolution methods, then configure user-defined conflict resolution methods to resolve conflicts. Oracle Streams does not provide prebuilt conflict resolution methods that are equivalent to these methods.

  However, the migration script supports the following conflict resolution methods automatically: overwrite, discard, maximum, and minimum. The script converts an earliest time stamp method to a minimum method automatically, and it converts a latest time stamp method to a maximum method automatically. If you use a time stamp conflict resolution method, then the script assumes that any triggers necessary to populate the time stamp column in a table already exist.

• Unique conflict resolution.

• Delete conflict resolution.

• Multiple conflict resolution methods to be executed in a specified order when a conflict occurs. Oracle Streams allows only one conflict resolution method to be specified for each column list.

• Procedural replication.

• Replication of data definition language (DDL) changes for nontable objects, including the following:

  • Functions

  • Indexes

  • Indextypes

  • Operators

  • Packages

  • Package bodies

  • Procedures

  • Synonyms

  • Triggers

  • Types

  • Type bodies

  • Views

Because changes to these objects were being replicated by Advanced Replication at all sites, the migration script does not need to take any action to migrate these objects. You can add DDL rules to the Oracle Streams environment to support the future modification and creation of these types of objects.

For example, to specify that a capture process named streams_capture at the orc1.example.com database captures DDL changes to all of the database objects in the hr schema, add the following to the script:

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name        => 'hr',
    streams_type       => 'capture',
    streams_name       => 'streams_capture',
    queue_name         => 'strmadmin.streams_queue',
    include_dml        => FALSE,
    include_ddl        => TRUE,
    include_tagged_lcr => FALSE,
    source_database    => 'orc1.example.com');
END;
/

Notice that the include_ddl parameter is set to TRUE. By setting this parameter to TRUE, this procedure adds a schema rule for DDL changes to the hr schema to the rule set for the capture process. This rule instructs the capture process to capture DDL changes to the hr schema and its objects. For the DDL changes to be replicated, you must add similar rules to the appropriate propagations and apply processes.

See Also:

• Chapter 9, "Oracle Streams Conflict Resolution"

• Oracle Streams Concepts and Administration for information about queue-to-queue propagations

Performing the Migration for Advanced Replication to Oracle Streams

This section explains how to perform the migration from an Advanced Replication environment to an Oracle Streams environment.

This section contains the following topics:

• Before Executing the Migration Script

• Executing the Migration Script

• After Executing the Script

Re-creating Master Sites to Retain Materialized View Groups

If one or more materialized view groups used a master group that you migrated to Oracle Streams, then you must re-create the master group to retain these materialized view groups. Therefore, each database acting as the master site for a materialized view group must become the master definition site for a one-master configuration of a replication group that contains the tables used by the materialized views in the materialized view group.

Use the replication management APIs to create a replication group similar to the original replication group that was migrated to Oracle Streams. That is, the new replication group should have the same replication group name, objects, conflict resolution methods, and key columns. To retain the existing materialized view groups, you must re-create each master group at each master site that contained a master group for a materialized view group, re-create the master replication objects in the master group, regenerate replication support for the master group, and resume replication activity for the master group.

For example, consider the following Advanced Replication environment:

• Two master sites, mdb1.example.com and mdb2.example.com, have the replication group rg1. The mdb1.example.com database is the master definition site, and the objects in the rg1 replication group are replicated between mdb1.example.com and mdb2.example.com.

• The rg1 replication group at mdb1.example.com is the master group to the mvg1 materialized view group at mv1.example.com.

• The rg1 replication group at mdb2.example.com is the master group to the mvg2 materialized view group at mv2.example.com.

If the rg1 replication group is migrated to Oracle Streams at both mdb1.example.com and mdb2.example.com, and you want to retain the materialized view groups mvg1 at mv1.example.com and mvg2 at mv2.example.com, then you must re-create the rg1 replication group at mdb1.example.com and mdb2.example.com after the migration to Oracle Streams. You configure both mdb1.example.com and mdb2.example.com to be the master definition site for the rg1 replication group in a one-master environment.

It is not necessary to drop or re-create materialized view groups at the materialized view sites. If a new master replication group resembles the original replication group, then the materialized view groups are not affected. Do not refresh these materialized view groups until generation of replication support for each master object is complete (Step 3 in the task in this section). Similarly, do not push the deferred transaction queue at any materialized view site with updatable materialized views until generation of replication support for each master object is complete.

For the sample environment described in "Example Advanced Replication Environment to be Migrated to Oracle Streams", only the hr_repg replication group at orc1.example.com was the master group to a materialized view group at mv1.example.com. To retain this materialized view group at mv1.example.com, complete the following steps while connected as the replication administrator:

1. Create the master group hr_repg at orc1.example.com.

   CONNECT repadmin@orc1.example.com
   Enter password: password
   
   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPGROUP (
         gname => 'hr_repg');
   END;
   /
   

2. Add the tables in the hr schema to the hr_repg master group. These tables are master tables to the materialized views at mv1.example.com.

   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
         gname               => 'hr_repg',
         type                => 'TABLE',
         oname               => 'countries',
         sname               => 'hr',
         use_existing_object => TRUE,
         copy_rows           => FALSE);
   END;
   /
   
   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
         gname               => 'hr_repg',
         type                => 'TABLE',
         oname               => 'departments',
         sname               => 'hr',
         use_existing_object => TRUE,
         copy_rows           => FALSE);
   END;
   /
   
   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
         gname               => 'hr_repg',
         type                => 'TABLE',
         oname               => 'employees',
         sname               => 'hr',
         use_existing_object => TRUE,
         copy_rows           => FALSE);
   END;
   /
   
   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
         gname               => 'hr_repg',
         type                => 'TABLE',
         oname               => 'jobs',
         sname               => 'hr',
         use_existing_object => TRUE,
         copy_rows           => FALSE);
   END;
   /
   
   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
         gname               => 'hr_repg',
         type                => 'TABLE',
         oname               => 'job_history',
         sname               => 'hr',
         use_existing_object => TRUE,
         copy_rows           => FALSE);
   END;
   /
   
   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
         gname               => 'hr_repg',
         type                => 'TABLE',
         oname               => 'locations',
         sname               => 'hr',
         use_existing_object => TRUE,
         copy_rows           => FALSE);
   END;
   /
   
   BEGIN
      DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
         gname               => 'hr_repg',
         type                => 'TABLE',
         oname               => 'regions',
         sname               => 'hr',
         use_existing_object => TRUE,
         copy_rows           => FALSE);
   END;
   /
   

3. Generate replication support for each object in the hr_repg master group.

   BEGIN 
       DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
         sname             => 'hr',
         oname             => 'countries', 
         type              => 'TABLE'); 
   END;
   /
   
   BEGIN 
       DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
         sname             => 'hr',
         oname             => 'departments', 
         type              => 'TABLE'); 
   END;
   /
   
   BEGIN 
       DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
         sname             => 'hr',
         oname             => 'employees', 
         type              => 'TABLE'); 
   END;
   /
   
   BEGIN 
       DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
         sname             => 'hr',
         oname             => 'jobs', 
         type              => 'TABLE'); 
   END;
   /
   
   BEGIN 
       DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
         sname             => 'hr',
         oname             => 'job_history', 
         type              => 'TABLE'); 
   END;
   /
   
   BEGIN 
       DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
         sname             => 'hr',
         oname             => 'locations', 
         type              => 'TABLE'); 
   END;
   /
   
   BEGIN 
       DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
         sname             => 'hr',
         oname             => 'regions', 
         type              => 'TABLE'); 
   END;
   /
   

4. Resume master activity for the hr_repg master group.

   BEGIN 
      DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
         gname => 'hr_repg'); 
   END;
   /
   

   Note:

   A materialized view log should exist for each table you added to the hr_repg master group, unless you deleted these logs manually after you migrated the replication group to Oracle Streams. If these materialized view logs do not exist, then you must create them.

Example Advanced Replication to Oracle Streams Migration Script

The following is an example script generated for the environment:

The following is an example script generated for the environment:
----------------------------------------------------------
-- Migration Script Generated on 12-JUN-05 by user STRMADMIN. --
----------------------------------------------------------
 
----------------------------------------------------------
--  ************** Notes and Assumptions ************** --
--
-- 1. The Oracle Streams Administrator is "strmadmin".
--    The user "strmadmin" must be created and granted the
--    required privileges before running the script.
--
-- 2. Names of queue tables, queues, capture processes
--    propagation jobs, and apply processes will be the
--    same at all sites. If the DBA wants different names,
--    he must edit the script manually before running it
--    at each master site.
--
-- 3. Archive logging must be enabled at all sites before
--    running the script.
--
-- 4. Users must set up database links for queue to queue
--    propagation, if needed.
--
-- 5. Repgroups must be quiesced before running the script.
----------------------------------------------------------
 
set pagesize 1000
set echo on
set serveroutput on
whenever sqlerror exit sql.sqlcode;
 
--
-- Raise error if Repgroups are not Quiesced.
--
declare
  repgroup_status VARCHAR2(10);
begin
  select status into repgroup_status
    from dba_repcat
   where gname = 'HR_REPG';
 
   if (repgroup_status != 'QUIESCED') THEN
     raise_application_error(-20000,
       'ORA-23310: object group "HR_REPG" is not quiesced.');
   end if;
exception when no_data_found then
  null;
end;
/
 
-------------------------------
-- Queue Owner
-------------------------------
-- streams queue owner at ORC1.EXAMPLE.COM
define QUEUE_OWNER_ORC1 = strmadmin
 
-- streams queue owner at ORC2.EXAMPLE.COM
define QUEUE_OWNER_ORC2 = strmadmin
 
-- streams queue owner at ORC3.EXAMPLE.COM
define QUEUE_OWNER_ORC3 = strmadmin
 
-------------------------------
-- Queue Table
-------------------------------
-- streams queue table at ORC1.EXAMPLE.COM
define QUEUE_TABLE_ORC1 = streams_queue_table
 
-- streams queue table at ORC2.EXAMPLE.COM
define QUEUE_TABLE_ORC2 = streams_queue_table
 
-- streams queue table at ORC3.EXAMPLE.COM
define QUEUE_TABLE_ORC3 = streams_queue_table
 
-------------------------------
-- Queue
-------------------------------
-- streams queue at ORC1.EXAMPLE.COM
define QUEUE_ORC1 = streams_queue
 
-- streams queue at ORC2.EXAMPLE.COM
define QUEUE_ORC2 = streams_queue
 
-- streams queue at ORC3.EXAMPLE.COM
define QUEUE_ORC3 = streams_queue
 
-------------------------------
-- Propagation names
-------------------------------
-- propagation process to ORC1.EXAMPLE.COM
define PROP_ORC1 = prop_to_ORC1
 
-- propagation process to ORC2.EXAMPLE.COM
define PROP_ORC2 = prop_to_ORC2
 
-- propagation process to ORC3.EXAMPLE.COM
define PROP_ORC3 = prop_to_ORC3
 
-------------------------------
-- Capture Process
-------------------------------
-- capture process to be used or created at the local site
define CAPTURE_NAME = streams_capture
 
-------------------------------
-- Apply processes
-------------------------------
-- apply process for applying LCRs from ORC1.EXAMPLE.COM
define APPLY_ORC1 = apply_from_ORC1
 
-- apply process for applying LCRs from ORC2.EXAMPLE.COM
define APPLY_ORC2 = apply_from_ORC2
 
-- apply process for applying LCRs from ORC3.EXAMPLE.COM
define APPLY_ORC3 = apply_from_ORC3
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
--
-- ** WARNING ** --
-- Oracle Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Oracle Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
 
-------------------------------
-- Setup Queue
-------------------------------
 
variable local_db          varchar2(128);
variable local_queue_table varchar2(30);
variable local_queue       varchar2(30);
variable local_queue_owner varchar2(30);
 
-- get the local database name
declare
  global_name varchar2(128);
begin
  select global_name into :local_db from global_name;
  dbms_output.put_line('The local database name is: ' || :local_db);
end;
/
 
-- get the local queue table and queue name
begin
  if :local_db = 'ORC1.EXAMPLE.COM' then
    :local_queue_table := '&QUEUE_TABLE_ORC1';
    :local_queue := '&QUEUE_ORC1';
    :local_queue_owner := '&QUEUE_OWNER_ORC1';
 
  elsif :local_db = 'ORC2.EXAMPLE.COM' then
    :local_queue_table := '&QUEUE_TABLE_ORC2';
    :local_queue := '&QUEUE_ORC2';
    :local_queue_owner := '&QUEUE_OWNER_ORC2';
 
  elsif :local_db = 'ORC3.EXAMPLE.COM' then
    :local_queue_table := '&QUEUE_TABLE_ORC3';
    :local_queue := '&QUEUE_ORC3';
    :local_queue_owner := '&QUEUE_OWNER_ORC3';
 
  end if;
 
  dbms_output.put_line('The local queue owner is: ' || :local_queue_owner);
  dbms_output.put_line('The local queue table is: ' || :local_queue_table);
  dbms_output.put_line('The local queue name  is: ' || :local_queue);
end;
/
 
begin
  dbms_streams_adm.set_up_queue(
    queue_table => :local_queue_table,
    storage_clause => NULL,
    queue_name => :local_queue,
    queue_user => :local_queue_owner,
    comment => 'streams_comment');
end;
/
 
-------------------------------
-- Set Instantiation SCN
-------------------------------
 
variable flashback_scn number;
 
begin
  select dbms_flashback.get_system_change_number into :flashback_scn
    from dual;
  dbms_output.put_line('local flashback SCN is: ' || :flashback_scn);
end;
/
 
--
-- Setup instantiation SCN for ORC1.EXAMPLE.COM
--
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."COUNTRIES" at
  -- ORC1.EXAMPLE.COM
  --
  if (:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.EXAMPLE.COM(
      source_object_name => '"HR"."COUNTRIES"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."DEPARTMENTS" at
  -- ORC1.EXAMPLE.COM
  --
  if (:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.EXAMPLE.COM(
      source_object_name => '"HR"."DEPARTMENTS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."EMPLOYEES" at
  -- ORC1.EXAMPLE.COM
  --
  if (:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.EXAMPLE.COM(
      source_object_name => '"HR"."EMPLOYEES"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOBS" at
  -- ORC1.EXAMPLE.COM
  --
  if (:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.EXAMPLE.COM(
      source_object_name => '"HR"."JOBS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOB_HISTORY" at
  -- ORC1.EXAMPLE.COM
  --
  if (:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.EXAMPLE.COM(
      source_object_name => '"HR"."JOB_HISTORY"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."LOCATIONS" at
  -- ORC1.EXAMPLE.COM
  --
  if (:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.EXAMPLE.COM(
      source_object_name => '"HR"."LOCATIONS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."REGIONS" at
  -- ORC1.EXAMPLE.COM
  --
  if (:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.EXAMPLE.COM(
      source_object_name => '"HR"."REGIONS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
--
-- Setup instantiation SCN for ORC2.EXAMPLE.COM
--
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."COUNTRIES" at
  -- ORC2.EXAMPLE.COM
  --
  if (:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.EXAMPLE.COM(
      source_object_name => '"HR"."COUNTRIES"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."DEPARTMENTS" at
  -- ORC2.EXAMPLE.COM
  --
  if (:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.EXAMPLE.COM(
      source_object_name => '"HR"."DEPARTMENTS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."EMPLOYEES" at
  -- ORC2.EXAMPLE.COM
  --
  if (:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.EXAMPLE.COM(
      source_object_name => '"HR"."EMPLOYEES"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOBS" at
  -- ORC2.EXAMPLE.COM
  --
  if (:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.EXAMPLE.COM(
      source_object_name => '"HR"."JOBS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOB_HISTORY" at
  -- ORC2.EXAMPLE.COM
  --
  if (:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.EXAMPLE.COM(
      source_object_name => '"HR"."JOB_HISTORY"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."LOCATIONS" at
  -- ORC2.EXAMPLE.COM
  --
  if (:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.EXAMPLE.COM(
      source_object_name => '"HR"."LOCATIONS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."REGIONS" at
  -- ORC2.EXAMPLE.COM
  --
  if (:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.EXAMPLE.COM(
      source_object_name => '"HR"."REGIONS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
--
-- Setup instantiation SCN for ORC3.EXAMPLE.COM
--
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."COUNTRIES" at
  -- ORC3.EXAMPLE.COM
  --
  if (:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.EXAMPLE.COM(
      source_object_name => '"HR"."COUNTRIES"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."DEPARTMENTS" at
  -- ORC3.EXAMPLE.COM
  --
  if (:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.EXAMPLE.COM(
      source_object_name => '"HR"."DEPARTMENTS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."EMPLOYEES" at
  -- ORC3.EXAMPLE.COM
  --
  if (:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.EXAMPLE.COM(
      source_object_name => '"HR"."EMPLOYEES"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOBS" at
  -- ORC3.EXAMPLE.COM
  --
  if (:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.EXAMPLE.COM(
      source_object_name => '"HR"."JOBS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOB_HISTORY" at
  -- ORC3.EXAMPLE.COM
  --
  if (:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.EXAMPLE.COM(
      source_object_name => '"HR"."JOB_HISTORY"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."LOCATIONS" at
  -- ORC3.EXAMPLE.COM
  --
  if (:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.EXAMPLE.COM(
      source_object_name => '"HR"."LOCATIONS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."REGIONS" at
  -- ORC3.EXAMPLE.COM
  --
  if (:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.EXAMPLE.COM(
      source_object_name => '"HR"."REGIONS"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/
 
-------------------------------
-- Setup Propagation
-------------------------------
 
--
-- Propagation from local queue to ORC1.EXAMPLE.COM
--
begin
  if :local_db != 'ORC1.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "COUNTRIES" from local queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_name => '&PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC1.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "DEPARTMENTS" from local queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."DEPARTMENTS"',
      streams_name => '&PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC1.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "EMPLOYEES" from local queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."EMPLOYEES"',
      streams_name => '&PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC1.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "JOBS" from local queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."JOBS"',
      streams_name => '&PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC1.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "JOB_HISTORY" from local queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."JOB_HISTORY"',
      streams_name => '&PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC1.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "LOCATIONS" from local queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_name => '&PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC1.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "REGIONS" from local queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."REGIONS"',
      streams_name => '&PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
--
-- Propagation from local queue to ORC2.EXAMPLE.COM
--
begin
  if :local_db != 'ORC2.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "COUNTRIES" from local queue to ORC2
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_name => '&PROP_ORC2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC2.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "DEPARTMENTS" from local queue to ORC2
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."DEPARTMENTS"',
      streams_name => '&PROP_ORC2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC2.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "EMPLOYEES" from local queue to ORC2
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."EMPLOYEES"',
      streams_name => '&PROP_ORC2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC2.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "JOBS" from local queue to ORC2
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."JOBS"',
      streams_name => '&PROP_ORC2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC2.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "JOB_HISTORY" from local queue to ORC2
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."JOB_HISTORY"',
      streams_name => '&PROP_ORC2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC2.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "LOCATIONS" from local queue to ORC2
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_name => '&PROP_ORC2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC2.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "REGIONS" from local queue to ORC2
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."REGIONS"',
      streams_name => '&PROP_ORC2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
--
-- Propagation from local queue to ORC3.EXAMPLE.COM
--
begin
  if :local_db != 'ORC3.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "COUNTRIES" from local queue to ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC3.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "DEPARTMENTS" from local queue to ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."DEPARTMENTS"',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC3.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "EMPLOYEES" from local queue to ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."EMPLOYEES"',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC3.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "JOBS" from local queue to ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."JOBS"',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC3.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "JOB_HISTORY" from local queue to ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."JOB_HISTORY"',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC3.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "LOCATIONS" from local queue to ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
begin
  if :local_db != 'ORC3.EXAMPLE.COM' then
    --
    -- HR_REPG: Propagate "REGIONS" from local queue to ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."REGIONS"',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.EXAMPLE.COM',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/
 
-------------------------------
-- Setup Capture
-------------------------------
begin
  --
  -- HR_REPG : Add "COUNTRIES"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."COUNTRIES"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/
 
begin
  --
  -- HR_REPG : Add "DEPARTMENTS"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."DEPARTMENTS"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/
 
begin
  --
  -- HR_REPG : Add "EMPLOYEES"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."EMPLOYEES"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/
 
begin
  --
  -- HR_REPG : Add "JOBS"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."JOBS"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/
 
begin
  --
  -- HR_REPG : Add "JOB_HISTORY"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."JOB_HISTORY"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/
 
begin
  --
  -- HR_REPG : Add "LOCATIONS"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."LOCATIONS"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/
 
begin
  --
  -- HR_REPG : Add "REGIONS"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."REGIONS"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/
 
-------------------------------
-- Setup Apply
-------------------------------
--
-- Setup Apply from ORC1.EXAMPLE.COM
--
 
begin
  --
  -- HR_REPG : Add "COUNTRIES" to apply rules for apply from
  -- ORC1.EXAMPLE.COM
  --
  if(:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "DEPARTMENTS" to apply rules for apply from
  -- ORC1.EXAMPLE.COM
  --
  if(:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."DEPARTMENTS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "EMPLOYEES" to apply rules for apply from
  -- ORC1.EXAMPLE.COM
  --
  if(:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."EMPLOYEES"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "JOBS" to apply rules for apply from
  -- ORC1.EXAMPLE.COM
  --
  if(:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOBS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "JOB_HISTORY" to apply rules for apply from
  -- ORC1.EXAMPLE.COM
  --
  if(:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOB_HISTORY"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "LOCATIONS" to apply rules for apply from
  -- ORC1.EXAMPLE.COM
  --
  if(:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "REGIONS" to apply rules for apply from
  -- ORC1.EXAMPLE.COM
  --
  if(:local_db != 'ORC1.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."REGIONS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.EXAMPLE.COM');
  end if;
end;
/
 
--
-- Setup Apply from ORC2.EXAMPLE.COM
--
 
begin
  --
  -- HR_REPG : Add "COUNTRIES" to apply rules for apply from
  -- ORC2.EXAMPLE.COM
  --
  if(:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "DEPARTMENTS" to apply rules for apply from
  -- ORC2.EXAMPLE.COM
  --
  if(:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."DEPARTMENTS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "EMPLOYEES" to apply rules for apply from
  -- ORC2.EXAMPLE.COM
  --
  if(:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."EMPLOYEES"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "JOBS" to apply rules for apply from
  -- ORC2.EXAMPLE.COM
  --
  if(:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOBS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "JOB_HISTORY" to apply rules for apply from
  -- ORC2.EXAMPLE.COM
  --
  if(:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOB_HISTORY"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "LOCATIONS" to apply rules for apply from
  -- ORC2.EXAMPLE.COM
  --
  if(:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "REGIONS" to apply rules for apply from
  -- ORC2.EXAMPLE.COM
  --
  if(:local_db != 'ORC2.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."REGIONS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.EXAMPLE.COM');
  end if;
end;
/
 
--
-- Setup Apply from ORC3.EXAMPLE.COM
--
 
begin
  --
  -- HR_REPG : Add "COUNTRIES" to apply rules for apply from
  -- ORC3.EXAMPLE.COM
  --
  if(:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "DEPARTMENTS" to apply rules for apply from
  -- ORC3.EXAMPLE.COM
  --
  if(:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."DEPARTMENTS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "EMPLOYEES" to apply rules for apply from
  -- ORC3.EXAMPLE.COM
  --
  if(:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."EMPLOYEES"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "JOBS" to apply rules for apply from
  -- ORC3.EXAMPLE.COM
  --
  if(:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOBS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "JOB_HISTORY" to apply rules for apply from
  -- ORC3.EXAMPLE.COM
  --
  if(:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOB_HISTORY"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "LOCATIONS" to apply rules for apply from
  -- ORC3.EXAMPLE.COM
  --
  if(:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.EXAMPLE.COM');
  end if;
end;
/
 
begin
  --
  -- HR_REPG : Add "REGIONS" to apply rules for apply from
  -- ORC3.EXAMPLE.COM
  --
  if(:local_db != 'ORC3.EXAMPLE.COM') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."REGIONS"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.EXAMPLE.COM');
  end if;
end;
/
 
-------------------------------
-- Add Supplemental Log Groups
-------------------------------
--
-- ** NOTE ** --
-- The primary key columns must be supplementally logged.
--
alter database add supplemental log data (primary key) columns;
 
--
-- ** NOTE ** --
-- The unique key columns must be supplementally logged.
--
alter database add supplemental log data (unique index) columns;
 
--
-- ** NOTE ** --
-- All the columns in a column group that is assigned an Oracle Streams
-- supported update conflict handler must be supplementally logged.
--
 
-- Supplementally log columns in column group 'COUNTRIES_TIMESTAMP_CG'
-- that is assigned the LATEST TIMESTAMP update conflict resolution method.
alter table "HR"."COUNTRIES" add supplemental log group COUNTRIES_LogGrp1 (
"COUNTRY_NAME"
,"REGION_ID"
,"TIMESTAMP"
);
 
-------------------------------
-- Setup Conflict Resolution
-------------------------------
--
-- ** WARNING ** --
-- Oracle Streams does not support LATEST TIMESTAMP
-- conflict resolution method.
-- Changing LATEST TIMESTAMP to MAXIMUM as
-- they handle the conflicts in a similar manner.
--
declare
  cols dbms_utility.name_array;
begin
  cols(1) := 'COUNTRY_NAME';
  cols(2) := 'REGION_ID';
  cols(3) := 'TIMESTAMP';
  dbms_apply_adm.set_update_conflict_handler(
    object_name => 'HR.COUNTRIES',
    method_name => 'MAXIMUM',
    resolution_column => 'TIMESTAMP',
    column_list => cols);
end;
/
 
-------------------------------
-- Verify Oracle Streams Setup
-------------------------------
 
-- Verify creation of queues
select * from dba_queues
 where name = upper(:local_queue)
   and owner = upper(:local_queue_owner)
   and queue_table = upper(:local_queue_table)
 order by name;
 
-- Verify creation of capture_process
select * from dba_capture
 where capture_name = upper('&CAPTURE_NAME');
 
-- Verify creation of apply processes
select * from dba_apply
 where apply_name IN (
       upper('&APPLY_ORC1'),
       upper('&APPLY_ORC2'),
       upper('&APPLY_ORC3') )
 order by apply_name;
 
-- Verify propagation processes
select * from dba_propagation
 where propagation_name IN (
       upper('&PROP_ORC1'),
       upper('&PROP_ORC2'),
       upper('&PROP_ORC3') )
 order by propagation_name;
 
-- Verify Oracle Streams rules
select * from dba_streams_table_rules
 where streams_name = upper('&CAPTURE_NAME');
 
select * from dba_streams_table_rules
 where streams_name IN (
       upper('&APPLY_ORC1'),
       upper('&APPLY_ORC2'),
       upper('&APPLY_ORC3') )
 order by source_database;
 
select * from dba_streams_table_rules
 where streams_name IN (
       upper('&PROP_ORC1'),
       upper('&PROP_ORC2'),
       upper('&PROP_ORC3') )
 order by source_database;
 
-- Do not resume Repcat activity once Oracle Streams is set up.
-- Drop all the repgroups that have been migrated to Oracle Streams.
-- Start apply and capture processes at all sites.