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

Migration Script Generation and Use

You can use the procedure DBMS_REPCAT.STREAMS_MIGRATION to generate a SQL*Plus script that migrates an existing Advanced Replication environment to a Streams environment. When you run the DBMS_REPCAT.STREAMS_MIGRATION procedure at a master definition site in a multimaster replication environment, it generates a SQL*Plus script in a file at a location that you specify. Once the script is generated, you run it at each master site in your Advanced Replication environment to set up a Streams environment for each master site. To successfully generate the Streams environment for your replication groups, the replication groups for which you run the script must have exactly the same master sites. If replication groups have different master sites, then you can generate 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 replication groups to run the script at the master sites, you do not have to stop any existing capture processes, propagation jobs, or apply processes at these sites.

Modification of the Migration Script

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

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

include_dml

include_ddl

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 necessary. 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 cannot be converted to Streams.

Creates a SYS.AnyData queue, if needed, using the DBMS_STREAMS_ADM.SET_UP_QUEUE procedure.

Configures propagation between 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 conflict 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 successfully.

Manual Migration of Updatable Materialized Views

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

Advanced Replication Elements That Cannot Be Migrated to Streams

Replication of changes to tables with columns of the following datatypes: BFILE, ROWID, UROWID, and user-defined types (including object types, REFs, varrays, and nested 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 environment 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

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:

Parameters for the DBMS_REPCAT.STREAMS_MIGRATION procedure include the following:

gnames: List of replication groups to migrate to Streams. The replication groups listed must all contain exactly 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 a 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 Streams

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

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

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 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 timestamp conflict resolution method resolves conflicts on this table.

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

To ensure security, use a password other than strmadminpw 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 edit 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 Streams administrator. The necessary privileges depend on your specific Streams environment. For the example environment described in "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;

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 Streams administrator at the master definition site currently, then connect as the Streams administrator, and create a directory object using the SQL statement CREATEDIRECTORY.

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:

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.

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. Streams 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, and minimum. The script converts an earliest timestamp method to a minimum method automatically, and it converts a latest timestamp method to a maximum method automatically. If you use a timestamp conflict resolution method, then the script assumes that any triggers necessary 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.

Procedural replication

Replication of data definition language (DDL) changes for non-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 database captures DDL changes to all of the database objects in the hr schema, add the following to the script:

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 perform the migration from an Advanced Replication environment to a Streams environment.

Step 2 Enable archive logging at all sites.

Make sure each master site is running in ARCHIVELOG mode, because a capture process requires ARCHIVELOG mode. In the example environment, orc1.world, orc2.world, and orc3.world must be running in ARCHIVELOG mode. You can check the log mode for a database by querying the LOG_MODE column in the V$DATABASE dynamic performance view.

Step 4 Quiesce each replication 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:

Check the spool file at each site to make sure there are no 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.

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 START_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:

Make sure you start each apply process at every database in the new Streams environment.

Step 3 Start the capture process at each site.

You can view the name of the capture process at each site by running the following query while connected as the Streams administrator:

SELECT CAPTURE_NAME FROM DBA_CAPTURE;

When you know the name of the capture process, you can start each one by running the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package while connected as the Streams administrator. For example, the following procedure starts a capture process named streams_capture at orc1.world:

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 migrated to 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 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.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 materialized 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 configure 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 groups at the materialized view sites. As long as a new master replication group resembles the original replication group, 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 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 retain this materialized view group at mv1.world, complete the following steps while connected as the replication administrator:

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 Streams. If these materialized view logs do not exist, then you must create them.