Oracle GoldenGate 23ai (23.6.0.24.10) now supports replicating full-featured business objects in an event-based, pub/sub architecture with Oracle JSON Relational Duality and GoldenGate Data Streams. This is implemented by combining two new Oracle technology features: Oracle Database JSON Relational Duality Views and Oracle GoldenGate Data Streams.
Oracle GoldenGate 23ai (23.6) supports following replication use-cases / scenarios for JSON Relational Duality Views and JSON Collection Tables:
- JSON-Relational Duality Views and/or JSON Collection Tables to Data Streams: Oracle GoldenGate can replicate changes made to JSON Duality View as JSON objects to GoldenGate Data Streams.
- JSON Collection Tables to JSON Collection Tables: Oracle GoldenGate can replicate data between JSON Collection Tables.
- Duality Views to Collection Tables: Oracle GoldenGate can replicate data from Duality Views to Collection Tables, essentially moving data between these two JSON-focused features.
- Replication between Duality Views (Underlying Tables ) to Duality Views (Underlying Tables): Oracle GoldenGate allows replicating the underlying tables from Duality Views to Duality Views, without replicating the view. If supplemental logging is enabled for a JSON Duality Views, it generates extra redo on top of the existing relational table.
Note: Replication from one Duality View to another is not supported.
For the scope of this blog, we are going to cover the first scenario i.e. Replication from JSON-Relational Duality Views to Data Streams.
Configuration Steps:
- Prerequisites
- Create JSON Relational Duality Views.
- Enable Supplemental Logging for JSON Relational Duality Views
- Create an Extract and trail file in Oracle GoldenGate for Oracle deployment.
- Add a Data Stream from Oracle GoldenGate Distribution Service
- Configure Downstream application as Consumer for GoldenGate Data Stream
- Insert change data to the source document/tables for testing.
- Consume the Change Data from the Data Stream.
Prerequisistes:
- Install Oracle GoldenGate for Oracle 23ai (23.6).
- Deployment Configuration: To add a GoldenGate for Oracle 23ai deployment, execute Oracle GoldenGate Configuration Assistant (oggca) program to start the wizard in Interactive or silent mode.Follow the steps outlined here to add a deployment using Oracle GoldenGate Configuration Assistant wizard.
- Oracle Database 23ai (23.6) instance
For the scope of this blog ,we will be provisioning an Oracle Database 23.6 instance using OCI Base Database service.
Note: Support for Oracle GoldenGate to replicate JSON-relational duality view data as JSON documents, instead of relational tables, from an Oracle Database to a target Oracle or non-Oracle database was added within Oracle Database 23.6 release.
Create JSON Relational Duality Views
A JSON-relational duality view exposes data stored in relational database tables as JSON documents. The documents are materialized — generated on demand, not stored as such. Duality views give your data both a conceptual and an operational duality: it’s organized both relationally and hierarchically. You can base different duality views on data that’s stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data.
Attendee schedule data for an event is used here to illustrate the features of JSON-relational duality views. This example begins with an analysis of the necessary types of JSON documents and proceeds to define the corresponding entities and their relationships. Subsequently, it creates relational tables and duality views based on those tables.
For the attendee event schedule example, we suppose a document-centric application that uses three kinds of JSON documents: attendee, session, and speaker. Each of these kinds shares some data with another kind. For example:
- An attendee document includes, in its information about an attendee, identification of the sessions the attendee will participate in.
- A session document includes, in its information about a particular session, details about the session, the room where it will take place, and the speakers presenting in that session.
- A speaker document includes, in its information about a speaker, the sessions in which they are speaking.
Attendee event schedule example, Tables
Normalized entities are modeled as database tables. Entity relationships are modeled as joins between participating tables. Tables attendee, session, attendee_session, speaker, and speaker_session are used to implement the duality views that provide and support the attendee, session, and speaker JSON documents used by the attendee event schedule application.
Creating the Attendee event schedule base tables and JSON relational duality view
This example creates each table with a primary-key column, whose values are automatically generated as a sequence of integers, and a unique-key column, name. This implicitly also creates unique indexes on the primary-key columns. The example also creates foreign-key indexes.
CREATE TABLE jdvuser.ATTENDEE ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), COMPANY VARCHAR2(100) ); CREATE TABLE jdvuser.SESSIONSS ( SESSION_ID VARCHAR2(10) PRIMARY KEY, SESSION_NAME VARCHAR2(100), SESSION_TIME TIMESTAMP, ROOM VARCHAR2(50) ); CREATE TABLE jdvuser.ATTENDEE_SESSION ( ATTENDEE_ID NUMBER, SESSION_ID VARCHAR2(10), PRIMARY KEY (ATTENDEE_ID, SESSION_ID), FOREIGN KEY (ATTENDEE_ID) REFERENCES jdvuser.ATTENDEE(ID), FOREIGN KEY (SESSION_ID) REFERENCES jdvuser.SESSIONSS(SESSION_ID) ); CREATE TABLE jdvuser.SPEAKER ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100) ); CREATE TABLE jdvuser.SPEAKER_SESSION ( SPEAKER_ID NUMBER, SESSION_ID VARCHAR2(10), PRIMARY KEY (SPEAKER_ID, SESSION_ID), FOREIGN KEY (SPEAKER_ID) REFERENCES jdvuser.SPEAKER(ID), FOREIGN KEY (SESSION_ID) REFERENCES jdvuser.SESSIONSS(SESSION_ID) );
JSON relational duality view definition
CREATE or replace JSON RELATIONAL DUALITY VIEW
jdvuser.attendeeSchedule AS
SELECT JSON {
'_id': a.ID,
'name': a.NAME,
'company': a.COMPANY
,'schedule' :
json[ select json{
'ATTENDEE_ID' : ass.ATTENDEE_ID,
'attendee_sess_id' : ass.session_id,
unnest(
select JSON {
'code': s.SESSION_ID,
'session_name': s.SESSION_NAME,
'time': s.SESSION_TIME,
'room': s.ROOM,
'speakers' : json[
select json{'speaker_sess_id' : ss.SESSION_ID,
'speaker_session_speaker_id' : ss.SPEAKER_ID,
unnest(
select json{
'speaker_id' : sp.id,
'speaker_name' : sp.name}
from jdvuser.speaker sp with insert update nocheck
where sp.id = ss.speaker_id)}
from jdvuser.speaker_session ss with insert update nocheck
where ss.session_id = s.session_id]}
from jdvuser.sessionss s with insert update nocheck
where s.SESSION_ID = ass.SESSION_ID)}
from jdvuser.ATTENDEE_SESSION ass with insert update nocheck
where ass.ATTENDEE_ID = a.id ]}
FROM jdvuser.ATTENDEE a with insert update delete nocheck;
Enable Supplemental Logging for JSON Relational Duality Views
- Refer to Preparing the database for Oracle GoldenGate for creating a database user with appropriate privileges and setting Oracle database configuration.
- Add Database Connections:
- Launch the Administration Service UI and log in.
- Click DB Connections from the left navigation pane. Click the plus sign (+) sign next to DB Connections.
- Specify an alias for your database credential, such as jrdv.Use the EZconnect syntax to configure the database connection in the User ID field: username@hostname:port/service_name
- Specify the Password used by database user to log in to the database. Click Submit.
- Click the Connect database icon to verify that the connection is working correctly. Other sections to set up checkpoint and heartbeat tables shows up after the connection is successful.
- After successfully connecting to the database, you can add TRANDATA, SCHEMATRANDATA, checkpoint, and heartbeat tables required by Extract.
- To enable supplemental logging for JSON relational duality view, connect to the database from the DB Connections page, select the Trandata menu, then perform the following steps:
Select the Table option as required and click plus sign to add.
Enter the name of the JSON relational duality view for which you need to set up supplemental logging. Click Submit. - You can skip ADD TRANDATA in case of initial load without CDC.
- After you add the trandata, you can search for the JSON relational duality view for which you’ve add the trandata, using the search icon. This will display the trandata information. The following image shows the trandata information for the attendeeschedule in the pluggable database PDB.
Create and Configure the GoldenGate for Oracle Extract
- In this section, you will add an Extract process (EXTCDC). The Extract process captures data from the source database and writes it to a trail file (zz).
- On the the Administration Service Home page, click the plus sign (+) under the Extract section.
- On the Extract Information screen, select Integrated Extract.
- Provide Process Name: Name of the Extract process. The name of the Extract process can be up to 8 characters.
- Description: Description of the Extract process being created. Click Next.
- On the Extract Options screen configure the following settings:
Source Credentials: Specify a domain for the database.
Alias: Specify the user ID alias used as the database connection for the source login or select from the displayed options. - Provide Extract Trail Name as zz and click Next.
- On the Managed Options screen, Select Default profile or configure the auto start and auto restart options for the Extract process.
- Click Next. On the Parameter File screen, you can edit the parameter file in the text area to add the JSON relational duality view name that you are interested in capturing.
- Click Create and Run to create and start the Extract. If you select Create, the Extract is created but you need to start it using the Extract options.
- To check the status of the Extract, select Extracts from the Administration Service left navigation pane.
Configure the GoldenGate Data Streams from Oracle GoldenGate Distribution Service
Data streams are created from the Distribution Service. Log in to the Distribution Service to begin creating a Data Stream process.
- From the Distribution Service home page, click the plus (+) sign next to Data Streams to start creating a Data Stream.
- On the Data Stream Information page, enter a Data Stream process name in the Name box and add a description for it. Click Next.
- On the Source Options page, enter the trail name as zz.
- (Optional)On the Filtering Options page, there are various options available to include and exclude filtering rules for Example Include only DML Object Type
- Click Create Data Stream.
- Click on created Data Stream to view the YAML document Data Streaming AsyncAPI definitions for the stream.
Configure Downstream application as Consumer for GoldenGate Data Stream
Oracle GoldenGate Data Streams is programming language agnostic so that it can interact with a client written in any programming language. Even though the client programs typically are simple and small, users still need to manually implement the client code to interact with the data streaming service.
Adopting the AsyncAPI specification into Oracle GoldenGate Data Streams has the following advantages:
- Ability to describe the data streams service API in industry-standard API specification and automatically generate API documentation.
- Automatically generate client-side code with @asyncapi/generator.
When a data streams resource is created, a URL link to a customized Async API specification document describing how to access this data stream endpoint, is returned in the HTTP response. This YAML document can then be used to generate the client-side code using @asyncapi/generator.
Note that to support the websocket protocol in @asyncapi/generator, you also need to implement/maintain the websocket client template for the @asyncapi/generator in GitHub.
Refer to the GitHub repository for more information about the websocket-client-template:
https://github.com/oracle-samples/websocket-client-template
For the scope of this blog, we can use a simple nodeJS application to consume data from GoldenGate Data Stream channel. Follow the step-by-step details provided here to configure NodeJS application and consume data from Oracle GoldenGate Data streams.
Perform DML on source for testing the replication
You can either insert data into the base tables (ATTENDEE, SESSIONSS, SPEAKER, ATTENDEE_SESSION and SPEAKER_SESSION) or directly into the JSON Relational duality view ATTENDEESCHEDULE.
INSERT INTO JRDV.SPEAKER (ID, NAME) VALUES (102, ‘Rahul’);
INSERT INTO JRDV.ATTENDEE (ID, NAME, COMPANY) VALUES (1, ‘Will’, ‘ACME Inc’);
INSERT INTO JRDV.ATTENDEE (ID, NAME, COMPANY) VALUES (2, ‘Raymond’, ‘WRIME AI’);
INSERT INTO JRDV.SESSIONSS (SESSION_ID, SESSION_NAME, SESSION_TIME, ROOM) VALUES (‘S001’, ‘Introduction to JSON’, TO_TIMESTAMP(‘2024-09-15 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘Room 101’);
INSERT INTO JRDV.SESSIONSS (SESSION_ID, SESSION_NAME, SESSION_TIME, ROOM) VALUES (‘S002’, ‘Advanced SQL Techniques’, TO_TIMESTAMP(‘2024-09-15 11:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘Room 102’);
INSERT INTO JRDV.SPEAKER_SESSION (SPEAKER_ID, SESSION_ID) VALUES (101, ‘S001’);
INSERT INTO JRDV.SPEAKER_SESSION (SPEAKER_ID, SESSION_ID) VALUES (102, ‘S002’);
INSERT INTO JRDV.ATTENDEE_SESSION (ATTENDEE_ID, SESSION_ID) VALUES (1, ‘S001’);
INSERT INTO JRDV.ATTENDEE_SESSION (ATTENDEE_ID, SESSION_ID) VALUES (2, ‘S002’);
commit;
Insert into jrdv.ATTENDEESCHEDULE (DATA) values (‘{“_id”:5,”name”:”Shawn”,”company”:”WRIME AI”,”schedule”:[{“ATTENDEE_ID”:5,”attendee_sess_id”:”S005″,”code”:”S005″,”session_name”:”Machine Learning in Databases”,”time”:”2024-09-15T11:00:00″,”room”:”Room 105″,”speakers”:[{“speaker_sess_id”:”S005″,”speaker_session_speaker_id”:105,”speaker_id”:105,”speaker_name”:”Cetin”}]}],”_metadata”:{“etag”:”953321179FA5E16D26BB009D199EE3E1″,”asof”:”00000000007B54D9″}}’);
Insert into jrdv.ATTENDEESCHEDULE (DATA) values (‘{“_id”:6,”name”:”Don”,”company”:”RYTHM CORP”,”schedule”:[{“ATTENDEE_ID”:6,”attendee_sess_id”:”S006″,”code”:”S006″,”session_name”:”Spatial graph Programming”,”time”:”2024-09-16T09:00:00″,”room”:”Room 106″,”speakers”:[{“speaker_sess_id”:”S006″,”speaker_session_speaker_id”:106,”speaker_id”:106,”speaker_name”:”Ronald”}]}],”_metadata”:{“etag”:”B22F4BC1BC2FF28533180C0C1BFAA18F”,”asof”:”00000000007B54D9″}}’);
commit;
-
Validate that the DML’s have been captured by the Extract process. Under the Extract in Administration Service, click on Statistics tab to view the count of the captured DML.
Consume or Preview the Change Data from the Data Stream
Finally run the downstream consumer application in order to retrieve and verify the data records published by GoldenGate Data stream channel.
You should be able to see both DDL and DML within the payload for JSON Relational duality view created on the source Oracle database.
In conclusion, Replicating business objects with Oracle JSON Relational Duality and GoldenGate Data Streams prioritizes data products and becomes the single source of truth for applications, transaction fabrics, data/event meshes, and data fabrics by ensuring the uniqueness of data objects.