High-Volume Agent Oracle Setup Guide link
Follow these instructions to replicate your Oracle database to your destination using Fivetran.
Prerequisiteslink
To connect your Oracle database to Fivetran using HVA, you need:
IMPORTANT: We do not support syncing database usernames or schema names with special or lowercase characters.
A Fivetran account with an Enterprise or Business Critical plan
Oracle 11g R2 and above
Your database host's IP address
- If using Oracle RAC, either a VIP (Application Virtual IP) or a node's public IP
- This IP address must be accessible from Fivetran either directly or through an SSH tunnel
Your database's port (usually
1521
)Connection string for the HVA to use to connect to your database
- For Single Instance, specify Oracle SID
- If using Oracle RAC or Multitenant (pluggable databases), specify a Service Name
- If using Service Name, it must have an entry in your local
tnsnames.ora
Installation of the HVA on your Oracle database host.
- The HVA IP address and port number (usually
4343
)
- The HVA IP address and port number (usually
Ensure supplemental logging is enabled in your database
Database must be in ARCHIVELOG mode
NOTE: Oracle RDS is not supported with the High-Volume Agent connector.
Setup instructionslink
Choose connection methodlink
IMPORTANT: Do not perform this step if you want to use Hybrid Deployment for your data pipeline.
First, decide whether to connect Fivetran to your Oracle database:
IMPORTANT: No matter which option you select, you must install the Fivetran High-Volume Agent on your database host.
Connect directlylink
IMPORTANT: Your database version must be 12.2 or above to connect directly. If your database version is 12.1 or below, connect using SSH.
Fivetran connects directly to the High-Volume Agent installed on the database host. All communication between Fivetran and the HVA is encrypted.
The HVA has a direct connection to your Oracle database.
To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your Oracle database host where the agent is installed.
This should be restricted to the database port (usually 1521
) and the agent port (usually 4343
) from Fivetran's IPs for your database's region. How you do this varies based on how your Oracle database is hosted (cloud platform, on-premises, etc.).
Connect using SSHlink
Fivetran connects to a separate server in your network that provides an SSH tunnel to your database host, or cluster running Oracle RAC. You must connect through SSH if your database is in an inaccessible subnet.
To connect using SSH, follow our SSH connection instructions.
NOTE: If you want to use Reverse SSH Tunnel, contact our support team for the additional setup steps for the connector configuration.
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.
Connect using Proxy Agent Betalink
NOTE: To connect via Proxy Agent, you must download and install a High-Volume Agent (HVA).
Fivetran connects to the HVA through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in the customer's network and creates an outbound network connection to the Fivetran-managed SaaS.
To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.
Create userlink
IMPORTANT: The Fivetran user requires a minimum of eight simultaneous database sessions. Oracle's default per-user session limit is unlimited. If you want to limit the concurrent session count for the Fivetran user, ensure that it is at least eight.
Create a database user for Fivetran's exclusive use.
Connect to your Oracle database as an admin user.
Execute the following SQL command 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. Follow the instructions below for your database type:NOTE: Usernames in Oracle are case-sensitive. For example,
fivetran
is not the same user asFIVETRAN
. Multitenant container database usernames must start withC##
because they are common users.For standalone databases:
CREATE USER <username> IDENTIFIED BY <password>; GRANT CREATE SESSION TO <username>;
alter profile DEFAULT limit SESSIONS_PER_USER 15;
NOTE: Use the above command if the profile name of the FIVETRAN user is set to DEFAULT. Otherwise, replace DEFAULT with your chosen profile name. Check your profile name with the following query:
SELECT USERNAME, PROFILE FROM DBA_USERS where USERNAME='FIVETRANUSER'
IMPORTANT: You must have at least 15 sessions if your FIVETRAN user profile is the default profile. You must have at least 5 sessions if your FIVETRAN user profile is not the default profile.
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;
NOTE: Your FIVETRAN user must have at least 15 sessions per user in both a constant database and a pluggable database. Run the following command at the container level and the pluggable level to check the parameter:
col username for a12 col profile for a19 col limit for a12 set lines 299 select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' and a.username='FIVETRANUSER';
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.
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 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>;
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>;
Grant the Fivetran user access to the
DBA_EXTENTS
,DBA_TABLESPACES
,DBA_SEGMENTS
, andTRANSACTION
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>;
Configure ARCHIVELOG modelink
It is required to have your Oracle database in ARCHIVELOG mode. This is normally the default for most production systems as it allows online backups and point in time recovery. However, to ensure Fivetran has access to all the required redo information having the database in ARCHIVELOG mode is required.
In addition, we also recommend that you keep at least 24 hours' worth of archive logs available.
The following section provides an overview on how archive logging can be enabled. We also recommend that you review the Oracle documentation.
If the ARCHIVELOG mode is not enabled on your database, enable ARCHIVELOG mode.
IMPORTANT: If your database is not in the ARCHIVELOG mode, you must restart the database to place it in the ARCHIVELOG mode.
If using Single Instance Database:
NOTE: Enabling the ARCHIVELOG mode requires the Oracle instance to be briefly taken offline. To learn more, see Oracle's archived redo log file documentation.
shutdown immediate; startup mount; alter database archivelog; alter database open;
If using Oracle RAC:
NOTE: Enabling the ARCHIVELOG mode requires the Oracle instance to be briefly taken offline. To learn more, see Oracle's archived redo log file documentation.
i. Shut down all database instances.
srvctl stop database -d <db_unique_name>
ii. Start the database in mount mode.
srvctl start database -d <db_unique_name> -o mount
iii. 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;
NOTE:
<SHARED_LOCATION>
, if using Oracle RAC, will be a specific ASM disk group, for example, +FRA.iv. Stop the database.
srvctl stop database -d <db_unique_name>
v. Restart all database instances.
srvctl start database -d <db_unique_name>
vi. Verify that archiving is enabled.
sql> archive log list;
IMPORTANT: The connector's database host must have access to the archived logs of all other nodes in the cluster.
Configure Oracle RMAN to retain backups and archive logs for at least 24 hours. We recommend that you retain 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;
(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.
Supplemental logginglink
HVA requires the Oracle supplemental logging feature to be enabled on tables it replicates. Without this feature, Oracle logs only the columns that are changed. The agent, however, needs additional data (e.g. key columns) to generate a full update statement for the target destination.
Oracle supplemental logging can be set at both the database level and for specific tables.
Enable supplemental logginglink
Below are the SQL commands for manually enabling supplemental logging:
Enable database-level minimal supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Enable additional table-level supplemental logging depending on the table type:
For a table with a primary key, enable primary key supplemental logging:
ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
For a table without a primary key or unique index, or for a table with any type of compression enabled (regardless of whether they have a primary key and/or unique indexes), enable logging for all columns:
ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
NOTE: Enabling supplemental logging for all table columns ensures that any row change captures the values of all columns. This method allows the replication process to identify changes based on the entire row's data, which is especially beneficial for tables without a primary key.
NOTE: For tables without a primary key, any update operation may incur additional MAR if it causes row movement. Oracle generates three log events for row movement —
UPDATE
(to an existing row),DELETE
(of the row from its current partition), andINSERT
(of the row into a new partition). Fivetran counts these as two MAR.For a table without a primary key but with a unique index, enable conditional supplemental logging:
ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG GROUP "<group_name>" ("<column_name>", ...) ALWAYS;
NOTE: This command creates a user-defined supplemental log group for specific column(s) within a table, offering a precise control over which column changes are logged.
Check supplemental logging statuslink
To check the status of supplemental logging at database-level:
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM v$database;
To check the status of supplemental logging at table-level:
SELECT log_group_type FROM all_log_groups WHERE table_name='<schema>.<table>';
Disable supplemental logginglink
To disable supplemental logging at database-level:
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
To disable supplemental logging at table-level:
For a table with a primary key:
ALTER TABLE "<schema>"."<table>" DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
For a table without a primary key or unique index:
ALTER TABLE "<schema>"."<table>" DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
For a table without a primary key but with a unique index:
ALTER TABLE "<schema>"."<table>" DROP SUPPLEMENTAL LOG GROUP "<group_name>";
Configure Direct Capture Betalink
To enable Direct Capture, do the following:
Create a temporary table in the standalone or pluggable database and grant read/write access to the Fivetran user:
CREATE GLOBAL TEMPORARY TABLE HVR_SYS_TABLE (TABLE_NAME VARCHAR(128), TABLE_OWNER VARCHAR(128)); GRANT SELECT, INSERT, UPDATE, DELETE ON HVR_SYS_TABLE TO <username>;
NOTE: HVA uses the temporary table to make its queries more efficient during replication. Since this is a temporary table, there is no database logging on this table, and no rows remain at the end of the session. Storage for this table goes into the temporary tablespace.
Grant the Fivetran user permission to select any dictionary. We need to read the data dictionaries in Oracle's SYS schema to run Direct Capture. Follow the instructions below for your database type:
For standalone databases:
GRANT SELECT ANY DICTIONARY TO <username>;
For multitenant container databases Beta:
GRANT SELECT ANY DICTIONARY TO <username> CONTAINER=ALL;
IMPORTANT: In Oracle 12, you must grant the Fivetran user explicit select privileges on the
sys.user$
andsys.enc$
tables to replicate the following encrypted columns:grant select on sys.user$ to ;
grant select on sys.enc$ to ;
Alternatively, you may grant the read access of the following views individually.
grant select on sys.v_$archive_dest to <username>; grant select on sys.v_$archived_log to <username>; grant select on sys.v_$database to <username>; grant select on sys.v_$database_incarnation to <username>; /* The following grant (sys.v_$dnfs_files) is required for identifying the redo files located on DirectNFS */ grant select on sys.v_$dnfs_files to <username>; /* The following grant (sys.v_$encryption_wallet) is required for decryption */ grant select on sys.v_$encryption_wallet to <username>; grant select on sys.v_$log to <username>; grant select on sys.v_$logfile to <username>; grant select on sys.v_$nls_parameters to <username>; grant select on sys.v_$parameter to <username>; grant select on sys.v_$pdbs to <username>; /* The following grant (sys.v_$system_parameter) is required for reading the value of 'filesystemio_options' parameter which in turn is used for reading the redo logs */ grant select on sys.v_$system_parameter to <username>; grant select on sys.all_cons_columns to <username>; grant select on sys.all_constraints to <username>; grant select on sys.all_ind_columns to <username>; grant select on sys.all_indexes to <username>; grant select on sys.all_lobs to <username>; grant select on sys.all_log_groups to <username>; grant select on sys.all_objects to <username>; grant select on sys.all_tab_cols to <username>; grant select on sys.all_tables to <username>; grant select on sys.all_encrypted_columns to <username>; grant select on sys.dba_tablespaces to <username>; grant select on sys.obj$ to <username>; /* The following grant (sys.ecol$) is required for Oracle Database 11.2 and above since default values for added columns are stored differently. */ grant select on sys.ecol$ to <username>; grant select on sys.user$ to <username>; grant select on sys.col$ to <username>; grant select on sys.enc$ to <username>; grant select on sys.indpart$ to <username>; grant select on sys.tabpart$ to <username>; grant select on sys.tabsubpart$ to <username>; /* The following three grants are required for the initial sync */ grant select on sys.v_$locked_object to <username>; grant select on sys.v_$transaction to <username>; grant select on sys.dba_objects to <username>;
For standalone databases:
GRANT SELECT <view_name> TO <username>;
For multitenant container databases Beta:
GRANT SELECT <view_name> TO <username> CONTAINER=ALL;
(Optional) Grant permissions to additional system tables.
NOTE: We recommend that you grant 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_$TEMPFILE TO <username>; GRANT SELECT ON SYS.V_$DATAFILE TO <username>;
For multitenant container databases Beta:
Execute the following command before granting access.
ALTER SESSION SET CONTAINER=CDB$ROOT;
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>;
If you are using:
Direct Capture without ASM, proceed to the Install HVA Step
Direct Capture via ASM, do the following to finish your configuration:
i. Create an ASM user with
SYSASM
privilege, connecting to the ASM instance usingSYS as SYSASM
.CREATE USER <asm_user> IDENTIFIED by <asm_user_password>; GRANT SYSASM TO <asm_user>;
IMPORTANT: If the ASM is only reachable through a TNS connection, you must specify the connection string in the ASM TNS property.
ii. Allow the following grants to the Fivetran user for ASM access:
grant select on sys.v_$asm_diskgroup_stat to <username>; grant select on sys.v_$asm_client to <username>;
Install HVAlink
This section provides detailed instructions on how to install HVA and outlines the necessary requirements.
HVA requirementslink
Before proceeding with the installation of HVA, ensure that you have the following prerequisites in place:
Compatibility: Verify that the HVA version is compatible with your operating system and DBMS. Refer to the COMPATIBILITY section in the relevant release notes on the Downloads page of your Fivetran dashboard.
Sufficient disk space: Ensure that the machine where you want to install HVA has ample disk space. We recommend a minimum of 10 GB of available disk space.
HVA installation file: Download the HVA installation file from the Downloads page of your Fivetran dashboard. Select the installation file suitable for your database server's operating system.
IMPORTANT: You must install HVA on the same host where your Oracle database is running.
- For Oracle RAC, you have the following options:
- Install the agent on one node. This does not provide High-Availability (HA) if the node goes down.
- Install the agent on all the nodes within the Oracle RAC.
- Install the agent on a shared storage system that is visible in all nodes. For example, ACFS file system. We recommend this approach.
- For Oracle Active Data Guard, install the agent on your standby nodes.
- For Oracle RAC, you have the following options:
System permissions: HVA requires permission to read Oracle's redo and archive files at the operating system-level. This can be achieved in one of the following ways:
- Install and run the agent under the database owner user account (for example,
oracle
). - Install and run the agent under a different user account (for example,
fivetran_user
) that must be a member of Oracle's default operating system group. Typically, this group is eitheroinstall
ordba
on Unix-based systems andora_dba
on Windows.
- Install and run the agent under the database owner user account (for example,
Depending on your database server's operating system, follow the instructions below to install HVA.
Install HVA on Linuxlink
Expand for instructions
Create folder structure
i. Create the HVA installation directory (for example,
/opt/fivetran
) and set the appropriate owner (for example,oracle
) and group (for example,oinstall
) for it.NOTE: This command must be executed by the
root
user.mkdir /opt/fivetran chown -R oracle:oinstall /opt/fivetran
NOTE: If you are using Oracle RAC with shared storage (for example, ACFS), ensure that the
/opt/fivetran
folder is created on the shared storage and mounted on all nodes in the cluster. If you are not using the shared storage, the/opt/fivetran
folder must be created on all nodes in the cluster.ii. In the
/opt/fivetran
directory, create three key subdirectorieshvr_home
,hvr_config
, andhvr_tmp
:mkdir -p /opt/fivetran/hvr_home mkdir -p -m 01775 /opt/fivetran/hvr_config/etc /opt/fivetran/hvr_tmp
Configure environment
NOTE: If using Oracle RAC, ensure this step is performed on all nodes in the cluster.
i. Configure the
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
environment variables to point to the relevant HVA installation subdirectories:HVR_HOME=/opt/fivetran/hvr_home HVR_CONFIG=/opt/fivetran/hvr_config HVR_TMP=/opt/fivetran/hvr_tmp
ii. Add the
$HVR_HOME/bin
executable directory path to the environment variablePATH
:export PATH=$PATH:$HVR_HOME/bin
iii. Add the environment variables and the executable directory path into the startup file (for example,
.profile
or.bash_profile
):export HVR_HOME=/opt/fivetran/hvr_home export HVR_CONFIG=/opt/fivetran/hvr_config export HVR_TMP=/opt/fivetran/hvr_tmp export PATH=$PATH:$HVR_HOME/bin
Install agent
NOTE: If you are using Oracle RAC with the
/opt/fivetran
folder created on shared storage, you need to perform this step only once. If you are not using shared storage, you must repeat this step for all nodes in the cluster.Navigate into the
$HVR_HOME
location and extract the contents of the HVA installation file (for example,fivetran-6.1.0_26-hub_and_agent-linux_glibc2.12-x64-64bit_ga_patch.tar.gz
).cd $HVR_HOME tar xzf /tmp/fivetran-6.1.0_26-hub_and_agent-linux_glibc2.12-x64-64bit_ga_patch.tar.gz
Once complete, the agent and all required components are installed into the
$HVR_HOME
location (/opt/fivetran/hvr_home
).NOTE: The size of the installation is less than 150 MB. After the installation (extract), you can remove the installation file as it is no longer required.
Install HVA on Unixlink
Expand for instructions
Create folder structure
i. Create the HVA installation base directory (for example,
/opt/fivetran
) and set the appropriate owner, group, and permissions on it. Run the following command as userroot
:mkdir /opt/fivetran chown -R oracle:oinstall /opt/fivetran
NOTE: If you are using Oracle RAC with shared storage (e.g., ACFS), ensure that the
/opt/fivetran
folder is created on the shared storage and mounted on all the nodes in the cluster. If you are not using shared storage, the/opt/fivetran
folder must be created on all the nodes in the cluster.ii. Within the
/opt/fivetran
directory, create three key subdirectories:hvr_home
,hvr_config
, andhvr_tmp
.mkdir -p /opt/fivetran/hvr_home mkdir -p -m 01775 /opt/fivetran/hvr_config /opt/fivetran/hvr_tmp
Configure environment
NOTE: If using Oracle RAC, ensure this step is performed on all the nodes in the cluster.
i. Configure the environment variables
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
:export HVR_HOME=/opt/fivetran/hvr_home export HVR_CONFIG=/opt/fivetran/hvr_config export HVR_TMP=/opt/fivetran/hvr_tmp
ii. Add the
$HVR_HOME/bin
executable directory path to the environment variablePATH
:export PATH=$PATH:$HVR_HOME/bin
iii. Add the HVR environment variables and the executable directory path into the startup file (e.g.,
.profile
or.bash_profile
).export HVR_HOME=/opt/fivetran/hvr_home export HVR_CONFIG=/opt/fivetran/hvr_config export HVR_TMP=/opt/fivetran/hvr_tmp export PATH=$PATH:$HVR_HOME/bin
Installing agent
NOTE: If you are using Oracle RAC with the
/opt/fivetran
folder created on shared storage, you need to perform this step only once. If you are not using shared storage, you must repeat this step for all nodes in the cluster.Navigate into the
$HVR_HOME
location and extract the contents of the installer.cd $HVR_HOME gzip -dc /tmp/hvr-*-agent_only-*.tar.gz | tar xf -
Once completed, the HVA and all required components are installed into the $HVR_HOME
location (/opt/fivetran/hvr_home
).
NOTE: The size of the installation is less than 150 MB. After installing (extracting) HVA, you can remove the installation file as it is no longer required.
Install HVA on Windows using ZIP filelink
Expand for instructions
Windows user account permissions:
NOTE: HVA installation file for Windows is available in the
.exe
and.zip
formats. The compressed file (.zip
) distribution is normally used as an alternative for the Windows executable based (.exe
) distribution. The steps to install HVA are also different for both formats.
Create folder structure
As the Administrator user, create an HVA installation directory (for example,
C:\hvr
) with three subdirectorieshvr_home
,hvr_config
, andhvr_tmp
:mkdir C:\hvr\hvr_home mkdir C:\hvr\hvr_config mkdir C:\hvr\hvr_tmp
Configure environment
Configure the
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
environment variables, each pointing to the relevant HVA installation directorieshvr_home
,hvr_config
, andhvr_temp
.NOTE: The following instructions assume that the directories are created inside the
C:\hvr
directory. Use your installation directory if it differs fromC:\hvr
.i. Navigate to Control Panel ▶ System and Security ▶ System ▶ Advanced system settings.
ii. Under the Advanced tab, click Environment Variables...
iii. In the System variables or User Variables for user_name section, click New.
iv. Enter Variable name (e.g,
HVR_HOME
) and Variable value (e.g,C:\hvr\hvr_home
).v. Click OK. Repeat the above steps iii and iv for environment variables
HVR_CONFIG
andHVR_TMP
.Add the HVR executable directory path to the environment variable
Path
.i. In the System variables or User Variables for user_name section, from the list of variables, select Path and click Edit....
ii. In the Edit environment variable dialog, click New and enter the path for the HVA executable. Click OK.
Install HVA
Navigate to the
$HVR_HOME
directory (C:\hvr\hvr_home
) and extract the contents of the downloaded.zip
file:Once completed, the HVA and all required components are installed into the
$HVR_HOME
directory (C:\hvr\hvr_home
).NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.
Install HVA on Windows using EXE filelink
Expand for instructions
Windows user account permissions:
NOTE: HVA's installation file for Windows is available in the
.exe
and.zip
formats. The compressed file (.zip
) distribution is normally used as an alternative for the Windows executable based (.exe
) distribution. The steps to install HVA are also different for both formats.
Run the downloaded
.exe
file (for example,hvr-6.1.0_10-hub_and_agent-windows-x64-64bit_ga_patch-setup.exe
).In the setup wizard dialog, click Next.
Read the License Agreement, select I accept the agreement and then click Next.
Specify the HVA installation directories and click Next.
Click Next to create a program folder for HVA.
Select High-Volume Agent (HVA) and click Next.
Enter a value for Agent Listener Port and click Next.
Select the user account to run the HVA service.
If you select Specified user, enter the values for User, Password, Confirm Password and click Next.
NOTE: For user with Windows authentication (if applicable), specify your Active Directory domain and username in the format
<domain>\<username>
.If you select Local System account, click Next.
Select Add HVR_HOME, HVR_CONFIG, and HVR_TMP (if required) and click Next.
This is to set the environment variables
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
in your operating system. These variables point to the corresponding directories created in step 4 above.Click Next to initiate the installation.
Click Finish to start the HVA service.
Once the installation is complete, the HVA and all required components are installed into the
$HVR_HOME
directory (C:\hvr\hvr_home
).NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.
Configure HVAlink
Follow the instructions below to configure HVA:
Create an HVA user to set up your Fivetran connector. In the following example, we use
hva_user
.NOTE: The minimum password length for the user must be 10 characters and not contain special characters.
hvragentuserconfig -c hva_user Password for 'hva_user': <enter a password> Retype password: <confirm the password>
Make a note of the username and password. You will need them to configure Fivetran.
Run the following command to disable the setup mode:
hvragentconfig Setup_Mode_Timed_Until=
Run the following command to extract the HVA public certificate:
hvragentconfig Agent_Server_Public_Certificate
Save the output value of the
Agent_Server_Public_Certificate
. You will need it to set up your connector.
Start HVAlink
Perform the following steps as the user who runs HVA.
To start the agent, use the hvragentlistener
command. Follow the instructions for the operating system of your database server:
In Unix and Linux, add flag
-d
followed by the port number that you want to run on. In the example, the default port is4343
.hvragentlistener -d 4343
In Windows, add flag
-acs
followed by the port number that you want to run on. In the example, the default port is4343
.hvragentlistener -acs 4343
After the initial setup, you should add the agent to your system's autostart/shutdown sequence to ensure the agent starts and stops with the database host. For instructions, see section Autostart.
NOTE: For Oracle RAC, configuration using shared storage is highly recommended. Running the agent as a cluster resource and only on one node at a time is the preferred setup. If you do not want to follow this approach, you can run the agent only on one of the nodes in the cluster. If this node fails, you will need to start the agent on one of the other nodes in the cluster.
Advanced configurationlink
Autostartlink
After the initial setup, you should add the agent to your system's autostart and shutdown procedure.
Autostart - Linuxlink
On Linux, there are two key options:
For Oracle non-RAC - add agent to systemd
Expand for instructions
The following steps should be performed as user
root
to configure systemd:Create the systemd unit files
hvr.socket
andhvr@.service
in the/etc/systemd/system
directory. Thehvr.socket
file should contain the following:[Unit] Description=Fivetran agent service socket [Socket] ListenStream=4343 Accept=true TriggerLimitIntervalSec=1s TriggerLimitBurst=10000 MaxConnectionsPerSource=100 MaxConnections=500 KeepAlive=true [Install] WantedBy=sockets.target
NOTE:
TriggerLimitIntervalSec
is supported since systemd version 230.TriggerLimitBurst
is supported since systemd version 230.MaxConnectionsPerSource
is supported since systemd version 232.The
hvr@.service
should contain the following:NOTE: You need to specify the
User
for which the agent is installed/running.[Unit] Description=Fivetran Agent service [Service] Environment="HVR_HOME=/opt/fivetran/hvr_home" Environment="HVR_CONFIG=/opt/fivetran/hvr_config" Environment="HVR_TMP=/opt/fivetran/hvr_tmp" User=agent_user ExecStart=/opt/fivetran/hvr_home/bin/hvragent StandardInput=socket KillMode=process [Install] WantedBy=multi-user.target
To enable and start the service, execute the following commands:
systemctl enable hvr.socket systemctl start hvr.socket
To verify whether the service is active, execute the following command:
systemctl status hvr.socket
A sample output:
hvr.socket - HVR service socket Loaded: loaded (/etc/systemd/system/hvr.socket; enabled; vendor preset: enabled) Active: active (listening) since Mon 2020-09-07 17:54:44 CEST; 5s ago Listen: [::]:4343 (Stream) Accepted: 0; Connected: 0
For Oracle RAC - add agent as a cluster resource
Expand for instructions
If you are using Oracle RAC, review the Oracle ASM Configuration section.
Adding the agent to an Oracle RAC cluster as a cluster resource requires a number of extra steps as outlined below. In the examples below, we use the following naming:
- Virtual IP Name -
fivetran-vip
- Oracle RAC node names -
racnode1
andracnode2
- The agent software is installed in the
/opt/fivetran
folder - The agent software owner is the Oracle software owner -
oracle
Substep 1: Create a Virtual IPlink
The Virtual IP (VIP) will be specifically used by the agent. This can be seen as an IP address dedicated to the High-Volume Agent which will run on only one node at a time in the cluster. This is also the node where the agent listener will be running, ideally using shared storage system such as an ACFS file system.
The commands to create the Virtual IP are as follows:
appvipcfg create -network=1 -ip=10.1.1.159 -vipname=fivetran-vip -user=root crsctl setperm resource fivetran-vip -u user:oracle:r-x crsctl setperm resource fivetran-vip -u user:grid:r-x crsctl start resource fivetran-vip -n racnode1
NOTE: The IP address
10.1.1.159
listed above is just an example. You have to use an IP address within your network that is part of your Oracle RAC public network subnet.Substep 2: Agent Action Scriptlink
To add the High-Volume Agent as an Oracle Clusterware resource, you need to make use of an action script.
See the following example of the action script:
#!/bin/bash # # Description: # A sample Fivetran High-Volume Agent action script # # set following to ensure oraenv is picked up from /usr/local/bin # set -x export PATH=/usr/local/bin:$PATH # set Fivetran-specific environment export HVR_HOME=/opt/fivetran/hvr_home export HVR_CONFIG=/opt/fivetran/hvr_config export HVR_TMP=/opt/fivetran/hvr_tmp export FTAGENT_BASE=/opt/fivetran # update agent port if different export AGENT_PORT=4343 ########### # Function to set the oracle environment # Note that it is making use of /etc/oratab # Your database must be listed on all nodes in the cluster ########### set_env () { export ORAENV_ASK=NO export ORACLE_SID=$1 . oraenv >> /dev/null export ORAENV_ASK=YES } ########### # Helper function to rename logfiles to keep clean history ########### rename_log () { utcdate=`date -u +"%Y-%m-%dT%H.%M.%SZ"` if [ -f $HVR_CONFIG/logs/hvragentlistener${AGENT_PORT}.log ]; then mv $HVR_CONFIG/logs/hvragentlistener${AGENT_PORT}.log $HVR_CONFIG/logs/hvragentlistener${AGENT_PORT}.log.${utcdate} fi } ########### ## Main ########### # This is logged to CRSD agent log file echo "`date` Action script '$_CRS_ACTION_SCRIPT' for resource [$_CRS_NAME] called for action $1" # Note that you must update the value for the current_host below to match your environment. # in the setup below the rac node 1 is called "racnode1" current_host=`hostname -s` if [ "$current_host" == "racnode1" ]; then set_env DEV1 else set_env DEV2 fi cd $HVR_HOME/bin case "$1" in 'start') NUM=`ps -ef | grep hvragentlistener | egrep -v 'grep|action-script|resource' | wc -l` if [ $NUM != 0 ]; then echo "Agent still running, stop first" RET=1 else rename_log ./hvragentlistener -d ${AGENT_PORT} RET=0 echo "Start fivetran agent (hvragentlistener) resource with return code $RET" fi ;; 'stop') NUM=`ps -ef | grep hvragentlistener | egrep -v 'grep|action-script|resource' | wc -l` if [ $NUM = 0 ]; then ## do a cleanup of pid ./hvragentlistener -k $AGENT_PORT >> $FTAGENT_BASE/ftagent.out 2>&1 rename_log RET=0 else ## now stop the agent ./hvragentlistener -k $AGENT_PORT >> $FTAGENT_BASE/ftagent.out 2>&1 rename_log NUM=`ps -ef | grep hvragentlistener | grep -v grep | wc -l` if [ $NUM = 0 ]; then RET=0 else RET=1 fi fi echo "Stop fivetran agent (hvragentlistener) resource with return code $RET" ;; 'check') NUM=`ps -ef | grep hvragentlistener | egrep -v 'grep|action-script|resource' | wc -l` if [ $NUM = 0 ]; then ## return code 1 for check means OFFLINE RET=1 else ## return code 0 for check means ONLINE RET=0 fi echo "Running check fivetran agent (hvragentlistener) resource with return code $RET" ;; 'clean') for c1 in `ps -ef|grep hvragentlistener |egrep -v 'grep|action-script|resource'| awk '{print $2}'` ; do echo "...force kill fivetran agent (hvragentlistener) pid $c1" kill -9 $c1 done ## do some cleanup ./hvragentlistener -k $AGENT_PORT >> $FTAGENT_BASE/ftagent.out 2>&1 rename_log RET=0 echo "Running clean fivetran agent (hvragentlistener) resource with return code $RET" ;; esac if [ $RET -eq 0 ]; then exit 0 else exit 1 fi
Substep 3: Create cluster resourcelink
After creating the action script, you can use it to create the cluster resource. In the following example, we copied the action script to shared storage such as
/opt/fivetran/hvr_config/action-script.scr
.NOTE: This location will be available to all nodes due to the location
/opt/fivetran
being on the shared storage (we recommend using ACFS).The cluster resource we create is for the agent, but we are making it dependent on the VIP created in Substep 1 above.
If this VIP moves between nodes, the agent will move with it.
As the grid user run:
crsctl add resource ftagent -type cluster_resource -attr "ACTION_SCRIPT=/opt/fivetran/hvr_config/action-script.scr, RESTART_ATTEMPTS=3, \ START_TIMEOUT=60, STOP_TIMEOUT=60, CHECK_INTERVAL=10, START_DEPENDENCIES='hard(fivetran-vip) pullup(fivetran-vip)', STOP_DEPENDENCIES='hard(fivetran-vip)', \ ACL='owner:oracle:rwx,pgrp:oinstall:rwx,other::r--', PLACEMENT='favored', HOSTING_MEMBERS='racnode1 racnode2'"
Substep 4: Start and stop cluster resourcelink
To manage cluster resources, see the Oracle Clusterware Administration and Deployment Guide.
To see the status, start or stop the agent, you can use the following commands:
crsctl status resource ftagent crsctl start resource ftagent -n <rac-node> -f crsctl stop resource ftagent -f
The following example command relocates the Virtual IP (
fivetran-vip
) to another node in the cluster. This will also stop the agent and move it along with the Virtual IP:crsctl relocate resource fivetran-vip -n <rac-node> -f
An example command to move the Virtual IP from
racnode1
toracnode2
:root@rac1 ~ : crsctl relocate resource fivetran-vip -n racnode2 -f CRS-2673: Attempting to stop 'ftagent' on 'racnode1' CRS-2677: Stop of 'ftagent' on 'racnode1' succeeded CRS-2673: Attempting to stop 'fivetran-vip' on 'racnode1' CRS-2677: Stop of 'fivetran-vip' on 'racnode1' succeeded CRS-2672: Attempting to start 'fivetran-vip' on 'racnode2' CRS-2676: Start of 'fivetran-vip' on 'racnode2' succeeded CRS-2672: Attempting to start 'ftagent' on 'racnode2' CRS-2676: Start of 'ftagent' on 'racnode2' succeeded
- Virtual IP Name -
Autostart - Solarislink
Expand for instructions
In Solaris 10 and higher, the /etc/inetd.conf
file has been replaced by SMF manifests. All inetd services are now configured/controlled under the SMF. The following steps should be performed as user root
to configure the SMF service:
In the
/etc/services
file, add the following line specifying the name (e.g.hva
) and the TCP/IP port number of the agent:hva 4343/tcp
Create a temporary file (e.g.
/tmp/inet.hvr
) containing the following inetd.conf-style entry for the agent:TIP: Option
-E
defines the environment variables.hva stream tcp nowait oracle /opt/fivetran/hvr_home/bin/hvragent hvragent -EHVR_HOME=/opt/fivetran/hvr_home -EHVR_CONFIG=/opt/fivetran/hvr_config -EHVR_TMP=/opt/fivetran/hvr_tmp
NOTE: The user must be set to the user (e.g.
oracle
) under which the HVA is installed/running. The service name (e.g.hva
) specified in this temporary file must match with the name specified (step #1) in the/etc/services
file. The above configuration should be defined in a single line (without manual line break).Import the temporary file (e.g.
/tmp/inet.hvr
) into System Management Facility (SMF) using theinetconv
command.For example:
inetconv -i /tmp/inet.hvr
Enable the service using the
inetadm
command.For example:
inetadm -e svc:/network/hvr/tcp:default
Refresh the service using the
svcadm
command:svcadm refresh inetd
Autostart - Unix (AIX)link
Expand for instructions
The following steps should be performed as user root
to configure the inetd
service:
In the
/etc/services
file, add the following line specifying the name (e.g.hva
) and the TCP/IP port number of the agent:hva 4343/tcp
Add the following line to the
/etc/inetd.conf
file:TIP: Option
-E
defines the environment variables.hva stream tcp nowait oracle /opt/fivetran/hvr_home/bin/hvragent hvragent -EHVR_HOME=/opt/fivetran/hvr_home -EHVR_CONFIG=/opt/fivetran/hvr_config -EHVR_TMP=/opt/fivetran/hvr_tmp
NOTE: The user must be set to the user (e.g.
oracle
) under which the agent is installed/running. The service name (e.g.hva
) specified in theinetd.conf
file must match with the name specified (step #1) in the/etc/services
file. The above configuration should be defined in a single line (without manual line break).Reload or restart the
inetd
service to apply the changes. For information about restarting theinetd
service, refer to the operating system documentation.
Oracle ASM Configurationlink
If using Oracle RAC configuration, we recommend that you run the High-Volume Agent as a cluster resource. When using this method, the agent needs to connect to the ASM instance using a TNS entry which is making use of a bequeath connection for direct access. To allow for this, you must create a tnsnames.ora
entry for your ASM instances on each of your cluster nodes.
For example, if you have a two node Oracle RAC with +ASM1 on node 1 and +ASM2 on node 2, then you need to add a TNS entry similar to the following on each of the nodes.
NOTE: You need to adjust the Grid Oracle Home path to match your environment.
In the example, we also call the TNS entry FASM.
Example configuration on node 1:
FASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = BEQ)
(PROGRAM = /u01/app/19.0.0/grid/bin/oracle)
(ARGV0 = oracle+ASM1)
(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
(ENVS = 'ORACLE_HOME=/u01/app/19.0.0/grid,ORACLE_SID=+ASM1'))
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
(INSTANCE_NAME = +ASM1)
)
)
Example configuration on node 2:
FASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = BEQ)
(PROGRAM = /u01/app/19.0.0/grid/bin/oracle)
(ARGV0 = oracle+ASM2)
(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
(ENVS = 'ORACLE_HOME=/u01/app/19.0.0/grid,ORACLE_SID=+ASM2'))
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
(INSTANCE_NAME = +ASM2)
)
)
IMPORTANT: When you specify the ASM instance details in the Connector Setup, specify the TNS value as
FASM
.
Finish Fivetran configurationlink
In the connector setup form, enter a Destination schema prefix of your choice. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter the following information:
- If you are using a single instance database, specify the hostname or IP address of your database.
- If you have installed the HVA on the database server, set the value to
localhost
. - If you are using Oracle RAC, specify an Application Virtual IP (VIP) address associated with the HVA.
NOTE: The VIP address must be part of the cluster configuration and will move between nodes in case of node failure or restarts. This provides you with the option to run the agent on a specific node in the cluster. For this approach, we recommend using shared storage for the HVA.
- If you are using Active Data Guard, enter the IP address where your standby database is running.
Enter your database's Port number. The default port number is
1521
.Enter the Fivetran-specific User created in Step 2.
Enter the Password for the Fivetran-specific user created in Step 2.
Enter your database's SID or Service Name.
- If you are using Oracle RAC, enter a Service Name. You can create the Service name using the Oracle
srvctl
command on RAC, for example,srvctl add service -db DEV -service FSRVC -preferred DEV1
. For more details on using thesrvctl
command and services, refer to Oracle Documentation.
- If you are using Oracle RAC, enter a Service Name. You can create the Service name using the Oracle
(Multitenant container databases only) Enter your database's PDB Name.
- If you are using Oracle RAC, you can specify the Service Name that points to your pluggable database.
Enter the database's TNS or SID for the agent to use.
NOTE: TNS is required for pluggable databases and RAC. TNS should be in the form of
<hostname>:<port>/<Service> or <SID>
.(Optional for Hybrid Deployment) By default, the Enable local data processing toggle is set to ON and the local processing agent associated with your destination is selected in the Select an existing local processing agent drop-down menu. To use a different local processing agent, select the agent of your choice, and then select the same agent for your destination.
NOTE: After completing this step, skip to Step 11.
Choose your connection method.
If you selected Connect via an SSH tunnel, provide the following information:
- SSH Host (do not use a load balancer's IP address/hostname)
- SSH Port (the default port number is
22
) - SSH User
If you selected Connect via proxy agent, choose the necessary proxy agent from the Proxy agents drop-down list (if available) or configure a new proxy agent.
(TDE-encrypted database only) If you want to use TDE encryption, set the Use TDE Encryption toggle to ON.
(Optional) Enter TDE Wallet Password if your database is configured with Password-Based keystore. Leave the field blank if your database is configured with Auto-Login keystore.
(Optional) If the database is using Oracle RAC, set the Using Oracle RAC? toggle to ON and provide the following information:
- ASM User
- ASM Password
- (optional) ASM Oracle Home
- (optional) ASM TNS
(Optional) If the database is using ASM, set the Using ASM? toggle to ON and provide the following information:
- ASM User
- ASM Password
- (optional) ASM Oracle Home
- (optional) ASM TNS
Set the Use archive log only mode toggle to ON if you want to use the Archive Log Only capture method instead of capturing directly from online redo logs. Enabling this option reveals the following fields:
(optional) Archive Log Path: Enter the directory path containing the archived log files on the machine where HVA is installed. If this field is left empty, High-Volume Agent will detect the location of the archived redo logs automatically.
(optional) Archive Log Format: Specify the filename format (template) of the archived log files stored in the directory specified by the Archive Log Path field. Fivetran will only read the archived log files that match the specified format, or the Oracle's initialization parameter LOG_ARCHIVE_FORMAT if no format is specified. This field accepts the following format variables:
%d
- match numbers (zero or more decimal digits). Numbers matched using this variable are ignored.%r
or%R
- resetlogs ID%s
or%S
- log sequence number%t
or%T
- thread number%z
or%Z
- match alphanumeric characters. Characters matched using this variable are ignored.
NOTE: Wildcard character
*
is not supported.
In the Agent Host field, enter the hostname or IP address of the HVA. In most cases, it should match the database hostname or IP address. However, it may vary depending on the connection method you have chosen. For more details, refer to High-Volume Agent network configuration options.
- For Oracle RAC where the High-Volume Agent is configured on a node, the HVA's IP address is the Virtual IP (VIP) of the RAC node where the agent is running.
- For Oracle RAC where the High-Volume Agent is configured as a cluster resource on a shared storage system, the HVA's IP address is the Virtual IP (VIP) created and dedicated to the agent.
NOTE: Ensure that your Firewall/Security Group allows Fivetran's IPs for your database's region.
In the Agent Port field, enter the HVA's port number. The default port number is
4343
.In the Agent User ID field, enter the name of the HVA user created in Step 8.
In the Agent User Password, enter the password of the HVA user created in Step 8.
In the Agent Public Cert, enter the HVA's Server Public Certificate you found in Step 8.
In the Oracle Home field, enter the directory path where your Oracle database is installed.
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 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 are not connecting using an SSH tunnel.
- The Validating Agent Setup Test verifies that we can connect to the HVA and whether it operates correctly.
- 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 the certificate and checks that we can connect to your database using TLS. We skip this test if you are not connecting directly.
- The Validating Database Version Test verifies your database version. The test succeeds if:
- your database version is 12.2 or above or
- your database version is 12.1 or below, and you are connecting using an SSH tunnel.
- The Validating Access to PDB Test verifies if 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 verifies if we have permissions to access the
DBA_EXTENTS
,DBA_TABLESPACES
, andDBA_SEGMENTS
system views. - The Access to Database-level Supplemental Logging Test checks if supplemental logging is enabled on your database. If supplemental logging is disabled, the test passes but generates a warning message in your dashboard.
- The Validating Permissions to Run Log Reader Test checks that the Fivetran user has permission to run Direct Capture.
- The Oracle DB Accessibility Test checks if the HVA has access to the source database.
- The Oracle DB Log Reading Test checks the if HVA can access the database log files.
NOTE: The tests may take a few minutes to finish running.
Related articleslink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration