Oracle RAC Setup Guide PRIVATE PREVIEW

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

Allow access

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.

Create user

Connect to your Oracle RAC 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;

replacing <password> with a password of your choice.

Grant read-only access

Once user 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_EXTENTS, DBA_TABLESPACES and DBA_SEGMENTS system views:

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

Please note that Oracle RAC defaults to using uppercase, unless values are surrounded by double quotes.

Incremental updates

To keep your data up to date after the initial sync, we use Oracle's built-in Flashback technology. Flashback keeps a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync.

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;
GRANT SELECT ON DBA_FLASHBACK_ARCHIVE_TABLES TO FIVETRAN;
GRANT SELECT ON V$SYSTEM_PARAMETER TO FIVETRAN;
GRANT SELECT ON V$DATABASE TO FIVETRAN;

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

Grant the FIVETRAN user 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

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

Documentation