SharePlex 9.2.8 - Installation and Setup for Oracle on RDS

Setup for EC2 source, RDS target

In this scenario, SharePlex runs as an Amazon Machine Image instance on the EC2 service. Capture connects to the source database through TNS and the Post process connects to the RDS database through TNS.

Obtain a SharePlex instance

To obtain a SharePlex AMI instance, see Configure a SharePlex AMI instance

Log on to the SharePlex AMI instance

Always log on to the SharePlex AMI instance as the ec2-user. This user is the SharePlex Administrator. Use the following command:

ssh -i ~/.ssh/pem_file_name.pem ec2-user@IP_address/hostname

where:

• pem_file_name is the name of the .pem file that you downloaded, which contains the private key of the security key pair.
• IP_address/hostname is either the IPv4 public IP address or, if present, the host name that is mapped to this IP address in your domain name server or /etc/hosts file.

Create a tnsnames.ora file

On the host of the SharePlex AMI instance, create a tnsnames.ora file with entries for the source and target databases.

The following are examples:

source =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = yoursource.amazonaws.com) (PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME=orcl)
    )
  )
target  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = yourtarget.amazonaws.com) (PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME=orcl)
    )
  )

Run ora_setup

1. On the SharePlex AMI instance, run ora_setup for the source database. See the directions for running Oracle Setup in Database Setup for Oracle.

   • Reply NO to "Will SharePlex install be using a BEQUEATH connection?"
   • 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)?"
   • Reply NO to "Are you setting up SharePlex for an RDS database?"

2. On the SharePlex AMI instance, run ora_setup for the 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?"
   • 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)?"

Set up remote capture

1. On the SharePlex AMI instance, start sp_cop.
2. Start sp_ctrl.
3. Set the SP_OCT_OLOG_USE_OCI parameter to 1, which enables Capture to read the redo records through OCI, rather than directly from the redo files.

Note: Enabling the SP_OCT_OLOG_RDS_MINER parameter is deprecated and no longer supported starting with Oracle 19c.

Set up routing in the configuration file

When you create the SharePlex configuration file, use the name of the EC2 host of the SharePlex AMI instance in the routing map so that Post runs locally. The following is an example:

Setup for RDS source, RDS target in the same region

In this scenario, SharePlex runs as an Amazon Machine Image instance on the EC2 service. Capture connects to the source database through TNS, and the Post process connects to the RDS database through TNS.

Obtain a SharePlex instance

Obtain a SharePlex AMI instance. See Configure a SharePlex AMI instance

Log on to the SharePlex AMI instance

Always log on to the SharePlex AMI instance as the ec2-user. This user is the SharePlex Administrator. Use the following command:

ssh -i ~/.ssh/pem_file_name.pem ec2-user@IP_address/hostname

where:

• pem_file_name is the name of the .pem file that you downloaded, which contains the private key of the security key pair.
• IP_address/hostname is either the IPv4 public IP address or, if present, the host name that is mapped to this IP address in your domain name server or /etc/hosts file.

Create a tnsnames.ora file

1. On the SharePlex AMI instance, create a tnsnames.ora file with entries for the source and target databases.

   Important! The tnsnames.ora file must be created in this directory: /usr/lib/oracle/11.2/client64/network/admin. The TNS alias cannot contain any dashes (-).

   The following are examples:

   source =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = yoursource.amazonaws.com) (PORT = 1521))
       (CONNECT_DATA =
         (SERVICE_NAME=orcl)
       )
     )
   target  =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = yourtarget.amazonaws.com) (PORT = 1521))
       (CONNECT_DATA =
         (SERVICE_NAME=orcl)
       )
     )
   

Run ora_setup

1. On the SharePlex AMI instance, run ora_setup for the source database. See the directions for running Oracle Setup in Database Setup for Oracle.

   • Reply NO to "Will SharePlex install be using a BEQUEATH connection?"
   • Reply YES 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)?"

2. On the SharePlex AMI instance, run ora_setup for the 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?"
   • 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)?"

Configure capture from RDS

1. On the SharePlex AMI instance, start sp_cop.
2. Start sp_ctrl.

3. On the SharePlex AMI instance, set the SP_OCT_OLOG_RDS_MINER parameter to 1.

   sp_ctrl>set param SP_OCT_OLOG_RDS_MINER 1

Note: Enabling the SP_OCT_OLOG_RDS_MINER parameter is deprecated and no longer supported starting with Oracle 19c.

Set up routing in the configuration file

When you create the SharePlex configuration file, use the name of the EC2 host of the SharePlex AMI instance in the routing map so that Post runs locally. The following is an example:

Setup for RDS source, RDS target in different regions

In this scenario, SharePlex runs as an EC2 Amazon Machine Image instance in two different AWS regions: one for the source database in region 1 and the other for the target database in region 2.

• Capture (in region 1) connects through TNS only to the source database to capture redo records through a remote connection.
• Post (in region 2) connects through TNS only to the target database to post the replicated operations through a remote connection.

Obtain SharePlex instances

Obtain two SharePlex AMI instances, one in region 1 to use as the source and the other in region 2 to use as the target. See Configure a SharePlex AMI instance.

Log on to the SharePlex AMI instances

Always log on to the SharePlex AMI instance as the ec2-user. This user is the SharePlex Administrator. Use the following command:

ssh -i ~/.ssh/pem_file_name.pem ec2-user@IP_address/hostname

where:

• pem_file_name is the name of the .pem file that you downloaded, which contains the private key of the security key pair.
• IP_address/hostname is either the IPv4 public IP address or, if present, the host name that is mapped to this IP address in your domain name server or /etc/hosts file.

Create the tnsnames.ora files

Important!

The tnsnames.ora file must be created in this directory: /usr/lib/oracle/11.2/client64/network/admin.

The TNS alias cannot contain any dashes (-).

1. On the source SharePlex AMI instance, create a tnsnames.ora file with an entry for the source database.

   The following is an example:

   source =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = yoursource.amazonaws.com) (PORT = 1521))
       (CONNECT_DATA =
         (SERVICE_NAME=orcl)
       )
     )
   

2. On the target SharePlex AMI instance, create a tnsnames.ora file with an entry for the target database. The tnsnames.ora file must be created in this directory: /usr/lib/oracle/11.2/client64/network/admin.

   The following is an example:

   target  =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = yourtarget.amazonaws.com) (PORT = 1521))
       (CONNECT_DATA =
         (SERVICE_NAME=orcl)
       )
     )
   

Run ora_setup

1. On the source SharePlex AMI instance, run ora_setup for the source database. See the directions for running Oracle Setup in Database Setup for Oracle.

   • Reply NO to "Will SharePlex install be using a BEQUEATH connection?"
   • Reply YES 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)?"

2. On the target SharePlex AMI instance, run ora_setup for the 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?"
   • 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)?"

Configure capture from RDS

1. On the source SharePlex AMI instance, start sp_cop.
2. Start sp_ctrl.

3. Set the SP_OCT_OLOG_RDS_MINER parameter to 1 to enable it.

   sp_ctrl>set param SP_OCT_OLOG_RDS_MINER 1

Note: Enabling the SP_OCT_OLOG_RDS_MINER parameter is deprecated and no longer supported starting with Oracle 19c.

Set up routing in the configuration file

When you create the SharePlex configuration file, create it on the source SharePlex AMI instance. Use the name of the EC2 host of the target SharePlex AMI instance in the routing map, so that Post runs on that AMI. The following is an example:

Oracle Setup (ora_setup)

Overview

Use the Database Setup utility for Oracle (ora_setup) to establish SharePlex as an Oracle user and create the required SharePlex database objects. This setup utility creates the following:

• A SharePlex account
• Tables and other objects for use by SharePlex and owned by the SharePlex account
• Default connection for the SharePlex user

It is recommended that you review all of the content in this topic before running the setup utility.

Supported databases

Oracle source or target on supported platforms

When to run Oracle Setup

Whether or not to run this utility at the time of SharePlex installation depends on whether the database is a source, intermediary, or target database, and on how you intend to synchronize the data. To view the initial synchronization procedures, see the SharePlex Administration Guide.

Supported Oracle Connections

The setup utility can configure any of the following connections for the SharePlex user to use when connecting to the database.

Required run privileges

The user who runs the Database Setup utility must have the following privileges:

Amazon RDS source or target

The user who runs the setup utility must be the master user that was created when the Oracle RDS instance was created. You are prompted for this user during the setup.

Non-multitenant (standard) database

The user who runs the setup utility must have DBA privileges.

Multitenant database

The user who runs the setup utility should have SYSDBA privileges (recommended), but at minimum the user should be a DBA user with privileges for sys.users$ and sys.enc$. The minimum following grants are required for the SharePlex user:

create user c##sp_admin identified by sp_admin;

grant dba to c##sp_admin container=ALL;

grant select on sys.user$ to c##sp_admin with grant option container=ALL;

Privileges granted to SharePlex