Oracle Setup Guide
Follow these instructions to replicate your generic Oracle database to your destination via Fivetran.
To connect your Oracle database to Fivetran, you need:
- Oracle 11g or above
- IP (e.g. 220.127.116.11) or host (your.server.com)
- Port (usually 1521)
Decide whether to connect to your warehouse directly or connect using an SSH tunnel. If your Oracle database is version 12.1 or below, you'll need to connect using an SSH tunnel.
- To connect using an SSH tunnel, follow these instructions.
- 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 IP.
Connect to your Oracle database as an admin user and execute the following SQL commands to create a user for Fivetran and grant it permission to connect to your database:
CREATE USER FIVETRAN IDENTIFIED BY <password>; GRANT CREATE SESSION TO FIVETRAN;
<password> with a password of your choice. Note that usernames in Oracle are case sensitive -
fivetran is not the same user as
Grant read-only access
FIVETRAN is created, grant it
SELECT permission for each schema and tables you would like 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;
Make sure to grant the
FIVETRAN user access to the
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 views to optimize our initial import queries.
Please note that Oracle database defaults to using uppercase, unless values are surrounded by double quotes.
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 PREVIEW
Flashback uses Automatic Undo Management to record all committed changes for each included table. To enable Flashback, you must first grant the
FIVETRAN user access to additional system views:
GRANT SELECT ON DBA_FLASHBACK_ARCHIVE TO FIVETRAN WITH GRANT OPTION; GRANT SELECT ON DBA_FLASHBACK_ARCHIVE_TABLES TO FIVETRAN WITH GRANT OPTION; GRANT SELECT ON V_$SYSTEM_PARAMETER TO FIVETRAN WITH GRANT OPTION; GRANT SELECT ON V_$DATABASE TO FIVETRAN WITH GRANT OPTION;
Next, you must either enable Flashback Data Archive or set the
UNDO_RETENTION system parameter to retain at least 24 hours of changes.
Flashback Data Archive
Create a tablespace with enough space to store about 7 days’ worth of database changes.
CREATE TABLESPACE <tablespace> DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE <maximum size>;
Create at least one Flashback Data Archive with at least 1 day of retention (Fivetran recommends 7 days).
CREATE FLASHBACK ARCHIVE <archive> TABLESPACE <tablespace> RETENTION 7 DAY;
Add all tables you want to sync to the archive.
ALTER TABLE "<schema>"."<table>" FLASHBACK ARCHIVE <archive>;
FIVETRANuser 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;
Undo Log Retention
UNDO_RETENTION system parameter to a minimum of 24 hours (Fivetran recommends 7 days). The parameter is measured in seconds.
ALTER SYSTEM SET UNDO_RETENTION = 604800;
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:
ARCHIVELOG mode enabled:
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 further reading, please refer to 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 7 days):
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
* Note: Fivetran must have a minimum of 3 hours worth of log data to pull from. Connection set up cannot be completed until 3 hours after RMAN has been configured to retain Archive Logs.
Expired and Obsolete Log and Backup files can quickly fill your disk. Fivetran recommends setting
DB_RECOVERY_FILE_DEST_SIZEto a value that agrees with your available disk space. For more information, see Oracle's Documentation.
Supplemental logging of primary key columns enabled:
ALTER DATABASE 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.)
Permissions for running LogMiner granted to 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;
Additional permissions for Oracle version 12:
GRANT LOGMINING TO FIVETRAN;