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