6 Extending an Oracle Streams Replication Environment

This chapter describes extending an Oracle Streams replication environment by adding database objects or databases.

This chapter contains the following sections:

About Extending an Oracle Streams Replication Environment
Example: Adding Database Objects to a Replication Environment
Example: Adding Databases to a Replication Environment

See Also:

About Extending an Oracle Streams Replication Environment

Sometimes it is necessary to extend an Oracle Streams replication environment when the needs of your organization change. You can extend an Oracle Streams replication environment by adding database objects or databases.

There are two ways to extend an Oracle Streams replication environment:

Use a Single Configuration Procedure in the DBMS_STREAMS_ADM Package
Add the Oracle Streams Components Individually in Multiple Steps

Use a Single Configuration Procedure in the DBMS_STREAMS_ADM Package

The easiest way to extend an Oracle Streams replication environment is to run one of the following procedures in the DBMS_STREAMS_ADM package:

The MAINTAIN_GLOBAL procedure can add a new database to an environment that replicates changes to all of the database objects in the databases.
The MAINTAIN_SCHEMAS procedure can add one or more new schemas to the existing databases in the replication environment, or it can add a new database that replicates schemas that are currently being replicated.
The MAINTAIN_SIMPLE_TTS procedure can add a new simple tablespace to an existing replication environment, or it can add a new database that replicates a simple tablespace that is currently being replicated.
The MAINTAIN_TABLES procedure can add one or more new tables to the existing databases in the replication environment, or it can add a new database that replicates tables that are currently being replicated.
The MAINTAIN_TTS procedure can add a new set of tablespaces to an existing replication environment, or it can add a new database that replicates a set of tablespaces that are currently being replicated.

To use one of these procedures to extend an Oracle Streams replication environment, the environment must meet the following conditions:

It must be a two-database or hub-and-spoke replication environment that was configured by one of the configuration procedures in the DBMS_STREAMS_ADM package. See "About the Common Types of Oracle Streams Replication Environments" for information about these types of replication environments.
It cannot use a synchronous capture at any database in the Oracle Streams replication environment. See "About Change Capture with a Synchronous Capture" for more information about synchronous capture.
If you are adding a database to the environment, then each database that captures changes must use a local capture process. No database can use a downstream capture process. If you are adding one or more database objects to the environment, then the databases can use either local or downstream capture processes. See "About Change Capture with a Capture Process" for more information about downstream capture.
If you are adding database objects to the replication environment, then the database objects must exist at the database specified in the source_database parameter of the configuration procedure but not at any of the other databases.
If you are adding a database to the replication environment, then the new database must not contain any of the database objects that are replicated in the current environment.

If your environment meets these conditions, then complete the steps in one of the following sections to extend the environment:

Example: Adding Database Objects to a Replication Environment
Example: Adding Databases to a Replication Environment

Add the Oracle Streams Components Individually in Multiple Steps

If you cannot extend the Oracle Streams replication environment by using a configuration procedure in the DBMS_STREAMS_ADM package, then you must complete the configuration steps manually. These steps include adding the necessary rules and Oracle Streams components to the environment, as well as other configuration steps.

If you must extend the Oracle Streams replication environment manually, then see the instructions in Oracle Streams Replication Administrator's Guide.

See Also:

Chapter 4, "Replicating Data Using Oracle Streams"
"About Hub-And-Spoke Replication Environments"
Oracle Database PL/SQL Packages and Types Reference for information about the procedures in the DBMS_STREAMS_ADM chapter

Example: Adding Database Objects to a Replication Environment

This topic includes an example that uses the MAINTAIN_TABLES procedure in the DBMS_STREAMS_ADM package to add tables to an existing hub-and-spoke replication environment. When the example is complete, the Oracle Streams replication environment replicates the changes made to the added tables at the databases in the environment. See "About the Oracle Streams Replication Configuration Procedures" for more information about the configuration procedures.

Specifically, the example in this topic extends the replication environment configured in "Example: Configuring Hub-and-Spoke Replication with Local Capture Processes". That configuration has the following characteristics:

The hr schema is replicated at the hub.net, spoke1.net, and spoke2.net databases.
The hub.net database is the hub database in the hub-and-spoke environment, while the other databases are the spoke databases.
The spoke databases allow changes to the replicated schema, and each database has a local capture process to capture these changes.
Update conflict handlers are configured for each replicated table at each database to resolve conflicts

This example adds the following tables to the environment:

oe.orders
oe.order_items

This example uses the tables in the oe sample schema. The oe sample schema is installed by default with Oracle Database.

Note:

Before you use a configuration procedure in the DBMS_STREAMS_ADM package to extend an Oracle Streams replication environment, ensure that the environment meets the conditions described in "About Extending an Oracle Streams Replication Environment".

To add database objects to an Oracle Streams replication environment:

Ensure that the following directory objects exist, and remove any files related to the previous configuration from them, including Data Pump export dump files and export log files:
- The hub_dir directory object at the hub.net database.
- The spoke1_dir directory object at the spoke1.net database.
- The spoke2_dir directory object at the spoke2.net database.
Stop the capture process at the hub database in the hub-and-spoke environment.

In this example, stop the capture process at the hub.net database. The replicated database objects can remain open to changes while the capture process is stopped. These changes will be captured when the capture process is restarted.
1. In Oracle Enterprise Manager, log in to the hub database as the Oracle Streams administrator.
2. Go to the Database Home page.
3. Click Data Movement to open the Data Movement subpage.
4. Click Manage in the Streams section.
  
  The Streams page appears, showing the Overview subpage.
5. Click Capture to open the Capture subpage.
  
  Description of the illustration tdpii_capture.gif
6. Select the capture process that you want to stop.
7. Click Stop.
8. Click Yes on the confirmation page to stop the capture process.
Note:
You can also use the DBMS_CAPTURE_ADM.STOP_CAPTURE procedure to stop a capture process.
In SQL*Plus, run the appropriate configuration procedure in the DBMS_STREAMS_ADM package at the hub database to add each new database object for each spoke database.

You might need to run the procedure several times if the environment has more than one spoke database. In this example, complete the following steps:
1. Open SQL*Plus and connect to the hub.net database as the Oracle Streams administrator.
  
  See Oracle Database 2 Day DBA for more information about starting SQL*Plus.
2. Run the MAINTAIN_TABLES procedure to add the oe.orders and oe.order_items tables for replication between hub.net and spoke1.net:
```
DECLARE
  tables DBMS_UTILITY.UNCL_ARRAY;
  BEGIN
    tables(1) := 'oe.orders';
    tables(2) := 'oe.order_items';
    DBMS_STREAMS_ADM.MAINTAIN_TABLES(
      table_names                  => tables,
      source_directory_object      => 'hub_dir',
      destination_directory_object => 'spoke1_dir',
      source_database              => 'hub.net',
      destination_database         => 'spoke1.net',
      capture_name                 => 'capture_hns',
      capture_queue_table          => 'source_hns_qt',
      capture_queue_name           => 'source_hns',
      propagation_name             => 'propagation_spoke1',
      apply_name                   => 'apply_spoke1',
      apply_queue_table            => 'destination_spoke1_qt',
      apply_queue_name             => 'destination_spoke1',
      bi_directional               => TRUE);
END;
/
```
  The MAINTAIN_TABLES procedure can take some time to run because it is performing many configuration tasks. Do not allow data manipulation language (DML) or data definition language (DDL) changes to the specified tables at the destination database while the procedure is running. When the procedure completes, the new database objects are added to the environment, and the capture process that was stopped in Step 2 is restarted. See "About the Oracle Streams Replication Configuration Procedures" for more information about the configuration procedures.
  
  When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator's Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to recover from these errors.
  
  The parameter values that specify Oracle Streams component names must be the same as the values specified in the configuration procedure in the DBMS_STREAMS_ADM package that configured the replication environment. The Oracle Streams component names specified include the capture process name, queue names, queue table names, the propagation name, and the apply process name. In this example, the Oracle Streams component names match the ones specified in "Example: Configuring Hub-and-Spoke Replication with Local Capture Processes".
3. Run the MAINTAIN_TABLES procedure to add the oe.orders and oe.order_items tables for replication between hub.net and spoke2.net:
```
DECLARE
  tables DBMS_UTILITY.UNCL_ARRAY;
  BEGIN
    tables(1) := 'oe.orders';
    tables(2) := 'oe.order_items';
    DBMS_STREAMS_ADM.MAINTAIN_TABLES(
      table_names                  => tables,
      source_directory_object      => 'hub_dir',
      destination_directory_object => 'spoke2_dir',
      source_database              => 'hub.net',
      destination_database         => 'spoke2.net',
      capture_name                 => 'capture_hns',
      capture_queue_table          => 'source_hns_qt',
      capture_queue_name           => 'source_hns',
      propagation_name             => 'propagation_spoke2',
      apply_name                   => 'apply_spoke2',
      apply_queue_table            => 'destination_spoke2_qt',
      apply_queue_name             => 'destination_spoke2',
      bi_directional               => TRUE);
END;
/
```

Set the instantiation SCN for the replicated tables at the spoke databases:

Note:

This step is required in this example because the replicated tables existed at the spoke databases before the MAINTAIN_TABLES procedure was run. If the replicated tables did not exist at the spoke databases before the MAINTAIN_TABLES procedure was run, then the procedure sets the instantiation SCN for the replicated tables and this step is not required.

In SQL*Plus, connect to the hub.net database as the Oracle Streams administrator.

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

Set the instantiation SCN for the oe.orders table at the spoke1.net database:

DECLARE
  iscn  NUMBER;    -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke1.net(
    source_object_name    => 'oe.orders',
    source_database_name  => 'hub.net',
    instantiation_scn     => iscn);
END;
/

Set the instantiation SCN for the oe.order_items table at the spoke1.net database:

DECLARE
  iscn  NUMBER;    -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke1.net(
    source_object_name    => 'oe.order_items',
    source_database_name  => 'hub.net',
    instantiation_scn     => iscn);
END;
/

Set the instantiation SCN for the oe.orders table at the spoke2.net database:

DECLARE
  iscn  NUMBER;    -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke2.net(
    source_object_name    => 'oe.orders',
    source_database_name  => 'hub.net',
    instantiation_scn     => iscn);
END;
/

Set the instantiation SCN for the oe.order_items table at the spoke2.net database:

DECLARE
  iscn  NUMBER;    -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@spoke2.net(
    source_object_name    => 'oe.order_items',
    source_database_name  => 'hub.net',
    instantiation_scn     => iscn);
END;
/

Configure latest time conflict resolution for the orders and order_items tables in the oe schema at the hub.net, spoke1.net, and spoke2.net databases. See "Example: Configuring Latest Time Conflict Resolution for a Table" for instructions.

See Also:

Chapter 4, "Replicating Data Using Oracle Streams"

Example: Adding Databases to a Replication Environment

This topic includes an example that uses the MAINTAIN_SCHEMAS procedure in the DBMS_STREAMS_ADM package to add a new spoke database to an existing hub-and-spoke replication environment. When the example is complete, the Oracle Streams replication environment replicates the changes made to the schema with the new database. See "About the Oracle Streams Replication Configuration Procedures" for more information about the configuration procedures.

The hr schema is replicated at the hub.net, spoke1.net, and spoke2.net databases.
The hub.net database is the hub database in the hub-and-spoke environment, while the other databases are the spoke databases.
The spoke databases allow changes to the replicated schema, and each database has a local capture process to capture these changes.

This example adds the spoke3.net database to the environment.

Note:

To add a database to an existing Oracle Streams replication environment:

Complete the following tasks to prepare the environment for the new database:
1. Configure network connectivity so that the hub database can communicate with the new spoke database. In this example, configure network connectivity so that the hub.net database and the spoke3.net databases can communicate with each other.
  
  See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.
2. Configure an Oracle Streams administrator at the new spoke database. In this example, configure an Oracle Streams administrator at the spoke3.net database. See "Example: Creating an Oracle Streams Administrator" for instructions. This example assumes that the Oracle Streams administrator is strmadmin.
3. Create a database link from the hub database to new spoke database and from new spoke database to the hub database. In this example, create the following database links:
  - From the hub.net database to the spoke3.net database. Both the name and the service name of the database link must be spoke3.net.
  - From the spoke3.net database to the hub.net database. Both the name and the service name of the database link must be hub.net.
  Each database link should be created in the Oracle Streams administrator's schema. Also, each database link should connect to the Oracle Streams administrator at the destination database. See "Example: Creating a Database Link" for instructions.
4. Set initialization parameters properly at the new spoke database. In this example, set initialization parameters properly at the spoke3.net database. See "Preparing for Oracle Streams Replication" for instructions.
5. Configure the new spoke database to run in ARCHIVELOG mode. For a capture process to capture changes generated at a source database, the source database must be running in ARCHIVELOG mode. In this example, configure the spoke3.net database to run in ARCHIVELOG mode. See Oracle Database Administrator's Guide for information about configuring a database to run in ARCHIVELOG mode.
6. Ensure that the hub_dir directory objects exist at the hub.net database, and remove any files related to the previous configuration from it, including Data Pump export dump files and export log files.
Open SQL*Plus and connect to the spoke3.net database as the Oracle Streams administrator.

See Oracle Database 2 Day DBA for more information about starting SQL*Plus.
Create a directory object to hold files that will be generated by the MAINTAIN_SCHEMAS procedure, including the Data Pump export dump file used for instantiation. The directory object can point to any accessible directory on the computer system. For example, the following statement creates a directory object named spoke3_dir that points to the /usr/spoke3_log_files directory:
```
CREATE DIRECTORY spoke3_dir AS '/usr/spoke3_log_files';
```
Stop the capture process at the hub database in the hub-and-spoke environment.

In this example, stop the capture process at the hub.net database. The replicated database objects can remain open to changes while the capture process is stopped. These changes will be captured when the capture process is restarted.
1. In Oracle Enterprise Manager, log in to the hub database as the Oracle Streams administrator.
2. Go to the Database Home page.
3. Click Data Movement to open the Data Movement subpage.
4. Click Manage in the Streams section.
  
  The Streams page appears, showing the Overview subpage.
5. Click Capture to open the Capture subpage.
  
  Description of the illustration tdpii_capture.gif
6. Select the capture process that you want to stop.
7. Click Stop.
8. Click Yes on the confirmation page to stop the capture process.
Note:
You can also use the DBMS_CAPTURE_ADM.STOP_CAPTURE procedure to stop a capture process.
In SQL*Plus, run the appropriate configuration procedure in the DBMS_STREAMS_ADM package at the hub database to add the new spoke database.

In this example, complete the following steps:
1. Open SQL*Plus and connect to the hub.net database as the Oracle Streams administrator.
2. Run the MAINTAIN_SCHEMAS procedure to add the spoke3.net database to the Oracle Streams replication environment:
```
BEGIN
  DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
    schema_names                 => 'hr',
    source_directory_object      => 'hub_dir',
    destination_directory_object => 'spoke3_dir',
    source_database              => 'hub.net',
    destination_database         => 'spoke3.net',
    capture_name                 => 'capture_hns',
    capture_queue_table          => 'source_hns_qt',
    capture_queue_name           => 'source_hns',
    propagation_name             => 'propagation_spoke3',
    apply_name                   => 'apply_spoke3',
    apply_queue_table            => 'destination_spoke3_qt',
    apply_queue_name             => 'destination_spoke3',
    bi_directional               => TRUE);
END;
/
```
  The MAINTAIN_SCHEMAS procedure can take some time to run because it is performing many configuration tasks. Do not allow data manipulation language (DML) or data definition language (DDL) changes to the database objects in the specified schema at the destination database while the procedure is running. When the procedure completes, the new database objects are added to the environment, and the capture process that was stopped in Step 4 is restarted. See "About the Oracle Streams Replication Configuration Procedures" for more information about the configuration procedures.
  
  The parameter values specified in capture_name, capture_queue_table, and capture_queue_name must be the same as the values specified in the configuration procedure in the DBMS_STREAMS_ADM package that configured the replication environment. In this example, these parameter values match the ones specified in "Example: Configuring Hub-and-Spoke Replication with Local Capture Processes".
  
  When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator's Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to recover from these errors.
Configure latest time conflict resolution for all of the tables in the hr schema at the spoke3.net database. This schema includes the countries, departments, employees, jobs, job_history, locations, and regions tables. "Example: Configuring Latest Time Conflict Resolution for a Table" for instructions.

See Also:

Chapter 4, "Replicating Data Using Oracle Streams"