Oracle E-Business Suite Setup Guide

Fivetran connects directly to your Oracle EBS database. This is the simplest connection method to set up, requiring minimal configuration.

To connect directly, you must do the following:

1. Enable TLS on your Oracle EBS database. Follow Oracle's instructions to enable TLS on your database either with a client wallet or without a client wallet.

2. Configure your firewall and/or other access control systems to allow incoming connections to your Oracle EBS 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 EBS database is hosted (cloud platform, on-premises, etc.).

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

To connect using SSH tunnel, you must do the following:

1. Configure an SSH tunnel between Fivetran and your Oracle EBS database. For more information, see our SSH connection setup documentation.

2. (Optional) Enable TLS on your Oracle EBS database if you want 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.

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

We support the following providers:

• AWS PrivateLink – used for VPCs and AWS-hosted or on-premises services. For more information about setting up a private networking connection using AWS PrivateLink, see our AWS PrivateLink setup documentation.

• Azure Private Link – used for Virtual Networks (VNets) and Azure-hosted or on-premises services. For more information about setting up a private networking connection using Azure Private Link, see our Azure PrivateLink setup documentation.

• Google Cloud Private Service Connect – used for VPCs and Google-hosted or on-premises services. For more information about setting up a private networking connection using Google Cloud Private Service Connect, see our Google Cloud Private Service Connect setup documentation.

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.

Enable Binary Log Reader

To enable Binary Log Reader, do the following:

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

2. If ARCHIVELOG mode is not enabled on your database, enable ARCHIVELOG mode. For multitenant container databases, execute these commands at the CDB level (ALTER SESSION SET CONTAINER=CDB$ROOT).

   We recommend that you set ARCHIVE_LAG_TARGET to a non-zero value to reduce the lag in changes being synced.

   Enabling ARCHIVELOG mode requires the Oracle instance to be briefly taken offline. To learn more, see Oracle's Managing Archived Redo Log Files documentation.

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

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

   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. For multitenant container databases, execute this command at the CDB level.

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   

   If you are also enabling supplemental logging at the table-level for PRIMARY KEY columns or for ALL columns on tables that include a primary key, enable it only after the table is created. Do not include the supplemental logging clause within the CREATE TABLE statement, as Oracle may not properly log the primary key columns in such cases. Instead, create the table first and then enable supplemental logging using an ALTER TABLE command. For example,

   CREATE TABLE "<schema>"."<table>" ... (<columns>, ...);
   ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (<PRIMARY KEY | ALL>) COLUMNS;
   

6. Grant the following SELECT permissions to the Fivetran user. For multitenant container databases, execute these commands at the PDB level (ALTER SESSION SET CONTAINER=<PDB>).

   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>;
   GRANT SELECT ON SYS.V_$PARAMETER TO <username>;
   GRANT SELECT ON SYS.V_$ENCRYPTED_TABLESPACES to <username>;
   GRANT SELECT ON SYS.V_$TABLESPACE to <username>;
   

7. (Optional) Grant the Fivetran user permission to capture from partitioned tables. For multitenant container databases, execute these commands at the PDB level (ALTER SESSION SET CONTAINER=<PDB>).

   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 directoryDynamic archive log directories

   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. For multitenant container databases, execute these commands at the PDB level.

   CREATE DIRECTORY FIVETRAN_LOGDIR AS ‘<your-archive-log-directory-full-path>’;
   GRANT READ ON DIRECTORY FIVETRAN_LOGDIR to <username>;
   

   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 manually creating DIRECTORY objects ahead of time, grant the Fivetran user permission to create and drop them dynamically for each sync. Choose one of the following methods:

   Method 1: Grant direct privileges

   Grant the Fivetran user permission to create and drop DIRECTORY objects. For multitenant container databases, execute these commands at the PDB level.

   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. For multitenant container databases, execute these commands at the PDB level.

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

9. (Optional) If your Oracle database uses TDE, grant the following permissions to the Fivetran database user. For multitenant (CDB/PDB) environments, grant them at the CDB level to a CDB-level user instead.

   GRANT SELECT ON SYS.V_$ENCRYPTION_WALLET to <username>;
   

   If column-level encryption is used:

   GRANT SELECT ON SYS.ENC$ to <username>;
   

10. (Optional) If you want to capture from Oracle databases that store archived redo logs in ASM, grant the following permissions to the Fivetran database user. For multitenant container databases, execute these commands at the PDB level.

    GRANT EXECUTE ON SYS.DBMS_FILE_TRANSFER TO <username>;
    GRANT EXECUTE ON SYS.UTL_FILE TO <username>;
    

    For more information about how Binary Log Reader works with ASM, see our Automatic Storage Management (ASM) documentation.

    If your archive log directory remains constant (Fixed archive log directory), create a staging directory and grant read/write access to the Fivetran database user:

    The operating system user (e.g., oracle) running the Oracle database process must have read and write permissions on the underlying file system path of the staging directory.

    CREATE DIRECTORY FIVETRAN_ASM_STG_DIR AS '<asm_staging_directory_path>';
    GRANT READ, WRITE ON DIRECTORY FIVETRAN_ASM_STG_DIR TO <username>;
    

Enable LogMiner

To enable LogMiner, do the following:

1. If ARCHIVELOG mode is not enabled on your database, enable ARCHIVELOG mode. We recommend that you set ARCHIVE_LAG_TARGET to a non-zero value to reduce the lag in changes being synced.

   Enabling ARCHIVELOG mode requires the Oracle instance to be briefly taken offline. To learn more, see Oracle's Managing Archived Redo Log Files documentation.

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

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

   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. For more information, see Oracle's LogMiner and Supplemental Logging documentation. Choose one of the logging options below and follow the corresponding instructions:

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

   We recommend using this option because it minimizes the overhead on your source database. According to Oracle's Database-Level Supplemental Logging documentation, "minimal supplemental logging does not impose significant overhead on the database that generates the redo log files".

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

      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
      

   2. Once minimal supplemental logging has been enabled at the database level, you must enable either PRIMARY KEY identification logging or ALL supplemental logging.

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

        You need to enable ALL supplemental logging only for tables without a primary key and for tables whose primary key or composite primary key values are expected to change

        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:

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

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

   Database-level identification key logging

   Enable either PRIMARY KEY identification logging or ALL supplemental logging at the database level if you want to enable logging for the entire database. For more information, see Oracle's Database-Level Identification Key Logging documentation.

   • 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:

     You need to enable ALL supplemental logging only for tables without a primary key and for tables whose primary key or composite primary key values are expected to change

     ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
     

   If you don't configure the logging data correctly, you will receive a warning when Fivetran encounters a primary key change. The warning includes instructions to resolve the issue

   
   

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

   Standalone DBsMultitenant Container DBs

   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 SELECT ON SYS.V_$DATABASE 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>;
   

   Execute the following ALTER 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 SELECT ON SYS.V_$DATABASE 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.

   We recommend granting these permissions to optimize the connection's performance and reliability.

   Standalone DBsMultitenant Container DBs

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

   Execute the following ALTER 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>;