Oracle E-Business Suite Setup Guide

Connect directly (TLS required)

IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran. Follow Oracle’s TLS setup instructions to enable TLS on your database.

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

To connect directly, 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.).

Connect via SSH (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 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 TLS setup instructions to enable TLS on your database.

LogMiner

To enable LogMiner, do the following:

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

   Note: Enabling ARCHIVELOG mode requires the Oracle instance to be taken offline for a brief period. For more information, see Oracle’s 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 (Fivetran recommends seven days):

   Note: Fivetran must have a minimum of 3 hours’ worth of log data to begin our initial sync. You cannot complete your Fivetran set up until 3 hours after RMAN has been configured to retain Archive Logs.

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

3. We recommend setting DB_RECOVERY_FILE_DEST_SIZE to a value that agrees with your available disk space, since expired and obsolete log and backup files can quickly fill your disk. For more information, see Oracle’s Documentation.

4. Enable supplemental logging. For Oracle EBS, we require database-level minimal supplemental logging with table-level identification key logging. We do not recommend enabling full supplemental logging at the database level because it can cause performance issues in 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 then need to enable either primary key identification logging or all supplemental logging. You only need to use all supplemental logging for tables without primary keys.

   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.

   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: 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 permissions for running LogMiner to the Fivetran user.

   GRANT SELECT ON SYS.V_$DATABASE 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. Grant additional permissions to the Fivetran user for Oracle version 12.

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

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

Fivetran Teleport Syncundefined

To enable Fivetran Teleport Sync, grant the following privileges to the Fivetran user:

GRANT CREATE TYPE TO <username>;
GRANT CREATE PROCEDURE TO <username>;

Fivetran needs these privileges so our connector can create the FT_BITXOR aggregate function.