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
Choose connection methodlink
First, decide whether to connect Fivetran to Oracle EBS directly or using an SSH tunnel.
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.
Create userlink
-
Connect to your Oracle EBS database as an admin user.
-
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.
-
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>;
-
Grant the Fivetran user access to the
DBA_EXTENTS
,DBA_TABLESPACES
, andDBA_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.
Configure incremental update mechanismlink
To keep your data up to date after the initial sync, we use one of the following incremental sync methods:
- LogMiner
- Fivetran Teleport Sync undefined
Each of these methods keeps a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync. To learn the differences between the two mechanisms, see our incremental update documentation.
Follow the instructions below for your incremental update mechanism.
LogMiner
To enable LogMiner, do the following:
-
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;
-
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;
-
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. -
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.
-
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>;
-
Grant additional permissions to the Fivetran user for Oracle version 12.
GRANT LOGMINING TO <username>;
-
(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.
-
Enter your chosen destination schema name in the connector setup form.
-
Enter the following information in the setup form:
- Host
- Port
- User
- Password
- SID
-
Choose a connection method.
-
Click Save & Test. Fivetran will take it from here and sync your data from your Oracle EBS account.
Setup testslink
Fivetran performs the following tests to ensure that we can connect to your Oracle EBS database and that it is properly configured:
- The Validating Archive Log Access Test checks that we can access your archive log.
- The Validating Archive Log Retention Period Test verifies that your archive log is set to retain at least 24 hours’ worth of changes.
- The Access to Database-Level Supplemental Logging Test verifies that supplemental logging is enabled on your database. If supplemental logging is not enabled, the test passes but generates a warning message in your dashboard.
- The Validating Speed Setup test checks how quickly Fivetran can fetch data from your source database. The test will show a warning if the speed is less than 5MB per second.
Related Contentlink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration