Use case: Step-by-step guide for replication of data to SAP-HANA. The scope of this Blog is limited to Replicat information. 

Oracle GoldenGate is a high-performance software application for real-time data movement from source to target. GoldenGate solutions are been used as an enterprise solution for both High Availability as well as for data integration. With the extension of Bigdata for GoldenGate, it provides the mechanism for real time integration to IN-MEMORY database like SAP HANA through JDBC handler.

Architecture:

Target prerequisties : 

  • Provision Oracle GoldenGate for BigData Marketplace image VM
  • Make sure the source Extract is pushing the trail files through Distribution Path to target GoldenGate for BigData VM
  • Install HANA Client Software with ngdbc.jar file on GoldenGate for BigData VM
  • Optinal HANA Studio can be installed to connect through UI
  • Port 30013, 30015 are used by HANA Database. Please make sure to open these ports on target for connectivity from OCI GoldenGate for BigData VM. 
  • DDL replication is not support by GoldenGate for HANA. Please make sure to create the tables manually on target. 

SAP HANA Client Install on GoldenGate for BigData VM :

  • Download the client installer for the respective platform (Microsoft Windows, Linux, or Mac) from the SAP Development Tools website under the HANA tab and the SAP HANA Client 2.0 section.
    https://tools.hana.ondemand.com/#hanatools
     
  • Stage the zip files into the OCI GoldenGatefor BigData VM


    [opc@compute-1 hana_client]$ ls -l
    total 196228
    -rw-rw-r–. 1 opc opc 200933415 Dec  8 13:36 HDB_CLIENT_LINUX_X86_64.zip

  • [opc@compute-1 hana_client]$ unzip HDB_CLIENT_LINUX_X86_64.zip
    Archive:  HDB_CLIENT_LINUX_X86_64.zip
      creating: HDB_CLIENT_LINUX_X86_64/client/
      inflating: HDB_CLIENT_LINUX_X86_64/client/CALCVIEWAPI.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/CALCVIEWAPI.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/CLIENTINST.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/CLIENTINST.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/COMMONCRYPTOLIB.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/COMMONCRYPTOLIB.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/DBCAPI.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/DBCAPI.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/DOTNETCORE.DEBUG.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/DOTNETCORE.DEBUG.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/DOTNETCORE.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/DOTNETCORE.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/ODBC.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/PYDBAPI.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/PYDBAPI.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/PYDBAPIML.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/PYDBAPIML.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/REPOTOOLS.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/REPOTOOLS.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/RUBY.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/RUBY.TGZ.lst
    extracting: HDB_CLIENT_LINUX_X86_64/client/SAPSYSMF.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/SAPSYSMF.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/client/SQLDBC.TGZ
      inflating: HDB_CLIENT_LINUX_X86_64/client/SQLDBC.TGZ.lst
      inflating: HDB_CLIENT_LINUX_X86_64/filelist.clientinst
      inflating: HDB_CLIENT_LINUX_X86_64/hdbclientreg
      inflating: HDB_CLIENT_LINUX_X86_64/hdbinst
      inflating: HDB_CLIENT_LINUX_X86_64/hdbsetup
      inflating: HDB_CLIENT_LINUX_X86_64/hdbuninst
      inflating: HDB_CLIENT_LINUX_X86_64/licenses/poco.txt
      inflating: HDB_CLIENT_LINUX_X86_64/README.html
      inflating: HDB_CLIENT_LINUX_X86_64/SIGNATURE.SMF

  • Install the HANA Client into the OCI Golden Gate for BigData VM in CLI mode

    [opc@compute-1 HDB_CLIENT_LINUX_X86_64]$./hdbinst
    SAP HANA Database Client installation kit detected.
    SAP HANA Lifecycle Management – Client Installation 2.9.28.1627673934
    *********************************************************************
    Enter Installation Path [/home/opc/sap/hdbclient]: /Data1/hana_client
    Checking installation…
    Preparing package ‘Product Manifest’…
    Preparing package ‘SQLDBC’…
    Preparing package ‘REPOTOOLS’…
    Preparing package ‘Python DB API’…
    Preparing package ‘Python Machine Learning Client’…
    Preparing package ‘ODBC’…
    Preparing package ‘R Machine Learning Client’…
    Preparing package ‘JDBC’…
    Preparing package ‘HALM Client’…
    Preparing package ‘DBCAPI’…
    Preparing package ‘node.js Client’…
    Preparing package ‘golang Client’…
    Preparing package ‘Ruby Client’…
    Preparing package ‘CommonCryptoLib’…
    Preparing package ‘Code Examples’…
    Preparing package ‘.NET Core’…
    Preparing package ‘Environment Script’…
    Preparing package ‘Client Installer’…
    Preparing package ‘Calculation View API’…
    Installing SAP HANA Database Client to /Data1/hana_client…
    Installing package ‘Product Manifest’…
    Installing package ‘SQLDBC’…
    Installing package ‘REPOTOOLS’…
    Installing package ‘Python DB API’…
    Installing package ‘Python Machine Learning Client’…
    Installing package ‘ODBC’…
    Installing package ‘R Machine Learning Client’…
    Installing package ‘JDBC’…
    Installing package ‘HALM Client’…
    Installing package ‘DBCAPI’…
    Installing package ‘node.js Client’…
    Installing package ‘golang Client’…
    Installing package ‘Ruby Client’…
    Installing package ‘CommonCryptoLib’…
    Installing package ‘Code Examples’…
    Installing package ‘.NET Core’…
    Installing package ‘Environment Script’…
    Installing package ‘Client Installer’…
    Installing package ‘Calculation View API’…
    Installation done
    Log file written to ‘/var/tmp/hdb_client_2021-12-23_06.21.48_30980/hdbinst_client.log’ on host ‘compute-1’.

  • Test the HANA Client connection from GoldenGate Marketplace VM to Database 
    Syntax : hdbsql  -n DBHostname:Port -i 00 -u username -p Password;

    [opc@ogg21bda hdbclient]$ ./hdbsql  -n Hostname/IP:30015
    Welcome to the SAP HANA Database interactive terminal.

    hdbsql=>
    Username: SYSTEM
    Password:*******

    hdbsql HGG=> select * from sys.m_databases;
    DATABASE_NAME,DESCRIPTION,ACTIVE_STATUS,ACTIVE_STATUS_DETAILS,OS_USER,OS_GROUP,RESTART_MODE,FALLBACK_SNAPSHOT_CREATE_TIME
    “HGG”,”HGG-00″,”YES”,””,””,””,”DEFAULT”,?
    1 row selected (overall time 5856 usec; server time 403 usec)
     
  • Optional Install of HANA Studio: SAP HANA Studio is an Eclipse based, integrated development environment (IDE) for development and administration of SAP HANA Database in the form of GUI tool. 

Create Replicat to Target HANA with JDBC Handler

Oracle GoldenGate for Big Data 21c is a comprehensive software package built on microservices architecture for real-time data integration and replication in database environments. Using the Java Database Connectivity (JDBC) Handler, Golden Gate can deliver/apply the data into SAP HANA in near real time mode. 

Login to Service Manager using oggadmin credentials of GoldenGate for Bigdata


 

From Service Manager, Login to Administration Server


 

 

Add Replicat – Select the Classic Replicat

Fill in the details for Process name, Source Trail Name, Trail Subdirectory, and the Target as JDBC

Sample Parameter file

REPLICAT REPHANA
GROUPTRANSOPS 1000
SOURCECATALOG “ebsdb”
MAP AP.AP_INVOICES_ALL, TARGET GGADMIN.AP_INVOICES_ALL;

Sample properties file – Cross check ngdbc.jar file path according to environment

# Properties file for Replicat REPHANA
gg.handlerlist=jdbc
gg.handler.jdbc.type=jdbc
#Hana Database Connection URL
gg.handler.jdbc.connectionURL=jdbc:sap://IPAddress:30015
#Hana Database JDBC Driver class
gg.handler.jdbc.driverClass=com.sap.db.jdbc.Driver
#Hana Database connection username
gg.handler.jdbc.userName=SchemaName
#Hana Database connection pasword
gg.handler.jdbc.password=Password
gg.handler.jdbc.mode=tx
### native library config ###
goldengate.userexit.nochkpt=TRUE
goldengate.userexit.timestamp=utc
goldengate.log.logname=cuserexit
goldengate.log.level=DEBUG
goldengate.log.tofile=true
goldengate.userexit.writers=javawriter
gg.report.time=30sec
#gg.classpath – Point the ngdbc.jar path
gg.classpath=/u01/app/ogg/ggjava/resources/lib/*:/u01/app/ogg/ggjava/ggjava.jar:/hana/hdbclient/ngdbc.jar
javawriter.stats.full=TRUE
javawriter.stats.display=TRUE
javawriter.bootoptions=-Xmx2048m -Xms2048m -Djava.class.path=/u01/app/ogg/ggjava/ggjava.jar -Dlog4j.configuration=debug-log4j.properties
 

Click on Create button to create the Replicat Process

Replicat process for REPHANA created. Click on action and start the Replicat

Validate Replication Data

Validate the data replicated from source to target. A simple PL/SQL procedure code has created to insert thousands of records in to the source database and validate the same number of records on target HANA. In this example, the approach of validation is just the ROWCOUNT. 

Source oracle Database ROWCOUNT of table AP_INVOICES_ALL

Target HANA DB ROWCOUNT of table AP_INVOICES_ALL

Both Source and Target database have same number of records. Insert 1000 records in to the source and verify the replication. 

Target row count on HANADB 

Similar information can also be captured from statistics tab of Administration Server

Happy Replicating with GoldenGate…!!!!!!!