Data Replication in a Data Consolidation Topology Using REST API

REST API Admin Client

Copy and use the following cURL script to set up Oracle GoldenGate data replication environment on a pre-installed Oracle Database 26ai Free and Oracle GoldenGate deployment.

Note: The given sample script uses names and values of database server, parameter values, Extract, Replicat, and other processes. You must change these values according to your environment for this script to work.

#!/bin/bash

# ----------------------------------------------------------------------------------------------------
# --
# -- Create USERIDALIAS to connection from GoldenGate to the Databases
# --
# ----------------------------------------------------------------------------------------------------


# Add UserIdAlias GGSOUTH to connect to the Database instance DBSOUTH
curl -s -k -X POST https://south:9101/services/v2/credentials/OracleGoldenGate/ggsouth                    \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"userid":"ggadmin@dbsouth","password":"ggadmin"}' | jq '.messages'

curl -s -k -X GET https://south:9101/services/v2/credentials/OracleGoldenGate/ggsouth                     \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='   | jq '.response'

# ----------------------------------------------------------------------------------------------------
# --
# -- Add Checkpointtables
# -- Add Heartbeattable
# --
# ----------------------------------------------------------------------------------------------------

# Add Checkpointtable on target database GGSOUTH
curl -s -k -X POST https://south:9101/services/v2/connections/OracleGoldenGate.ggsouth/tables/checkpoint  \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"operation":"add","name":"ggadmin.ggs_checkpointtable"}' | jq '.messages'

curl -s -k -X POST https://south:9101/services/v2/connections/OracleGoldenGate.ggsouth/tables/checkpoint  \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"operation":"info","name":"ggadmin.ggs_checkpointtable"}' | jq '.messages'

# Add Heartbeattable on target database GGSOUTH
curl -s -k -X POST https://south:9101/services/v2/connections/OracleGoldenGate.ggsouth/tables/heartbeat   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"frequency":60}' | jq '.messages'

curl -s -k -X GET https://south:9101/services/v2/connections/OracleGoldenGate.ggsouth/tables/heartbeat    \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='   | jq '.response'

# ----------------------------------------------------------------------------------------------------
# --
# -- Add Replicat at target Database GGSOUTH
# --
# ----------------------------------------------------------------------------------------------------

# 1st Replicat: REPNA

curl -s -k -X POST https://south:9101/services/v2/replicats/REPNA                                          \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"description":"Replicat - Region South (Consuming from A)"
	     ,"config":["REPLICAT repna"
                       ,"USERIDALIAS ggsouth DOMAIN OracleGoldenGate"
                       ,"DDL INCLUDE MAPPED"
                       ,"DDLOPTIONS REPORT"
                       ,"DDLERROR DEFAULT, DISCARD"
                       ,"REPORTCOUNT EVERY 10 MINUTES, RATE"
                       ,"REPERROR (DEFAULT, DISCARD)"
                       ,"MAP hr.*, TARGET hr.*;"
                   ]
	     ,"credentials": {"alias": "ggsouth"}
         ,"mode": {"parallel":true,"type": "nonintegrated"}
         ,"source": {"name": "da", "path": "north"}
         ,"checkpoint":{"table": "ggadmin.ggs_checkpointtable"}
         ,"status": "running"
         }' | jq '.messages'

# 2nd Replicat: REPNB

curl -s -k -X POST https://south:9101/services/v2/replicats/REPNB                                 \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"description":"Replicat - Region South (Consuming from B)"
             ,"config":["REPLICAT repnb"
                       ,"USERIDALIAS ggsouth DOMAIN OracleGoldenGate"
                       ,"DDL INCLUDE MAPPED"
                       ,"DDLOPTIONS REPORT"
                       ,"DDLERROR DEFAULT, DISCARD"
                       ,"REPORTCOUNT EVERY 10 MINUTES, RATE"
                       ,"REPERROR (DEFAULT, DISCARD)"
                       ,"MAP hr.*, TARGET hr.*;"
                   ]
             ,"credentials": {"alias": "ggsouth"}
         ,"mode": {"parallel":true,"type": "nonintegrated"}
         ,"source": {"name": "da", "path": "north"}
         ,"checkpoint":{"table": "ggadmin.ggs_checkpointtable"}
         ,"status": "running"
         }' | jq '.messages'

# 3rd Replicat: REPNC

curl -s -k -X POST https://south:9101/services/v2/replicats/REPNC                                          \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"description":"Replicat - Region South (Consuming from C)"
             ,"config":["REPLICAT repnc"
                       ,"USERIDALIAS ggsouth DOMAIN OracleGoldenGate"
                       ,"DDL INCLUDE MAPPED"
                       ,"DDLOPTIONS REPORT"
                       ,"DDLERROR DEFAULT, DISCARD"
                       ,"REPORTCOUNT EVERY 10 MINUTES, RATE"
                       ,"REPERROR (DEFAULT, DISCARD)"
                       ,"MAP hr.*, TARGET hr.*;"
                   ]
             ,"credentials": {"alias": "ggsouth"}
         ,"mode": {"parallel":true,"type": "nonintegrated"}
         ,"source": {"name": "da", "path": "north"}
         ,"checkpoint":{"table": "ggadmin.ggs_checkpointtable"}
         ,"status": "running"
         }' | jq '.messages'

# List all Replicats
curl -s -k -X GET https://south:9101/services/v2/replicats                                                \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='   | jq '.response'

# List each individual Replicat (REPNA - REPND):
curl -s -k -X GET https://south:9101/services/v2/replicats/REPNA                                          \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='   | jq '.response'

curl -s -k -X GET https://south:9101/services/v2/replicats/REPNB                                          \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='   | jq '.response'

curl -s -k -X GET https://south:9101/services/v2/replicats/REPNC                                          \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='   | jq '.response'

# ----------------------------------------------------------------------------------------------------

Copy and use the following OBEY script to set up Oracle GoldenGate data replication environment on an pre-installed database.

Note: The given sample script uses names and values of database server, parameter values, Extract, Replicat, and other processes. You must change these values according to your environment for this script to work.

# Connect the GoldenGate Deployment depl_north

CONNECT https://north:9001 DEPLOYMENT depl_north AS ggma PASSWORD GGma_26ai !

ALTER CREDENTIALSTORE ADD USER ggadmin@dbnorth ALIAS ggnorth DOMAIN OracleGoldenGate PASSWORD ggadmin
INFO CREDENTIALSTORE
DBLOGIN USERIDALIAS ggnorth DOMAIN OracleGoldenGate
ADD SCHEMATRANDATA hr
ADD HEARTBEATTABLE

ADD EXTRACT extn INTEGRATED TRANLOG BEGIN NOW
REGISTER EXTRACT extn database
ADD EXTTRAIL north/ea, EXTRACT extn
START EXTRACT extn

ADD DISTPATH dpnw SOURCE trail://north:9002/services/v2/sources?trail=north/ea TARGET wss://west:9203/services/v2/targets?trail=north/da !
START DISTPATH dpnw

INFO ALL
INFO DISTPATH ALL


# Connect the GoldenGate Deployment depl_south

CONNECT https://south:9101 DEPLOYMENT depl_south AS ggma PASSWORD GGma_26ai !

ALTER CREDENTIALSTORE ADD USER ggadmin@dbsouth ALIAS ggsouth DOMAIN OracleGoldenGate PASSWORD ggadmin
INFO CREDENTIALSTORE
DBLOGIN USERIDALIAS ggsouth DOMAIN OracleGoldenGate
ADD SCHEMATRANDATA hr
ADD HEARTBEATTABLE

ADD EXTRACT exts INTEGRATED TRANLOG BEGIN NOW
REGISTER EXTRACT exts database
ADD EXTTRAIL south/ea, EXTRACT exts
START EXTRACT exts

ADD DISTPATH dpsw SOURCE trail://south:9102/services/v2/sources?trail=south/ea TARGET wss://west:9203/services/v2/targets?trail=south/da !
START DISTPATH dpsw

INFO ALL
INFO DISTPATH ALL


# Connect the GoldenGate Deployment depl_west

CONNECT https://west:9201 DEPLOYMENT depl_west AS ggma PASSWORD GGma_26ai !

ALTER CREDENTIALSTORE ADD USER ggadmin@dbwest ALIAS ggwest DOMAIN OracleGoldenGate PASSWORD ggadmin
INFO CREDENTIALSTORE
DBLOGIN USERIDALIAS ggwest DOMAIN OracleGoldenGate
ADD CHECKPOINTTABLE ggadmin.ggs_checkpointtable
ADD HEARTBEATTABLE

ADD REPLICAT repn, PARALLEL, EXTTRAIL north/da, CHECKPOINTTABLE ggadmin.ggs_checkpointtable
ADD REPLICAT reps, PARALLEL, EXTTRAIL south/da, CHECKPOINTTABLE ggadmin.ggs_checkpointtable

START REPLICAT repn
START REPLICAT reps
INFO ALL

DISCONNECT

After creating the OBEY file, create a shell script to run in Admin Client. This script runs triggers the data consolidation replication setup using the .oby file:

#!/bin/bash

# Clean up environment
/home/oracle/scripts/misc/cleanup.sh > /dev/null

# Copy parameter file (usually edited with EDIT PARAMs)
cp EXTN.prm  /u01/app/oracle/deployments/depl_north/etc/conf/ogg/
cp EXTS.prm  /u01/app/oracle/deployments/depl_south/etc/conf/ogg/

cp REPN.prm  /u01/app/oracle/deployments/depl_west/etc/conf/ogg/
cp REPS.prm  /u01/app/oracle/deployments/depl_west/etc/conf/ogg/

# Run the GoldenGate Obey script
echo "obey add_replication_dataconsolidation.oby" | adminclient