Multi-Path Replication from Oracle to HANA DB

Set up a Multi-Path Replication system with two primary and replicate connections for end-to-end replication from an Oracle primary to a HANA DB replicate.

Prerequisites

This scenario assumes you have already installed and configured:

a primary Oracle database with LogMiner
two Replication Agent instances
Replication Server
ExpressConnect for HANA DB
a replicate HANA DB database

Task

At the primary Oracle database, select or create two tables that you want to replicate through two replication paths.
Create a default connection to the primary Oracle database:
```
create connection to pds.pdb
set error class rs_sqlserver_error_class
set function string class rs_oracle_function_class
set username muser
set password mpwd
with log transfer on,
dsi_suspended
go
```
where:
- pds is the value of the rs_source_ds parameter specified in Replication Agent.
- pdb is the value of rs_source_db specified in Replication Agent.
- muser is the maintenance user for the primary Oracle database.
- mpwd is the maintenance user password.

Create an alternate connection to the primary Oracle database:

create alternate connection to pds.pdb
named pds.conn2
set error class rs_sqlserver_error_class
set function string class rs_oracle_function_class
set username muser
set password mpwd
with primary only
go

At the replicate HANA DB instance, create a maintenance user for the replicate HANA DB instance:
```
CREATE USER muser PASSWORD mpwd;
```
Grant these authorities to the maintenance user:
- CREATE ANY
- DELETE
- DROP
- EXECUTE (on all replicate stored procedures)
- INDEX
- SELECT
- UPDATE (on all replicate tables)
If you are connecting to HANA DB with a standard connection and not using SAP Secure User Store, add an entry to your interfaces file identifying the replicate HANA DB data server, and restart Replication Server:
```
[dataservername]
master tcp ether hostname port 
query tcp ether hostname port
```
where hostname and port are the host and port number of the HANA DB dataserver, and dataservername is a label used to identify the host and port number.
If you are using SAP Secure User Store, create a user store of encrypted credentials:
```
hdbuserstore set rds myhost:xxxxx my_securestore_user my_securestore_pwd
```
where
- rds is the key for the secure store entry
- myhost:xxxxx is the connection environment host name and port number
- my_securestore_user and my_securestore_pwd are SAP Secure User Store credentials
Create a connection to the replicate HANA DB instance using ExpressConnect for HANA DB.
For a standard connection:
```
create connection to rds.rdb
using profile rs_ase_to_hanadb;ech
set username muser
set password mpwd
go
```
For SAP Secure User Store:
```
create connection to rds.rdb
using profile rs_ase_to_hanadb;ech
set username auser
set password apwd
set dsi_connector_sec_mech to "hdbuserstore"
go
```
where:
- rds is the replicate HANA DB data server. For a standard connection, this must match the interfaces file entry. For an SAP Secure User Store connection, this must match what you used as the key to create a user store of encrypted credentials with the hdbuserstore utility.
- rdb is placeholder: You must provide a value, but it is not used..
- muser is the maintenance user for the replicate HANA DB instance.
- mpwd is the replicate HANA DB maintenance user password.
- auser and apwd are unused values supplied only to satisfy the syntax of the create connection command.
Note: The secure store must be created with the same operating system user ID that starts and runs Replication Server. Otherwise, Replication Server cannot access the secure user store.

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

For a standard connection:

create alternate connection to rds.conn2
using profile rs_ase_to_hanadb;ech
set username muser
set password mpwd
go

For SAP Secure User Store:

create connection to rds.conn2
using profile rs_ase_to_hanadb;ech
set username auser
set password apwd
set dsi_connector_sec_mech to "hdbuserstore"
go

Grant create object permission to rs_username on Replication Server:
```
grant create object to rs_username
go
```
where rs_username is the user login name that Replication Agent uses for Replication Server access.
For one instance of Replication Agent, set the ra_admin_owner, ra_admin_prefix, ra_admin_instance_prefix, rs_source_ds, and rs_source_db parameters:
```
ra_config ra_admin_owner, ra_user_1
ra_config ra_admin_prefix, ra_
ra_config ra_admin_instance_prefix, ri1
ra_config rs_source_ds, pds
ra_config rs_source_db, pdb
```
where
- ra_user_1 is the user name used to create shared and instance objects in the primary database for use by Replication Agent instances. This user name must already be defined at the primary data server.
- ra_ is the prefix used to identify share objects in the primary datbase. This prefix can be no longer than three characters.
- ri1 is the prefix that uniquely identifies this Replication Agent instance in the replication group.
- The value of rs_source_ds combined with the value of rs_source_db forms the connection name that this Replication Agent instance uses to connect to Replication Server.
Initialize the Replication Agent instance:
```
ra_admin init
```
For the other instance of Replication Agent, set the ra_admin_owner, ra_admin_prefix, ra_admin_instance_prefix, rs_source_ds, and rs_source_db parameters:
```
ra_config ra_admin_owner, ra_user_1
ra_config ra_admin_prefix, ra_
ra_config ra_admin_instance_prefix, ri2
ra_config rs_source_ds, pds
ra_config rs_source_db, conn2
```
where
- ri2 is the prefix that uniquely identifies this Replication Agent instance in the replication group.
- The values of ra_admin_owner and ra_admin_prefix are the same as all other Replication Agent instances in the replication group.
- The value of rs_source_ds combined with the value of rs_source_db forms the connection name that this Replication Agent instance uses to connect to Replication Server.
Initialize the Replication Agent instance:
```
ra_admin init
```
Mark the two tables from Step1 for replication. On the Replication Agent instance identified by ri1:
```
pdb_setreptable ptab1, mark
go
```
On the Replication Agent instance identified by ri2:
```
pdb_setreptable ptab2, mark
go
```
where ptab1 and ptab2 are the primary database tables to be replicated.
Create two replication definitions against the primary Oracle 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.
Resume the Replication Agent instances:
```
resume
```
If the Replication Agent instance fails to resume, verify that LogMiner is installed and configured. See the Replication Agent Primary Database Guide > Replication Agent for Oracle > Oracle-Specific Considerations > Oracle Transaction and Operation Troubleshooting > Setting Up Replication Agent and Oracle to use ra_dumptran and ra_helpop.
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 rds.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 rds.conn2
without materialization
go
```