Oracle E-Business Suite Setup Guide

Follow our setup guide to connect Oracle E-Business Suite (EBS) to Fivetran.

Prerequisiteslink

To connect Oracle EBS to Fivetran, you need:

• Oracle 11g or above
• Your database host’s IP (e.g., 1.2.3.4) or domain (your.server.com)
• Your database’s port (usually 1521)
• TLS enabled on your database (if you want to connect to Fivetran directly)

Setup instructionslink

Allow accesslink

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

Create userlink

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

2. Execute the following SQL commands to create a user for Fivetran and grant it permission to connect to your database. Replace <username> and <password> with a username and password of your choice:

   CREATE USER <username> IDENTIFIED BY <password>;
   GRANT CREATE SESSION TO <username>;
   

Grant read-only accesslink

Note: Unless you wrap an identifier (schema name, table name, etc.) in double quotes, Oracle will convert it to upper case when it performs the operation.

1. Once the Fivetran user is created, grant it SELECT permission for each schema and table 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>;
   

2. Grant the Fivetran user access to the DBA_EXTENTS, DBA_TABLESPACES, and DBA_SEGMENTS system views:

   GRANT SELECT ON DBA_EXTENTS TO <username>;
   GRANT SELECT ON DBA_TABLESPACES TO <username>;
   GRANT SELECT ON DBA_SEGMENTS TO <username>;
   

   We use these system views to optimize our initial import queries.

Incremental updateslink

To keep your data up to date after the initial sync, we use Oracle’s built-in LogMiner technology. LogMiner uses Archived Redo Log files on your Oracle database to get a list of modified tables and determine which rows need to be updated, which allows Fivetran to update only the data that has changed since our last sync.

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

Finish Fivetran configurationlink

1. Enter your chosen destination schema name in the connector setup form.

2. Enter the following information in the setup form:

   • Host
   • Port
   • User
   • Password
   • SID

3. Choose a connection method.

4. Click Save & Test. Fivetran will take it from here and sync your data from your Oracle EBS account.

Related Contentlink

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connector Configuration

home Documentation Home