Tutorial: Configuring Two-Database Replication with Local Capture Processes

This example configures an Oracle Streams replication environment that replicates data manipulation language (DML) changes to all of the tables in the hr schema. This example configures a two-database replication environment with local capture processes to capture changes. This example uses the global database names db1.example.com and db2.example.com. However, you can substitute databases in your environment to complete the example. See "About Two-Database Replication Environments" for more information about two-database replication environments.

This example uses the Setup Streams Replication Wizard in Oracle Enterprise Manager to configure the two-database 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.

The database objects being configured for replication might or might not exist at the destination database when you run the wizard. If the database objects do not exist at the 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 the 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 both the db1.example.com database and the db2.example.com database before the wizard is run.

This example provides instructions for configuring either one-way or bi-directional replication. To configure bi-directional replication, you must complete additional steps and select Setup Bi-directional on the appropriate wizard page.

Figure: Two-Database Replication Environment with Local Capture Processes provides an overview of the environment created in this example. The additional components required for bi-directional replication are shown in gray, and their actions are indicated by dashed lines.

To configure this two-database replication environment: 

1. Complete the following tasks to prepare for the two-database replication environment:

   1. Configure network connectivity so that the db1.example.com database can communicate with the db2.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 the db1.example.com 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. See Oracle Database Administrator's Guide for information about configuring a database to run in ARCHIVELOG mode.

   5. If you are configuring a bi-directional replication environment, then configure the db2.example.com database to run in ARCHIVELOG mode. If you are configuring a one-way replication environment, then this step is not required, and you can move on to Step 2.

2. In Enterprise Manager, log in to the source database as the Oracle Streams administrator.

   In this example, the source database is db1.example.com.

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 destination database by specifying its host name, port, SID or service name, and Oracle Streams administrator credentials.

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. If you want to configure bi-directional replication, then select Setup Bi-directional replication.

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.

When the job completes successfully, a two-database replication environment with the following characteristics is configured:

• At db1.example.com, supplemental logging is configured for the tables in the hr schema.

• The db1.example.com database has the following components:

  • A capture process with a system-generated name. The capture process captures DML changes to the hr schema.

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

  • A propagation with a system-generated name that sends changes from the queue at the db1.example.com database to the queue at the db2.example.com database.

• The db2.example.com database has the following components:

  • A queue with a system-generated name that receives the changes sent from the db1.example.com database. This queue is for the apply process at the local database.

  • An apply process with a system-generated name. The apply process dequeues changes from its queue and applies them to the hr schema.

• If the replication environment is bi-directional, then the following are also configured:

  • At db2.example.com, supplemental logging for the tables in the hr schema.

  • At db2.example.com, a capture process with a system-generated name. The capture process captures DML changes to the hr schema.

  • At db2.example.com, a queue with a system-generated name. This queue is for the capture process at the database.

  • At db1.example.com, a queue with a system-generated name that receives the changes sent from the db2.example.com database. This queue is for the apply process at the local database.

  • At db1.example.com, an apply process with a system-generated name. The apply process dequeues changes from its queue and applies them to the hr schema.

• If the replication environment is bi-directional, then 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 the other 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" for more information about how the replication environment avoids change cycling. If you configured one-way replication, then change cycling is not possible because changes are only captured in a single location.

To check the Oracle Streams replication configuration: 

1. At the db1.example.com 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 the db1.example.com database, ensure that the propagation is enabled. To do so, follow the instructions in "Viewing Information About a Propagation", and check the Status field on the Propagation subpage.

3. At the db2.example.com database, ensure that the 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.

4. If you configured bi-directional replication, then complete the following additional steps:

   1. At the db2.example.com database, ensure that the capture process is enabled and that the capture type is local.

   2. At the db2.example.com database, ensure that the propagation is enabled.

   3. At the db1.example.com database, ensure that the apply process is enabled.

To replicate changes: 

1. At a database that captures changes to the hr schema, make DML changes to any table in the hr schema. In this example, the db1.example.com database captures changes to the hr schema, and, if you configured bi-directional replication, then db2.example.com also captures changes to 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 database to view the DML changes.