Oracle RAC Setup Guide link

Follow these instructions to replicate your Oracle RAC database to your destination using Fivetran.

Prerequisiteslink

To connect your Oracle RAC database 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 (e.g., your.server.com)
• Your database's port (usually 1521 for unencrypted connections and 2484 for encrypted connections using SSL/TLS)
• Your database's system identifier (SID)/service name

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 RAC 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 using SSH (TLS optional)link

IMPORTANT: You must connect using an SSH tunnel if your Oracle RAC 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

Create a database user for Fivetran's exclusive use.

1. Connect to your Oracle RAC database as an admin user.

2. 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.

   NOTE: Usernames in Oracle are case sensitive. For example, fivetran is not the same user as FIVETRAN. Multitenant container database usernames must start with C## because they are common users.

   For standalone databases:

   CREATE USER <username> IDENTIFIED BY <password>;
   GRANT CREATE SESSION TO <username>;
   content_copy

   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

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.

1. Grant the Fivetran user SELECT permission for each schema and table you want to sync. Follow the instructions below for your database type:

   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

2. Grant the Fivetran user access to the DBA_EXTENTS, DBA_TABLESPACES, DBA_SEGMENTS, and TRANSACTION system views. We use these views to optimize our initial import queries.

   GRANT SELECT ON DBA_EXTENTS TO <username>;
   GRANT SELECT ON DBA_TABLESPACES TO <username>;
   GRANT SELECT ON DBA_SEGMENTS TO <username>;
   GRANT SELECT ANY TRANSACTION TO <username>;
   content_copy

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

IMPORTANT: Fivetran's Oracle RAC connector can only connect to a single node in a cluster. We do not support SCAN listener connections.

To enable LogMiner, do the following:

1. 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.

   Shut down all database instances.

   $ srvctl stop database -d <db_unique_name>
   content_copy

   Start the database in mount mode.

   $ srvctl start database -d <db_unique_name> -o mount
   content_copy

   Enable archive logging.

   $ sqlplus / as sysdba
   sql> ALTER DATABASE ARCHIVELOG;
   sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=<SHARED_LOCATION>' SCOPE=SPFILE;
   sql> EXIT;
   content_copy

   NOTE: <SHARED_LOCATION> could be any of the following:

   • Automatic Storage Management disks
   • Flash Recovery Area
   • NFS
   • Shared storage

   Stop the database.

   $ srvctl stop database -d <db_unique_name>
   content_copy

   Restart all database instances.

   $ srvctl start database -d <db_unique_name>
   content_copy

   Verify that archiving is enabled.

   sql> archive log list;
   content_copy

   IMPORTANT: The connector's database host must have access to the archived logs of all other nodes in the cluster.

   NOTE: We recommend that you set ARCHIVE_LAG_TARGET to a non-zero value. This setting reduces the lag in changes being synced.

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

3. (Recommended) Set the DB_RECOVERY_FILE_DEST_SIZE parameter 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.

4. Enable supplemental logging. We require one of the following logging options:

   • Database-level minimal supplemental logging with table level identification key logging. We recommend this option because it minimizes the overhead on your source database.
   • Database-level identification key logging.

   Follow the instructions below for your chosen logging option.

   Database-level minimal supplemental logging with table-level identification key logging

   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.

   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: With all supplemental logging, if a row is updated, all of the columns associated with that row are placed in the redo log file.

   Database-level identification key logging

   Enable either primary key identification key logging or all supplemental logging at the database level if you want to enable logging for the entire database. You only need to use all supplemental logging for tables without a primary key.

   To enable primary key supplemental logging at the database level, run the following SQL statement:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
   content_copy

   To enable all supplemental logging at the database level, run the following SQL statement:

   ALTER DATABASE 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.

5. Grant the Fivetran user permission to run LogMiner.

   For standalone databases:

   GRANT SELECT ON SYS.V_$DATABASE TO <username>;
   GRANT SELECT ON SYS.V_$PARAMETER 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

   For multitenant container databases Beta:

   Execute the following command before granting access to LogMiner.

   ALTER SESSION SET CONTAINER=CDB$ROOT;
   content_copy

   GRANT SELECT ON SYS.V_$DATABASE TO <username>;
   GRANT SELECT ON SYS.V_$PARAMETER 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

6. (Oracle version 12 and above) Grant logmining permissions to the Fivetran user.

   GRANT LOGMINING TO <username>;
   content_copy

7. (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

1. 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.

2. In the Host field, enter your RAC node public IP (for example, 1.2.3.4) or URL (for example, your.server.com).

3. Enter your database's Port number. The port number is usually 1521 for unencrypted connections and 2484 for encrypted connections using SSL/TLS.

4. Enter the Fivetran-specific User that you created in Step 2.

5. Enter the Password for the Fivetran-specific user that you created in Step 2.

6. Enter your database's SID/Service Name.

7. (Multitenant container databases only) Enter your database's PDB Name.

8. 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.

9. Click Save & Test. Fivetran tests and validates our connection to your Oracle database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup testslink

Fivetran performs the following tests to ensure that we can connect to your Oracle RAC database and that it is properly configured:

• The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH tunnel. (We skip this test if you aren't connecting using an SSH tunnel.)
• The Connecting to Host Test validates the database credentials you provided in the setup form. It then verifies that the database host is not private and checks that we can connect to the host.
• The Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you aren't connecting directly.)
• The Validating Database Version Test checks your database version. The test passes if your database version is 12.2 or above or if your database version is 12.1 or below and you are connecting using an SSH tunnel.
• The Validating Access to PDB Test checks that we have access to your pluggable database. We skip this test if your database is not a multitenant container database.
• The Validating System View Permission Test checks that we have permission to access the DBA_EXTENTS, DBA_TABLESPACES, and DBA_SEGMENTS system views.
• 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.

NOTE: The tests may take a few minutes to finish running.

Related articleslink

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connector Configuration

home Documentation Home