A
Migrating Advanced Replication to Streams

Database administrators who have been using Advanced Replicatio n to maintain replicated database objects at different sites can migrate their Advanced Replication environment to a Streams environm ent. This chapter provides a conceptual overview of the steps in this process and documents each step with procedures and examples.

This chapter contains these topics:

Overview of the Migration Process
Preparing to Generate the Migration Script
Generating and Modifying the Migration Script
Performing the Migration for Advanced Replication to Streams
Recreating Master Sites to Retain Materialized View Groups
Example Advanced Replication to Streams Migration Script

See Also:
Oracle Dat abase Advanced Replication and Oracle Da tabase Advanced Replication Management API Reference for more information about Advanced Replication

Overview of the Migration Process

The following sections provide a co nceptual overview of the migration process:

Migration Script Generation and Use

You can use the procedure DBMS_REPCAT.STREAMS_MIGRATION to generate a SQL*Plus script that migrates an existing Adv anced Replication environment to a Streams environment. When you run the DBMS_REPCAT.STREAMS_MIGRATION procedure at a ma ster definition site in a multimaster replication environment, it generates a SQL*Plus script in a file at a location that you specif y. Once the script is generated, you run it at each master site in your Advanced Replication environment to set up a Streams environm ent for each master site. To successfully generate the Streams environment for your replication groups, the replication groups for wh ich you run the script must have exactly the same master sites. If replication groups have different master sites, then you can gener ate multiple scripts to migrate each replication group to Streams.

At times, you must stop, or quiesce, all replication activity for a replication group so that you can perform certain administrative tasks. You do not need to quiesce the replication groups when you run the DBMS_REPCAT.STREAMS_MIGRATION procedure. However, you must quiesce the replication groups being migrated to Streams when you run the generated script at the master sites. Because you have queisced the rep lication groups to run the script at the master sites, you do not have to stop any existing capture processes, propagation jobs, or a pply processes at these sites.

Modification of the Migration Script

The generate d migration script uses comments to indicate Advanced Replication elements that cannot be converted to Streams. It also provides sugg estions for modifying the script to convert these elements to Streams. You can use these suggestions to edit the script before you ru n it. You also can customize the migration script in other ways to meet your needs.

The scri pt sets all parameters when it runs PL/SQL procedures and functions. When you generate the script, it sets default values for paramet ers that typically do not need to be changed. However, you can change these default parameters by editing the script if necessary. Th e parameters with default settings include the following:

include_dml
include_ddl
< a name="634853">include_tagged_lcr

The beginning of the script has a list of variables for names that are used by the procedures and functions in the script. When you generate the script, it sets these variables to default values that you should not need to change. However, you can change the default settings for these variables if n ecessary. The variables specify names of queues, capture processes, propagations, and apply processes.

Actions Performed by the Generated Script

The migration script performs the following actions:

Prints warnings in comments if the replication groups contain features that ca nnot be converted to Streams.
Creates a SYS.AnyData queue, if needed, using the DBMS_STREAMS_ADM.SET_UP_QUEUE procedure.
Configures propagation b etween all master sites using the DBMS_STREAMS_ADMIN.ADD_TABLE_PROPAGATION_RULES procedure for each table.
Configures capture at each master site using the DBMS_STREAMS_ADMIN.ADD_TABLE_RULES procedure for each table.


Configures apply for changes from all the other
master sites using the DBMS_STREAMS_ADMIN.ADD_TABLE_RULES procedure for each table.
Sets the instantiation SCN for each replicated object at each site where changes to the object are applied.
Creates the necessary supplemental log groups at source databases.
Sets key columns, if any.
Configures conflic
t resolution if it was configured for the Advanced Replication environment being migrated.


 
Migration Script Errors

 
If Oracle encounters an error while running the migration script, then the migration script
exits immediately. If this happens, then you must modify the script to run any commands that have not already been executed successfu
lly.
 
Manual
 Migration of Updatable Materialized Views

 
You cannot migrate updatab
le materialized views using the migration script. You must migrate updatable materialized views from an Advanced Replication environm
ent to a Streams environment manually.
 



See Also:  
"Recreating Master Sites to Retain Mate
rialized View Groups"
 
Advanced Replication Elements That Cannot Be Migrated to Str
eams

 
Streams does not support the following:

Replication of changes to tables with columns of the following datatypes: BFILE, ROWID, UROWID, and user-defined types (including object types, REFs, varrays, and neste
d tables)

Synchronous replication

 
If your current Advanced Replication environment uses these features, then these elements of the environment cannot be migrated to
Streams. In this case, you may decide not to migrate the environment to Streams at this time, or you may decide to modify the environ
ment so that it can be migrated to Streams.
 
Preparing to Generate the Migration Script

 
Before generating the migration script, make sure 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.
The other master sites that run the script, but do not generate the script, must be
running Oracle9i release 2 (9.2) or higher.
 
Generating and Modifying the Migration Script

Example Advanced Replication Environment to be Migrated to Streams

 
Figure A-1 shows the Advanced Replication environme
nt that will be migrated to Streams in this example.
 
Figure A-1 Advanced Replication Environment to be Migrated to Streams
 
Text description of the illustration strep
101.gif
 
This Advanced Replication environment has the following characteristics:

The orc1.world database is the master definition site for
 a three-way master configuration that also includes orc2.world and orc3.world.
The orc1.world database is the master site for the mv1.world materialized view s
ite.
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 timestamp conflict resolution metho
d resolves conflicts on this table.
The materialized views at the mv1.wor
ld 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.world only).
Create the hr_repg mast
er group for the three master sites with orc1.world as the master definition site.

Configure timestamp conflict r
esolution for the hr.countries table.
Create the materialized view group at mv1.world based on the <
code>hr_repg master group at orc1.world.
 
To generate the migra
tion script for this Advanced Replication environment, complete the following steps:

Create the Streams administrator at all master sites.
Make a directory location accessible.
Generate the migration script.
Verify the generated migration script creation and modify script.
 
Step 1 Create the Streams administrator at all master sites.

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

<
a name="673204">Connect as an administrative user who can create users, grant privileges, and create tablespaces.
Either create a tablespace for the Streams administrator or use an existing tablespace.
 For example, the following statement creates a new tablespace for the Streams administrator: 

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



Create a new user to act as t
he Streams administrator or use an existing user. For example, to create a new user named strmadmin and specify that thi
s user uses the streams_tbs tablespace, run the following statement: 
CREATE USER
strmadmin IDENTIFIED BY strmadminpw
   DEFAULT TABLESPACE streams_tbs
   QUOTA UNLIMITED ON
 streams_tbs;

GRANT CONNECT, RESOURCE, DBA TO strmadmin;









Note:  

To ensure security, use a password other than str
madminpw for the Streams administrator.
The migration script assumes that
 the username of the Streams administrator is strmadmin. If your Streams administrator has a different username, then ed
it the migration script to replace all instances of strmadmin with the username of your Streams administrator.
Make sure you grant DBA role to the Streams administrator.

Grant any additional privileges required by the St
reams administrator. The necessary privileges depend on your specific Streams environment. For the example environment described in <
a href="rep2strm.htm#636972">"Example Advanced Replication Environment to be Migrated to Streams", the Streams administrator must be able to create supplemental log groups for the tables in the hr schema. Therefore,
 grant the Streams administrator all privileges on these tables: 
GRANT ALL ON hr.countries TO
strmadmin;
GRANT ALL ON hr.departments TO strmadmin;
GRANT ALL ON hr.employees TO strmadmin
;
GRANT ALL ON hr.jobs TO strmadmin;
GRANT ALL ON hr.job_history TO strmadmin;
GRANT ALL ON hr.locations TO strmadmin;
GRANT ALL ON hr.regions TO strmadmin;




Make sure you complete all of these steps at each master site.
 

Step 2 Make a directory location accessi
ble.
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 Streams administrator at the master definition site currently, then connect as the Streams administrator, and create a direct
ory object using the SQL statement CREATE DIRECTORY.
A directory o
bject 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:
CONNE
CT strmadmin/strmadminpw@orc1.world

CREATE DIRECTORY MIG2STR_DIR AS '/usr/scripts';




See Also:  
Oracle Streams Concepts an
d Administration for information about addition privileges that may be required for a Streams administrator
 

 

 






See Also:<
/font>  
Oracle Database SQL Reference for more information about the CREATE DIRECTORY statement
 
Step 3 Gene
rate the migration script.
 
To generate the migration script, run the DBMS_REPC
AT.STREAMS_MIGRATION procedure at the master definition site and specify the appropriate parameters. For example, the followin
g 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 com
puter system. This directory is represented by the directory object MIG2STR_DIR.
CONNECT strmadmin/strmadminpw@orc1.world

DECLARE
  rep_groups DBMS_
UTILITY.NAME_ARRAY;
  BEGIN
    rep_groups(1) := 'HR_REPG';
    DBMS_R
EPCAT.STREAMS_MIGRATION(
      gnames         =>  rep_groups,
      file_location  =>
  'MIG2STR_DIR',
      filename       =>  'rep2streams.sql');
END;

/






See Also:  
"Example Advanced Replication to 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 A
dvanced Replication conflict resolution methods, then configure user-defined conflict resolution methods to resolve conflicts. Stream
s does not provide built-in conflict resolution methods that are equivalent to these methods. 

However, the migration script supports the following conflict resolution methods automatically: overwrite, discard, maximum, an
d minimum. The script converts an earliest timestamp method to a minimum method automatically, and it converts a latest timestamp met
hod to a maximum method automatically. If you use a timestamp conflict resolution method, then the script assumes that any triggers n
ecessary to populate the timestamp 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. Streams allows
 only one conflict resolution method to be specified for each column list.
Proc
edural replication
Replication of data definition language (DDL) changes for no
n-table 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 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.world dat
abase 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       => 'stre
ams_capture',
    queue_name         => 'strmadmin.streams_queue',
    include_dml
  => false,
    include_ddl        => true,
    include_tagged_lcr => false,
    source_database    => 'orc1.world');
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.
 
Performing the Migration
 for Advanced Replication to Streams

 
This section explains how to per
form the migration from an Advanced Replication environment to a Streams environment.
 
This s
ection contains the following topics:

Before Executing the Migration Script
Ex
ecuting the Migration Script
After Executing
the Script
Recreating Master Sites to Retain
Materialized View Groups
 
Before Executing the Migration Script

 
Complet
e the following steps before executing the migration script:

Set initialization parameters that are relevant to Streams.
Enable archive logging at all sites.
Set up database links, if needed, for queue to queue propagation.
Quiesce each replication group that you are migrating to Streams.
 
Step 1 Set initialization parameters that are rel
evant to Streams.
 
At each replication database, set initialization parameters that a
re relevant to Streams and restart the database if necessary.
 



See Also:  
Oracle Streams Concepts and Administration for information about init
ialization parameters that are important to Streams
 
Step 2 Enable archive logging at all sites.
 
Make sure ea
ch master site is running in ARCHIVELOG mode, because a capture process requires ARCHIVELOG mode. In the ex
ample environment, orc1.world, orc2.world, and orc3.world must be running in ARCHIVELOG<
/code> mode. You can check the log mode for a database by querying the LOG_MODE column in the V$DATABASE dy
namic performance view.

 



See Also:  
Oracle Database Administrator's Guide for information about running a database in ARCHIVELOG m
ode
 
Step 3 Set up databa
se links, if needed, for queue to queue propagation.
 
Create a database link from the
 Streams administrator at each master site to the Streams administrator at the other master sites. For the example environment descri
bed in "Example Advanced Replication Environment to be Migrated to Streams", create the following database links:
CONNECT strmadmin/strmadminpw@orc1.world

CREATE DATABASE LINK orc2.world CONNECT TO strmadmin 
   IDENTIFIED BY
strmadminpw USING 'orc2.world';

CREATE DATABASE LINK orc3.world CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'orc3.world';


CO
NNECT strmadmin/strmadminpw@orc2.world

CREATE DATABASE LINK orc1.world CONNECT TO strmadmi
n 
   IDENTIFIED BY strmadminpw USING 'orc1.world';

CREATE DATABASE L
INK orc3.world CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'orc3.world';


CONNECT strmadmin/strmadminpw@orc3.world

CREATE DAT
ABASE LINK orc1.world CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING 'orc1.world';

CREATE DATABASE LINK orc2.world CONNECT TO strmadmin 
   IDENTIFIED BY strmadminpw USING
 'orc2.world';



Step 4 Quiesce each replicatio
n group that you are migrating to Streams.
 
Run the DBMS_REPCAT.SUSPEND_MASTER_
ACTIVITY procedure at the master definition site for each replication group that you are migrating to Streams.
 
In the example environment, orc1.world is the master definition site, and hr_repg
 is the replication group being migrated to Streams. So, connect to orc1.world as the replication administrator and run
the SUSPEND_MASTER_ACTIVITY procedure:
CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (

  gname => 'hr_repg');
END;
/




Do not proceed until the master group is quiesced. You can check the status of a master group by querying the STATUS column in the DBA_REPGROUP data dictionary view.

 
Executing the Migration Script

 
Perform the following steps to migrate:

Connect as the Streams administrator and run the script at each site.
<
a name="637737">Verify that Streams configuration completed successfully at all sites.
 
Step 1 Connect as the Streams administrator and ru
n the script at each site.
 
In the example environment, connect in SQL*Plus as the St
reams administrator strmadmin in SQL*Plus at orc1.world, orc2.world, and orc3.world and execute the migration script rep2streams.sql:

CONNECT strmadmin/strmadmi
npw@orc1.world
SET ECHO ON
SPOOL rep2streams.out
@rep2streams.sql

CONNECT strmadmin/strmadminpw@orc2.world
SET ECHO ON
SPOOL rep2streams.out
@rep2streams.sql

CONNECT strmadmin/strmadminpw
@orc3.world
SET ECHO ON
SPOOL rep2streams.out
@rep2streams.sql



Step 2 Verify that Streams configuration completed s
uccessfully at all sites.
 
Check the spool file at each site to make sure there are n
o errors. If there are errors, then you should modify the script to execute the steps that were not completed successfully, and then
rerun the script. In the example environment, the spool file is rep2streams.out at each master site.
 
After Executing the Script


 
Perform the following steps to complete the migration process:

Drop replication groups you migrated at ea
ch site.
Start the apply processes at each si
te.
Start the capture process at each site.

 
Step 1 Drop replication groups you migr
ated at each site.
 
To drop a replication group that you successfully migrated to Str
eams, connect as the replication administrator to the master definition site, and run the DBMS_REPCAT.DROP_MASTER_REPGROUP procedure.

 



Attention
:  
Make sure the drop_contents parameter is set to fa
lse in the DROP_MASTER_REPGROUP procedure. If it is set to true, then the replicated database objects are dropped
.



CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.DROP_MASTER_REPGROUP (
     gname         => 'hr
_repg',
     drop_contents => false,
     all_sites     => true);
END;
/




To make sure the migrated replication
 groups are dropped at each database, query the GNAME column in the DBA_REPGROUP data dictionary view. The
migrated replication groups should not appear in the query output at any database.
 
If you no
 longer need the replication administrator, then you may drop this user also.
 





Caution:  
Do not resume any Advanced Replication activity once Streams is set up.

 
Step 2 Start the apply processes at each site.
 
You can view the names of the apply processes at each site by running the following query while connected
 as the Streams administrator:
SELECT APPLY_NAME FROM DBA_APPLY;




When you know the names of the apply processes, you can start each one by running the ST
ART_APPLY procedure in the DBMS_APPLY_ADM package while connected as the Streams administrator. For example, the
following procedure starts an apply process named apply_from_orc2 at orc1.world:
CONNECT strmadmin/strmadminpw@orc1.world

BEGIN
  DBMS_
APPLY_ADM.START_APPLY(
    apply_name => 'apply_from_orc2');
END;
/





Make sure you start each apply process at every database in the ne
w Streams environment.
 
Step 3 Start the capture
process at each site.
 
You can view the name of the capture process at each site by r
unning the following query while connected as the Streams administrator:
SELECT CAPTURE_NAME
 FROM DBA_CAPTURE;




When you know the name of the capture process, y
ou can start each one by running the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package while connecte
d as the Streams administrator. For example, the following procedure starts a capture process named streams_capture at <
code>orc1.world:
CONNECT strmadmin/strmadminpw@orc1.world

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




Make sure
you start each capture process at every database in the new Streams environment.
 
Recreating Master Sites to Retain Materialized View Groups


 
If one or more materialized view groups used a master group that you migr
ated to Streams, then you must re-create the master group to retain these materialized view groups. Therefore, each database acting a
s the master site for a materialized view group must become the master definition site for a one-master configuration of a replicatio
n 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
Streams. That is, the new replication group should have the same replication group name, objects, conflict resolution methods, and ke
y 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 sup
port for the master group, and resume replication activity for the master group.
 
For example
, consider the following Advanced Replication environment:

Two
master sites, mdb1.net and mdb2.net, have the replication group rg1. The mdb1.net
 database is the master definition site, and the objects in the rg1 replication group are replicated between mdb1.
net and mdb2.net.
The rg1 replication group at
 mdb1.net is the master group to the mvg1 materialized view group at mv1.net.
The rg1 replication group at mdb2.net is the master group to the mvg2 materialized view group at mv2.net.
 
If the rg1
 replication group is migrated to Streams at both mdb1.net and mdb2.net, and you want to retain the materia
lized view groups mvg1 at mv1.net and mvg2 at mv2.net, then you need to re-create
 the rg1 replication group at mdb1.net and mdb2.net after the migration to Streams. You config
ure both mdb1.net and mdb2.net 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 grou
ps at the materialized view sites. As long as a new master replication group resembles the original replication group, the materializ
ed view groups are not affected. Do not refresh these materialized view groups until generation of replication support for each maste
r object is complete (Step 3 in the task in this section). Similarly, do not push the deferred tran
saction queue at any materialized view site with updatable materialized views until generation of replication support for each master
 object is complete.
 
For the example environment described in 
"Example Advanced Replication Environment to be Migrated to Streams", only the hr_repg replication group at orc1.world was the master group to a materialized view group at mv1.world. To ret
ain this materialized view group at mv1.world, complete the following steps while connected as the replication administr
ator:


Create the master group hr_repg at orc1.world. 
CONNECT repadmin/repadmin@orc1.world

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



Add the tables in the hr schema to the hr_repg master group. These tables are master tables to the materialize
d views at mv1.world. 
BEGIN
   DBMS_REPCAT.CREATE_MASTER_REP
OBJECT (
      gname               => 'hr_repg',
      type                => 'TABLE'
,
      oname               => 'countries',
      sname               => 'hr',
      use_existing_object => true,
      copy_rows           => false);
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPO
BJECT (
      gname               => 'hr_repg',
      type                => 'TABLE',

      oname               => 'departments',
      sname               => 'hr',
      use_existing_object => true,
      copy_rows           => false);
<
/a>END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REP
OBJECT (
      gname               => 'hr_repg',
      type                => 'TABLE'
,
      oname               => 'employees',
      sname               => 'hr',
      use_existing_object => true,
      copy_rows           => false);
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPO
BJECT (
      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;
/



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

    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname             => 'hr',
      on
ame             => 'countries', 
      type              => 'TABLE'); 
END;
/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
<
a name="638360">      sname             => 'hr',
      oname             => 'departments', 
      type              => 'TABLE'); 
END;
/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname             =&
gt; '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;
/

BEG
IN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname             => 'hr',
      oname             => 'locations', 
      type              => 'TABLE'); 
END;
/

BEGIN 
    DBMS_REPCAT.GENERAT
E_REPLICATION_SUPPORT (
      sname             => 'hr',
      oname             => '
regions', 
      type              => 'TABLE'); 
END;
/



Resume master activity for the hr_repg m
aster group. 
BEGIN 
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'hr_repg'); 
END;
/




Example Advanced Replication to Streams Migration Script
The foll
owing is an example script generated for the environment:
----------------------------------
------------------------
-- Migration Script Generated on 15-OCT-03 by user STRMADMIN. --
-
---------------------------------------------------------

--------------------------------
--------------------------
--  ************** Notes and Assumptions ************** --
--
-- 1. The Streams Administrator is "strmadmin".
--    The user "strmadmin" must be created an
d granted the
--    required privileges before running the script.
--

-- 2. Names of queue tables, queues, capture processes
--    propagation jobs, and apply processes will be the
<
a name="694268">--    same at all sites. If the DBA wants different names,
--    he must edit the script man
ually before running it
--    at each master site.
--
-- 3. Archive lo
gging 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
-------------------------------
-- st
reams queue owner at ORC1.WORLD
define QUEUE_OWNER_ORC1 = strmadmin

-
- streams queue owner at ORC2.WORLD
define QUEUE_OWNER_ORC2 = strmadmin

<
/a>-- streams queue owner at ORC3.WORLD
define QUEUE_OWNER_ORC3 = strmadmin

-------------------------------
-- Queue Table
-------------------------------
-- streams queue table at ORC1.WORLD
define QUEUE_TABLE_ORC1 = streams_queue_table

-- streams queue table at ORC2.WORLD
define QUEUE_TABLE_ORC2 = streams_queue_table


-- streams queue table at ORC3.WORLD
define QUEUE_TABLE_ORC3 = strea
ms_queue_table

-------------------------------
-- Queue
-------------------------------
-- streams queue at ORC1.WORLD
define QUEUE_ORC1 =
streams_queue

-- streams queue at ORC2.WORLD
define QUEUE_ORC2 = stre
ams_queue

-- streams queue at ORC3.WORLD
define QUEUE_ORC3 = streams_
queue

-------------------------------
-- Propagation names
-------------------------------
-- propagation process to ORC1.WORLD
define PROP
_ORC1 = prop_to_ORC1

-- propagation process to ORC2.WORLD
define PROP
_ORC2 = prop_to_ORC2

-- propagation process to ORC3.WORLD
define PROP
_ORC3 = prop_to_ORC3

-------------------------------
-- Capture Proce
ss
-------------------------------
-- capture process to be used or created at the local si
te
define CAPTURE_NAME = streams_capture

----------------------------
---
-- Apply processes
-------------------------------
-- apply proces
s for applying LCRs from ORC1.WORLD
define APPLY_ORC1 = apply_from_ORC1

<
/a>-- apply process for applying LCRs from ORC2.WORLD
define APPLY_ORC2 = apply_from_ORC2

-- apply process for applying LCRs from ORC3.WORLD
define APPLY_ORC3 = apply_from_ORC3

--
-- ** WARNING ** --
-- Streams does not support t
he repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user c
an add DDL rules to the Streams environment
-- to support creation or any future modifications

-- of this type of object.
--

--
-- ** WARNI
NG ** --
-- Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belon
ging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to su
pport creation or any future modifications
-- of this type of object.
--
<
/a>
--
-- ** WARNING ** --
-- Streams does not support the repobject
<
a name="694392">-- EMP_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to
the Streams environment
-- to support creation or any future modifications
-- of this type
of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REP
G.
-- The user can add DDL rules to the Streams environment
-- to support creation or any f
uture modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
--
INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environm
ent
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any future modificat
ions
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not s
upport the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The us
er can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** W
ARNING ** --
-- Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belo
nging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to s
upport creation or any future modifications
-- of this type of object.
--


--
-- ** WARNING ** --
-- Streams does not support the repobject
-- DEPT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL ru
les to the Streams environment
-- to support creation or any future modifications
-- of thi
s type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgr
oup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creatio
n or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Streams e
nvironment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
--
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 Streams environment
-- to support creation or any future modific
ations
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- INSERT_TIME o
f type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
-
-

--
-- ** WARNING ** --
-- Streams does not sup
port the repobject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- T
he user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
--
 ** WARNING ** --
-- Streams does not support the repobject
-- JHIST_EMPLOYEE_IX of type IN
DEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the re
pobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DD
L rules to the Streams environment
-- to support creation or any future modifications
-- of
 this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repg
roup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creati
on or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 St
reams environment
-- to support creation or any future modifications
-- of this type of obj
ect.
--

--
-- ** WARNING ** --

-- 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 Streams environment
-- to support creation or any futu
re modifications
-- of this type of object.
--

-
-
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--

-- ** WARNING ** --
-- Streams does not support the repobject
-- INSERT_TIME of type TRIGGE
R belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-
- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repo
bject
-- JHIST_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can a
dd DDL rules to the Streams environment
-- to support creation or any future modifications

-- of this type of object.
--

--
-- ** WARNING *
* --
-- 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 Streams environment
-- to suppor
t creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to th
e Streams environment
-- to support creation or any future modifications
-- of this type of
 object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any fut
ure modifications
-- of this type of object.
--


--
-- ** WARNING ** --
-- Streams does not support the repobject
-- DE
PT_LOCATION_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environ
ment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Stream
s does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modificati
ons
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- EMP_JOB_IX of ty
pe INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support t
he repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can
 add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING
 ** --
-- Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging t
o repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support
creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any future modifications
-- of this ty
pe of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or
 any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
<
/a>-- JHIST_JOB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams env
ironment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- St
reams does not support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modificat
ions
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- DEPT_LOCATION_I
X of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.

--

--
-- ** WARNING ** --
-- Streams does not su
pport the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- Th
e user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
--
** WARNING ** --
-- Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belo
nging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to s
upport creation or any future modifications
-- of this type of object.
--


--
-- ** WARNING ** --
-- Streams does not support the repobject
-- EMP_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rule
s to the Streams environment
-- to support creation or any future modifications
-- of this
type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR
_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or a
ny future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Stream
s environment
-- to support creation or any future modifications
-- of this type of object.

--

--
-- ** WARNING ** --

-- 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 Streams environment
-- to support creation or any future m
odifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- JHIST_J
OB_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.

--

--
-- ** WARNING ** --
-- Streams does no
t support the repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- T
he user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
--
 ** WARNING ** --
-- Streams does not support the repobject
-- DEPT_LOCATION_IX of type IND
EX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment

-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the rep
object
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can ad
d DDL rules to the Streams environment
-- to support creation or any future modifications
-
- of this type of object.
--

--
-- ** WARNING **
 --
-- Streams does not support the repobject
-- EMP_JOB_IX of type INDEX belonging to repg
roup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creati
on or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Stre
ams environment
-- to support creation or any future modifications
-- of this type of objec
t.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future mod
ifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the repobject
-- JHIST_DEP
ARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment

-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams do
es not support the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.

-- The user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment

-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams does not support the
 repobject
-- LOC_COUNTRY_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can a
dd DDL rules to the Streams environment
-- to support creation or any future modifications

-- of this type of object.
--

--
-- ** WARNING *
* --
-- 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 Streams environment
-- to support
 creation or any future modifications
-- of this type of object.
--

<
a name="694866">--
-- ** WARNING ** --
-- Streams does not support the repobject
-- EMP_DEPARTMENT_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules t
o the Streams environment
-- to support creation or any future modifications
-- of this typ
e of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any fut
ure modifications
-- of this type of object.
--


--
-- ** WARNING ** --
-- Streams does not support the repobject
-- EM
P_MANAGER_IX of type INDEX belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environme
nt
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- Streams
does not support the repobject
-- INSERT_TIME of type TRIGGER belonging to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--

-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any future modifications
-- of this type of object.
--
<
a name="694910">
--
-- ** WARNING ** --
-- Streams does not suppor
t the repobject
-- JHIST_EMPLOYEE_IX of type INDEX belonging to repgroup HR_REPG.
-- The us
er can add DDL rules to the Streams environment
-- to support creation or any future modifications
-- of this type of object.
--

--
-- ** W
ARNING ** --
-- Streams does not support the repobject
-- JHIST_JOB_IX of type INDEX belong
ing to repgroup HR_REPG.
-- The user can add DDL rules to the Streams environment
-- to sup
port creation or any future modifications
-- of this type of object.
--

--
-- ** WARNING ** --
-- 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 Streams environment
-- to support creation or any future modifications
-- of this ty
pe of object.
--

-------------------------------
-- Setup Queue
-------------------------------

variable local_db
      varchar2(128);
variable local_queue_table varchar2(30);
variable local_queue       va
rchar2(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 i
s: ' || :local_db);
end;
/

-- get the local queu
e table and queue name
begin
  if :local_db = 'ORC1.WORLD' then
    :l
ocal_queue_table := '&QUEUE_TABLE_ORC1';
    :local_queue := '&QUEUE_ORC1';
    :lo
cal_queue_owner := '&QUEUE_OWNER_ORC1';

  elsif :local_db = 'ORC2.WORLD' then
    :local_queue_table := '&QUEUE_TABLE_ORC2';
    :local_queue := '&QUEUE_ORC2';
    :local_queue_owner := '&QUEUE_OWNER_ORC2';

  elsif :local_db = 'ORC3.W
ORLD' then
    :local_queue_table := '&QUEUE_TABLE_ORC3';
    :local_queue := '&QUE
UE_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_us
er => :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.WORLD
--
begin
  --
  -- HR_REPG : Set insta
ntiation SCN for "HR"."COUNTRIES" at
  -- ORC1.WORLD
  --
  if (:local
_db != 'ORC1.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.WORLD(

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.WORLD
  --
  if (:local_db != 'ORC1.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn
@ORC1.WORLD(
      source_object_name => '"HR"."DEPARTMENTS"',
      source_database_nam
e => :local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link
=> NULL);
  end if;
end;
/

<
/a>begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."EMPLOYEES" at
  -- ORC1.WORLD
  --
  if (:local_db != 'ORC1.WORLD') then
    db
ms_apply_adm.set_table_instantiation_scn@ORC1.WORLD(
      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 fo
r "HR"."JOBS" at
  -- ORC1.WORLD
  --
  if (:local_db != 'ORC1.WORLD')
 then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.WORLD(
      source_object_name =
> '"HR"."JOBS"',
      source_database_name => :local_db,
      instantiation_scn =&g
t; :flashback_scn,
      apply_database_link => NULL);
  end if;
en
d;
/

begin
  --
  -- HR_REP
G : Set instantiation SCN for "HR"."JOB_HISTORY" at
  -- ORC1.WORLD
  --
<
/a>  if (:local_db != 'ORC1.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.WORLD(
      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.WORLD
  --
  if (:local_db != 'ORC1.WORLD') then
    dbms_apply_adm.set_table_in
stantiation_scn@ORC1.WORLD(
      source_object_name => '"HR"."LOCATIONS"',
      source
_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_d
atabase_link => NULL);
  end if;
end;
/

begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."REGIONS" at
  -- ORC1.WORLD
  --
  if (:local_db != 'ORC1.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC1.WORLD(
      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.WORLD

--
begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."COUNT
RIES" at
  -- ORC2.WORLD
  --
  if (:local_db != 'ORC2.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.WORLD(
      source_object_name => '"H
R"."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.WORLD
  --

  if (:local_db != 'ORC2.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.WORLD(
      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.WORLD
  --
  if (:local_db != 'ORC2.WORLD') then
    dbms_apply_adm.set_table_insta
ntiation_scn@ORC2.WORLD(
      source_object_name => '"HR"."EMPLOYEES"',
      source_da
tabase_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_data
base_link => NULL);
  end if;
end;
/

begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOBS" at
  -- ORC2.WORLD
  --
  if (:local_db != 'ORC2.WORLD') then

   dbms_apply_adm.set_table_instantiation_scn@ORC2.WORLD(
      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 fo
r "HR"."JOB_HISTORY" at
  -- ORC2.WORLD
  --
  if (:local_db != 'ORC2.
WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.WORLD(
      source_object
_name => '"HR"."JOB_HISTORY"',
      source_database_name => :local_db,
      instant
iation_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/

begin
  --
<
/a>  -- HR_REPG : Set instantiation SCN for "HR"."LOCATIONS" at
  -- ORC2.WORLD
  --
  if (:local_db != 'ORC2.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC2.WORLD(
      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.WO
RLD
  --
  if (:local_db != 'ORC2.WORLD') then
    dbms_apply_adm.set_
table_instantiation_scn@ORC2.WORLD(
      source_object_name => '"HR"."REGIONS"',

source_database_name => :local_db,
      instantiation_scn => :flashback_scn,
      a
pply_database_link => NULL);
  end if;
end;
/


--
-- Setup instantiation SCN for ORC3.WORLD
--

begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."COUNTRIES" at
  -- ORC3.WORLD
  --
  if (:local_db != 'ORC3.WORLD') then
    dbms_
apply_adm.set_table_instantiation_scn@ORC3.WORLD(
      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.WORLD
  --
  if (:local_db != 'ORC3.WOR
LD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.WORLD(
      source_object_na
me => '"HR"."DEPARTMENTS"',
      source_database_name => :local_db,
      instantiat
ion_scn => :flashback_scn,
      apply_database_link => NULL);
  end if;
end;
/

begin
  --

  -- HR_REPG : Set instantiation SCN for "HR"."EMPLOYEES" at
  -- ORC3.WORLD
  --
  if (:local_db != 'ORC3.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.WORLD(
      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.WORLD
  --
  if (:local_db != 'ORC3.WORLD') then
    dbms_apply_adm.set_table_
instantiation_scn@ORC3.WORLD(
      source_object_name => '"HR"."JOBS"',
      source_da
tabase_name => :local_db,
      instantiation_scn => :flashback_scn,
      apply_data
base_link => NULL);
  end if;
end;
/

begin
  --
  -- HR_REPG : Set instantiation SCN for "HR"."JOB_HISTORY" at
  -- ORC3.WORLD
  --
  if (:local_db != 'ORC3.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.WORLD(
      source_object_name => '"HR"."JOB_HISTO
RY"',
      source_database_name => :local_db,
      instantiation_scn => :flashback_
scn,
      apply_database_link => NULL);
  end if;
end;
/

begin
  --
  -- HR_REPG : Set instan
tiation SCN for "HR"."LOCATIONS" at
  -- ORC3.WORLD
  --
  if (:local_
db != 'ORC3.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3.WORLD(
      s
ource_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.WORLD
  -
-
  if (:local_db != 'ORC3.WORLD') then
    dbms_apply_adm.set_table_instantiation_scn@ORC3
.WORLD(
      source_object_name => '"HR"."REGIONS"',
      source_database_name => :
local_db,
      instantiation_scn => :flashback_scn,
      apply_database_link => NUL
L);
  end if;
end;
/

------
-------------------------
-- Setup Propagation
-------------------------------

--
-- Propagation from local queue to ORC1.WORLD
--
begin
  if :local_db != 'ORC1.WORLD' then
    --
    -- H
R_REPG: Propagate "COUNTRIES" from local queue to ORC1
    --
    dbms_streams_adm.add_tabl
e_propagation_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_name => '&
amp;PROP_ORC1',
      source_queue_name => :local_queue_owner || '.' || :local_queue,

    destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC1.WORLD' then
    --
    -- HR_REPG: Propagate "DEPARTMENTS" from l
ocal queue to ORC1
    --
    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."DEPARTMENTS"',
      streams_name => '&PROP_ORC1',
<
/a>      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '
&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_t
agged_lcr => FALSE,
      source_database => :local_db);
  end if;
<
/a>end;
/

begin
  if :local_db != 'ORC1.WORLD' t
hen
    --
    -- 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 => :loca
l_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&QUEUE_ORC1' ||
        '@ORC1.WORLD',
      include_dml =>
 TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC1.WORLD' then
    --
    -- HR_REPG: Propagate "JOBS" from local queue to ORC1
    --
    dbms_st
reams_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.WORLD',
      include_dml => TRUE,
      include_ddl =&
gt; FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC1.WORLD' then
    --
    -- HR_REPG: Propagate "JOB_HI
STORY" 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.W
ORLD',
      include_dml => TRUE,
      include_ddl => FALSE,

    include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != '
ORC1.WORLD' 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_nam
e => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||

        '.' || '&QUEUE_ORC1' ||
        '@ORC1.WORLD',
      incl
ude_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;

/

begin
  if :local_db != 'ORC1.WORLD' 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 || '.' || :loca
l_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC1' ||
        '.' || '&
;QUEUE_ORC1' ||
        '@ORC1.WORLD',
      include_dml => TRUE,

     include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database =
> :local_db);
  end if;
end;
/

--
-- Propagation from local queue to ORC2.WORLD
--
begin
  if :local_db != 'ORC2.WORLD' then
    --
    -- HR_REPG: Propagate "COUNT
RIES" 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_na
me => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.WORL
D',
      include_dml => TRUE,
      include_ddl => FALSE,

 include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC
2.WORLD' 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_na
me => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' |
|
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.WORLD',
      inc
lude_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
<
a name="695513">      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC2.WORLD' 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.WORLD',
      include_dml => TRUE,

      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_datab
ase => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC2.WORLD' then
    --
    -- HR
_REPG: Propagate "JOBS" from local queue to ORC2
    --
    dbms_streams_adm.add_table_prop
agation_rules(
      table_name => '"HR"."JOBS"',
      streams_name => '&PROP_OR
C2',
      source_queue_name => :local_queue_owner || '.' || :local_queue,
      destina
tion_queue_name => '&QUEUE_OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||

  '@ORC2.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end
 if;
end;
/

begin
  if :loc
al_db != 'ORC2.WORLD' 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',
      so
urce_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_
OWNER_ORC2' ||
        '.' || '&QUEUE_ORC2' ||
        '@ORC2.WORLD',

      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr =
> FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC2.WORLD' 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_own
er || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC2' ||

      '.' || '&QUEUE_ORC2' ||
        '@ORC2.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,

 source_database => :local_db);
  end if;
end;
/
<
/a>
begin
  if :local_db != 'ORC2.WORLD' then
    --

    -- HR_REPG: Propagate "REGIONS" from local queue to ORC2
    --
    dbms_streams_ad
m.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.WORLD',
      include_dml => TRUE,
      include_ddl => F
ALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

--
-- Propagation from local queue to ORC3.WORLD
--
begin
  if :lo
cal_db != 'ORC3.WORLD' then
    --
    -- HR_REPG: Propagate "COUNTRIES" from local queue t
o ORC3
    --
    dbms_streams_adm.add_table_propagation_rules(

table_name => '"HR"."COUNTRIES"',
      streams_name => '&PROP_ORC3',
      sourc
e_queue_name => :local_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWN
ER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr =>
; FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC3.WORLD' then
    --
    -- HR_REPG: Propagate "DEPARTMENTS" from local queue to ORC3
    --
<
a name="695646">    dbms_streams_adm.add_table_propagation_rules(
      table_name => '"HR"."DEPARTMENTS"
',
      streams_name => '&PROP_ORC3',
      source_queue_name => :local_queue_ow
ner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||

       '.' || '&QUEUE_ORC3' ||
        '@ORC3.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,

  source_database => :local_db);
  end if;
end;
/


begin
  if :local_db != 'ORC3.WORLD' 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.WORLD',
      include_dml => TRUE,
      include_ddl =&
gt; FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC3.WORLD' 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 => '&
amp;QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
      include_ta
gged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC3.WORLD' th
en
    --
    -- 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 => :l
ocal_queue_owner || '.' || :local_queue,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE_ORC3' ||
        '@ORC3.WORLD',
      include_dml =&
gt; TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC3.WORLD' 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_queu
e,
      destination_queue_name => '&QUEUE_OWNER_ORC3' ||
        '.' || '&QUEUE
_ORC3' ||
        '@ORC3.WORLD',
      include_dml => TRUE,
      i
nclude_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => :
local_db);
  end if;
end;
/

begin
  if :local_db != 'ORC3.WORLD' then
    --
    -- HR_REPG: Prop
agate "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' ||
        '@O
RC3.WORLD',
      include_dml => TRUE,
      include_ddl => FALSE,
<
/a>      include_tagged_lcr => FALSE,
      source_database => :local_db);
  end if;
end;
/

-------------------------------
-- Setup Capture
-------------------------------
begin
  -
-
  -- HR_REPG : Add "COUNTRIES"
  --
  dbms_streams_adm.add_table_rul
es(
    table_name => '"HR"."COUNTRIES"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_owner || '.' || :local_qu
eue,
    include_dml => TRUE,
    include_ddl => FALSE,
    incl
ude_tagged_lcr => FALSE,
    source_database => :local_db);
end;
/

begin
  --
  -- HR_REPG : Add "DEPARTMENTS"
<
a name="695783">  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."DE
PARTMENTS"',
    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
<
a name="695797">  --
  -- HR_REPG : Add "EMPLOYEES"
  --
  dbms_st
reams_adm.add_table_rules(
    table_name => '"HR"."EMPLOYEES"',
    streams_type =>
'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :local_queue_ow
ner || '.' || :local_queue,
    include_dml => TRUE,
    include_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_db);
en
d;
/

begin
  --
  -- HR_REP
G : Add "JOBS"
  --
  dbms_streams_adm.add_table_rules(
    table_name
 => '"HR"."JOBS"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTUR
E_NAME',
    queue_name => :local_queue_owner || '.' || :local_queue,
    include_dml =&
gt; 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"',
    stream
s_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queue_name => :l
ocal_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;
/
<
/a>
begin
  --
  -- HR_REPG : Add "REGIONS"
  --
  dbms_streams_adm.add_table_rules(
    table_name => '"HR"."REGIONS"',
    streams_type => 'CAPTURE',
    streams_name => '&CAPTURE_NAME',
    queu
e_name => :local_queue_owner || '.' || :local_queue,
    include_dml => TRUE,
    inc
lude_ddl => FALSE,
    include_tagged_lcr => FALSE,
    source_database => :local_
db);
end;
/

-------------------------------
-- Setup Apply
-------------------------------
--
--
Setup Apply from ORC1.WORLD
--

begin
  --
  -- HR_REPG : Add "COUNTRIES" to apply rules for apply from
  -- ORC1.WORLD
  --
  if(:local_db != 'ORC1.WORLD') 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.WORLD');
  end if;
end;
/

begin
  --

-- HR_REPG : Add "DEPARTMENTS" to apply rules for apply from
  -- ORC1.WORLD
  --
  if(:local_db != 'ORC1.WORLD') then
    dbms_streams_adm.add_table_rules(

  table_name => '"HR"."DEPARTMENTS"',
      streams_type => 'APPLY',
      streams_na
me => '&APPLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
<
/a>      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr =&
gt; FALSE,
      source_database => 'ORC1.WORLD');
  end if;
end;
<
a name="695919">/

begin
  --
  -- HR_REPG :
Add "EMPLOYEES" to apply rules for apply from
  -- ORC1.WORLD
  --
  i
f(:local_db != 'ORC1.WORLD') then
    dbms_streams_adm.add_table_rules(
      table_name =&
gt; '"HR"."EMPLOYEES"',
      streams_type => 'APPLY',
      streams_name => '&AP
PLY_ORC1',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_
dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.WORLD');
  end if;
end;
<
/a>/

begin
  --
  -- HR_REPG : Add "JOBS" to app
ly rules for apply from
  -- ORC1.WORLD
  --
  if(:local_db != 'ORC1.W
ORLD') 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.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "JOB_HISTORY" to apply rules for apply f
rom
  -- ORC1.WORLD
  --
  if(:local_db != 'ORC1.WORLD') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOB_HISTORY"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      qu
eue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,

   include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database =&g
t; 'ORC1.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "LOCATIONS" to apply rules for apply from
  -- ORC1.WORLD
  --
  if(:local_db != 'ORC1.WORLD') then

  dbms_streams_adm.add_table_rules(
      table_name => '"HR"."LOCATIONS"',
      stream
s_type => 'APPLY',
      streams_name => '&APPLY_ORC1',
      queue_name => :l
ocal_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl =&
gt; FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC1.WORLD');

  end if;
end;
/

begin
  --
  -- HR_REPG : Add "REGIONS" to apply rules for apply from
  -- ORC1
.WORLD
  --
  if(:local_db != 'ORC1.WORLD') 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.WORLD');
  end if;
end;
/

--
-- Set
up Apply from ORC2.WORLD
--

begin
  --
  -- HR_REPG : Add "COUNTRIES" to apply rules for apply from
  -- ORC2.WORLD

--
  if(:local_db != 'ORC2.WORLD') 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_tag
ged_lcr => FALSE,
      source_database => 'ORC2.WORLD');
  end if;

end;
/

begin
  --
  --
HR_REPG : Add "DEPARTMENTS" to apply rules for apply from
  -- ORC2.WORLD
  --
  if(:local_db != 'ORC2.WORLD') then
    dbms_streams_adm.add_table_rules(
      t
able_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.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add
 "EMPLOYEES" to apply rules for apply from
  -- ORC2.WORLD
  --
  if(:
local_db != 'ORC2.WORLD') 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.WORLD');
  end if;
end;

/

begin
  --
  -- HR_REPG : Add "JOBS" to apply
rules for apply from
  -- ORC2.WORLD
  --
  if(:local_db != 'ORC2.WORL
D') 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_da
tabase => 'ORC2.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "JOB_HISTORY" to apply rules for apply from

  -- ORC2.WORLD
  --
  if(:local_db != 'ORC2.WORLD') 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.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "LOCATIONS" to apply rules for apply from
  -- ORC2.WORLD
  --
  if(:local_db != 'ORC2.WORLD') then
    d
bms_streams_adm.add_table_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_t
ype => 'APPLY',
      streams_name => '&APPLY_ORC2',
      queue_name => :loca
l_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl =>
 FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC2.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "REGIONS" to apply rules for apply from
  -- ORC2.WO
RLD
  --
  if(:local_db != 'ORC2.WORLD') 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.WORLD');

end if;
end;
/

--
-- Setup
Apply from ORC3.WORLD
--

begin
  --
  -- HR_REPG : Add "COUNTRIES" to apply rules for apply from
  -- ORC3.WORLD
  --
  if(:local_db != 'ORC3.WORLD') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."COUNTRIES"',
      streams_type => 'APPLY',
      str
eams_name => '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged
_lcr => FALSE,
      source_database => 'ORC3.WORLD');
  end if;
end;
/

begin
  --
  -- HR_
REPG : Add "DEPARTMENTS" to apply rules for apply from
  -- ORC3.WORLD
  --
  if(:local_db != 'ORC3.WORLD') then
    dbms_streams_adm.add_table_rules(
      tabl
e_name => '"HR"."DEPARTMENTS"',
      streams_type => 'APPLY',
      streams_name =&g
t; '&APPLY_ORC3',
      queue_name => :local_queue_owner || '.' || :local_queue,

   include_dml => TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FA
LSE,
      source_database => 'ORC3.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "E
MPLOYEES" to apply rules for apply from
  -- ORC3.WORLD
  --
  if(:loc
al_db != 'ORC3.WORLD') then
    dbms_streams_adm.add_table_rules(
      table_name => '"
HR"."EMPLOYEES"',
      streams_type => 'APPLY',
      streams_name => '&APPLY_OR
C3',
      queue_name => :local_queue_owner || '.' || :local_queue,
      include_dml =&
gt; TRUE,
      include_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.WORLD');
  end if;
end;
/
<
a name="696213">
begin
  --
  -- HR_REPG : Add "JOBS" to apply rul
es for apply from
  -- ORC3.WORLD
  --
  if(:local_db != 'ORC3.WORLD')
 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_datab
ase => 'ORC3.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "JOB_HISTORY" to apply rules for apply from
  -- ORC3.WORLD
  --
  if(:local_db != 'ORC3.WORLD') then
    dbms_streams_adm.add_table_rules(
      table_name => '"HR"."JOB_HISTORY"',

     streams_type => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_na
me => :local_queue_owner || '.' || :local_queue,
      include_dml => TRUE,
      inc
lude_ddl => FALSE,
      include_tagged_lcr => FALSE,
      source_database => 'OR
C3.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "LOCATIONS" to apply rules for apply from

  -- ORC3.WORLD
  --
  if(:local_db != 'ORC3.WORLD') then
    dbms
_streams_adm.add_table_rules(
      table_name => '"HR"."LOCATIONS"',
      streams_type
 => 'APPLY',
      streams_name => '&APPLY_ORC3',
      queue_name => :local_q
ueue_owner || '.' || :local_queue,
      include_dml => TRUE,
      include_ddl => FA
LSE,
      include_tagged_lcr => FALSE,
      source_database => 'ORC3.WORLD');
  end if;
end;
/

begin
  --
  -- HR_REPG : Add "REGIONS" to apply rules for apply from
  -- ORC3.WORLD

  --
  if(:local_db != 'ORC3.WORLD') then
    dbms_streams_adm.add_ta
ble_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.WORLD');
  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 a 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 COUN
TRIES_LogGrp1 (
"COUNTRY_NAME"
,"REGION_ID"
,"TIMESTAMP"
);

-------------------------------
-- Setup Conflict Resoluti
on
-------------------------------
--
-- ** WARNING ** --
-- Streams does not support LATEST TIMESTAMP
-- conflict resolution method.
-- Cha
nging LATEST TIMESTAMP to MAXIMUM as
-- they handle the conflicts in a similar manner.
--
<
a name="696329">declare
  cols dbms_utility.name_array;
begin
  co
ls(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 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_ta
ble)
 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 appl
y_name IN (
       upper('&APPLY_ORC1'),
       upper('&APPLY_ORC2'),
       upper('&APPLY_ORC3') )
 order by apply_name;

-- Ve
rify propagation processes
select * from dba_propagation
 where propagation_name IN (
       upper('&PROP_ORC1'),
       upper('&PROP_ORC2'),
       uppe
r('&PROP_ORC3') )
 order by propagation_name;

-- Verify Streams r
ules
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'),
       u
pper('&APPLY_ORC3') )
 order by source_database;

select * from db
a_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 Streams is set up.
--
 Drop all the repgroups that have been migrated to Streams.
-- Start apply and capture processes at all sites.
<
a name="696395">







Note:  
A materialized view log sho
uld exist for each table you added to the hr_repg master group, unless you deleted these logs manually after you migrate
d the replication group to Streams. If these materialized view logs do not exist, then you must create them.

 


 










Previous


Next


Copyright © 2003 Oracle Corporation

All Rights Reserved.


<
td align="center" valign="top">

Book List



Home


Contents



Index


Master Index


Feedback

A Migrating Advanced Replication to Streams

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 Str eams

Preparing to Generate the Migration Script

Generating and Modifying the Migration Script

Example Advanced Replication Environment to be Migrated to Streams

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

Step 1 Create the Streams administrator at all master sites.

Step 2 Make a directory location accessi ble.

Step 3 Gene rate the migration script.

Step 4 Verify the generated migration script creation and modify script.

Performing the Migration for Advanced Replication to Streams

Before Executing the Migration Script

Step 1 Set initialization parameters that are rel evant to Streams.

Step 2 Enable archive logging at all sites.

Step 3 Set up databa se links, if needed, for queue to queue propagation.

Step 4 Quiesce each replicatio n group that you are migrating to Streams.

Executing the Migration Script

Step 1 Connect as the Streams administrator and ru n the script at each site.

Step 2 Verify that Streams configuration completed s uccessfully at all sites.

After Executing the Script

Step 1 Drop replication groups you migr ated at each site.

Step 2 Start the apply processes at each site.

Step 3 Start the capture process at each site.

Recreating Master Sites to Retain Materialized View Groups

Example Advanced Replication to Streams Migration Script

A
Migrating Advanced Replication to Streams