-
Title
How to setup Oracle to RDS SharePlex Replication -
Description
SharePlex replication from Oracle On-premises to Oracle on RDS.
-
Cause
In this scenario, Capture, running on premises, connects to the source database through a bequeath connection and the Post process connects to the RDS database through TNS.
NOTE: In a high-volume transactional environment, the buildup of data in the post queues and the presence of multiple Post processes may generate unacceptable overhead for a production system. In that case, you should use an intermediary server.
You can use an on-premises intermediary server to run the Import and Post components (and the post queues). This method removes most of the replication overhead from the source server.
Demo Environment:
Source: Oracle on Premises.
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago).Hostname: Source_Server.SharePlex Version: SharePlex-9.1.0-b11-oracle110-rh-40-amd64-m64.DB: Oracle Version 11.2.0.4.ORACLE_SID= ora11gr2ORACLE_HOME= /u01/app/oracle11.2.0.4/product/11.2.0/dbhome_1/Port :1521Target: Oracle Amazon RDS
DB : Oracle 12c Oracle Amazon RDSDB Name : oraclerdsMaster Username/passwd : oraclerds/oraclerdsEndpoint/Hostname: oraclerds.ch2b8qrh6oss.us-east-2.rds.amazonaws.comPort :1521 -
Resolution
Steps overview:
1. Install SharePlex.2. Configure TNSnames.ora.3. Run ora_setup.4. Prepare and activate config.5. Verify Replication.Steps in detail:
1. Install SharePlex
Install SharePlex on the source (and on the intermediary server, if using one for the Post process).
This demo assumes the SharePlex binaries are already installed. If you need assitance with the install, please refer to the SharePlex 9.1 Install guide.
2. Configure TNSnames.ora:
ORACLERDS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =oraclerds.ch2b8qrh6oss.us-east-2.rds.amazonaws.com )(PORT = 1521))
(CONNECT_DATA =
(SID = ORCL)
)
)
3. Running ora_setup:
This process creates tables and user accounts needed to run Oracle SharePlex replication.
NOTE: ora_setup will need to be run twice, once for the source on premises instance and secondly for the RDS target instance.
On the source, run ora_setup for the source database.
- Reply YES to "Will SharePlex install be using a BEQUEATH connection?"
- Reply NO to "Are you setting up SharePlex for an RDS database?"
- Reply YES to "Will the current setup for sid: SID be used as a source (including cases as source for failover or master-master setups)?"
- Will the SharePlex install be using a BEQUEATH connection? (Entering 'n' implies a SQL*net connection) [y] : y
- Enter the Oracle SID for which SharePlex should be installed [ora11gr2] : ora11gr2
- Enter a DBA user for ora11gr2 : system
- Enter user password for splexrds : example
- Would you like to create a new SharePlex user ? [y] : y
- Enter username of an existing user : splexrds
- Enter user password for splexrds :splexrds
On the source or intermediary (if using one) run ora_setup again, this time for the RDS target database.- Reply NO to "Will SharePlex install be using a BEQUEATH connection?"
- Reply YES to "Are you setting up SharePlex for an RDS database?" When you answer YES, it automatically configures Post to use a TNS connection and prompts for the RDS master user, which has the required privileges to install SharePlex replication objects in the RDS database.
- Reply NO to "Will the current setup for sid: SID be used as a source (including cases as source for failover or master-master setups)?"
- Will the SharePlex install be using a BEQUEATH connection? (Entering 'n' implies a SQL*net connection) [y] : n
- Are you configuring SharePlex for an AWS RDS Database? (Entering 'n' implies non-RDS database) [n] : y
- Enter the TNS alias for which SharePlex should be installed (Blank to abort) : oraclerds
- Enter the RDS Master user for oraclerds (Blank to abort) : oraclerds
- Enter password for the RDS Master user, which will not echo. Note: It is not necessary to append the TNS alias to the password with an @ sign : oraclerds.
4. Prepare and activate the configuration file:Prepare Config:
Set up routing in the configuration file
When you create your configuration file, use the name of the local host (where SharePlex is installed) in therouting map so that Post runs locally. The following is an example:datasource:o.sourcesplex.demo_src splex.demo_dest localhost@o.targetdatasource:o.ora11gr2#source tables target tables routing mapsplexrds.demo_src splexrds.demo_dest Source_Server@o.ORACLERDSWhere:Source SID: ora11gr2Source Table: splexrds.demo_srcTarget Table: splexrds.demo_destTarget SID: oraclerds ( tns_alias ORACLERDS,HOST=oraclerds.ch2b8qrh6oss.us-east-2.rds.amazonaws.com)Activate config
sp_ctrl> verify config rds
sp_ctrl> activate config rdssp_ctrl (Source_Server:2018)> showProcess Source Target State PID---------- ------------------------------------ ---------------------- -------------------- ------Capture o.ora11gr2 Running 15835Read o.ora11gr2 Running 15842Post o.ora11gr2-Source_Server o.oraclerds Running 158595. Verify Replication:
Oracle on Premises, Insert DML into splexrds.demo_src and verify the data is replicated to splexrds.demo_dest on Oracle RDS.
In Toad for Oracle, establish a connection to Oracle RDS.
For Example: oraclerds@ORACLERDS.CH2B8QRH6OSS.US-EAST-2.RDS.AMAZONAWS.COM:1521/ORCL
Select * from splexrds.demo_dest;
-
Additional Information
Connections.yaml o.ORA11GR2: database: ora11gr2 dstype: oracle password: 579fd983ba5101fd plugin_direction: both plugin_version: 1 user: SPLEXRDS o.ORACLERDS: database: oraclerds dstype: oracle password: 579fd983ba5101fd plugin_direction: both plugin_version: 1 tns_alias: oraclerds user: SPLEXRDS