Multi-Path Replication from Adaptive Server to Oracle

Set up a Multi-Path Replication system with two primary and replicate paths for end-to-end replication from an Adaptive Server primary to an Oracle replicate.

Prerequisites

This scenario assumes you have already installed and configured:

a primary Adaptive Server database and RepAgent thread
Replication Server
ExpressConnect for Oracle
a replicate Oracle database

Task

At the primary Adaptive Server database, select or create two sets of tables or stored procedures that you want to replicate through two replication paths. These transaction sets must be divisible into parallel replication paths.
Create a default connection to the primary Adaptive Server database.

Use create connection or rs_init to create the default connection. See Replication Server Configuration Guide > Configure Replication Server and Add Databases using rs_init.
Enable Multithreaded Adaptive Server RepAgent and Multiple Paths for Adaptive Server RepAgent.
You can also set the memory and send buffers available to Adaptive Server RepAgent. See Replication Server Administration Guide Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Enabling Multithreaded RepAgent and Multiple Paths for RepAgent.
1. Enable multithreaded RepAgent.
  At the primary Adaptive Server, enter:
```
sp_config_rep_agent pdb, ‘multithread rep agent’, ‘true’
```
  where pdb is the primary Adaptive Server database.
2. Set the number of replication paths for RepAgent.
  For example, to enable two paths, enter:
```
sp_config_rep_agent pdb, 'max number of replication paths', '2'
```
Create an alternate replication path from the primary database.
See Replication Server > Administration Guide: Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Creating Multiple Primary Replication Paths.
1. Create an alternate replication path named pdb_conn2 between the primary database and Replication Server.
  At the primary database, enter:
```
sp_replication_path "pdb", 'add',
"pdb_conn2", "PRS",
"muser", "mpwd"
```
  where:
  - PRS is the Replication Server.
  - muser is the maintenance user.
  - mpwd is the maintenance user password.
  (Optionally) Create logical paths that you can use to distribute data and objects bound to a physical path to multiple Replication Servers. See Replication Server Administration Guide: Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Creating Logical Primary Replication Paths .
2. Create the corresponding alternate primary connection in Replication Server, and use the same Adaptive Server data server name with the alternate path name—pdb_conn2.
  At the Replication Server, enter:
```
create alternate connection to pds.pdb
named pds.pdb_conn2
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to muser
set password to mpwd
with primary only
```
  where pds is the primary Adaptive Server.
The replication system contains two primary replication paths—the default and pdb_conn2.
Use admin show_connections, 'primary' to display the primary connections you have created.
Copy the tnsnames.ora file for your replicate Oracle database to the Replication Server RS_installation_directory\ REP-15_5\connector\oraoci\network\admin directory, and restart Replication Server.
Create a connection to the replicate Oracle database through ExpressConnect for Oracle.
```
create connection to tns_alias_name.rdb
using profile rs_oracle_to_oracle;eco
set username muser
set password mpwd
set dsi_dataserver_make to 'ora'
set dsi_connector_type to 'oci'
set batch to 'off'
go
```
where:
- tns_alias_name is the alias name for the replicate Oracle database defined in the tnsnames.ora file for the replicate Oracle database.
- rdb is the replicate Oracle System ID (SID) that is paired with the above tns_alias_name in the tnsnames.ora file. The default value is ORCL.
- muser is the maintenance user for the replicate Oracle database.
- mpwd is the replicate Oracle maintenance user password.
See the ExpressConnect for Oracle Installation and Configuration Guide for more information about naming the default connection.

Create an alternate connection to the replicate Oracle database through ExpressConnect for Oracle.

create alternate connection to tns_alias_name.rdb
named tns_alias_name.conn2
set error class rs_oracle_error_class
set function string class rs_oracle_function_class 
set username muser
set password mpwd
set dsi_dataserver_make to 'ora'
set dsi_dataserver_type to 'oci'
set batch to 'off'
set dsi_proc_as_rpc to 'on'
go

At the primary database, mark the ptab1 and ptab2 tables for replication.
```
sp_setreptable ptab1,'true'
go
sp_setreptable ptab2,'true'
go
```
Set the distribution mode at the primary database to distribute by object binding:
```
sp_config_rep_agent pdb, ‘multipath distribution model’, 'object’
```
Quiesce Replication Server and restart RepAgent.
See Replication Server Administration Guide Volume 1 > Manage a Replication System > Quiesce Replication Server > Quiescing a Replication System.
Bind the ptab2 table to the pdb_conn2 alternate connection.
```
sp_replication_path pdb, 'bind', "table", "dbo.ptab2", "pdb_conn2"
```
You can only bind objects to alternate replication paths. All objects that you do not bind to an alternate replication path, such as the ptab1 table, use the default path instead.
Create two replication definitions against the primary Adaptive Server database:
For example to create the ptab1_repdef replication definition for the ptab1 table:
```
create replication definition ptab1_repdef
with primary at pds.pdb
with all tables named 'ptab1'
...
go
```
To create the ptab2_repdef replication definition for the ptab2 table:
```
create replication definition ptab2_repdef
with primary at pds.pdb
with all tables named 'ptab2'
...
go
```
Note: These replication definitions must use the default primary connection name.
If the primary connection and alternate primary connection are on different Replication Servers, create replication definitions on each Replication Server.
Create a subscription against the default replicate connection.
For example, to create the ptab1_sub subscription for the ptab1_repdef replication definition:
```
create subscription ptab1_sub 
for ptab1_repdef
with replicate at tns_alias_name.rdb
without materialization
go
```
Create a subscription against the alternate replicate connection.
For example, to create the ptab2_sub subscription for the ptab2_repdef replication definition:
```
create subscription ptab2_sub 
for ptab2_repdef
with replicate at tns_alias_name.conn2
without materialization
go
```