Generic Oracle Destination Setup Guide Private Preview

Follow our setup guide to connect Oracle database as a destination to Fivetran.

Prerequisites

To connect Oracle to Fivetran, you need the following:

• An Oracle v19c database or higher
• Your database host's IP address (for example, 1.2.3.4) or domain (your.server.com)
• Your database's port (usually 1521 for unencrypted connections and 2484 for connections encrypted using SSL or TLS)
• A Fivetran role with the Create Destinations or Manage Destinations permissions

Setup instructions

Choose your deployment model

Before setting up your destination, decide which deployment model best suits your organization's requirements. This destination supports both SaaS and Hybrid deployment models, offering flexibility to meet diverse compliance and data governance needs.

See our Deployment Models documentation to understand the use cases of each model and choose the model that aligns with your security and operational requirements.

Choose connection method

Decide whether to connect Fivetran to your Oracle database directly or using an SSH tunnel.

Create Fivetran user

1. Connect to your Oracle database as an Admin user with the SYSDBA privilege.

2. Create a Fivetran user in the pluggable database (PDB).

3. Grant the following privileges to the Fivetran user to enable full access for setup, data operations, and testing within the PDB:

   • CREATE USER: Allows creation of users and schemas within the PDB.
   • DROP USER: Allows deletion of created users and schemas, and is required for setup tests.
   • CREATE ANY TABLE: Allows creation of tables in any schema within the PDB.
   • DROP ANY TABLE: Allows deletion of tables in any schema within the PDB.
   • ALTER ANY TABLE: Allows modification of tables in any schema.
   • DELETE ANY TABLE: Allows deletion of data from tables in any schema.
   • UPDATE ANY TABLE: Allows updating of data in tables across schemas.
   • SELECT ANY TABLE: Allows reading data from tables in any schema.
   • CREATE ANY INDEX: Allows creation of indexes in any schema. This privilege is required for creating primary keys.
   • DROP ANY INDEX: Allows deletion of indexes, including those created for primary keys.
   • INSERT ANY TABLE: Allows inserting data into tables in any schema.
   • UNLIMITED TABLESPACE: Allows writing data to tables without tablespace usage restrictions.
   The ANY privileges are required because the Fivetran user cannot write data to the schema it creates by default. To allow the user to read from and write to its own schema, you must grant the privileges listed above.

Complete Fivetran configuration

1. Log in to your Fivetran account.

2. Go to the Destinations page and click Add destination.

3. Enter a Destination name of your choice and then click Add.

4. Select Oracle as the destination type.

5. Enter a Destination schema prefix of your choice. Fivetran will apply this prefix to every replicated schema.

   You cannot change the prefix after setting up the connection.

6. In the Host field, enter your database's host URL (for example, abc.xyz.us-east-1.rds.amazonaws.com).

7. Enter the Port number for your database. The default is usually 1521 for unencrypted connections and 2484 for connections secured with SSL or TLS encryption.

8. Enter the username of the Fivetran User you created.

   In Oracle, usernames are case-sensitive. Make sure you enter the exact username in this field.

9. Enter the Password for the Fivetran user you created.

10. Enter the name of the Oracle Database you want to use as your destination.

11. (Enterprise and Business Critical accounts only) Select the deployment model of your choice:

    • SaaS Deployment
    • Hybrid Deployment

12. If you choose Hybrid Deployment, select an existing Hybrid Deployment Agent in the Select an exising agent drop-down menu or configure a new agent.

13. (Not applicable to Hybrid Deployment) Choose your Connection method:

    • Connect directly
    • Connect via an SSH

14. If you selected Connect via an SSH tunnel as the connection method, enter the following information:

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

15. (Not applicable to Hybrid Deployment) Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our Destinations documentation.

16. Choose your Timezone.

17. (Optional for Business Critical accounts and SaaS Deployment) To enable regional failover, set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make a note of the IP addresses of the secondary region and safelist these addresses in your firewall.

18. Click Save & Test.

Fivetran tests and validates the Oracle destination connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the Oracle destination.

In addition, Fivetran automatically configures a Fivetran Platform Connector to transfer the connection logs and account metadata to a schema in this destination. The Fivetran Platform Connector enables you to monitor your connections, track your usage, and audit changes. The connector sends all these details at the destination level.

Setup tests

Fivetran performs the following Oracle connection tests:

• The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and checks whether Fivetran can connect to your database through the tunnel. We skip this test if you are not using an SSH tunnel.
• The Connecting to Host Test validates the database credentials you provided in the setup form. It also verifies that the database host is publicly accessible and checks connectivity to the host.
• The Validating Certificate Test prompts you to select the TLS certificate Fivetran should use. Once selected, it validates the certificate and checks if a secure TLS connection to your database can be established. We skip this test if you chose to connect through an SSH tunnel and set the Require TLS through Tunnel toggle to OFF.
• The Validating Database Version Test verifies that your Oracle database version is 19c. The test passes only if the version matches.
• The Permission Test checks whether the Fivetran user has the permissions to do the following:
  • Create a user
  • Create a table with a primary key
  • Create staging temporary tables
  • Insert a record into the main table
  • Run a MERGE query
  • Update and delete a record
  • Drop the table and schema
  • Alter the schema

Related articles

description Destination Overview

settings API Destination Configuration

home Documentation Home