Oracle E-Business Suite Setup Guide

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

Prerequisites

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 instructions

Choose connection method

IMPORTANT: Do not perform this step if you want to use the Hybrid Deployment model for your data pipeline.

First, decide whether to connect Fivetran to your Oracle database:

directly
using an SSH tunnel
using private networking
Connect via Proxy Agent

Connect directly (TLS required)

IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran. Follow Oracle's instructions to enable TLS on your database either with a client wallet or without a client wallet.

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 instructions to enable TLS on your database either with a client wallet or without a client wallet.

Connect using private networking

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 user

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>;
```
```
alter profile DEFAULT limit SESSIONS_PER_USER 15;
```
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>;
```
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;
```
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>;
```

Grant read-only access

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

Alternatively, you can grant access to all tables:

GRANT SELECT ANY TABLE TO <username>;

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

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, 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.
Grant the Fivetran user access to the SYS.V_$DATABASE view:
```
GRANT SELECT ON SYS.V_$DATABASE TO <username>;
```
We use this view to determine whether the database is a container database (CDB).

Configure incremental sync method

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 methods, see our incremental sync documentation.

Follow the instructions below for your incremental sync method.

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 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;
```
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_$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 Beta:

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 SyncBeta

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.

Connect via Proxy Agent

The Fivetran Proxy Agent eliminates the need for other complex networking options. Installed within a customer's network, it creates an outbound network connection to the Fivetran Managed SaaS. To connect using Proxy Agent, see the Proxy Agent configuration section.

Finish Fivetran configuration

In the connector setup form, enter a Destination schema prefix of your choice. This prefix applies to each replicated schema and cannot be changed once your connector is created.
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected in the Select an existing agent drop-down menu. To use a different agent, select the agent of your choice, and then select the same agent for your destination.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
Enter your database's Port number. The default port number is 1521.
Enter the Fivetran-specific User you created.
TIP: In Oracle, usernames are case-sensitive. Make sure to enter the exact username.
Enter the Password for the Fivetran-specific user you created.
Enter your database's SID or Service Name.
(Multitenant container databases only) Enter your database's PDB Name.
(Not applicable to Hybrid Deployment) Select your chosen Connection method:
- Connect directly: Fivetran connects to your Oracle EBS database using the database hostname or IP address.
- Connect via an SSH tunnel:
  - SSH Host: Enter the hostname or IP address of your SSH server.
  - SSH Port: Enter the port number of your SSH server.
  - SSH User: Enter the username for SSH access.
  - (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.
- Connect via PrivateLink: Fivetran connects to your Oracle EBS database using AWS PrivateLink, Azure Private Link, or Google Cloud Private Service Connect, depending on the cloud provider you have selected.
- Connect via proxy agent: Choose the necessary Proxy Agent from the Proxy agents drop-down list (if available) or configure a new proxy agent.
Select the Update Method: Read Changes via LogMiner or Detect Changes via Fivetran Teleport Sync.
Click Save & Test. Fivetran will take it from here and sync your data from your Oracle EBS account.

Setup tests

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.

Connector Overview

Schema Information

Release Notes

API Connector Configuration

Documentation Home