Migrating Oracle GoldenGate processes with APEX, say what now?!!

April 21, 2022 | 9 minute read
Sydney Nurse
EMEA AppDev & Security Expert
Text Size 100%:

Let's migrate an Oracle GoldenGate process using an APEX developed application

In my original blog post 2 years back, I tried to See How Easily You Can Use Oracle GoldenGate Rest API and in recent months Eloi Lopes demonstrated how to Automate OCI GoldenGate with REST APIs, Jenkins and Ansible and Julien TESTUT with Using REST APIs with Oracle Cloud Infrastructure (OCI) GoldenGate

Many of the customers I worked with were seeking options to adopt the new releases of Oracle GoldenGate and asked me for the tools to help assist moving to the Microservices Architecture or how to manage the software life-cycle of Oracle GoldenGate between different environments, say promotion from Development to Test or User Acceptance Testing. Leveraging the Resfult APIs is a first step to bring Oracle GoldenGate into the world of DevOps and once the configuration is centrally managed, the potential to do more would be right at our finger tips.

Developing such a solution, the published posts are all great reads and I thought, if I'm the end user, in DevOps, or perhaps a system operator, I would need an app that hides the technology, providing a simple, yet powerful User Experience. Can this be achieved with LowCode?

 

The answer is of course yes, and with Restful APIs as the basis for intergration, many things are possible but before we begin, if you are new to either Oracle APEX or the Oracle GoldenGate (OGG) Microservices Architecture (MSA) Rest APIs, then let me suggest some starter links:

  1. Oracle APEX (ApplicationExpress)
  2. OGG MSA version 21c Rest API docs

Now this is a Data Integration blog, so I won't be diving too deep into my proof of concept implementation. I will describe at a high level the components I used to establish a connection, consuming the APIs and highlight in brief some of the challenges I faced and with, BIG assumption, that before you go down this path, you've read tonnes of documentation and are ready to design and build your own version.

OGG MSA Rest APIs: for Oracle vs. for Non-Oracle including Big Data

In August 2021, Mack Bell published the Oracle GoldenGate 21c Release Announcement and the Microservices Architecture support for DB2 for z/OS, MySQL, PostgreSQL, and SQL Server. The Big Data MSA deployment option followed a few short months later.

The standard OGG MSA is a giant leap forward with the deep integration between Oracle GoldenGate and the Oracle Database, the same is reflected in the availability of APIs and its responses. 

During the build I found some differences, for example the LIST replicats for Oracle deployments returns the credential array but no so for all of the other Non-Oracle types. Be weary of these differences and validate all of the API responses between the different deployment choices. 

Rest Response: Oracle GoldenGate for Postgres

Rest Response: Oracle GoldenGate for Oracle         

Another hint I can provide is to utilize the Network tab on browser's developer web console as you navigate the OGG MSA console. This can provide valuable insight into the use of the various APIs and structure of the request body used in POST, PATCH calls.  As an example I used this method to get the correct format to the Administrative Server Execute Command API.

Execute Command Request Body
Show OS & DB Version  {
    "name":"report",
    "credentials": {"alias" : "gguser", "domain":"MSSQLServer"},
    "reportType":"versions"
}
List DB Schemas {
    "name":"report",
    "credentials": {"alias" : "ggadmin", "domain":"OracleGoldenGate"},
    "reportType":"schemas",
    "specification":"OGGOOW191.*"
}
List DB Schema Tables {
    "name":"report",
    "credentials": {"alias" : "gguser", "domain":"MSSQLServer"},
    "reportType":"tables",
    "specification":"SNURSE_SRC.*"
}

Consuming Restful APIs in APEX

APEX is a very mature low code development environment and added the ability to consume restful services long before I got introduced to developing productivity apps in APEX. It has robust support for authentication mechanisms and provides both a Web Interface to add Rest Data Sources as well as the ability to invoke them via PL/SQL APIs. So much for LowCode :) but I could use it for the User Interface at least.

In my use case I stored  my OGG MSA user information as Web Credentials and the PL/SQL procedures to both consume the APIs and management for the collected deployment details.

The APEX_WEB_SERVICE.MAKE_REST_REQUEST function is simple to use and for OGG MSA only a small subset of the parameters are required.

Request without Body Request with Body
l_response := apex_web_service.make_rest_request(
                p_url => l_vc_url,
                p_http_method => l_vc_method,
                p_credential_static_id => l_vc_credential
            );
l_response := apex_web_service.make_rest_request(
                p_url => l_vc_url,
                p_http_method => l_vc_method,
                p_credential_static_id => l_vc_credential,
                p_body => to_clob(l_vc_request)
            );

 

The credential (p_credential_static_id) is a reference to the Web Credential stored in the application's shared components and all other parameters set according to the OGG MSA Rest API. This function is fairly generic and can be used by all of the calls regardless of the HTTP method (GET, PUT, POST, PATCH, DELTE, etc.) and the json response is stored and returned to my calling function where it was parsed with speific handling SQL. For example to get the build details for the deployment I use the following:

select title, dbms,label, platform, version into l_vc_build_title, l_vc_build_dbms, l_vc_build_label, l_vc_build_platform, l_vc_build_version 
                        from json_table
                        (
                            l_build_response, '$.response[*]'
                            NULL ON EMPTY
                            columns
                                (
                                    title varchar2(400) path '$.title',
                                    dbms varchar2(400) path '$.build.dbms',
                                    label varchar2(400) path '$.build.label',
                                    platform varchar2(400) path '$.build.platform',
                                    version varchar2(400) path '$.build.version'
                                )
                        ) jt;

 

The Oracle database has built-in json support and in the example I query the reponse directly as a json table object with the path given to each of the column items. You can an Introduction to JSON Data and the Oracle Database 19c here. I use the JSON_TABLE and the SQL MERGE comand exclusively to parse and manage the rest responses.

Building a Better Mouse Trap

Well, not a trap but identifying the best way to represent the information and simplify any task that needs to be accomplised was quite the challenged. I finally settled on the JavaScript TreeView, provided by APEX. The Treeview module gave me access to the user's actions as events and a hook back to database procedures via as AJAX Callback and the German APEX Community has a tip article on AJAX Progamming.

**WARNING** JavaScript is used to control and handle the browser events and transistions to the AJAX callback code. If you are not familiar with JavaScript, collaborate with someone who is as I found it a steep learning curve from the highy structued world of SQL to the what seemed like the Wild Wild West. Once I got some basics under my belt I found my way to a solution that worked but I had assistance along the way from the APEX JavaScript experts to get there.

// AJAX Callback

apex.server.process(
                'Copy Nodes',
                {
                x01: source_process_id,
                x02: target_deployment_id
                },
                {
                success: function(pData){
                        apex.region("targetDeployTree").refresh();
                        let sProcLink = pData;
                        console.log(sProcLink); // let's see what we've got returned
                        apex.navigation.dialog(sProcLink
                            ,{title:'Update Process Details',height:'auto',width:'720',maxWidth:'960',modal:true,dialog:null}
                            ,'t-Dialog-page--standard '+'',apex.jQuery('#targetDeployTree'));
                        
                },
                dataType: 'text'
                }

Why the Gateway

Well, let's start with my architecture.

APEX to OGG MSA OCI cloud deployment

I have my source and target databases and OGG MSA deployment installed on compute nodes in the Oracle Cloud Infrastructure (OCI), while leveraging APEX on Autonomous Database (ADB) instance in another tenant. 

To simplify the deployments and reduce the effort in acquiring a trusted and certified set of certificates, I used self-signed certificates to implement OGG MSA. For user managed database instances hosting APEX, this is not an issue but Oracle has a fairly strict set of security checks for ADB including the validation of certificates on secure (https) connections. Self-Signed Certificates will also be the default for any OGG marketplace image on OCI.

The OCI API Gateway provides the capability to manage API endpoints and terminate SSL connections while still providing external Public secure communications with a valid trusted Root CA certificate chain. It is fast and easy to configure and met my requirements completely, allowing me to consume the set of services without needing to revert back to non-secure connections for API calls.

In a "Real-World" deployment this might be considered good practice, providing protected gatway access to Rest resources externally and private network communications between resources only, never exposing the backend resources and services. 

What About the App, the Code ...

I under took this as an excerise and proof of concept and with most proof of concepts, it is an MVP (minimal value product) in the most complete sense of the term. Perhaps with additional member support, a code re-write and cleanup I'll release it but if you have questions feel free to to comment and let me know what you think.

 

Regards

Sydney

 

Sydney Nurse

EMEA AppDev & Security Expert

One thing Golf teaches you, is attention to detail, practice to execution and trust.

These same principals apply to expertise in an given area, and even more so in Software Systems.

 


Previous Post

Using REST APIs with Oracle Cloud Infrastructure (OCI) GoldenGate

Julien TESTUT | 10 min read

Next Post


Configuring OCI Events and Notification Services for OCI Data Integration (OCI-DI)

GuruDixit Chepuri | 6 min read