Use SharePlex to Replicate Oracle Data to Amazon RDS

In a previous blog we introduced you to SharePlex 8.6.6, which is certified to replicate data to Amazon and Azure Cloud Databases.   In this blog, we’ll show you how easy it is to set up replication from an Oracle database, either on-premise or in an Infrastructure Cloud, to an Amazon RDS database.

AWS Configuration

The Target Database

The first thing you’ll need is an RDS Oracle database for a target.  There’s nothing special needed at the database level, but you will need to make sure to note the Username and Endpoint and make your database Publicly Accessible.  Here’s a screenshot that shows how your database should be configured.

Security Groups

Next, you’ll need to set up a Security Group so that your database is accessible from outside the Amazon Virtual Private Cloud. You may need to ask your Network Administrators for the external Internet Address that your connection to AWS uses.   Here’s what this should look like.

SharePlex Configuration

Here’s where things get a little different from a “standard” SharePlex installation.   Normally, we install and run SharePlex processes on both the source and target servers.   However, since we’re letting Amazon manage the server where our RDS instance is running, we don’t have, or need, access to the underlying server on the target.  So, we’ll only install SharePlex on the source server.

Installation

Source Database

The first part of the installation (SharePlex Binaries and directories) on the source server doesn’t change at all. Just follow and answer the prompts.   The second part of the installation is where things are different.   First, you’ll run ora_setup against the SOURCE database, as normal.   Don’t forget to answer “Y” to the question about using the database as a master.

Target Database

For the target database, since we don’t have a server, we only need to run ora_setup, a second time, on the source server.   Before starting that, you’ll probably want to create a TNS NAMES entry, so that you won’t need to enter the long endpoint.  Here’s an example TNS entry:

AWS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL=TCP)(HOST=sptgt.xyzznyenm88tt.us-west-2.rds.amazonaws.com)(PORT=1521)

)

(CONNECT_DATA= (SID=ORCL)

)

)

Once you have the TNS entry, use the Oracle tnsping utility to verify connectivity, and login with the Username and Password you noted earlier.   After you resolve any connectivity issues, run ora_setup, which can be found in the SharePlex proddir/bin directory.   When we run ora_setup for the second time, we need to be sure to respond ‘N’ to the “Will the SharePlex install be using a BEQUEATH connection?” question, and enter the TNS alias for the target database.  Here’s what a setup session for an RDS target should look like:

[oracle@test bin]$ ./ora_setup

 

Welcome to the Oracle SharePlex setup process for port 2100.

This process creates tables and user accounts needed to run

Oracle SharePlex replication.

 

Will the SharePlex install be using a BEQUEATH connection? (Entering 'n' implies a SQL*net connection)  [y] : N

Please note the following:

** In response to prompts, a carriage return will choose the default

   given in brackets.  If there is no default, a reply must be entered.

 

** To exit the program while the program is waiting for input, use the

   CTRL-C key sequence.

   This sequences can be entered by holding down the CONTROL key and

   pressing the C key.

 

Enter the TNS alias for which SharePlex should be installed [ORLI11] : AWS

 

Verifying TNS supplied ... done.

 

In order to create the SharePlex tables and user account, we must

connect to the database as a DBA user

 

Enter a DBA user for AWS  : spmstr

NOTE: Appending the tns alias to the password via @ sign is not needed.

 

Enter password for the DBA account, which will not echo :

 

connecting--This may take a few seconds.

 

validating user name and password. . . This may take a few seconds.

SharePlex objects will need to be created under a special

account.  You can pick an existing user or create a new one.

 

Current SharePlex user is : SPLEX

 

Warning: Changing SharePlex user requires

reactivating the current configuration.

Would you like to create a new SharePlex user ? [y] : Y

NOTE: Appending the tns alias to the password via @ sign is not needed.

Enter username for new user [splex/splex] :

 

Warning:  This user is now being granted unlimited tablespace.

This privilege will remain in effect until it is explicitly changed.

Granting select on sys.user$ to splex

SPLEX_ROLE_BOTH  already exists; continuing setup . . .

 

Do you want to enable replication of tables with TDE? [n] :

 

To enable replication of tables with TDE in the future, please rerun ora_setup.

 

Setup will now install SharePlex objects.

 

These are the existing tablespaces.

 

SYSTEM SYSAUX UNDO_T1 TEMP USERS RDSADMIN

 

Enter the default tablespace for use by SharePlex [USERS] : users

 

Enter the temporary tablespace for use by SharePlex [TEMP] :

Enter the index tablespace for use by SharePlex [ ] : users

 

Creating SharePlex objects [Installation type: Fresh]. . .

 

Creating SharePlex Oracle-timezone-region map . . . Done.

 

Creating Conflict Resolution Package . . . Done.

 

Creating SharePlex Dataequator package . . .

 

Loading Compare Package from "/home/oracle/splex/proddir/util/sp_deq_pkg.plb"...Done.

 

Note: Cannot install Compare Varray package because Oracle Spatial datatype is not installed.

 

Do you want to continue? [n] : y

 

Will the current setup for sid: [AWS] be used as source (including cases as source for failover or master-master setups)?  [y] : n

 

Setup of SharePlex objects successful . . .

 

Setup completed successfully

Configuration Files

The last step in setting up replication is a configuration file.   Here’s a sample that will replicate splex.demo_src on the source to splex.demo_dest on the target.

 datasource:o.ORLI11

 

#source tables      target tables           routing map

 

splex.demo_src      splex.demo_dest         test.dev@o.aws

Note that we use the hostname of the source server in the target routing map.  This tell’s SharePlex to run all of its processes on the same (source) server.  The only connection to the target is through SQL*NET.   Once we’ve activated the config file (don’t forget to VERIFY CONFIG first), we can use the STATUS command to see that we’re running all of the processes on the source.

sp_ctrl (test:2100)> status

Brief Status for test

Process          State                             PID     Running   Since

---------------  ------------------------------  --------  --------------------

Cop              Running                            31464  08-Feb-17 19:48:10

Cmd & Ctrl       Running                            31466  08-Feb-17 19:48:15

Capture          Running                            31510  08-Feb-17 19:52:18

Read             Running                            31517  08-Feb-17 19:52:18

Post             Running                            31527  08-Feb-17 19:52:20

 

System is used as a source machine

There is 1 active configuration file

 

Note that there are no export or import processes.  Since we’re running on the same host, read can communicate directly with post.

From this point, SharePlex will operate normally, including the compare and repair utilities.

Find more information or download a SharePlex data replication tool free trial.   In a future blog, we’ll cover replication from an Oracle on-premise database to a SQLServer RDS database.

About the Author
Clay.Jackson
Clay Jackson is a Database Systems Consultant for Quest, specializing in Database Performance Management and Replication Tools. Prior to joining Quest, Jackson was the DBA Manager at Darigold. He also...