High-Volume Agent SAP ECC on Oracle Setup Guide Betalink
Follow these instructions to replicate your SAP ECC on Oracle database to your destination using Fivetran.
Prerequisiteslink
To connect your SAP ECC on Oracle database using the High-Volume Agent connector to Fivetran, you need:
IMPORTANT: We do not support syncing 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
- The IP address must be accessible from Fivetran either directly or through an SSH tunnel
Your database's port (usually
1521
)Connection string for the agent to use to connect to your database:
- For a single instance, specify Oracle SID.
- If using Oracle RAC or Multitenant (pluggable) databases, specify a Service Name. The Service Name, it must have an entry in your local
tnsnames.ora
.
Installation of the agent on your Oracle database host.
- The agent IP address and port number (usually
4343
)
- The agent IP address and port number (usually
Ensure supplemental logging is enabled in your database
Database must be in the ARCHIVELOG mode
Oracle database schema name under which the SAP tables reside, for example,
SAPSR3
.
Setup instructionslink
Choose connection methodlink
First, decide whether to connect Fivetran to your Oracle database:
IMPORTANT: No matter which option you select, you must install HVA on your database host.
Connect directlylink
Fivetran will connect directly to the High-Volume Agent installed on the database host. All communication between Fivetran and the agent is encrypted. The agent will have 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 via 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 Agentlink
Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your 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 database 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 database 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 10;
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 10 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 10 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 the 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 of 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 a 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 on 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 Capturelink
To enable Direct Capture, do the following:
Create a temporary table and grant read/write access to the Fivetran database 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 <database_user>;
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 database 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 <database_user>;
For multitenant container databases:
GRANT SELECT ANY DICTIONARY TO <database_user> CONTAINER=ALL;
IMPORTANT: In Oracle 12, you must grant the Fivetran database 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.
ALL_CONSTRAINTS ALL_CONS_COLUMNS ALL_ENCRYPTED_COLUMNS ALL_INDEXES ALL_IND_COLUMNS ALL_LOBS ALL_LOG_GROUPS ALL_OBJECTS ALL_TABLES ALL_TAB_COLS COL$ DBA_OBJECTS DBA_TABLESPACES ENC$ INDPART$ OBJ$ TABPART$ TABSUBPART$ USER$ V_$ARCHIVED_LOG V_$ARCHIVE_DEST V_$DATABASE V_$DATABASE_INCARNATION V_$LOG V_$LOGFILE V_$NLS_PARAMETERS V_$PARAMETER V_$PDBS ECOL$ - required for Oracle Database 11.2 and above since default values for added columns are stored differently V_$DNFS_FILES - required for identifying the redo files located on DirectNFS V_$ENCRYPTION_WALLET - required for decryption V_$SYSTEM_PARAMETER - required for reading the value of 'filesystemio_options' parameter which in turn is used for reading the redo logs DBA_OBJECTS - required for Refreshing Data V_$LOCKED_OBJECT - required for Refreshing Data V_$TRANSACTION - required for Refreshing Data
For standalone databases:
GRANT SELECT <view_name> TO <database_user>;
For multitenant container databases:
GRANT SELECT <view_name> TO <database_user> CONTAINER=ALL;
(Optional) Grant permissions to additional system tables to Fivetran database user.
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 <database_user>; GRANT SELECT ON SYS.V_$TEMPFILE TO <database_user>; GRANT SELECT ON SYS.V_$DATAFILE TO <database_user>;
For multitenant container databases:
Execute the following command before granting access.
ALTER SESSION SET CONTAINER=CDB$ROOT;
GRANT SELECT ON DBA_FREE_SPACE TO <database_user>; GRANT SELECT ON SYS.V_$LOG TO <database_user>; GRANT SELECT ON SYS.V_$TEMPFILE TO <database_user>; GRANT SELECT ON SYS.V_$DATAFILE TO <database_user>;
If you are using:
Direct Capture without ASM, proceed to the Install HVA Step
Direct Capture with ASM, do the following to finish your configuration:
i. Create an ASM user with
sysasm
privilege.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 database user for ASM access:
grant select on sys.v_$asm_diskgroup_stat to <database_user>; grant select on sys.v_$asm_client to <database_user>;
Install HVAlink
This section provides detailed instructions on how to install HVA and outlines the necessary requirements. The steps below cover both Oracle non-RAC and Oracle RAC configurations.
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 in 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 the 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 is eitheroinstall
ordba
on Unix-based systems andora_dba
on Windows.
- Install and run the agent under the database owner user account (for example,
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.
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, you need to run the hvragentlistener
command with the -d
flag followed by the port number that you want to run on. In the example, the default port is 4343
.
hvragentlistener -d 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
Oracle ASM configurationlink
If using Oracle RAC configuration, we recommend that you run the High-Volume Agent as a cluster resource only on one of the cluster nodes. 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 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.
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 -
Advanced topicslink
This section covers any advanced topics related to the setup and configuration of the High-Volume Agent connector.
User grantslink
The Fivetran database user must be granted the select any dictionary
privilege to read the data dictionaries in Oracle's SYS schema.
grant select any dictionary to <database_user>;
Alternatively, the Fivetran database User may be granted the select
privilege only for the required data dictionary objects:
grant select on sys.v_$archive_dest to fivetran;
grant select on sys.v_$archived_log to fivetran;
grant select on sys.v_$database to fivetran;
grant select on sys.v_$database_incarnation to fivetran;
/* 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 fivetran;
/* The following grant (sys.v_$encryption_wallet) is required for decryption */
grant select on sys.v_$encryption_wallet to fivetran;
grant select on sys.v_$log to fivetran;
grant select on sys.v_$logfile to fivetran;
grant select on sys.v_$logmnr_contents to fivetran;
grant select on sys.v_$nls_parameters to fivetran;
grant select on sys.v_$parameter to fivetran;
grant select on sys.v_$pdbs to fivetran;
/* 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 fivetran;
grant select on sys.all_cons_columns to fivetran;
grant select on sys.all_constraints to fivetran;
grant select on sys.all_ind_columns to fivetran;
grant select on sys.all_indexes to fivetran;
grant select on sys.all_lobs to fivetran;
grant select on sys.all_log_groups to fivetran;
grant select on sys.all_objects to fivetran;
grant select on sys.all_tab_cols to fivetran;
grant select on sys.all_tables to fivetran;
grant select on sys.all_triggers to fivetran;
grant select on sys.all_encrypted_columns to fivetran;
grant select on sys.dba_tablespaces to fivetran;
grant select on sys.obj$ to fivetran;
/* 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 fivetran;
grant select on sys.user$ to fivetran;
grant select on sys.col$ to fivetran;
grant select on sys.enc$ to fivetran;
grant select on sys.tabpart$ to fivetran;
grant select on sys.tabsubpart$ to fivetran;
/* The following three grants are required for Refreshing Data and DDL */
grant select on sys.v_$locked_object to fivetran;
grant select on sys.v_$transaction to fivetran;
grant select on sys.dba_objects to fivetran;
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>
.Enter the name of the SAP Schema. SAP tables reside under this Oracle schema, for example,
SAPSR3
.NOTE: You cannot modify this field once it is saved.
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 a 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
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, the HVA's IP address is the Virtual IP (VIP) of the RAC node where the agent is running.
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 generic 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 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
, andDBA_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.
- The Validating Permissions to Run Direct Capture Test checks that we have permission to either all system views or selective system views required to run Direct Capture.
- The Validating Agent Setup Test checks that we can connect to the high-volume agent and whether it operates correctly.
- The Oracle Log Accessibility Test checks that the high-volume agent can access the database redo log and archived log files.
- The Validating Speed Setup test checks how quickly Fivetran can fetch data from your source database. The test will show a warning if the speed is less than 5 MBps.
NOTE: The tests may take a few minutes to finish running.
Related articleslink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration