Tutorial: Configuring Two-Database Replication with Synchronous Captures

The example in this topic configures an Oracle Streams replication environment that replicates data manipulation language (DML) changes to two tables in the hr schema. This example uses a synchronous capture at each database to capture these changes. In this example, the global names of the databases in the Oracle Streams replication environment are sync1.example.com and sync2.example.com. However, you can substitute any two databases in your environment to complete the example. See "About Two-Database Replication Environments" for more information about two-database replication environments.

Specifically, this example configures a two-database Oracle Streams replication environment that shares the hr.employees and hr.departments tables at the sync1.example.com and sync2.example.com databases. The two databases replicate all of the DML changes to these tables. The hr sample schema is installed by default with Oracle Database.

Note: A synchronous capture can only capture changes at the table level. It cannot capture changes at the schema or database level. You can configure a synchronous capture using the ADD_TABLE_RULES and ADD_SUBSET_RULES procedures in the DBMS_STREAMS_ADM package.

Figure: Two-Database Replication Environment with Synchronous Captures provides an overview of the environment created in this example.

Two-Database Replication Environment with Synchronous Captures

Description of "Two-Database Replication Environment with Synchronous Captures"

To configure this replication environment with synchronous captures:

Complete the following tasks to prepare for the two-database replication environment:
1. Configure network connectivity so that the two databases can communicate with each other. 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. Ensure that the hr.employees and hr.departments tables exist at the two databases and are consistent at these databases. If the database objects exist at only one database, then you can use export/import to create and populate them at the other database. See Oracle Database Utilities for information about export/import.
Create two ANYDATA queues at each database. For this example, create the following two queues at each database:
- A queue named capture_queue owned by the Oracle Streams administrator strmadmin. This queue will be used by the synchronous capture at the database.
- A queue named apply_queue owned by the Oracle Streams administrator strmadmin. This queue will be used by the apply process at the database.
See "Creating an ANYDATA Queue" for instructions.
Create a database link from each database to the other database:
1. Create a database link from the sync1.example.com database to the sync2.example.com database. The database link should be created in the Oracle Streams administrator's schema. Also, the database link should connect to the Oracle Streams administrator at the sync2.example.com database. Both the name and the service name of the database link must be sync2.example.com.
2. Create a database link from the sync2.example.com database to the sync1.example.com database. The database link should be created in the Oracle Streams administrator's schema. Also, the database link should connect to the Oracle Streams administrator at the sync1.example.com database. Both the name and the service name of the database link must be sync1.example.com.
See "Tutorial: Creating a Database Link" for instructions.
Configure an apply process at the sync1.example.com database. This apply process will apply changes to the shared tables that were captured at the sync2.example.com database and propagated to the sync1.example.com database.
1. Open SQL*Plus and connect to the sync1.example.com database as the Oracle Streams administrator.
  
  See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.
2. Create the apply process:
```
BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name     => 'strmadmin.apply_queue',
    apply_name     => 'apply_emp_dep',
    apply_captured => FALSE);
END;
/
```
  The apply_captured parameter is set to FALSE because the apply process applies changes in the persistent queue. These are changes that were captured by a synchronous capture. The apply_captured parameter should be set to TRUE only when the apply process applies changes captured by a capture process.
  
  Do not start the apply process.
3. Add a rule to the apply process rule set:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.employees',
    streams_type    => 'apply',
    streams_name    => 'apply_emp_dep',
    queue_name      => 'strmadmin.apply_queue',
    source_database => 'sync2.example.com');
END;
/
```
  This rule instructs the apply process apply_emp_dep to apply all DML changes to the hr.employees table that appear in the apply_queue queue. The rule also specifies that the apply process applies only changes that were captured at the sync2.example.com source database.
4. Add an additional rule to the apply process rule set:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.departments',
    streams_type    => 'apply',
    streams_name    => 'apply_emp_dep',
    queue_name      => 'strmadmin.apply_queue',
    source_database => 'sync2.example.com');
END;
/
```
  This rule instructs the apply process apply_emp_dep to apply all DML changes to the hr.departments table that appear in the apply_queue queue. The rule also specifies that the apply process applies only changes that were captured at the sync2.example.com source database.
Configure an apply process at the sync2.example.com database. This apply process will apply changes that were captured at the sync1.example.com database and propagated to the sync2.example.com database.
1. In SQL*Plus, connect to the sync2.example.com database as the Oracle Streams administrator.
  
  See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
2. Create the apply process:
```
BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name     => 'strmadmin.apply_queue',
    apply_name     => 'apply_emp_dep',
    apply_captured => FALSE);
END;
/
```
  The apply_captured parameter is set to FALSE because the apply process applies changes in the persistent queue. These changes were captured by a synchronous capture. The apply_captured parameter should be set to TRUE only when the apply process applies changes captured by a capture process.
  
  Do not start the apply process.
3. Add a rule to the apply process rule set:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.employees',
    streams_type    => 'apply',
    streams_name    => 'apply_emp_dep',
    queue_name      => 'strmadmin.apply_queue',
    source_database => 'sync1.example.com');
END;
/
```
  This rule instructs the apply process apply_emp_dep to apply all DML changes that appear in the apply_queue queue to the hr.employees table. The rule also specifies that the apply process applies only changes that were captured at the sync1.example.com source database.
4. Add an additional rule to the apply process rule set:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.departments',
    streams_type    => 'apply',
    streams_name    => 'apply_emp_dep',
    queue_name      => 'strmadmin.apply_queue',
    source_database => 'sync1.example.com');
END;
/
```
  This rule instructs the apply process apply_emp_dep to apply all DML changes that appear in the apply_queue queue to the hr.departments table. The rule also specifies that the apply process applies only changes that were captured at the sync1.example.com source database.
Create a propagation to send changes from a queue at the sync1.example.com database to a queue at the sync2.example.com database:
1. In SQL*Plus, connect to the sync1.example.com database as the Oracle Streams administrator.
2. Create the propagation that sends changes to the sync2.example.com database:
```
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.employees',
    streams_name            => 'send_emp_dep',
    source_queue_name       => 'strmadmin.capture_queue',
    destination_queue_name  => 'strmadmin.apply_queue@sync2.example.com',
    source_database         => 'sync1.example.com',
    queue_to_queue          => TRUE);
END;
/
```
  The ADD_TABLE_PROPAGATION_RULES procedure creates the propagation and its positive rule set. This procedure also adds a rule to the propagation rule set that instructs it to send DML changes to the hr.employees table to the apply_queue queue in the sync2.example.com database.
3. Add an additional rule to the propagation rule set:
```
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.departments',
    streams_name            => 'send_emp_dep',
    source_queue_name       => 'strmadmin.capture_queue',
    destination_queue_name  => 'strmadmin.apply_queue@sync2.example.com',
    source_database         => 'sync1.example.com',
    queue_to_queue          => TRUE);
END;
/
```
  The ADD_TABLE_PROPAGATION_RULES procedure adds a rule to the propagation rule set that instructs it to send DML changes to the hr.departments table to the apply_queue queue in the sync2.example.com database.
Create a propagation to send changes from a queue at the sync2.example.com database to a queue at the sync1.example.com database:
1. In SQL*Plus, connect to the sync2.example.com database as the Oracle Streams administrator.
2. Create the propagation that sends changes to the sync1.example.com database:
```
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.employees',
    streams_name            => 'send_emp_dep',
    source_queue_name       => 'strmadmin.capture_queue',
    destination_queue_name  => 'strmadmin.apply_queue@sync1.example.com',
    source_database         => 'sync2.example.com',
    queue_to_queue          => TRUE);
END;
/
```
  The ADD_TABLE_PROPAGATION_RULES procedure creates the propagation and its positive rule set. This procedure also adds a rule to the propagation rule set that instructs it to send DML changes to the hr.employees table to the apply_queue queue in the sync1.example.com database.
3. Add an additional rule to the propagation rule set:
```
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.departments',
    streams_name            => 'send_emp_dep',
    source_queue_name       => 'strmadmin.capture_queue',
    destination_queue_name  => 'strmadmin.apply_queue@sync1.example.com',
    source_database         => 'sync2.example.com',
    queue_to_queue          => TRUE);
END;
/
```
  The ADD_TABLE_PROPAGATION_RULES procedure adds a rule to the propagation rule set that instructs it to send DML changes to the hr.departments table to the apply_queue queue in the sync1.example.com database.
Configure a synchronous capture at the sync1.example.com database:
1. In SQL*Plus, connect to the sync1.example.com database as the Oracle Streams administrator.
2. Run the ADD_TABLE_RULES procedure to create the synchronous capture and add a rule to instruct it to capture changes to the hr.employees table:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name    => 'hr.employees',
    streams_type  => 'sync_capture',
    streams_name  => 'sync_capture',
    queue_name    => 'strmadmin.capture_queue');
END;
/
```
3. Add an additional rule to the synchronous capture rule set:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name    => 'hr.departments',
    streams_type  => 'sync_capture',
    streams_name  => 'sync_capture',
    queue_name    => 'strmadmin.capture_queue');
END;
/
```
Running these procedures performs the following actions:
- Creates a synchronous capture named sync_capture at the current database. A synchronous capture with the same name must not exist.
- Enables the synchronous capture. A synchronous capture cannot be disabled.
- Associates the synchronous capture with an existing queue named capture_queue owned by strmadmin.
- Creates a positive rule set for synchronous capture sync_capture. The rule set has a system-generated name.
- Creates a rule that captures DML changes to the hr.employees table and adds the rule to the positive rule set for the synchronous capture. The rule has a system-generated name.
- Prepares the hr.employees table for instantiation by running the DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION function for the table automatically.
- Creates a rule that captures DML changes to the hr.departments table and adds the rule to the positive rule set for the synchronous capture. The rule has a system-generated name.
- Prepares the hr.departments table for instantiation by running the DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION function for the table automatically.
Configure a synchronous capture at the sync2.example.com database:
1. In SQL*Plus, connect to the sync2.example.com database as the Oracle Streams administrator.
2. Run the ADD_TABLE_RULES procedure to create the synchronous capture and add a rule to instruct it to capture changes to the hr.employees table:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name    => 'hr.employees',
    streams_type  => 'sync_capture',
    streams_name  => 'sync_capture',
    queue_name    => 'strmadmin.capture_queue');
END;
/
```
3. Add an additional rule to the synchronous capture rule set:
```
BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name    => 'hr.departments',
    streams_type  => 'sync_capture',
    streams_name  => 'sync_capture',
    queue_name    => 'strmadmin.capture_queue');
END;
/
```
Step 8 describes the actions performed by these procedures at the current database.

Set the instantiation SCN for the tables at the sync2.example.com database:

In SQL*Plus, connect to the sync1.example.com database as the Oracle Streams administrator.

Set the instantiation SCN for the hr.employees table at the sync2.example.com 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@sync2.example.com(
    source_object_name    => 'hr.employees',
    source_database_name  => 'sync1.example.com',
    instantiation_scn     => iscn);
END;
/

Set the instantiation SCN for the hr.departments table at the sync2.example.com 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@sync2.example.com(
    source_object_name    => 'hr.departments',
    source_database_name  => 'sync1.example.com',
    instantiation_scn     => iscn);
END;
/

An instantiation SCN is the lowest SCN for which an apply process can apply changes to a table. Before the apply process can apply changes to the shared tables at the sync2.example.com database, an instantiation SCN must be set for each table.

Set the instantiation SCN for the tables at the sync1.example.com database:

In SQL*Plus, connect to the sync2.example.com database as the Oracle Streams administrator.

Set the instantiation SCN for the hr.employees table at the sync1.example.com 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@sync1.example.com(
    source_object_name    => 'hr.employees',
    source_database_name  => 'sync2.example.com',
    instantiation_scn     => iscn);
END;
/

Set the instantiation SCN for the hr.departments table at the sync2.example.com 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@sync1.example.com(
    source_object_name    => 'hr.departments',
    source_database_name  => 'sync2.example.com',
    instantiation_scn     => iscn);
END;
/

Start the apply process at each database:
1. In SQL*Plus, connect to the sync1.example.com database as the Oracle Streams administrator.
2. Start the apply process:
```
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_emp_dep');
END;
/
```
3. In SQL*Plus, connect to the sync2.example.com database as the Oracle Streams administrator.
4. Start the apply process:
```
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_emp_dep');
END;
/
```
If you would rather start the apply processes using Enterprise Manager, then see "Starting and Stopping an Apply Process" for instructions.
Configure latest time conflict resolution for the hr.departments and hr.employees tables at the sync1.example.com and sync2.example.com databases. See "Tutorial: Configuring Latest Time Conflict Resolution for a Table" for instructions.

A two-database replication environment with the following characteristics is configured:

Each database has a synchronous capture named sync_capture. The synchronous capture captures all DML changes to the hr.employees hr.departments tables.
Each database has a queue named capture_queue. This queue is for the synchronous capture at the database.
Each database has an apply process named apply_emp_dep. The apply process applies all DML changes to the hr.employees table and hr.departments tables.
Each database has a queue named apply_queue. This queue is for the apply process at the database.
Each database has a propagation named send_emp_dep. The propagation sends changes from the capture_queue in the local database to the apply_queue in the other database. The propagation sends all DML changes to the hr.employees and hr.departments tables.
Tags are used to avoid change cycling in the following way:
- Each apply process uses the default apply tag. The default apply tag is the hexadecimal equivalent of '00' (double zero).
- Each synchronous capture only captures changes in a session with a NULL tag. Therefore, neither synchronous capture captures the changes that are being applied by the local apply process. The synchronous capture rules instruct the synchronous capture not to capture these changes.
See "About Tags for Avoiding Change Cycling" for more information about how the replication environment avoids change cycling.

To check the Oracle Streams replication configuration:

At each database, complete the following steps to ensure that synchronous capture is configured:
1. Start SQL*Plus and connect to the database as the Oracle Streams administrator.
  
  See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.
2. Query the ALL_SYNC_CAPTURE data dictionary view:
```
SELECT CAPTURE_NAME FROM ALL_SYNC_CAPTURE;
```
  Ensure that a synchronous capture named sync_capture exists at each database.
At each database, ensure that the propagation is enabled. To do so, follow the instructions in "Viewing Information About a Propagation", and check Status on the Propagation subpage.
At each database, ensure that the apply process is enabled. To do so, follow the instructions in "Viewing Information About an Apply Process", and check Status on the Apply subpage.

To replicate changes:

At one of the databases, make DML changes to the hr.employees table or hr.departments table.
After some time has passed to allow for replication of the changes, use SQL*Plus to query the hr.employees or hr.departments table at the other database to view the changes.

Tutorial: Configuring Two-Database Replication with Synchronous Captures

Related Topics