Change data capture gains huge popularity as a data integration mechanism as a non-intrusive way of obtaining technical events from database. This 2 part series shows how to set up such environment.
HVR is a tool that replicates transactions between databases that HVR calls ‘locations’. Each change it captures is applied to the target locations. It can also replicate between directories (file locations) or replicate between databases and directories.
The HUB DATABASE is a small database from which HVR controls replication of the other databases. It is created especially for the HVR installation. It contains HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the replication direction and the list of tables to be replicated.
A LOCATION is a database that HVR will replicate to or from. A location can also be a directory (a ‘file location’) from which HVR replicates files or a Salesforce endpoint.
A CHANNEL is an object in HVR that groups together the locations that should be connected together and the tables that should be replicated. It also contains actions that control how replication should be done. Channels are defined in the hub database. As well as replicating changes, channels can also be used to refresh data. Refresh means all data is read from a source and loaded into another database, without replication.
Installation
WHERE TO INSTALL
The HVR distribution must be installed on the hub machine. If a database involved in replication is not located on the hub machine HVR can connect to it in two ways; either using the network database’s own protocol (e.g. SQL*Net or Ingres/Net) or using an HVR remote connection (recommended).
For an HVR remote location, the full HVR distribution should also be installed on the remote machine, although a few of its files are used. If the DBMS’s own protocol is used then no HVR files need to be installed on the remote machine.
If the HVR replication configuration is altered, all the reconfiguration steps are performed on the hub machine; installation files on the remote machine are unchanged.
It is recommended that HVR is installed under its own login account (e.g. hvr) on the hub machine and each machine containing the replicated database or directory. However, HVR can also use the account that owns the replicated tables (e.g. the DBA’s account) or it can use the DBMS owner account (e.g. oracle or ingres). Any login shell is sufficient.
The HVR distribution files are installed under environment variable $HVR_HOME. HVR also creates files underneath $HVR_CONFIG. Both these environment variables must be configured. An extra environment variable $HVR_TMP is also recognized, but if it is not configured it will default to $HVR_CONFIG/tmp.
INSTALLING HVR ON LINUX
Create user which HVR will run as.Example. User hvr identified with password ‘hvruser’. Run this commands:
The following steps should be performed as the user which HVR will run as, not as user root. So first create user hvr identified with password ‘hvruser’ and then:
1. Choose directories for read only and read write parts of HVR. In our case:
Place these variables to .bashrc file:
2. Modify the $PATH and also put this into .bashrc file
3. Read and uncompress the distribution file:
- If this is a hub machine then install the HVR license file (hvr.lic) into the $HVR_HOME/lib folder.
- Now define environment variable $HVR_HUB_CLASS as a default DBMS type for the hub database. For us is oracle. Place this in .bashrc file:
- HVR listen port must be configured.
- Port 4343 is recommended
- Login as root and configure systemd to invoke HVR.
- We need to create 2 files (hvr.socket and hvr@.service) in /etc/systemd/system folder.
- hvr.socket should contain the following:
- hvr@.service should contain the following:
- To enable and start the service execute the following commands:
- To verify whether the service is active:
Preparing Oracle database for use with HVR
This section describes the requirements, access privileges, and other features of HVR when using Oracle for replication. It also helps you to get started with HVR for replicating data between Oracle databases.
CREATE HUB DATABASE
HVR allows you to create hub database (schema) in Oracle. The hub database is a small database which HVR uses to control its replication activities. This database stores HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the list of replicated tables, and the replication direction. Create the hub database (hvrhub) with password (hvr):
GRANTS FOR HUB SCHEMA
The hub database (schema) can be located inside an Oracle instance on the hub machine, or it can be located on another machine and connected using a TNS connection. The following grants are required for hub database user ( hvrhub):
CAPTURE
HVR allows you to capture changes from Oracle database. By default, HVR performs log-based capture.
Enable Log-Based Capture
To enable log-base capture, configure the following:
- The user name that HVR uses must be added to Oracle’s group.
- On Linux, this can be done by adding the user name used by HVR ( hvr) to the line in /etc/group that begins with dba.
The Oracle instance must have archiving enabled, otherwise (a) HVR will lose changes if it falls behind or it is suspended for a time, and (b) HVR will not capture changes made with Oracle insert statements with ‘append hints’.
Archiving can be enabled by running the following statement as sysdba against a mounted but unopened database:
Grants for Log-Based Capture
HVR does log-based capture if action Capture is defined.
1. The databaseUser (user that is used to replicate tables — Ex. hr schema) that HVR uses must be granted create session privilege.
2. To improve the performance of HVR Initialize for channels with large number of tables (more than 150), HVR creates a temporary table (hvr_sys_table) within a schema. For HVR to automatically create this temporary table.
The User should be granted create table privilege.
3. To replicate tables which are owned by other schemas (using action TableProperties/Schema) the User must be also granted select any table privilege:
4. The User must be granted select privilege for the following data dictionary objects:
5. To use action DbSequence the User must be granted following privileges:
Supplemental Logging
Background: HVR also needs Oracle’s “supplemental logging” feature enabled on replicate tables that it must replicate. Otherwise when an update is done Oracle will only log the columns which are changed. But HVR also needs other data (e.g. the key columns) so that it can generate a full update statement on the target database. Oracle supplemental logging can be set at database level and on specific tables.
The very first time that HVR Initialize runs it will check if the database allows any supplemental logging at all. If it is not then HVR Initializewill attempt statement alter database add supplemental log data.
- To execute alter database add supplemental log data the User must have sysdba privilege
- Also we need :
Log Read Method — Direct (Default)
By default, HVR captures changes using using the DIRECT log read method (Capture /LogReadMethod=DIRECT). In this method, HVR reads transaction log records directly from the DBMS log file using file I/O. This method is very fast in capturing changes from Oracle database. The DIRECT log read method requires that HVR agent is installed on the source database machine. Also we need to grant another privilege:
Native Access to Redo Files
HVR’s capture job needs permission to read Oracle’s redo and archive files at the Operating System level. On Linux the default group of user oracle can be seen in the 4th field of its line in /etc/passwd. The HVR user needs to be made a member of that group by adding its name to file /etc/group (e.g. line oinstall:x:101:oracle,hvr). Note that adding HVR’s user to group dba will also give HVR sysdba privilege.
Pluggable Databases
HVR supports capturing from Oracle’s pluggable database (PDB). To enable capturing from PDB, the connection should point to the database service of the PDB. You need to have the remotelistener running on the database server (either on primary or standby server if using dataguard). The connection method is always TNS to the PDB.
Location connection details required for pluggable databases:
- TNSis the connection to the database server, port, and database service (for example, dns:1521/HVR1210).
- User is the username to connect HVR to container database (where the PDB is located). The user should be an Oracle common user. The privileges required for User is same as the log-based capture grants.
- Password is the password of the User to connect HVR to container database.
Please visit the second part of the blog series:
Originally published at https://www.syntio.net, May 28, 2019.
