Configuring Synchronous Capture - Oracle Streams

Synchronous capture (also implicit capture) is an optional Oracle Streams client that captures data manipulation language (DML) changes made to tables. Synchronous capture uses an internal mechanism to capture DML changes to specified tables.

When a DML change it made to a table, it can result in changes to one or more rows in the table. Synchronous capture captures each row change and converts it into a specific message format called a row logical change record (row LCR). After capturing a row LCR, synchronous capture enqueues a message containing the row LCR into a queue. Row LCRs created by synchronous capture always contain values for all the columns in a row, even if some of the columns where not modified by the change.

The need of using Synchronous capture is when:

a) you have fewer number of tables to replicate
b) replicating DML changes is a concern
c) you are using Oracle RDBMS Standard Edition

I will be using Oracle RDBMS 11.2.0.4 for this setup. We currently have:

1. Source database (11.2.0.4 Standard Edition) with schema ORDERS having only one table ORDATA - PROD.EXMPLE.COM
2. ORDERS schema has default tablespace USERS.
3. A newly created destination database on another server, a USERS tablespace has been added - REPL.EXAMPLE.COM
4. Schema Export of ORDERS is imported into REPL database.

Aim: Replicate DML changes in ORDERS.ORDATA table from PROD to REPL.

In my previous posts, I have already discussed about setting up environment and pre-requisite tasks. Please follow steps in Setup Oracle Streams - Datapump Instantiation to setup Stream Administrator user and other prerequisites.

Setup Synchronous Capture Replication

1. Create ANYDATA queue on source and destination databases

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_table        =>'prod_to_repl_queue_table',
   queue_name          =>'prod_to_repl');
END;
/

ANYDATA queue stores LCRs (logical change records) captured by synchronous capture process at source database and ships them to destination database's ANYDATA queue.

2. Create APPLY process at destination database

BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name     => 'streamadm.prod_to_repl',
    apply_name     => 'APPLY_REPL',
    apply_captured => FALSE);
END;
/

As we are using change capture by a synchronous capture the apply_captured parameter is set to FALSE because the apply process applies changes in the persistent queue. The apply_captured parameter should be set to TRUE only when the apply process applies changes captured by a capture process.

APPLY process will be started later, thus do not start it at this point.

3. Add APPLY rules at destination database

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'ORDERS.ORDATA',
    streams_type    => 'apply',
    streams_name    => 'APPLY_REPL',
    queue_name      => 'streamadm.prod_to_repl',
    source_database => 'PROD.EXAMPLE.COM');
END;
/

Apply rules indicates apply process to apply all DML changes that appear in apply queue to specified table name. Repeat this step for as many tables as replication is desired for.

4. Create PROPAGATION on source

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name             => 'ORDERS.ORDATA',
    streams_name           => 'CAPTURE_PROD',
    source_queue_name      => 'streamadm.prod_to_repl',
    destination_queue_name => 'streamadm.prod_to_repl@REPL.EXAMPLE.COM',
    source_database        => 'PROD.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 from ORDERS.ORDATA to apply_queue. Repeat this step for as many tables as replication is desired for.

5. Create SYNCHRONOUS CAPTURE process at source database

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'ORDERS.ORDATA',
    streams_type    => 'sync_capture',
    streams_name    => 'CAPTURE_PROD',
    queue_name      => 'streamadm.prod_to_repl');
END; 
/

Executing this procedure:

• 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 prod_to_repl owned by streamadm.

• 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 ORDERS.ORDATA table and adds the rule to the positive rule set for the synchronous capture. The rule has a system-generated name.

• Prepares the ORDERS.ORDATA table for instantiation by running the DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION function for the table automatically.

Repeat this step for as many tables as replication is desired for.

6. INSTANTIATE objects at destination

DECLARE
  iscn  NUMBER;
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@REPL.EXAMPLE.COM(
    source_object_name      => 'ORDERS.ORDATA',
    source_database_name    => 'PROD.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 tables at the destination database, an instantiation SCN must be set for each table.

7. Start APPLY process
Run below procedure to start APPLY process at destination database.

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'APPLY_REPL');
END;
/

Test out the replication!

References:
Oracle's Data Replication and Integration Guide [https://docs.oracle.com/cd/E11882_01/server.112/e17516/toc.htm]