Generic Oracle Setup Guidelink
Follow these instructions to replicate your generic Oracle database to your destination using Fivetran.
To connect your Oracle database to Fivetran, you need:
- Oracle 11g or above
- Your database host's IP (e.g.,
22.214.171.124) or domain (e.g.,
- Your database's port (usually
1521for unencrypted connections and
2484for encrypted connections using SSL/TLS)
- Your database's system identifier (SID)/service name
Choose connection methodlink
First, decide whether to connect Fivetran to your generic Oracle database 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 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 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.
Create a database user for Fivetran's exclusive use.
Connect to your Oracle database as an admin user.
Execute the following SQL command to create a user for Fivetran and grant it permission to connect to your database. Choose a memorable name (for example,
<password>with a password of your choice.
CREATE USER FIVETRAN IDENTIFIED BY <password>; GRANT CREATE SESSION TO FIVETRAN;
NOTE: Usernames in Oracle are case sensitive. For example,
fivetranis not the same user as
Grant read-only accesslink
Grant the Fivetran user read-only access to the data you want to sync.
NOTE: Oracle database defaults to using upper case letters, unless the values are surrounded by double quotes.
Grant the Fivetran user
SELECTpermission 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_SEGMENTSsystem views. We use these views to optimize our initial import queries.
GRANT SELECT ON DBA_EXTENTS TO FIVETRAN; GRANT SELECT ON DBA_TABLESPACES TO FIVETRAN; GRANT SELECT ON DBA_SEGMENTS TO FIVETRAN;
Configure incremental update mechanismlink
To keep your data up to date after the initial sync, we use one of Oracle's two built-in tracking mechanisms for incremental updates: 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 learn more about Flashback and LogMiner, see our Updating Data documentation.
Flashback PRIVATE PREVIEW
Flashback uses either Automatic Undo Management or Flashback Data Archive to record all committed changes for each included table. Fivetran requires that Flashback Data Archive be enabled for all of the tables you want to sync.
To enable Flashback Data Archive, do the following:
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 of the tables that 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 plan 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 indices to FDA-generated history tables (optional)
Flashback queries are more efficient if the history tables generated by the Flashback Data Archive have indices. Adding indices to these history tables can make your syncs significantly faster and more resource-efficient, especially if your database is large or has a high change volume. To add indices, do the following:
Run the following query to find the system-generated history tables for each of your Flashback-enabled tables.
SELECT TABLE_NAME, ARCHIVE_TABLE_NAME FROM DBA_FLASHBACK_ARCHIVE_TABLES;
For each of the
ARCHIVE_TABLE_NAMEs returned in the previous query, add an index on the
CREATE INDEX <index-name> ON <archive-table-name> (STARTSCN);
LogMiner uses archived redo log files to identify modified tables and determine which rows need to be updated in the destination. 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 briefly taken offline. To learn more, see Oracle's archived redo log file 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. We recommend retaining data for seven days.
IMPORTANT: To sync your data, Fivetran must have a minimum of 3 hours' worth of log data to analyze. You cannot finish setting up your connector until 3 hours after you configure RMAN to retain archive logs.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
(Recommended) Set the
DB_RECOVERY_FILE_DEST_SIZEparameter to a value that matches your available disk space, because expired and obsolete log and backup files can quickly fill your disk. For more information, see Oracle's DB_RECOVERY_FILE_DEST_SIZE documentation.
Enable supplemental logging on all the tables you plan to sync.
If you plan to sync all tables in your database, run the following query.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
If you plan to only sync specific tables, run the following query for each table you want to include.
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
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.
Grant the Fivetran user permission to run LogMiner.
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;
(Oracle version 12 only) Grant logmining permissions to the Fivetran user.
GRANT LOGMINING TO FIVETRAN;
Finish Fivetran configurationlink
In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database host's IP (for example,
126.96.36.199) or URL (for example,
Enter your database's port number. The port number is usually
1521for unencrypted connections and
2484for encrypted connections using SSL/TLS.
Enter the Fivetran-specific user that you created in Step 2 (for example,
Enter the password for the Fivetran-specific user that you created in Step 2.
Enter your database's SID/Service Name.
Choose your connection method. If you selected Connect via an SSH tunnel, provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port
- SSH user
- If you enabled TLS on your database in Step 1, set the Require TLS through tunnel toggle to ON.
Click Save & Test . Fivetran will take it from here and sync your data from your Oracle database.