Oracle RAC Setup Guide

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

Prerequisites

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 instructions

Choose connection method

Decide how you want to connect your Oracle RAC database to Fivetran:

Create user

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>, <password>, and <profile_name> with a username, password, and profile name of your choice.

   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 PROFILE <profile_name> LIMIT SESSIONS_PER_USER 10;
   CREATE USER <username> IDENTIFIED BY <password>;
   ALTER USER <username> PROFILE <profile_name>;
   GRANT CREATE SESSION TO <username>;
   

   For multitenant container databases

   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 PROFILE <profile_name> LIMIT SESSIONS_PER_USER 10;
   CREATE USER <username> IDENTIFIED BY <password> CONTAINER=ALL;
   ALTER USER <username> PROFILE <profile_name>;
   GRANT CREATE SESSION, ALTER SESSION, SET CONTAINER TO <username> CONTAINER=ALL;
   ALTER USER <username> SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
   

Grant read-only access

Grant the Fivetran user read-only access to the data you want to sync.

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

   Alternatively, you can grant access to all tables.

   GRANT SELECT ANY TABLE TO <username>;
   

   For multitenant container databases

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

2. Grant the Fivetran user access to the DBA_EXTENTS, DBA_TABLESPACES, and DBA_SEGMENTS 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>;
   

Configure incremental sync method

To keep your data up to date after the initial sync, we use one of the following incremental sync methods. 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:

Finish Fivetran configuration

1. In your connection setup form, enter a Destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connection is created.

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

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

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

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

   In Oracle, usernames are case-sensitive. Make sure to enter the exact username.

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

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

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

9. (Not applicable to Hybrid Deployment) Choose your Connection method. If you selected Connect via an SSH tunnel, copy or make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel, and 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, make sure to keep the Require TLS through Tunnel toggle turned ON.

10. Select the Update Method: Detect Changes via Fivetran Teleport Sync.

11. (Not applicable to Hybrid Deployment) Copy the Fivetran's IP addresses (or CIDR) that you must safelist in your firewall.

12. 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 tests

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 selected an indirect connection method and then disabled the Require TLS through Tunnel toggle.)
• 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.

Related articles

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connection Configuration

home Documentation Home