Amazon RDS for Oracle Setup Guide

Fivetran connects directly to your Amazon RDS for Oracle database. This is the simplest method.

IMPORTANT: You must have TLS enabled on your Oracle database to connect directly to Fivetran. Follow Oracle's instructions to enable TLS on your database either with a client wallet or without a client wallet.

To connect directly, you must create a rule in a security group that allows Fivetran access to your database instance.

Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect using an SSH tunnel if your database is in an inaccessible subnet.

IMPORTANT: You must connect using an SSH tunnel if your Oracle database is version 12.1 or below.

To connect using SSH tunnel, you must configure your SSH tunnel host's security group to allow Fivetran access and configure your database's security to allow access from the tunnel host.

For more information about using SSH tunnel, follow our SSH connection instructions. If you want Fivetran to use end-to-end encryption using TLS, follow Amazon's TLS setup instructions for Oracle to enable TLS on your database.

Private networking enables communication between private networks and services without exposing traffic to the public internet. Private networking is the most secure connection method.

IMPORTANT: You must have a Business Critical plan to use private networking.

We support the following providers:

• AWS PrivateLink – used for VPCs and AWS-hosted or on-premises services. See our AWS PrivateLink setup guide for details.

• Azure PrivateLink – used for Virtual Networks (VNets) and Azure-hosted or on-premises services. See our Azure PrivateLink setup guide for details.

• Google Cloud Private Service Connect – used for VPCs and Google-hosted or on-premises services. See our Google Cloud Private Service Connect setup guide for details.

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

NOTE: These instructions assume that your database instance is in a VPC. If your database instance is not in a VPC, you can still use these instructions because configuring a non-VPC security group is an almost identical process.

1. In your Amazon RDS dashboard, click on the database instance you want to connect to Fivetran.

   

2. A panel of details for your read replica appears. In the Connectivity & security section, find the database's port number and make a note of it. You will need the port number to configure Fivetran.

   

3. (If you're connecting directly) In the Security column, verify that the Public Accessibility value is Yes.

   IMPORTANT: If you're connecting using an SSH tunnel, skip this step. You do not have to make your database publicly accessible.

   

4. Click the link to your database's security group.

   

5. On the Security Groups page, click on the security group ID.

   

6. On the Inbound tab, click Edit inbound rules.

   

7. Click Add Rule. This creates a new Custom TCP Rule at the bottom of the list.

8. Fill in the new Custom TCP Rule.

   • In the Port Range field, enter your database's port number that you copied in step 2 of this section (usually 1521).
   • What you enter in the Source Custom IP field depends on whether you're connecting directly or using an SSH tunnel.
     • If you're connecting directly, enter Fivetran's IPs for your database's region.
     • If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.
   • (Optional) Enter a brief description in the Description field.

   

9. Click Save rules.

If your database is not in a VPC, skip ahead to Step 3.

1. Return to the instance details page.

2. In the Connectivity and security section, click the link to the VPC.

   

3. On the Your VPCs page, click the VPC ID.

   

4. In the Details section, click the Main network ACL link.

   

5. Click the Network ACL ID.

   

6. Select the Inbound Rules tab.

   

7. If you have a default VPC that was automatically created by AWS, the settings already allow all incoming traffic. To verify that the settings allow incoming traffic, confirm that the Source value is 0.0.0.0/0 and that the ALLOW entry is listed above the DENY entry.

   

8. If your inbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow the Source to access the port number of your database instance. (The port will be 1521 for direct connections, unless you changed the default.) For additional help, see Amazon's Network ACL documentation.

   • If you're connecting directly, enter Fivetran's IPs for your database's region.
   • If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.

To enable LogMiner, do the following:

1. If ARCHIVELOG mode is not enabled on your database, enable ARCHIVELOG mode.

   NOTE: ARCHIVELOG mode is enabled automatically when automated backups are enabled by setting the backup retention period to a value greater than 0. For more information, see Amazon RDS's automated backups documentation.

   NOTE: We recommend that you set ARCHIVE_LAG_TARGET to a non-zero value. This setting reduces the lag in changes being synced.

2. Retain redo log files for at least 24 hours. We recommend retaining data for seven days.

   BEGIN
   rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 24);
   END;
   

3. Enable minimal supplemental logging by executing the following SQL statement. Minimal supplemental logging ensures that LogMiner has sufficient information to process the redo operations associated with DML changes.

   NOTE: To learn about minimal supplemental logging, read Oracle's database-level supplemental logging documentation.

   BEGIN
   rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD');
   END;
   

4. Enable primary key supplemental logging if you expect a row containing a primary key to change.

   To enable supplemental logging on primary key columns, execute the following SQL statement:

   BEGIN
   rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');
   END;
   

5. Grant the Fivetran user permission to run LogMiner.

   BEGIN
   rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVE_DEST','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','<username>','EXECUTE');
   rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','<username>','EXECUTE');
   END;
   

   GRANT SELECT ANY TRANSACTION TO "<username>";
   

   NOTE: To learn more about common DBA tasks for Amazon RDS for Oracle databases, see Amazon's Oracle DB instance documentation.

6. (Oracle version 12 and above) Grant logmining permissions to the Fivetran user.

   GRANT LOGMINING TO "<username>";
   

7. (Optional) Grant permissions to additional system tables.

   NOTE: We recommend granting these permissions to optimize the connector's performance and reliability.

   BEGIN
   rdsadmin.rdsadmin_util.grant_sys_object('DBA_FREE_SPACE','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('V_$TEMPFILE','<username>','SELECT');
   rdsadmin.rdsadmin_util.grant_sys_object('V_$DATAFILE','<username>','SELECT');
   END;
   

You do not need to do any further setup to use Fivetran Teleport Sync. However, we recommend that you read our recommendations for Fivetran Teleport Sync before using it.