Tutorial: Configuring Hub-and-Spoke Replication with Local Capture Processes

This example configures an Oracle Streams hub-and-spoke replication environment that replicates data manipulation language (DML) changes to all of the tables in the hr schema. This example uses a capture process at each database to capture these changes. Hub-and-spoke replication means that a central hub database replicates changes with one or more spoke databases. The spoke databases do not communicate with each other directly. In this sample configuration, the hub database sends changes generated at one spoke database to the other spoke database. See "About Hub-And-Spoke Replication Environments" for more information about hub-and-spoke replication environments.

This example uses the Setup Streams Replication Wizard in Oracle Enterprise Manager to configure the hub-and-spoke replication environment. This wizard is the fastest and simplest way to configure an Oracle Streams environment that replicates one or more schemas. In addition, the wizard follows established best practices for Oracle Streams replication environments.

In this example, the global name of the hub database is hub.example.com, and the global names of the spoke databases are spoke1.example.com and spoke2.example.com. However, you can substitute databases in your environment to complete the example.

The database objects being configured for replication might or might not exist at the destination databases when you run the wizard. If the database objects do not exist at a destination database, then the wizard instantiates them at the destination database using a Data Pump export/import. During instantiation, the instantiation SCN is set for these database objects. If the database objects already exist at a destination database, then the wizard retains the existing database objects and sets the instantiation SCN for them. In this example, the hr schema exists at each database before the wizard is run.

Figure: Hub-and-Spoke Environment with Capture Processes and Read/Write Spokes provides an overview of the environment created in this example.

To configure this hub-and-spoke replication environment with read/write spokes: 

1. Complete the following tasks to prepare for the hub-and-spoke replication environment:

   1. Configure network connectivity so that the following databases can communicate with each other:

      • The hub.example.com database and the spoke1.example.com database

      • The hub.example.com database and the spoke2.example.com database

      See Configuring the Network Environment for information about configuring network connectivity between databases.

   2. Configure an Oracle Streams administrator at each database that will participate in the replication environment. See "Tutorial: Configuring an Oracle Streams Administrator" for instructions. This example assumes that the Oracle Streams administrator is strmadmin.

   3. Set initialization parameters properly at each database that will participate in the Oracle Streams replication environment. See "Preparing for Oracle Streams Replication" for instructions.

   4. Configure each source 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, all databases must be running in ARCHIVELOG mode. See Oracle Database Administrator's Guide for information about configuring a database to run in ARCHIVELOG mode.

2. In Enterprise Manager, log in to the hub database hub.example.com as the Oracle Streams administrator.

3. Go to the Database Home page for the database instance.

4. Click Data Movement to open the Data Movement subpage.

5. Click Setup in the Streams section.

   The Streams page appears, showing the setup options.

6. Select Replicate Schemas in Setup Streams Replication.

7. In the Host Credentials section, enter the username and password for an administrative user on the host computer system.

8. Click Continue.

9. On the Object Selection page, select HR and click Next.

10. On the Destination Options page, identify the spoke database spoke1.example.com by specifying its host name, port, SID or service name, and Oracle Streams administrator credentials.

    (When you configure replication with the spoke2.example.com database, identify spoke2.example.com.)

11. Click Next.

12. Complete the Replication Options page:

    1. In the Directory Path section, leave the directory paths for the source and destination database unchanged if the host user you specified in Step 7 can read from and write to the directories and the directories have enough space for a Data Pump export dump file. Otherwise, specify different directory paths, or create directory objects that meet these requirements and specify those.

    2. Expand Advanced Options.

    3. In the Options section, ensure that Capture, Propagate and Apply data manipulation language (DML) changes is selected.

    4. If you do not want to replicate DDL changes, then deselect Capture, Propagate and Apply data definition language (DDL) changes.

    5. Select Setup Bi-directional replication.

    6. In the Capture Process section, enter capture_hns in Capture Name.

    7. In the Propagation Process section, enter propagation_spoke1 in Propagation Name. (When you configure replication with the spoke2.example.com database, enter propagation_spoke2.)

    8. In the Apply Process section, enter apply_spoke1 in Apply Name. (When you configure replication with the spoke2.example.com database, enter apply_spoke2.)

13. Click Next.

14. On the Schedule Job page, either select Immediately or specify a time for the job to run later.

15. Click Next.

16. On the Review page, review the configuration information and click Submit.

17. On the Confirmation page, optionally click the job link to monitor the job.

    When the job is running, 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 job 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.

18. While still connected as the Oracle Streams administrator to the hub.example.com database, complete Steps 3 to 17 again. However, in Step 10, on the Destination Options page, identify the spoke database spoke2.example.com by specifying its host name, port, SID or service name, and Oracle Streams administrator credentials.

    Also, in Step 12g, enter propagation_spoke2 in Propagation Name, and in Step 12h, enter apply_spoke2 in Apply Name

When you complete the example, a hub-and-spoke replication environment with the following characteristics is configured:

• Supplemental logging is configured for the tables in the hr schema at each database.

• Each database has a capture process named capture_hns. The capture process captures changes to the hr schema at the database.

• Each database has a queue with a system-generated name. Each queue is for the capture process at the database.

• The hub database hub.example.com has the following additional components:

  • An apply process named apply_spoke1. This apply process applies changes to the hr schema that were sent from the spoke1.example.com database.

  • A queue with a system-generated name. This queue is for the apply_spoke1 apply process at the database.

  • An apply process named apply_spoke2. This apply process applies changes to the hr schema that were sent from the spoke2.example.com database.

  • A queue with a system-generated name. This queue is for the apply_spoke2 apply process at the database.

  • A propagation named propagation_spoke1. This propagation sends changes to the hr schema from a local queue to a queue at the spoke1.example.com database.

  • A propagation named propagation_spoke2. This propagation sends changes to the hr schema from a local queue to a queue at the spoke2.example.com database.

• The spoke database spoke1.example.com has the following additional components:

  • An apply process named apply_spoke1. The apply process applies changes to the hr schema that were sent from the hub.example.com database.

  • A queue with a system-generated name. This queue is for the apply_spoke1 apply process at the database.

  • A propagation named propagation_spoke1. This propagation sends changes to the hr schema from a local queue to a queue at the hub.example.com database.

• The spoke database spoke2.example.com has the following additional components:

  • An apply process named apply_spoke2. The apply process applies changes to the hr schema that were sent from the hub.example.com database.

  • A queue with a system-generated name. This queue is for the apply_spoke2 apply process at the database.

  • A propagation named propagation_spoke2. This propagation sends changes to the hr schema from a local queue to a queue at the hub.example.com database.

• Tags are used to avoid change cycling in the following way:

  • Each apply process uses an apply tag, and redo records for changes applied by the apply process include the tag. Each apply process uses an apply tag that is unique in the replication environment.

  • Each capture process captures all of the changes to the replicated database objects, regardless of the tag in the redo record. Therefore, each capture process captures the changes applied by the apply processes on its source database.

  • Each propagation sends all changes made to the replicated database objects to another database in the replication environment, except for changes that originated at the other database. The propagation rules instruct the propagation to discard these changes.

  See "About Tags for Avoiding Change Cycling" and Oracle Database PL/SQL Packages and Types Reference for more information about how the replication environment avoids change cycling.

To check the Oracle Streams replication configuration: 

1. At each database, ensure that the capture process is enabled and that the capture type is local. To do so, follow the instructions in "Viewing Information About a Capture Process", and check the Status and Capture Type fields on the Capture subpage.

2. At each database, ensure that each propagation is enabled. To do so, follow the instructions in "Viewing Information About a Propagation", and check the Status field on the Propagation subpage. The hub database should have two propagations, and they should both be enabled. Each spoke database should have one propagation that is enabled.

3. At each database, ensure that each apply process is enabled. To do so, follow the instructions in "Viewing Information About an Apply Process", and check the Status field on the Apply subpage. The hub database should have two apply processes, and they should both be enabled. Each spoke database should have one apply process that is enabled.

To replicate changes: 

1. At one of the databases, make DML changes to any table in the hr schema.

2. After some time has passed to allow for replication of the changes, use SQL*Plus to query the modified table at the other databases to view the DML changes.