Oracle E-Business Suite Setup Guidelink
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
-
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
<password>
with a password of your choice:CREATE USER FIVETRAN IDENTIFIED BY <password>; GRANT CREATE SESSION TO FIVETRAN;
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 itSELECT
permission for each schema and table you want to sync:GRANT SELECT ON "<schemaA>"."<tableA>" TO FIVETRAN; GRANT SELECT ON "<schemaA>"."<tableB>" TO FIVETRAN; GRANT SELECT ON "<schemaB>"."<tableC>" TO FIVETRAN;
Alternatively, you can grant access to all tables:
GRANT SELECT ANY TABLE TO FIVETRAN;
-
Grant the
FIVETRAN
user access to theDBA_EXTENTS
,DBA_TABLESPACES
, andDBA_SEGMENTS
system views:GRANT SELECT ON DBA_EXTENTS TO FIVETRAN; GRANT SELECT ON DBA_TABLESPACES TO FIVETRAN; GRANT SELECT ON DBA_SEGMENTS TO FIVETRAN;
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 one of two built-in Oracle technologies: Flashback and LogMiner. Both Flashback and LogMiner keep a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync.
Choose either Flashback or LogMiner as your incremental update mechanism.
- To enable Flashback, proceed to the next section.
- To enable LogMiner, skip ahead to the LogMiner section.
Flashback PRIVATE PREVIEWlink
Flashback uses Automatic Undo Management to record all committed changes for each included table. To enable Flashback, follow these steps:
-
Grant the
FIVETRAN
user access to additional system views:GRANT SELECT ON DBA_FLASHBACK_ARCHIVE TO FIVETRAN; GRANT SELECT ON DBA_FLASHBACK_ARCHIVE_TABLES TO FIVETRAN; GRANT SELECT ON V$SYSTEM_PARAMETER TO FIVETRAN; GRANT SELECT ON V$DATABASE TO FIVETRAN;
-
Ensure that your UNDO tablespace has enough space to support Flashback by running the following query:
SELECT (UR * (UPS * DBS))/1000000000 AS "Recommended UNDO size in GB" FROM (select max(tuned_undoretention) AS UR from v$undostat), (SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), (SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
Compare the result of the above query with the current size of your UNDO tablespace:
SELECT SUM(a.bytes)/1000000000 as "Actual UNDO size in GB" FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#;
If your current UNDO size is too small, add new datafiles or resize existing ones to add more space.
-
Create a tablespace with enough space to store at least 1 day's worth of retention (Fivetran recommends 2 or more days' worth).
CREATE TABLESPACE <tablespace> DATAFILE SIZE <size> AUTOEXTEND ON MAXSIZE <maximum size>;
-
Create a Flashback Data Archive with the retention time chosen in the previous step.
CREATE FLASHBACK ARCHIVE <archive> TABLESPACE <tablespace> RETENTION 2 DAY;
-
Add all tables you want to sync to the archive.
ALTER TABLE "<schema>"."<table>" FLASHBACK ARCHIVE <archive>;
-
Grant the tables' owner(s) an unlimited quota on the Flashback Data Archive's tablespace.
ALTER USER <owner> QUOTA UNLIMITED ON <tablespace>;
-
Grant the
FIVETRAN
user permission to run Flashback queries on the tables you want to sync. You can either do this per table:GRANT FLASHBACK ON "<schema>"."<table>" TO FIVETRAN;
or for all tables:
GRANT FLASHBACK ANY TABLE TO FIVETRAN;
Add an index to FDA-generated history tables (Optional)
Flashback queries are significantly more efficient with an index on the history tables generated by Flashback Data Archive. The addition of this index can make your sync significantly faster and more resource-efficient, especially if your database is large or has a high change volume. To add indices, follow these steps:
-
Find the system-generated history tables for each of your Flashback-enabled tables by running the following query:
SELECT TABLE_NAME, ARCHIVE_TABLE_NAME FROM DBA_FLASHBACK_ARCHIVE_TABLES;
-
For each of the
ARCHIVE_TABLE_NAME
s returned in the previous query, add an index on theSTARTSCN
column:CREATE INDEX <index-name> ON <archive-table-name> (STARTSCN);
LogMinerlink
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 incrementally in the destination. Therefore, we require the following configuration to be present on your Oracle database:
-
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. -
For each table you plan to sync, run the following query to enable extra supplemental logging:
ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-
For each table where the primary key is expected to change, run the following query:
ALTER TABLE "<schema>"."<table>" 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 will give you customized instructions.
-
Grant permissions for running LogMiner to the
FIVETRAN
user.GRANT SELECT ON SYS.V_$DATABASE TO FIVETRAN; GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO FIVETRAN; GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO FIVETRAN; GRANT EXECUTE ON DBMS_LOGMNR TO FIVETRAN; GRANT EXECUTE ON DBMS_LOGMNR_D TO FIVETRAN; GRANT SELECT ANY TRANSACTION TO FIVETRAN; GRANT EXECUTE_CATALOG_ROLE TO FIVETRAN;
-
Grant additional permissions to the
FIVETRAN
user for Oracle version 12.GRANT LOGMINING TO FIVETRAN;
Finish Fivetran configurationlink
-
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.
Related articleslink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration