Generic Oracle Setup Guide

Fivetran connects directly to your 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, 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.).

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.

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

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

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.

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

   NOTE: Enabling ARCHIVELOG mode requires the Oracle instance to be briefly taken offline. To learn more, see Oracle's archived redo log file documentation.

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;
   

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

2. Configure Oracle RMAN to retain backups and archive logs for at least 24 hours. We recommend retaining data for seven days.

   RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
   

3. (Recommended) Set the DB_RECOVERY_FILE_DEST_SIZE parameter to a value that matches your available disk space, because expired and obsolete log and backup files can quickly fill your disk. For more information, see Oracle's DB_RECOVERY_FILE_DEST_SIZE documentation.

4. Enable supplemental logging. Choose one of the logging options below and follow the corresponding instructions:

   Database-level minimal supplemental logging with table-level identification key logging

   NOTE: We recommend using this option because it minimizes the overhead on your source database.

   i. 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: Per Oracle's database-level supplemental logging documentation, "minimal supplemental logging does not impose significant overhead on the database that generates the redo log files."

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   

   ii. Once minimal supplemental logging has been enabled at the database level, you must enable either primary key identification logging or all supplemental logging.

   NOTE: You need to enable all supplemental logging only for:

   • tables without a primary key
   • tables whose primary key or composite primary key values are expected to change

   To enable primary key supplemental logging only for specific tables, run the following SQL statement for each table:

   ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
   

   To enable all supplemental logging only for specific tables, run the following SQL statement for each table:

   ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   

   NOTE: With all supplemental logging, if a row is updated, all of the columns associated with that row are placed in the redo log file.

   Database-level identification key logging

   Enable either primary key identification key logging or all supplemental logging at the database level if you want to enable logging for the entire database.

   NOTE: You need to enable all supplemental logging only for:

   • tables without a primary key
   • tables whose primary key or composite primary key values are expected to change

   To enable primary key supplemental logging at the database level, run the following SQL statement:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
   

   To enable all supplemental logging at the database level, run the following SQL statement:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

   NOTE: If you don't configure the logging data correctly, you will receive a warning when Fivetran encounters a primary key change. The warning will give you customized instructions on how to fix the problem.

5. Grant the Fivetran user permission to run LogMiner. Follow the instructions below for your database type:

   For standalone databases

   GRANT SELECT ON SYS.V_$PARAMETER TO <username>;
   GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <username>;
   GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <username>;
   GRANT EXECUTE ON DBMS_LOGMNR TO <username>;
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <username>;
   GRANT SELECT ANY TRANSACTION TO <username>;
   GRANT EXECUTE_CATALOG_ROLE TO <username>;
   

   For multitenant container databases

   Execute the following command before granting access to LogMiner.

   ALTER SESSION SET CONTAINER=CDB$ROOT;
   

   GRANT SELECT ON SYS.V_$PARAMETER TO <username>;
   GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <username>;
   GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO <username>;
   GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <username>;
   GRANT EXECUTE ON DBMS_LOGMNR TO <username>;
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <username>;
   GRANT SELECT ANY TRANSACTION TO <username>;
   GRANT EXECUTE_CATALOG_ROLE TO <username>;
   

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.

   For standalone databases

   GRANT SELECT ON DBA_FREE_SPACE TO <username>;
   GRANT SELECT ON SYS.V_$LOG TO <username>;
   GRANT SELECT ON SYS.V_$TEMPFILE TO <username>;
   GRANT SELECT ON SYS.V_$DATAFILE TO <username>;
   

   For multitenant container databases

   Execute the following command before granting access.

   ALTER SESSION SET CONTAINER=CDB$ROOT;
   

   GRANT SELECT ON DBA_FREE_SPACE TO <username>;
   GRANT SELECT ON SYS.V_$LOG TO <username>;
   GRANT SELECT ON SYS.V_$TEMPFILE TO <username>;
   GRANT SELECT ON SYS.V_$DATAFILE TO <username>;
   

8. Grant the Fivetran user access to the SYS.V_$DATABASE view:

   GRANT SELECT ON SYS.V_$DATABASE TO <username>;
   

   We use this view to determine whether the database is a container database (CDB).

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.

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

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

   NOTE: Enabling ARCHIVELOG mode requires the Oracle instance to be briefly taken offline. To learn more, see Oracle's archived redo log file documentation.

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;
   

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

3. Configure Oracle RMAN to retain backups and archive logs for at least 24 hours. We recommend retaining data for seven days.

    RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
   

4. (Recommended) Set the DB_RECOVERY_FILE_DEST_SIZE parameter to a value that matches your available disk space, because expired and obsolete log and backup files can quickly fill your disk. For more information, see Oracle's DB_RECOVERY_FILE_DEST_SIZE documentation.

5. Enable database-level minimal supplemental logging:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   

6. Grant the Fivetran user the following SELECT permissions:

   GRANT SELECT ON SYS.V_$PDBS TO <username>;
   GRANT SELECT ON SYS.COL$ TO <username>;
   GRANT SELECT ON SYS.V_$DATABASE TO <username>;
   GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <username>;
   GRANT SELECT ON SYS.V_$TRANSACTION TO <username>;
   

7. (Optional) Grant the Fivetran user permission to capture from partitioned tables:

   GRANT SELECT ON SYS.TABPART$ to <username>;
   GRANT SELECT ON SYS.TABCOMPART$ to <username>;
   GRANT SELECT ON SYS.TABSUBPART$ to <username>;
   

8. Configure the archive redo log file access based on how your Oracle database stores logs:

   • Fixed archive log directory

     If your Oracle database is not configured with the fast recovery area, the archive log directory may remain constant (e.g., /u01/app/oracle/product/19c/dbhome_1). If your archive log directory remains constant, you must manually create a DIRECTORY object so Fivetran can access log files without requiring dynamic directory management. Run the following command to create the DIRECTORY object:

     CREATE DIRECTORY “FIVETRAN_LOGDIR” AS ‘<your-archive-log-directory-full-path>’;
     

   • Dynamic archive log directories

     If your Oracle database is configured with the fast recovery area, the archive log directory changes dynamically each day (e.g., /u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2025_01_25). To avoid the manual work of creating DIRECTORY objects ahead of time, grant the Fivetran user permission to create and drop DIRECTORY objects. This allows Fivetran to dynamically create and drop them for each sync.

     • Method 1: Grant direct privileges

       Grant the Fivetran user permission to create and drop DIRECTORY objects:

       GRANT CREATE ANY DIRECTORY TO <username>;
       GRANT DROP ANY DIRECTORY TO <username>;
       

     • Method 2: Install a predefined package

       If you prefer not to grant direct privileges, you can use the following stored procedure package (FIVETRAN_BFILE) to control DIRECTORY object creation and deletion. Ensure to replace <username> with your Fivetran username:

       CREATE OR REPLACE PACKAGE FIVETRAN_BFILE AUTHID DEFINER AS
           PROCEDURE dir_create(dirobj_name VARCHAR2, directory_name VARCHAR2);
           PROCEDURE dir_remove(dirobj_name VARCHAR2);
       END FIVETRAN_BFILE;
       /
       CREATE OR REPLACE PACKAGE BODY FIVETRAN_BFILE AS
       PROCEDURE dir_create(dirobj_name VARCHAR2, directory_name VARCHAR2) IS
           user_id VARCHAR2(256);
           BEGIN
             EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || dirobj_name || ' AS ''' || directory_name || '''';
             user_id := USER;
             EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY ' || dirobj_name || ' TO ' || user_id;
           END;
       PROCEDURE dir_remove(dirobj_name VARCHAR2) IS
           BEGIN
             EXECUTE IMMEDIATE 'DROP DIRECTORY ' || dirobj_name;
           END;
       END FIVETRAN_BFILE;
       /
       CREATE OR REPLACE PUBLIC SYNONYM PKG_FIVETRAN_BFILE FOR SYS.FIVETRAN_BFILE;
       GRANT EXECUTE ON PKG_FIVETRAN_BFILE TO <username>;