Quick Start for HVR into HANA
This appendix shows how to set up an HVR channel (called hvr_demo01) to replicate between a local Oracle database and a SAP HANA database. To connect efficiently to the SAP HANA server and to use HVR Bulk Refresh and Integrate with Burst, an HVR agent installation on the SAP HANA server is used.
The steps actually start by creating new databases and tables for HVR to replicate between. In real life these databases would already exist and be filled with the user tables, but for simplicity everything is created from scratch. Also, for simplicity, we will assume the source database resides on the hub as well and Oracle has already been installed there.
Before proceeding with this example ensure that the requirements for using HVR with HANA are met. For information about access privileges and advanced configuration changes required for performing replication in HANA, see Requirements for HANA.
- 1 Create Test Databases and Tables
- 2 Install HVR
- 3 Create the Hub Database
- 4 Connect To Hub Database
- 5 Create Oracle and HANA Locations
- 6 Create Location Groups
- 7 Define Actions
- 8 Perform Initial Loading and Table Creation
- 9 Enable Replication with HVR Initialize
- 10 Start Scheduling of Replication Jobs
- 11 Test Replication
- 12 HVR Compare and Refresh
Create Test Databases and Tables
Generally when getting started with HVR a source schema with tables and data already exists. If so then this step can be skipped.
This Quickstart uses two empty tables named dm01_product and dm01_order. In an existing Oracle database, create a test schema and create the tables using the following commands.
Make sure the database is setup for log-based capture as described in Grants for Log-Based Capture Database (Oracle).
$ sqlplus system/manager SQL> create user testdb1 identified by hvr 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; SQL> grant create session to testdb1; SQL> grant create table to testdb1; SQL> grant create sequence to testdb1; SQL> grant create procedure to testdb1; SQL> grant create trigger to testdb1; SQL> grant create view to testdb1; SQL> grant execute any procedure to testdb1;
Create the test tables.
$ cd $HVR_HOME/demo/hvr_demo01/base/oracle $ sqlplus testdb1/hvr < hvr_demo01.cre $ sqlplus testdb1/hvr < hvr_demo01.mod
The target tables in SAP HANA do not have to be created manually, since HVR will create these tables automatically in the step Perform Initial Loading and Table Creation.
First read section Introduction which explains the HVR's terminology and architecture. In particular this explains the importance of a hub database.
Then install the HVR software on the hub machine by following the installation steps in section Installing HVR on Windows or Installing HVR on Unix or Linux. If the hub machine is a Unix machine then HVR can either be installed on a Windows PC (so the HVR GUI can run on the PC and connect to the Unix hub machine) or the HVR GUI can be run on the Unix hub machine and connect back to an X server running on a PC.
This Quickstart assumes the Oracle Database on the hub server is also the source database. Most real-time integration scenarios use log-based capture. To enable log-base capture, configure the following:
- The user name that HVR uses must be in Oracle's group. On Unix and Linux this can be done by adding the user name used by HVR to the line in /etc/group that begins with dba. On Windows right–click My Computer and select Manage ▶ Local Users and Groups ▶ Groups ▶ ora_dba ▶ Add to Group ▶ Add.
- The Oracle instance should have archiving enabled. Archiving can be enabled by running the following statement as sysdba against a mounted but unopened database: alter database archivelog. The current state of archiving can be checked with query select log_mode from v$database.
The current archive destination can be checked with query select destination, status from v$archive_dest. By default, this will return values USE_DB_RECOVERY_FILE_DEST, VALID, which is inside the flashback recovery area. Alternatively, an archive destination can be defined with the following statement: alter system set log_archive_dest_1='location=/disk1/arc' and then restart the instance.
Additionally HVR needs to be installed on the SAP HANA server machine. This HVR agent will establish the connection to the SAP HANA database and the hub can connect to it via a HVR remote listener.
Create the Hub Database
Create the hub database, in which the HVR GUI will store the channel definition. This is actually another user/schema in the Oracle instance.
$ sqlplus system/manager SQL> create user hvrhub identified by hvr 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; SQL> grant create session to hvrhub; SQL> grant create table to hvrhub; SQL> grant create sequence to hvrhub; SQL> grant create procedure to hvrhub; SQL> grant create trigger to hvrhub; SQL> grant create view to hvrhub; SQL> grant execute any procedure to hvrhub;
Connect To Hub Database
Start the HVR GUI on a PC by clicking on the HVR GUI icon (this is created by the HVR Installer for Windows) or by running hvrgui on Linux.
First, Register the hub database: right–click on hub machines ▶ Register hub. Enter connection details.
In this example the hub is a machine called guam, where an INET daemon is listening on port 4343. See section Installing HVR on Unix or Linux for how to configure this.
For a new hub database a dialog will prompt Do you wish to create the catalogs?; answer Yes.
Create Oracle and HANA Locations
Next create the source location using right–click on Location Configuration ▶ New Location.
For the source, enter the credentials of the Oracle database testdb1 created earlier. The corresponding HVR location will be called db1:
In this example there is no need to check Connect to HVR on remote machine because testdb1 is on the same machine as the hub.
Ignore the Group Membership tab for now.
Create another location for the SAP HANA database testdb2 too and call it db2. Now do tick Connect to HVR on remote machine, because HVR needs to connect to the HVR remote listener agent installed before. Fill in the details of the VM running the remote listener there (in our example the HVR remote listener runs on the same machine as the SAP HANA server). Use operating system user hvr with any password. Choose HANA as database and fill in the HANA Connection info.
Note that the HVR agent on the SAP HANA server needs to run a HVR remote listener on port 4343 for this connection to work.
Create Location Groups
Add location db1 as a member of this group by checking the box for db1.
Then create a second location group, called DECENTRAL that has member db2.
The new channel also needs a list of tables to replicate. This can be done as follows; right–click on Tables ▶ Table Explore.
- Choose the first location ▶ Connect.
- In the Table Explore window, click on both tables and click Add.
- In new dialog HVR Table Name click OK.
- Close the Table Explore window.
- Perform table select again on one of the other locations and confirm that all tables to be replicated have value Same in column Match.
The new channel needs two actions to indicate the direction of replication and a third action for the S3 copy location needed for the "Copy from" feature:
- Right–click on group CENTRAL ▶ New Action ▶ Capture.
- Right–click on Group DECENTRAL ▶ New Action ▶ Integrate.
- Right–click on Group DECENTRAL ▶ New Action ▶ LocationProperties. Check both /StagingDirectoryHvr and /StagingDirectoryDb with argument /hvrhub.
Note that the Actions pane only displays actions related to the objects selected in the left–hand pane. So click on channel hvr_demo01 to see all three actions.
Perform Initial Loading and Table Creation
HVR Refresh copies the data from one location to another location and optionally creates missing or mismatched tables and keys. In the HVR GUI, right-click on the channel and select HVR Refresh
For the source select location db1 and for target select location check db2. Check the options Create Absent Tables, With Key, Recreate Mismatched Tables and click Refresh.
Enable Replication with HVR Initialize
Now that the channel definition is complete, create the runtime replication system.
Right–click on channel hvr_demo01 ▶ HVR Initialize. Choose Create or Replace Objects and click HVR Initialize.
From the moment that HVR Initialize is done, all changes to database testdb1 will be captured by HVR when its capture job looks inside the logging.
HVR initialize also creates three replication jobs, which can be seen under the Scheduler node in the GUI.
Start Scheduling of Replication Jobs
Next, instruct the HVR Scheduler to trigger the replication jobs.
The replication jobs inside the Scheduler each execute a script under $HVR_CONFIG/job/hvrhub/hvr_demo01 that has the same name as the job. So job hvr_demo01–cap–db1 detects changes on database testdb1 and stores these as transactions files on the hub machine.
The other job (hvr_demo01–integ–db2) picka up these transaction files and perform inserts, updates and deletes on the target database.
To test replication, make a change in testdb1:
$ sqlplus testdb1/hvr SQL> insert into dm01_product values (1, 19.99, 'DVD'); SQL> commit;
Here is sample job output:
C:\> notepad %HVR_CONFIG%\log\hvrhub\hvr.out hvr_demo01–cap–db1: Capture cycle 1. hvr_demo01–cap–db1: Selected 1 row from 'dm01_product__c0' (201 wide). hvr_demo01–cap–db1: Routed 212 bytes (compression=42.7%) from 'db1' into \ 1 location. hvr_demo01–cap–db1: Finished. (elapsed=1.20s) hvr_demo01–integ–db2: Integrate cycle 1 for 1 transaction file (212 bytes). hvr_demo01–integ–db2: Integrated 1 change from 'dm01_product' (1 ins). hvr_demo01–integ–db2: Integrate used 1 transaction and took 0.017 seconds. hvr_demo01–integ–db2: Finished. (elapsed=0.14s)
This log indicates that the jobs replicated the original change to testdb2. Run a query on testdb2 to confirm:
$ sqlplus testdb2/hvr SQL> select * from dm01_product;
HVR Compare and Refresh
HVR Compare checks whether two locations have identical rows, and HVR Refresh copies the content of one location to the second location. In the HVR GUI, right–click on a channel ▶ HVR Compare (or HVR Refresh). Choose two locations by clicking on the Select buttons.