OCI GoldenGate Cloud Service data streaming to Azure Data Lake Storage

This note describes the procedure used to ingest data into an Azure Blob Storage Container using Oracle GoldenGate Cloud Service from a source Oracle Cloud Autonomous Database.

One of the new features introduced late 2022 for OCI GoldenGate Cloud Service was to add Azure Data Lake Storage as a GoldenGate Replicat target.

For illustrating this example we have created an Azure Storage Container and two separate OCI GoldenGate Cloud Service deployments in the same OCI tenancy.

• ADLS: Oracle GoldenGate Services 21.7.0.0.2 for Big Data
• OGGADW: Oracle GoldenGate Services 21.8.0.0.5 for Oracle

Configure the source Autonomous Database

Unlock the existing ggadmin database user account, enable supplemental logging and create the DEMO schema and sample table

C:\oracle\product\21.3.0\dbhome_1\bin>set TNS_ADMIN=C:\app\oracle\Wallet_ADW19C

C:\oracle\product\21.3.0\dbhome_1\bin>sqlplus admin/mypassword@adw19c_high

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Feb 1 10:06:39 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.1.0

SQL> alter user ggadmin identified by ******** account unlock;

User altered.

SQL> alter database add supplemental log data;

Database altered.

SQL>  select minimal from dba_supplemental_logging;

MIN
---
YES


SQL> create user demo identified by ********** ;

User created.

SQL> grant dwrole to demo;

Grant succeeded.

SQL> alter user demo quota unlimited on data;

User altered.

SQL> create table demo.myobjects (object_id number ,owner varchar2(100), object_name varchar2(100), object_type varchar2(20));

Table created.

SQL> alter table demo.myobjects add constraint pk_myobjects primary key (object_id);

Table altered.

Create Connection to Autonomous Database

Create a connection to the Oracle Autonomous Database and assign the connection to the GoldenGate for Oracle deployment.

Provide the password for the Autonomous database user ggadmin.

Create Connection to Azure Data Lake Storage

Create a connection to the Azure Data Lake Storage and assign the connection to the GoldenGate for Big Data deployment.

Provide the Azure Storage Container account name and storage access key.

Note that a credential called ADLS has been automatically created in the OGGADLS deployment.

Create a user oggadls with the operator role in the Big Data deployment

Create a credential oggadls in the GoldenGate for Oracle deployment

Provide the username and the password for the oggadls user which we just created in the GoldenGate Big Data deployment. Note the credential domain name OGGADLS.

Add supplemental logging for the DEMO schema

Create the integrated extract EXT2

Create a Distribution Path to copy the trail files to the GoldenGate for Big Data deployment

Provide the host name of the GoldenGate for Big Data deployment and the port number 443.

Provide the domain name (OGGADLS) and the credential (oggadls).

Create a Replicat in the GoldenGate for Big Data deployment

The target for the replicat is Azure Data Lake Storage.

Configure the properties file for replicat REP1

# Properties file for Replicat REP1
gg.target=abs
# ABS Event Handler Template
gg.eventhandler.abs.connectionId=ocid1.goldengateconnection.oc1.ap-sydney-1.amaaaaaaeetb5tqaltkorxop3ohm7hza357dbn7wrslrrqardspcvgrnce5q
#TODO: Edit the Azure Data Lake container/bucket name
gg.eventhandler.abs.bucketMappingTemplate=oggnew
gg.classpath=$THIRD_PARTY_DIR/abs/*
gg.handler.abs.format=avro_row_ocf

gg.eventhandler.abs.fileNameMappingTemplate=${groupName}/${fullyQualifiedTableName}/${currentTimestamp[yyyy]}/${currentTimestamp[MM]}/${currentTimestamp[dd]}/${groupName}_${fullyQualifiedTableName}_${currentTimestamp[yyyyMMdd_hhmmss_SSS]}.avro

gg.handler.abs.format.metaColumnsTemplate=${csn[x_gg_csn]},${opseqno[x_gg_opseqno]},${optype[x_gg_optype]},${timestamp[x_gg_txn_timestamp]},${position[x_gg_pos]}

gg.handler.abs.format.pkUpdateHandling=update

Start the replicat process

Execute INSERT transactions on the source Autonomous Database

SQL> insert into demo.myobjects values (1001, 'DEMO','EMP','TABLE');

1 row created.

SQL> insert into demo.myobjects values (1002, 'DEMO','DEPT','TABLE');

1 row created.

SQL> commit;

Commit complete.

The INSERT statements have been captured by the Extract

The replicat process running on the Big Data for GoldenGate deployment has processed the INSERT statements

Note the contents of the Azure Container oggnew

Using a tool like Altova XMLSpy, we can view the contents of the downloaded avro files