SAP Data Intelligence - Realtime replication from Oracle database as source

[rajendra_chandrasekhar]

About:

If you are using SAP Data Intelligence to replicate data from an on-perm database ( Oracle) to a Cloud storage such as Azure Data Lake or AWS S3 then we have an operator available in SAP Data Intelligence 3.1 that provides a simple and easy way to achieve. This blog post is about my experience doing this on a customer landscape.

Overview:

Customer had multiple Database source on their On-Prem Data Centre and wanted to replicate all the on-prem OLTP Application data to their newly established Data Lake on Azure platform ( ADLS Gen 2) .

To achieve this customer decided to use SAP Data Intelligence 3.1 on cloud.

Pre-Rec:

1. Since Cloud Connector don't support connection to an on-prem databases from SAP Data Intelligence on cloud, we need to have VPN or VNet Peering to establish a direct connect from the SAP Data Intelligence on Cloud to Customer On-Prem. This is done by raising an OSS ticket to SAP Support and fill in a questioner. The questions will be different base on which option you choose  ( Vnet Peering or VPN) . I will not elaborate more on this topic as the focus here is about replicating data from On-Prem Oracle database to a Azure Data Lake.


2. Setup a Connection to On-prem database using Connection Type Oracle. We used an account that had the following permission which is documented in the Table replicator operator help.
   
   
   
   • CREATE TABLE
   
   
   • CREATE VIEW
   
   
   • CREATE SEQUENCE
   
   
   • CREATE PROCEDURE
   
   
   • CREATE TRIGGER
   
   
   
   


3. Since Table replicator don't support ADLS Gen 2 as a Target we need to use Semantic Data Lake (SDL) type in Connection Manager and create a Connection to ADLS Gen 2

The Graph:

The Graph was pretty simple and looks as below.

Note: All source and target connections are configured under Table Replicator Settings

 

Observation and Learnings:

We checked the oracle table access from SAP Data Intelligence via Metadata Explorer and we were able to view the content. Hence, we ran the graph but unfortunately it failed immediately. The logs were not helpful, it only suggested that there are some permission/privileges missing.

Oracle Database Administrator(DBA) was not ready to give a full admin access to the user account we were using to establish the connection in SAP Data Intelligence and this is true in a production environment. However, DBA looked into the logs on the oracle database each time we ran the graph to figure out the missing permission. This required multiple iteration as we were not able to figure out all the privileges in one single go. Finally we also look at the scripts that SAP Data Intelligence executes on the oracle database while setting up the Change Data Capture (CDC). The script can be found in SAP Data Intelligence filesystem under : /vrep/vflow/subengines/com/sap/dh/flowagent/operators/com/sap/dh/cdc/initialize/sqlFiles/V2

Based on our database log observation and going through the scripts we capture all the missing permission/privileges and DBA created the following Stored Procedure (SP) in oracle to automate the user creation with all the necessary permission/privileges on any other oracle database environment :

Even though this SP was created on oracle database this can be adapted to other databases as well. Note that I have not tested this again any other databases.

After creating the use with this SP we were able to run the graph successfully and test the real-time replication of data to Azure ADLS Gen 2

****************************************************************************************************************

CREATE USER <username>IDENTIFIED BY **** DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

GRANT CONNECT TO <username>;

GRANT RESOURCE TO <username>;

--following will be re-granted thru the role below

REVOKE SELECT ON <SCHEMA.TABLE> FROM SAPDIUSER;

--creating database role

CREATE ROLE sap_data_intelligence;

--Create a table in the given schema (in order to successfully create the logging table)

GRANT CREATE ANY TABLE TO sap_data_intelligence;

--Create sequence which can be used as a unique identifier in the logging table

GRANT CREATE ANY SEQUENCE TO sap_data_intelligence;

ALTER ANY SEQUENCE, SELECT ANY SEQUENCE TO <username>

--Select from the logging table

--cannot grant object-level permission for non-existing object. The possible workaround is a system-level privilege GRANT SELECT ANY TABLE which defeats "Select from the specific table" requirement

GRANT SELECT ANY TABLE TO sap_data_intelligence;

--Delete the logging table

--cannot grant object-level permission for non-existing object. The possible workaround is a system-level privilege GRANT DROP ANY TABLE

GRANT DROP ANY TABLE TO sap_data_intelligence;

--Create database triggers for the specific table

GRANT CREATE ANY TRIGGER TO sap_data_intelligence;

--Delete the database triggers

GRANT DROP ANY TRIGGER TO sap_data_intelligence;

--Create synonyms and views for the specific table

GRANT CREATE ANY SYNONYM TO sap_data_intelligence;

GRANT CREATE ANY VIEW TO sap_data_intelligence;

--Delete the synonyms and the views

GRANT DROP ANY SYNONYM TO sap_data_intelligence;

GRANT DROP ANY VIEW TO sap_data_intelligence;

GRANT INSERT ANY TABLE TO sap_data_intelligence;

GRANT UPDATE ANY TABLE TO sap_data_intelligence;

GRANT DELETE ANY TABLE TO sap_data_intelligence;

GRANT DROP ANY SEQUENCE TO sap_data_intelligence;

GRANT CREATE ANY PROCEDURE TO sap_data_intelligence;

GRANT DROP ANY PROCEDURE TO sap_data_intelligence;

GRANT CREATE ANY INDEX TO sap_data_intelligence;

GRANT DROP ANY INDEX TO sap_data_intelligence;

GRANT EXECUTE ANY PROCEDURE TO sap_data_intelligence;

--Additionally the user needs to read from the tables/views listed below:

--Check if table exists

GRANT SELECT ON all_tables TO sap_data_intelligence;

--Check table definition

GRANT SELECT ON all_tab_columns TO sap_data_intelligence;

GRANT SELECT ON all_constraints TO sap_data_intelligence;

GRANT SELECT ON all_cons_columns TO sap_data_intelligence;

GRANT SELECT ON all_indexes TO sap_data_intelligence;

GRANT SELECT ON all_ind_columns TO sap_data_intelligence;

GRANT SELECT ON V_$NLS_PARAMETERS TO sap_data_intelligence;

--Check open transactions

GRANT SELECT ON GV_$TRANSACTION TO sap_data_intelligence;

GRANT SELECT ON GV_$SESSION TO sap_data_intelligence;

GRANT SELECT ON GV_$LOCKED_OBJECT TO sap_data_intelligence;

GRANT SELECT ON all_objects TO sap_data_intelligence;

--finally: granting role to the user

GRANT sap_data_intelligence TO <username>;

****************************************************************************************************************

Summary:

Even though the replication task is very simple to setup, having database user with all the necessary permission/privileges was an uphill task. Also, the kind of permission/privileges this database user demands is quite powerful and hence should be managed and used by DBA only.

For more information about SAP Data Intelligence, please see:

Exchange Knowledge: SAP Community | Q&A | Blogs