Oracle RDS Setup Guide
Follow these instructions to replicate your Oracle RDS database to your destination via Fivetran.
If you would like us to connect through your SSH Tunnel, follow the SSH Tunnel instructions. All databases running Oracle version 12.1 or below must connect using an SSH tunnel.
To connect your Oracle database to Fivetran, you need:
- Oracle 11g or above
- IP (e.g. 18.104.22.168) or host (your.server.com)
- Port (usually 1521)
The Fivetran data processing servers will need access to your database server.
You will need to configure your VPC Security Groups and Network ACLs (Access Control Lists) to allow
incoming connections to your Oracle database host and port (usually
1521) from Fivetran's IP
Configure security group
These instructions assume that your instance is in a VPC.
Expand the instance by clicking on the oracle instance and click the Configuration Details tab:
A panel of details for your read replica will appear.
Check that Publicly Accessible reads Yes.
Write down the read instance's port number (you will need this later), then click the link to its Security Group:
In the security group panel, select the "Inbound" tab:
Click "Add Rule":
A new Custom TCP Rule gets created at the bottom of the list with a blank space for a Port Range and a Source IP address.
For the Port, enter your instance's port number that you wrote down earlier (usually
For the Source, enter Fivetran's IP:
Step 3a Configure Network ACLs
Return to the RDS Dashboard and expand the view on the instance:
Click the link to the instance's VPC:
Select the VPC:
In the "Summary" tab, click the "Network ACL" link:
You will see tabs for Inbound Rules and Outbound Rules. We will need to edit both.
Step 3b Edit inbound rules
Select "Inbound Rules":
If you have a default VPC that was automatically created by AWS, then the settings already allow all incoming traffic as indicated by the Source value 0.0.0.0/0 and the fact that the ALLOW entry is listed above the DENY entry:
If your inbound rules don't include an
ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow Fivetran's IP to access the port number of your read replica (usually
Help on ACL configuration can be found here.
Step 3c Edit outbound rules
Select "Outbound Rules":
If your outbound rules don't include an
ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow
outbound traffic to all ports
destination Fivetran's IP
Step 4 Create fivetran user
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
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:
BEGIN rdsadmin.rdsadmin_util.grant_sys_object('DBA_EXTENTS','"FIVETRAN"','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('DBA_TABLESPACES','"FIVETRAN"','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('DBA_SEGMENTS','"FIVETRAN"','SELECT'); END;
Please note that Oracle database defaults to using uppercase, unless values are surrounded by double quotes.
Step 5 Incremental Updates
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:
BEGIN rdsadmin.rdsadmin_util.grant_sys_object('DBA_FLASHBACK_ARCHIVE','FIVETRAN','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('DBA_FLASHBACK_ARCHIVE_TABLES','FIVETRAN','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('V_$SYSTEM_PARAMETER','FIVETRAN','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','FIVETRAN','SELECT'); END;
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>;
Undo Log Retention
You must set the
UNDO_RETENTION system parameter to a minimum of 24 hours. In RDS, system parameters are configured
in a custom parameter group. If you're currently using a default parameter group, you first need to add a custom one and then reboot
to apply the changes. If you'd like to avoid rebooting, you can enable Flashback Data Archive instead.
If you're already using a custom parameter group, skip ahead to the Set the UNDO_RETENTION parameter section.
Add a custom parameter group
In the left menu pane, click Parameter groups.
Click Create parameter group.
Choose a parameter group family that matches your database version. Name your custom parameter group and fill out a description.
Return to your database page and click Modify in the top right.
Find the Database options section and select your new custom parameter group in the dropdown menu.
At the bottom of the page, click Continue.
Choose when you would like to apply the changes, and click Modify DB instance.
On the Configuration tab for your database, you should now see your new parameter group listed under Parameter groups. Once it says Pending reboot, you can reboot your database to apply the changes.
Click on the Actions dropdown menu.
The next page will ask if you're sure. Click Reboot again.
Once reboot is complete, your new custom parameter group will have the status "in-sync."
Set the UNDO_RETENTION parameter
In the Configuration tab, click on your custom parameter group to set the parameter.
Search for "undo_retention," then select the checkbox next to the result:
Click Edit parameters.
Choose a value for UNDO_RETENTION that is at least 24 hours long (Fivetran recommends 7 days). The parameter is measured in seconds:
Once the parameter is applied, your parameter group will have the status "in-sync."
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:
1. ARCHIVELOG mode enabled
ARCHIVELOG mode is enabled automatically when automated backups are enabled by setting the Backup Retention Period to a value greater than 0. For more information, please see Amazon RDS automated backups documentation here
2. Archived redo log files retained for at least 24 hours (Fivetran recommends 7 days)
BEGIN rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 24); END;
3. Supplemental logging of primary key columns enabled
BEGIN rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY'); END;
4. Permissions for running LogMiner granted to user
BEGIN rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','FIVETRAN','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','FIVETRAN','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','FIVETRAN','SELECT'); rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','FIVETRAN','EXECUTE'); rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','FIVETRAN','EXECUTE'); END; GRANT SELECT ANY TRANSACTION TO "FIVETRAN";
**Please note that Amazon has online documentation on common DBA tasks for Oracle database instances here
4b. Additional permissions for Oracle version 12c only:
GRANT LOGMINING TO "FIVETRAN";