Common Files and Scripts Used in Cascading Data Replication

Source Database DBNORTH DML Operation

#!/usr/bin/bash

sqlplus -s system/oracle4GG@dbnorth << EOF
set lines 100
col firstname     format a30
col last_name     format a30
col employee_id   format 9999
col salary        format 999999.99
col department_id format 999

select employee_id, first_name, last_name, salary, department_id
  from hr.employees 
  where department_id = 60;

begin
  for i in 1..10 loop
    update hr.employees set salary = 1.05 * salary where department_id =60;
    dbms_lock.sleep(1);
    commit;
  end loop;
end;
/

select employee_id, first_name, last_name, salary, department_id
  from hr.employees
  where department_id = 60;

EOF
exit

Intermediate Database DBSOUTH DML Operations

#!/usr/bin/bash

sqlplus -s system/oracle4GG@dbnorth << EOF
set lines 100
col firstname     format a30
col last_name     format a30
col employee_id   format 9999
col salary        format 999999.99
col department_id format 999

drop table if exists hr.employees_test;
create table hr.employees_test as 
select employee_id, first_name, last_name, salary, department_id
  from hr.employees 
  where department_id = 60;

begin
  for i in 1..10 loop
    update hr.employees_test set salary = 100 + 1.05 * salary where department_id =60;
    dbms_lock.sleep(1);
    commit;
  end loop;
end;
/

select employee_id, first_name, last_name, salary, department_id
  from hr.employees_test
  where department_id = 60;

EOF
exit

Check the Status and Statistics for Processes in a Cascaded Replication Environment

#!/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 DPNS"
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "


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

curl -s -k -X GET https://north:9002/services/v2/sources/DPNS/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 "-- Replicat REPN "
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "

echo "Replicat REPN status:"
curl -s -k -X GET https://south:9101/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://south:9101/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://south:9101/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 " "
echo "------------------------------------------------------------------------------------------"
echo "--"
echo "-- Extract EXS "
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "

echo "Extract EXTS status:"
curl -s -k -X GET https://north:9001/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'

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

echo " "
echo "------------------------------------------------------------------------------------------"
echo "--"
echo "-- Replicat REPW "
echo "--"
echo "------------------------------------------------------------------------------------------"
echo " "

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

echo "Replicat REPW lag:"
curl -s -k -X POST https://west:9201/services/v2/replicats/REPW/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 REPW statistics:"
curl -s -k -X POST https://west:9201/services/v2/replicats/REPW/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

exit

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

Select Statement for Source to Intermediate Transactions

#!/usr/bin/bash

sqlplus -s  /nolog  << EOF

set lines 100
col firstname     format a30
col last_name     format a30
col employee_id   format 9999
col salary        format 999999.99
col department_id format 999

-- Connect to DBNorth
connect  ggadmin/ggadmin@dbnorth

PROMPT
PROMPT Source Database:

select employee_id, first_name, last_name, salary, department_id
  from hr.employees 
  where department_id = 60;

-- Connect to DBSouth
connect  ggadmin/ggadmin@dbsouth

PROMPT Intermediate Database:

select employee_id, first_name, last_name, salary, department_id
  from hr.employees
  where department_id = 60;

-- Connect to DBWest
connect  ggadmin/ggadmin@dbwest

PROMPT Target Database:

select employee_id, first_name, last_name, salary, department_id
  from hr.employees
  where department_id = 60;

EOF
exit

Select Statement for Intermediate Database DBSOUTH

#!/usr/bin/bash

sqlplus -s ggadmin/ggadmin@dbsouth << EOF
set lines 100
col firstname     format a30
col last_name     format a30
col employee_id   format 9999
col salary        format 99999.99
col department_id format 999

select employee_id, first_name, last_name, salary, department_id
  from hr.employees 
  where department_id = 60;

EOF
exit

Select Statement for Target Database DBWEST

#!/usr/bin/bash

sqlplus -s system/oracle4GG@dbsouth << EOF
set lines 100
col firstname     format a30
col last_name     format a30
col employee_id   format 9999
col salary        format 99999.99
col department_id format 999

select employee_id, first_name, last_name, salary, department_id
  from hr.employees 
  where department_id = 60;

EOF
exit