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.
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.
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.
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.
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.
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:
(ADDRESS = (PROTOCOL=TCP)(HOST=sptgt.xyzznyenm88tt.us-west-2.rds.amazonaws.com)(PORT=1521)
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
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
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.
#source tables target tables routing map
splex.demo_src splex.demo_dest firstname.lastname@example.org
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.