Oracle E-Business Suite Setup Guide link

Updated November 16, 2023

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

Prerequisiteslink

To connect Oracle EBS to Fivetran, you need:

A Fivetran account with an Enterprise or Business Critical plan
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 your Oracle database:

directly
using an SSH tunnel
using private networking

Connect directly (TLS required)link

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)link

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.

Connect using private networkinglink

IMPORTANT: You must have a Business Critical plan to use private networking.

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. See our AWS PrivateLink setup guide for details.
Azure PrivateLink – used for Virtual Networks (VNets) and Azure-hosted or on-premises services. See our Azure PrivateLink setup guide for details.
Google Cloud Private Service Connect – used for VPCs and Google-hosted or on-premises services. See our Google Cloud Private Service Connect setup guide for details.

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:
For standalone databases:
```
CREATE USER <username> IDENTIFIED BY <password>;
GRANT CREATE SESSION TO <username>;
content_copy
```
```
alter profile DEFAULT limit SESSIONS_PER_USER 15;
content_copy
```
NOTE: Use the above command if the profile name of the FIVETRAN user is set to DEFAULT. Otherwise, replace DEFAULT with your chosen profile name. Check your profile name with the following query:
```
SELECT USERNAME, PROFILE FROM DBA_USERS where USERNAME = <username>;
content_copy
```
IMPORTANT: You must have at least 15 sessions if your FIVETRAN user profile is the default profile. You must have at least 5 sessions if your FIVETRAN user profile is not the default profile.
For multitenant container databases Beta:
You must create a common user at the container level. Create the user with the container as the active session and grant the following permissions:
```
ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE USER <username> IDENTIFIED BY <password> CONTAINER=ALL;
GRANT CREATE SESSION, ALTER SESSION, SET CONTAINER TO <username> CONTAINER=ALL;
ALTER USER <username> SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
content_copy
```
NOTE: Your FIVETRAN user must have at least 15 sessions per user in both a constant database and a pluggable database. Run the following command at the container level and the pluggable level to check the parameter:
```
col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile
and b.RESOURCE_NAME='SESSIONS_PER_USER' and a.username = <username>;
content_copy
```

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:

For standalone databases:

GRANT SELECT ON "<schemaA>"."<tableA>" TO <username>;
GRANT SELECT ON "<schemaA>"."<tableB>" TO <username>;
GRANT SELECT ON "<schemaB>"."<tableC>" TO <username>;
content_copy

Alternatively, you can grant access to all tables:

GRANT SELECT ANY TABLE TO <username>;
content_copy

For multitenant container databases Beta:

Execute the following command before granting permissions. Replace <PDB> with the name of the pluggable database (PDB) that you want to connect to Fivetran.

ALTER SESSION SET CONTAINER=<PDB>;
content_copy

GRANT SELECT ON "<schemaA>"."<tableA>" TO <username>;
GRANT SELECT ON "<schemaA>"."<tableB>" TO <username>;
GRANT SELECT ON "<schemaB>"."<tableC>" TO <username>;
content_copy

Alternatively, you can grant access to all tables.

GRANT SELECT ANY TABLE TO <username>;
content_copy

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

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 Beta

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.

LogMinerlink

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;
content_copy
```
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;
content_copy
```
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;
content_copy
```
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 a primary key.
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;
content_copy
```
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;
content_copy
```
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>;
content_copy

Grant additional permissions to the Fivetran user for Oracle version 12.
```
GRANT LOGMINING TO <username>;
content_copy
```

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

For multitenant container databases Beta:

Execute the following command before granting access.

ALTER SESSION SET CONTAINER=CDB$ROOT;
content_copy

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

Fivetran Teleport SyncBetalink

You do not need to do any further setup to use Fivetran Teleport Sync. However, we recommend that you read our recommendations for Fivetran Teleport Sync before using it.

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.

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.

Documentation