Capture from Oracle TDE

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. HVR must always be provided with access to the wallet file:

• For configurations where redo logs are captured remotely from ASM and the wallet is also located in ASM: See section Accessing TDE Wallet Through BFile Interface to learn how to access the wallet through BFile.

• For configurations where redo logs are captured remotely through BFile: The wallet will also be accessed through BFile, and no additional configuration is needed.

• For wallets located in a local file system: HVR requires either permission to read the wallet file, or a trusted executable for HVR should be created in HVR_HOME/sbin with the command chmod +4755.

• For wallets configured with local auto-login: The HVR process reading the wallet must run on the same machine where the wallet was originally created and by the user who created the wallet (usually the oracle user). This requirement ensures HVR can decrypt the content of the wallet correctly. Note that this may restrict remote access options to the wallet.

  Steps for creating the trusted executable

  The following steps should be performed/executed on behalf of the Oracle's operating system user.

  1. Copy the hvr executable to hvr_oracle directory using the following command:

     cp $HVR_HOME/bin/hvr $HVR_HOME/sbin/hvr_oracle
     

  2. 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
     

  3. Change the permissions for hvr_oracle with the following command:

     chmod 4755 $HVR_HOME/sbin/hvr_oracle
     

  4. 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 Interface

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):

1. Select capture method Direct Redo Access (equivalent to Capture_Method=DIRECT).

2. If the wallet is not configured as auto-login, select option TDE Encryption and specify the wallet’s password in the WALLET PASSWORD field (equivalent to Oracle_TDE_Wallet_Password).

3. Select Access TDE Wallet through BFile interface (equivalent to Oracle_TDE_Wallet_Reading_By_BFile).

4. 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.

   

5. 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.

Oracle Key Vault

Since v6.1.5/7, HVR supports capture from Oracle databases that have TDE configured with wallets stored in Oracle Key Vault (OKV).

The following is required to use HVR with OKV:

• The OKV client must be installed on:

  • The capture machine if the HVR agent captures changes from the Oracle database, or
  • The hub machine if the HVR hub directly connects to the source Oracle database.

• Oracle location:

  • Select the TDE Encryption option while creating a location or by editing the existing location's source and target properties and specify the wallet’s password in the WALLET PASSWORD field (the equivalent location property is Oracle_TDE_Wallet_Password).

• Define the following three actions on the source Oracle location:

  Location	Action	Parameter(s)
  Oracle	Environment	Name=HVR_EXPORT Value=OKV_HOME=OKV_client_installation_path
  Oracle	Environment	Name=HVR_WALLET_PKCS11_LIB_DIR Value=OKV_HOME/lib
  Oracle	Environment	Name=OKV_HOME Value=OKV_client_installation_path

HVR does not support auto-login wallet for OKV.

See Also

• Capture from Oracle Pluggable Database