Capture From Oracle TDElink
Fivetran HVR supports capturing tables that are encrypted using Oracle Transparent Data Encryption (TDE). Capturing tables located in encrypted tablespace and tables with encrypted columns are supported for Oracle version 11 and higher.
HVR supports software and hardware (HSM) wallets. If the wallet is not configured as auto-login (refer to Oracle internal file cwallet.sso), use the location property/field WALLET PASSWORD (Oracle_TDE_Wallet_Password) to set password for the Oracle TDE wallet.
Software wallets can be located in ASM or in a local file system:
For wallets located in ASM, see the following section Accessing TDE Wallet Through BFile Interface.
For wallets located in a local file system, the HVR requires either permission to read the wallet file, or a trusted executable for HVR should be created in HVR_HOME/sbin with chmod +4755. If the wallet, which is located in a local file system, is configured for auto-login, then either HVR or the trusted executable must be run by the user who created the wallet (usually the oracle user).
Steps for creating the trusted executable
The following steps should be performed/executed on behalf of the Oracle's operating system user.
Copy the hvr executable to hvr_oracle directory using the following command:
cp $HVR_HOME/bin/hvr $HVR_HOME/sbin/hvr_oracle
Use patchelf to set the rpath for hvr_oracle as follows:
$HVR_HOME/lib/patchelf --set-rpath $HVR_HOME/lib --force-rpath $HVR_HOME/sbin/hvr_oracle
Change the permissions for hvr_oracle with the following command:
chmod 4755 $HVR_HOME/sbin/hvr_oracle
Check the permissions of hvr_oracle using the ls command:
ls -l $HVR_HOME/sbin/hvr_oracle
The output should resemble the following:
-rwsr-xr-x 1 oracle oinstall 93288688 Sep 21 10:39
/hvr_home/sbin/hvr_oracle
In Oracle 12, for replicating encrypted columns, the HVR database user must have explicit select
privileges on sys.user$ and sys.enc$ tables.
grant select on sys.user$ to hvruser; grant select on sys.enc$ to hvruser;
Further channel configuration changes are not required; HVR automatically detects encryption and opens the wallet when it is encountered.
Since version 6.1.5/3, auto-login wallets on Oracle version 11 are no longer supported. They are still supported for Oracle version 12 or higher.
Accessing TDE Wallet Through BFile Interfacelink
The primary usage of this method is remote capturing on ASM systems.
To configure access to a TDE wallet on a remote ASM system through the BFile interface, follow these steps (while creating a location or by editing the existing location's source and target properties):
Select capture method Direct Redo Access (equivalent to Capture_Method=DIRECT) and then select option TDE Encryption.
If the wallet is not configured as auto-login, specify the wallet’s password in the WALLET PASSWORD field (equivalent to Oracle_TDE_Wallet_Password).
Select Access TDE Wallet through BFile interface (equivalent to Oracle_TDE_Wallet_Reading_By_BFile).
The WALLET DIRECTORY MAPPING fields (equivalent to Oracle_BFile_Dirs_Mapping) should only be filled if the wallet directory path includes symbolic links. This is necessary because Oracle does not allow access through the BFile interface to a directory that has symbolic links in its path.
For example, if the wallet directory is /var/foo/xxx, where /var/foo/ is a symbolic link to path /yyy/zzz. Thus, the real path to the wallet directory is /yyy/zzz/xxx. In this case, /var/foo should be specified in the Symbolic link field and /yyy/zzz should be specified in the Target field.
Configure access to the wallet directory through the BFile interface. A dba user must execute the following commands:
create or replace directory dir_name as 'full_path_to_wallet_dir_on_oracle_server'
grant read on directory dir_name to username
Once everything is set up, HVR will automatically find the wallet's directory and read the wallet through the BFile interface.