Skip to main content

Oracle RDS Setup Guide

Justin Stevenson
Updated

Oracle RDS Setup Guide

Follow these instructions to replicate your Oracle RDS database to your destination via Fivetran.

Prerequisites

To connect your Oracle database to Fivetran, you need:

  • A Fivetran account with an Enterprise or Business Critical plan
  • Oracle 12c or above
  • An AWS account with administrator access to the Oracle RDS database
  • Access to your database server
  • Your database host's IP (e.g., 1.2.3.4) or domain (your.server.com)
  • Your database's port (usually 1521)
  • Your database's system identifier (SID)/service name
  • (If you want to connect using an SSH tunnel) An SSH server

Setup instructions

Choose connection method

First, decide whether to connect Fivetran to your Oracle RDS database directly, using an SSH tunnel, or using AWS PrivateLink.

Connect directly (TLS required)

IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran. Follow Amazon's to enable TLS on your database.

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

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

Connect using an SSH tunnel (TLS optional)

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

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

If you connect using an SSH tunnel, you will 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.

Before you proceed to the next step, you must follow our . If you want Fivetran to use end-to-end encryption using TLS, follow Amazon's to enable TLS on your database.

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in .

Follow to configure PrivateLink for your database.

Enable database access

Grant Fivetran's data processing servers access to your database server. How you grant access depends on whether or not your database instance is in a VPC.

If your instance is in a VPC, you must configure the two mechanisms that control access: VPC security groups and network access control lists (ACLs). If your instance is not in a VPC, you only need to configure security groups.

Configure security group

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 , click on the database instance you want to connect to Fivetran.

    rds-expand-instance

  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.

    rds-instance-details

  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.

    rds-publicly-accessible

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

    rds-security_group

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

    rds-security_group-id

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

    rds-select-inbound-tab

  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 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.

    rds-inbound-add-rule

  9. Click Save rules.

Configure Network ACLs (VPCs only)

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

  1. Return to the instance details page.

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

    rds-click-vpc

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

    rds-select-vpc-id

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

    rds-click-network-acl

  5. Click the Network ACL ID.

    rds-click-network-acl-id

  6. Select the Inbound Rules tab.

    rds-click-inbound-rules

  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.

    rds-all-are-allowed

  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 .

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

Create user

Create a database user for Fivetran's exclusive use.

  1. Connect to your Oracle database as an admin user.

  2. Create a user for Fivetran and grant it permission to connect to your database by executing the following SQL command. Replace <username> and password with a username and password of your choice.

    CREATE USER <username> IDENTIFIED BY <password>;
GRANT CREATE SESSION TO "<username>";
content_copy

    NOTE: Usernames in Oracle are case sensitive. For example, fivetran is not the same user as FIVETRAN.

Grant read-only access

Grant the Fivetran user read-only access to the data you want to sync.

NOTE: Oracle database defaults to using upper case letters, unless the values are surrounded by double quotes.

  1. Grant the Fivetran user SELECT permission for each schema and tables you want to sync.

    GRANT SELECT ON "<schemaA>"."<tableA>" TO "<username>";
GRANT SELECT ON "<schemaA>"."<tableB>" TO "<username>";
GRANT SELECT ON "<schemaB>"."<tableC>" TO "<username>";
content_copy

    Alternatively, you can grant access to all tables:

    GRANT SELECT ANY TABLE TO "<username>";
content_copy

  2. Grant the Fivetran user access to the DBA_EXTENTS, DBA_TABLESPACES, and DBA_SEGMENTS system views. We use these views to optimize our initial import queries.

    BEGIN
    rdsadmin.rdsadmin_util.grant_sys_object('DBA_EXTENTS','<username>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('DBA_TABLESPACES','<username>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('DBA_SEGMENTS','<username>','SELECT');
END;
content_copy

Configure incremental update mechanism

To keep your data up to date after the initial sync, we use one of the following incremental sync methods:

  • LogMiner
  • Fivetran Teleport Sync Beta

Each of these methods keeps a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync. To learn the differences between the two mechanisms, see our .

Follow the instructions below for your incremental update mechanism.

LogMiner

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 .

  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;
content_copy

  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 .

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

  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;
content_copy

  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;
content_copy
    GRANT SELECT ANY TRANSACTION TO "<username>";
content_copy

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

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

    GRANT LOGMINING TO "<username>";
content_copy

  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;
content_copy

Fivetran Teleport Sync Beta

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

Finish Fivetran configuration

  1. In your , enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.

  2. In the Host field, enter your database host's IP (for example, 1.2.3.4) or DNS name (for example, your.server.com).

  3. Enter your database's port number. The port number is usually 1521 or 2483 for unencrypted connections and 1521 or 2484 for encrypted connections using SSL/TLS.

  4. Enter the Fivetran-specific user that you created in .

  5. Enter the password for the Fivetran-specific user that you created in .

  6. Enter your database's SID/Service Name.

  7. Choose your connection method. If you selected Connect via an SSH tunnel, provide the following information:

    • SSH hostname (do not use a load balancer's IP address/hostname)
    • SSH port
    • SSH user
    • If you enabled TLS on your database in , set the Require TLS through tunnel toggle to ON.

  8. Click Save & Test. Fivetran tests and validates our connection to your Oracle RDS database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your Oracle RDS database and that it is properly configured:

  • The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH tunnel. (We skip this test if you aren't connecting using an SSH tunnel.)
  • The Connecting to Host Test validates the database credentials you provided in the setup form. It then verifies that the database host is not private and checks that we can connect to the host.
  • The Validating Certificate Test generates a pop-up window only if the certificate is not already trusted or has not been used previously. In that pop-up window, you must choose which certificate you want Fivetran to use. The test then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you aren't connecting directly.)
  • The Validating Database Version Test checks your database version. The test passes if your database version is 12.2 or above or if your database version is 12.1 or below and you are connecting using an SSH tunnel.
  • The Validating System View Permission Test checks that we have permission to access the DBA_EXTENTS, DBA_TABLESPACES, and DBA_SEGMENTS system views.
  • The Validating Archive Log Access Test checks that we can access your archive log.
  • The Validating Archive Log Retention Period Test verifies that your archive log is set to retain at least 24 hours' worth of changes.
  • The Access to Database-Level Supplemental Logging Test verifies that supplemental logging is enabled on your database. If supplemental logging is not enabled, the test passes but generates a warning message in your dashboard.

NOTE: The tests may take a few minutes to finish running.

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk