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:

Oracle 11g or above
IP (e.g. 1.2.3.4) or host (your.server.com)
Port (usually 1521)

In Oracle

Allow access

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 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 <password> with a password of your choice:
```
CREATE USER FIVETRAN IDENTIFIED BY <password>;
GRANT CREATE SESSION TO FIVETRAN;
```

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:

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 the DBA_EXTENTS, DBA_TABLESPACES, and DBA_SEGMENTS system views:

GRANT SELECT ON DBA_EXTENTS TO FIVETRAN WITH GRANT OPTION;
GRANT SELECT ON DBA_TABLESPACES TO FIVETRAN WITH GRANT OPTION;
GRANT SELECT ON DBA_SEGMENTS TO FIVETRAN WITH GRANT OPTION;

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

Incremental updates

During our incremental update syncs, we detect modified rows in your source tables by processing your archived redo log files with LogMiner.

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):
```
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
```
Fivetran must have a minimum of three hours' worth of log data to begin our initial sync. You cannot complete your Fivetran set up until three hours after RMAN has been configured to retain Archive Logs.
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;
```

In Fivetran

In the top right corner of your Fivetran dashboard, click + Connector.
Select the Oracle EBS connector to launch the setup form.
Enter the destination schema name you'd like to use.
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.

Documentation