Oracle High-Volume Agent Setup Guide – Fivetran Support Portal

Follow these instructions to replicate your Oracle database using the High-Volume Agent connector to your destination using Fivetran.

Prerequisiteslink

To connect your 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.

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 agent 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 agent on your Oracle database host.
- The agent IP address and port number (usually 4343)
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

First, decide whether to connect Fivetran to your Oracle database:

directly
using an SSH tunnel
using AWS PrivateLink

IMPORTANT: No matter which option you select, you must install the Fivetran High-Volume Agent on your database host.

Connect directly

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 SSH

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 Reverse SSH Tunnel is required, contact our support team as additional steps will be required in the setup of the connector.

Connect using AWS PrivateLink

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet.

Learn more in AWS’ PrivateLink documentation.

Follow our AWS PrivateLink setup guide to configure PrivateLink for your database.

Create userlink

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 as FIVETRAN. Multitenant container database usernames must start with C## because they are common users.

For standalone databases:
```
CREATE USER <username> IDENTIFIED BY <password>;
GRANT CREATE SESSION TO <username>;
```
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;
```

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, and TRANSACTION 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 Mode

It is required to have your Oracle database in archivelog mode. This is normally the default for most production systems as it does allow online backups and point in time recovery. However, to ensure Fivetran does have access to all the required redo information having the database in archivelog mode is required.

In addition, we also recommend to keep at least 24 hours’ worth of archive logs available.

The following section provides an overview on how archive logging can be enabled. However, we recommend to also review the Oracle documentation.

If the ARCHIVELOG mode is not enabled on your database, enable ARCHIVELOG mode.

IMPORTANT: If your database is not in archivelog mode, you will require a restart of the database to place it in 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, 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 retaining 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

The Fivetran High-Volume Agent needs the Oracle supplemental logging feature enabled on tables that it replicates.

Otherwise, when an update is done, Oracle will only log the columns which are changed. But other data is also needed (e.g. the key columns) by the agent so that it can generate a full update statement on the target destination warehouse.

The Oracle supplemental logging can be set at the database level and on specific tables.

Enable supplemental logging

We require one of the following logging options:

Database-level minimal supplemental logging with table level identification key logging. We recommend this option because it minimizes the overhead on your source database.
Database-level identification key logging.

Follow the instructions below for your chosen logging option.

Database-level minimal supplemental logging with table-level identification key logging

Enable minimal supplemental logging by executing the following SQL statement:

NOTE: Per Oracle’s database-level supplemental logging documentation, “minimal supplemental logging does not impose significant overhead on the database that generates the redo log files.”
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
```
Once minimal supplemental logging has been enabled at the database level, you then need to enable either primary key identification logging or all supplemental logging. You only need to use all supplemental logging for tables without primary keys.

To enable primary key supplemental logging only for specific tables, run the following SQL statement for each table:
```
ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
```
To enable all supplemental logging only for specific tables, run the following SQL statement for each table.

IMPORTANT: Below is required for tables without a primary key
```
ALTER TABLE "<schema>"."<table>" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
```
NOTE: With all supplemental logging, if a row is updated, all of the columns associated with that row are placed in the redo log file.

Database-level identification key logging

Enable either primary key identification key logging or all supplemental logging at the database level if you want to enable logging for the entire database. You only need to use all supplemental logging for tables without primary keys.

Perform either of the following actions:

To enable primary key supplemental logging at the database level, run the following SQL statement:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
```
To enable all supplemental logging at the database level, run the following SQL statement:

IMPORTANT: Below is required for tables without a primary key
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
```

NOTE: If you don’t configure the logging data correctly, you will receive a warning when Fivetran encounters a primary key change. The warning will give you customized instructions on how to fix the problem.

Configure Direct Capture PRIVATE PREVIEWlink

To enable Direct Capture, do the following:

Create a temporary table 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>;

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$ and sys.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$
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 <username>;

For multitenant container databases Beta:

GRANT SELECT <view_name> TO <username> CONTAINER=ALL;

(Optional) Grant permissions to additional system tables.

NOTE: We recommend granting 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 High Volume Agent step
- Direct Capture via 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 user for ASM access:
```
grant select on sys.v_$asm_diskgroup_stat to <username>;
grant select on sys.v_$asm_client to <username>;
```

Install the High-Volume Agentlink

In order to use the High-Volume Agent Connectors, you must first install the agent on your database host. The steps below cover both Oracle non-RAC and Oracle RAC configurations.

Agent Requirements

The agent must be installed on the same host running your Oracle database
If using Oracle RAC (three options):
- Installing the agent on one node (this does not provide High-Availability (HA) if the node goes down)
- Install the agent on all nodes in the Oracle RAC Cluster
- Install the agent on a shared storage system (for example, ACFS file system), which is visible in all nodes (This method is the recommended approach)
Minimum of 10 GB disk space is recommended
Install the agent as the oracle database software owner (e.g., oracle)

If you use a different user, they must be a member of Oracle’s default Operating System group (typically either oinstall or dba on Unix based systems and ora_dba on Windows).

Download Agent

During the initial setup of the connector, we provide you with a download link. Download the agent for your database server operating system.

Install Agent

Perform the following steps as the user that will run the agent. The examples below will assume the use of the oracle user.

Create folder structure

The agent should be installed into a base location, for example, /opt/fivetran.
Within this location, three key subdirectories must be created, hvr_home, hvr_config, and hvr_tmp.

NOTE: If using Oracle RAC shared storage (e.g., ACFS), make sure the /opt/fivetran folder is on shared storage and mounted on all nodes in the Cluster. If not using shared storage, the location must be created on all nodes in the cluster.
- As the root user:
```
mkdir /opt/fivetran
chown -R oracle:oinstall /opt/fivetran
```
- As the oracle user:
```
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, make sure this step is performed on all nodes in the cluster.

i. It is required to configure the following environment variables:
```
- `HVR_HOME`
- `HVR_CONFIG`
- `HVR_TMP`
```
```
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 variable PATH:
```
PATH=$PATH:$HVR_HOME/bin
```
iii. Add the HVR environment and the executable directory path into the startup file (e.g., .profile or .bash_profile):

Add the HVR environment to the Oracle user:
```
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 using shared storage in Oracle RAC (e.g., ACFS for /opt/fivetran), you need to perform the actions in this step once. If not using shared storage, you have to repeat the actions in this step for all nodes in the cluster.

i. Navigate into the $HVR_HOME location and extract the contents of the installer.

As the oracle user:
```
cd $HVR_HOME
tar xzf /tmp/hvr-*-agent-*.tar.gz
```
The above process only takes a few seconds. Once complete, the agent and all required components are installed into the $HVR_HOME location (/opt/fivetran/hvr_home). 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 the agent

i. Create an agent user that you will use to set up your Fivetran connector. Make a note of the username and password. You will need them to configure Fivetran. In the following example, we use the username hvr.

 hvragentuserconfig -c hvr  
 Password for 'hvr': <enter a password>
 Retype password:  <confirm the password>

ii. Disable the setup mode.

hvragentconfig Setup_Mode_Timed_Until=
hvragentconfig: F_JR055E: Agent property Setup_Mode_Timed_Until is not set. \
F_JT05C9: The previous error was an agent administration input validation error.

Note that the error message is normal.

iii. Extract and save the agent public certificate. You will need it to set up your connector.

hvragentconfig Agent_Server_Public_Certificate
(Save the value of Agent_Server_Public_Certificate for the connector configuration later)

Start the agent

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.

As the oracle user:
```
cd $HVR_HOME/bin
./hvragentlistener -d 4343
```
This is the simplest way to run the agent. However, after initial setup, you should add the agent to the system auto startup/shutdown sequence to ensure the agent is started and stopped with the database host.

See the Advanced Startup Guide for Oracle High-Volume Agent section in the guide for more detail on using Linux’ systemd or adding the agent to Oracle Clusterware as a cluster resource:

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 does fail, you will need to start the agent on one of the other nodes in the cluster.

To keep your data up to date after the initial sync, we use Change Data Capture (CDC). This allows Fivetran to capture and then update only the data that has changed since our last sync.

Advanced configurationlink

Oracle ASM Configuration

If using Oracle RAC configuration, it is recommended to 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 for this, you will need to 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 cluster 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 that you will 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 FASM.

Auto startup

Once the agent installation is complete, you should look at adding it to your system’s auto start and shutdown procedure. On Linux, there are two key options:

For non-RAC - use Linux’ systemd
For Oracle RAC - add the agent as a cluster resource

These two options are described in more detail below.

Add agent to systemd

The following steps should be performed as user root to configure systemd:

Create the systemd unit files hvr.socket and hvr@.service in the /etc/systemd/system directory.

The hvr.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:

[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=oracle
ExecStart=/opt/fivetran/hvr_home/bin/hvragent
StandardInput=socket
KillMode=process

[Install]
WantedBy=multi-user.target

You need to specify the user for which the agent is installed/running.
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

Agent as cluster resource

If you are using Oracle RAC, review the Oracle ASM Configuration section.

Adding the agent to an Oracle RAC cluster as a cluster resource does require 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 and racnode2
Agent software is installed into - /opt/fivetran
The agent software owner is the oracle software owner - oracle

Substep 1: Create a Virtual IP

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 a 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 above of 10.1.1.159 is just an example, you have to use an IP address within your network that is a part of your Oracle RAC public network subnet.

Substep 2: Agent Action Script

To add the Fivetran High-Volume agent as an Oracle Clusterware resource, you need to make use of an Action Script.

See the following example of an Action Script:

#!/bin/bash
#


#



export PATH=/usr/local/bin:$PATH


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


export AGENT_PORT=4343


###########



###########
set_env ()
{
  export ORAENV_ASK=NO
  export ORACLE_SID=$1
  . oraenv >> /dev/null
  export ORAENV_ASK=YES
}


###########

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


echo "`date` Action script '$_CRS_ACTION_SCRIPT' for resource [$_CRS_NAME] called for action $1"



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 resource

After creating an action script, you can use it to create the cluster resource. In the following example, we copied the action script to a shared storage such as /opt/fivetran/hvr_config/action-script.scr. Note that as this location will be available to all nodes due to the location /opt/fivetran being on shared storage (we recommend using ACFS).

Note that 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 resource

To manage cluster resources, see the Oracle Clusterware Administration and Deployment Guide.
See the commands required to start, stop, and relocate the resource in the following example:

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 to racnode2:

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

Advanced topics

This section covers any advanced topics related to the setup and configuration of the Oracle High-Volume Agent connector.

User grants

The Fivetran user must be granted the select any dictionary privilege to read the data dictionaries in Oracle’s SYS schema.

grant select any dictionary to <username>;

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

Indicate if the database is Oracle RAC.
In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database host name (or IP address).
- If you are using a single instance database, then specify the database host IP address
- If using Oracle RAC:
  - Specify an Application Virtual IP (VIP) address which is associated with the High-Volume Agent.
  - This IP Address must be part of the cluster configuration and will move between the nodes in case of node failure or restarts.
  - This also provides you with the option to run the agent on a specific node in the cluster.
  - Using shared storage for the High-Volume Agent is recommended for this approach.
- This host must be accessible from Fivetran either directly or thru ssh tunnel depending on your connectivity configuration.
Enter your database’s port number. The port number is usually 1521.
Enter the Fivetran-specific user that you created in Step 2.
Enter the password for the Fivetran-specific user that you created in Step 2.
Enter your database’s SID or Service Name.
- If using Oracle RAC, supply a Service Name.
- Service names can be created using the Oracle “srvctl” command on RAC.
- Example: srvctl add service -db DEV -service FSRVC -preferred DEV1
- For more detail on using the srvctl command and services please see the Oracle Documentation
Enter the database’s TNS or SID for the agent to use. If you use TNS, it is in the form of <hostname>:<port>/<ServiceName>. You will need to use TNS for pluggable databases and RAC.
(Multitenant container databases only) Enter your database’s PDB Name.
- If using Oracle RAC, you can specify a Service Name that points to your Pluggable Database.
Indicate if the database is using Oracle ASM. If you selected Oracle ASM, an additional ASM user and password will be required.
Choose your connection method. If you selected Connect via an SSH tunnel, provide the following information:
- SSH IP address (do not use a load balancer’s IP address/hostname)
- SSH port
- SSH user
Enter the High-Volume Agent IP address and port (usually 4343).
- For Oracle RAC, the Agent IP address will be the VIP of the RAC node where the agent is running.
- In most other cases, the Agent IP address will match the database host IP address.
- Ensure that your Firewall / Security Group allows Fivetran’s IPs for your database’s region.
Enter the High-Volume Agent username and password.
Enter the High-Volume Agent’s Server Public Certificate.
Enter the ORACLE_HOME of environment of your database.
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, and DBA_SEGMENTS system views.
The Preparing Infrastructure for Direct Capture Test prepares the local infrastructure to perform the remainder of the tests.
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.

Oracle High-Volume Agent Setup Guide Private Preview

Prerequisiteslink

Setup instructionslink

Choose connection methodlink

Connect directly

Connect via SSH

Connect using AWS PrivateLink

Create userlink

Grant read-only accesslink

Supplemental logginglink

Enable supplemental logging

Database-level minimal supplemental logging with table-level identification key logging

Database-level identification key logging

Configure Direct Capture PRIVATE PREVIEWlink

Install the High-Volume Agentlink

Agent Requirements

Download Agent

Install Agent

Advanced configurationlink

Oracle ASM Configuration

Auto startup

Add agent to systemd

Agent as cluster resource

Substep 1: Create a Virtual IP

Substep 2: Agent Action Script

Substep 3: Create cluster resource

Substep 4: Start and stop cluster resource

Advanced topics

User grants

Finish Fivetran configurationlink

Setup testslink

Related Contentlink

Didn’t find what you need?

Prerequisiteslink

Setup instructionslink

Choose connection methodlink

Connect directly

Connect via SSH

Connect using AWS PrivateLink

Create userlink

Grant read-only accesslink

Supplemental logginglink

Enable supplemental logging

Database-level minimal supplemental logging with table-level identification key logging

Database-level identification key logging

Configure Direct Capture PRIVATE PREVIEWlink

Install the High-Volume Agentlink

Agent Requirements

Download Agent

Install Agent

Advanced configurationlink

Oracle ASM Configuration

Auto startup

Add agent to systemd

Agent as cluster resource

Substep 1: Create a Virtual IP

Substep 2: Agent Action Script

Substep 3: Create cluster resource

Substep 4: Start and stop cluster resource

Advanced topics

User grants

Finish Fivetran configurationlink

Setup testslink

Related Contentlink

Related articles

Didn’t find what you need?