Data Replication in a Data Consolidation Topology Using REST API

Create a Consolidation Topology Based Data Replication Using REST API Commands

Copy and use the following cURL script to set up Oracle GoldenGate data replication environment on a pre-installed Oracle Database 23ai 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'

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

Check the Statistics in a Consolidation Topology Based Data Replication Using REST API Commands

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
# --
# ----------------------------------------------------------------------------------------------------

echo " "
echo "------------------------------------------------------------------------------------------"
echo "--"
echo "-- Extract EXN "
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "

echo "Extract EXTN status:"
curl -s -k -X GET https://north:9001/services/v2/extracts/EXTN                                            \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='  | jq '.response.status'

echo "Extract EXTN lag:"
curl -s -k -X POST https://north:9001/services/v2/extracts/EXTN/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command": "GETLAG", "isReported": true}' | jq '.response.reply'                                \
      | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT 

echo "Extract EXTN statistics:"
curl -s -k -X POST https://north:9001/services/v2/extracts/EXTN/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command":"STATS", "arguments":"TOTAL"}'                                                        \
      | jq '.response.reply' | json_reformat | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT


echo " "
echo "------------------------------------------------------------------------------------------"
echo "--"
echo "-- DistPath DPNW"
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "


echo "DistPath DPNW status:"
curl -s -k -X GET https://north:9002/services/v2/sources/DPNW                                             \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='  | jq '.response.status'

echo "DistPath DPNW statistics:"
curl -s -k -X GET https://north:9002/services/v2/sources/DPNW/stats                                       \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
      | jq '.response' | json_reformat | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT

echo " "
echo "------------------------------------------------------------------------------------------"
echo "--"
echo "-- Extract EXTS "
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "


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

echo "Extract EXTS lag:"
curl -s -k -X POST https://south:9101/services/v2/extracts/EXTS/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command": "GETLAG", "isReported": true}' | jq '.response.reply'                                \
      | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT

echo "Extract EXTS statistics:"
curl -s -k -X POST https://south:9101/services/v2/extracts/EXTS/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command":"STATS", "arguments":"TOTAL"}'                                                        \
      | jq '.response.reply' | json_reformat | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT

echo " "
echo "------------------------------------------------------------------------------------------"
echo "--"
echo "-- DistPath DPSW"
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "


echo "DistPath DPSW status:"
curl -s -k -X GET https://south:9102/services/v2/sources/DPSW                                             \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='  | jq '.response.status'

echo "DistPath DPSW statistics:"
curl -s -k -X GET https://south:9102/services/v2/sources/DPSW/stats                                       \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
      | jq '.response' | json_reformat | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT


ls -ltrR /u01/app/oracle/deployments/depl_north/var/lib/data/north
ls -ltrR /u01/app/oracle/deployments/depl_south/var/lib/data/south

echo " "
echo "------------------------------------------------------------------------------------------"
echo "--"
echo "-- Replicats REPN and REPS "
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "

echo "Replicat REPN status:"
curl -s -k -X GET https://west:9201/services/v2/replicats/REPN                                            \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='  | jq '.response.status'

echo "Replicat REPN lag:"
curl -s -k -X POST https://west:9201/services/v2/replicats/REPN/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command": "GETLAG", "isReported": true}' | jq '.response.reply'                                \
      |  sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT

echo "Replicat REPN statistics:"
curl -s -k -X POST https://west:9201/services/v2/replicats/REPN/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command":"STATS", "arguments":"TOTAL"}'                                                        \
      | jq '.response.reply' | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT

echo "Replicat REPS status:"
curl -s -k -X GET https://west:9201/services/v2/replicats/REPS                                            \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='  | jq '.response.status'

echo "Replicat RESN lag:"
curl -s -k -X POST https://west:9201/services/v2/replicats/REPS/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command": "GETLAG", "isReported": true}' | jq '.response.reply'                                \
      |  sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT

echo "Replicat REPS statistics:"
curl -s -k -X POST https://west:9201/services/v2/replicats/REPS/command                                   \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"command":"STATS", "arguments":"TOTAL"}'                                                        \
      | jq '.response.reply' | sed 's/\\n/\n/g' |  sed 's/\\t/\t/g'| grep -v OKNODOT

ls -ltrR /u01/app/oracle/deployments/depl_west/var/lib/data/north
ls -ltrR /u01/app/oracle/deployments/depl_west/var/lib/data/south
exit

Remove the Consolidation Topology Replication Setup Using REST API Commands

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

# ----------------------------------------------------------------------------------------------------
# --
# -- stop & Delete Replicat
# --
# ----------------------------------------------------------------------------------------------------

curl -s -k -X PATCH https://south:9101/services/v2/replicats/REPNA                                        \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"status":"stopped"}'                                         |  jq '.messages'

curl -s -k -X PATCH https://south:9101/services/v2/replicats/REPNB                                        \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"status":"stopped"}'                                         |  jq '.messages'

curl -s -k -X PATCH https://south:9101/services/v2/replicats/REPNC                                        \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"status":"stopped"}'                                         |  jq '.messages'

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

curl -s -k -X POST https://south:9101/services/v2/commands/execute                                        \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"name": "purge"
	 ,"purgeType": "trails"
         ,"trails": [{"name": "ea","path": "north/"}]
         ,"useCheckpoints": false
         ,"keep": [{"type": "min","units": "files","value": 0}]
         }'                                                            | jq '.messages'
curl -s -k -X POST https://south:9101/services/v2/commands/execute                                        \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"name": "purge"
         ,"purgeType": "trails"
         ,"trails": [{"name": "da","path": "north/"}]
         ,"useCheckpoints": false
         ,"keep": [{"type": "min","units": "files","value": 0}]
         }'                                  

curl -s -k -X POST https://south:9101/services/v2/commands/execute                                        \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"name": "purge"
         ,"purgeType": "trails"
         ,"trails": [{"name": "db","path": "north/"}]
         ,"useCheckpoints": false
         ,"keep": [{"type": "min","units": "files","value": 0}]
         }'                                 
	
curl -s -k -X POST https://south:9101/services/v2/commands/execute                                        \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                                                       \
     -d '{"name": "purge"
         ,"purgeType": "trails"
         ,"trails": [{"name": "dc","path": "north/"}]
         ,"useCheckpoints": false
         ,"keep": [{"type": "min","units": "files","value": 0}]
         }'                                  	 
# ----------------------------------------------------------------------------------------------------
# --
# -- Delete Schematranda
# -- Delete Checkpointtable
# -- Delete Heartbeattables
# --
# ----------------------------------------------------------------------------------------------------

# Delete Checkpointtable at 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":"delete","name":"ggadmin.ggs_checkpointtable"}'  |  jq '.messages'

# Delete Heartbeattable at Database GGSOUTH
curl -s -k -X DELETE https://south:9101/services/v2/connections/OracleGoldenGate.ggsouth/tables/heartbeat \
     -H "Content-Type: application/json"                                                                  \
     -H "Accept: application/json"                                                                        \
     -H 'Authorization: Basic Z2dtYTpHR21hXzIzYWk='                    | jq '.messages'            

# ----------------------------------------------------------------------------------------------------
# --
# --Delete USERIDALIAS from GoldenGate
# --
# ----------------------------------------------------------------------------------------------------

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

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