Generic Oracle Setup Guide

Follow these instructions to replicate your generic Oracle database to your destination using Fivetran.

Prerequisites

To connect your Oracle database to Fivetran, you need:

Oracle 19 or above
Your database host's IP (e.g., 1.2.3.4) or domain (e.g., your.server.com)
Your database's port (usually 1521 for unencrypted connections and 2484 for encrypted connections using SSL/TLS)
Your database's system identifier (SID)/service name

Setup instructions

Choose connection method

First, decide whether to connect Fivetran to your generic Oracle 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 Oracle's instructions to enable TLS on your database either with a client wallet or without a client wallet.

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

To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your Oracle database host and port (usually 1521) from Fivetran's IPs for your database's region. How you do this will vary based on how your Oracle database is hosted (cloud platform, on-premises, etc.).

Connect via SSH (TLS optional)

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

To connect using SSH, follow our SSH connection instructions. If you want Fivetran to use end-to-end encryption using TLS, follow Oracle's instructions to enable TLS on your database either with a client wallet or without a client wallet.

Connect using Proxy Agent

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.

Create user

Create a database user for Fivetran's exclusive use.

Connect to your Oracle database as an admin user.
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>";
```
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.

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

Alternatively, you can grant access to all tables:

GRANT SELECT ANY TABLE TO "<username>";

Grant the Fivetran user access to the ALL_CONS_COLUMNS, ALL_OBJECTS, ALL_TAB_COLUMNS, ALL_TABLES, and V$NLS_PARAMETERS system views. We use these views to optimize our initial import queries.

Finish Fivetran configuration

In your connector setup form, enter a Destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or URL (for example, your.server.com).
Enter your database's Port number. The port number is usually 1521 for unencrypted connections and 2484 for encrypted connections using SSL/TLS.
Enter the Fivetran-specific User that you created in Step 2.
TIP: In Oracle, usernames are case-sensitive. Make sure to enter the exact username.
Enter the Password for the Fivetran-specific user that you created in Step 2.
Enter your database's SID/Service Name.
Click Save & Test. Fivetran tests and validates our connection to your Oracle 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 generic Oracle 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 where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you selected an indirect connection method and then disabled the Require TLS through Tunnel toggle.)
The Validating Database Version Test checks your database version. The test passes if your database version is 19 or above.
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.

Connector Overview

Schema Information

Release Notes

API Connector Configuration

Documentation Home