Skip to Main Content

GG DR

RobeenNov 11 2020

Oracle DB 12.1.0.2

site A - RAC 2 nodes with below configurations.
trail files on shared filesystem (acfs)
SO: Solaris 11
User:

DR site:
OS - Solaris 11
Oracle DB 12.1.0.2
Dataguard has been configured from RAC DB to DR.
Golden Gate extract,pump and replicat are running on site A. I have checked doc 1322547 for GoldenGate configs when there is switchover.
After creating trigger:
CREATE OR REPLACE TRIGGER failover_actions AFTER DB_ROLE_CHANGE ON DATABASE
DECLARE
role VARCHAR(30);
BEGIN
Page 12 of 50
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
dbms_scheduler.create_job (
job_name => 'BIREPORT.jobGG',
job_type => 'EXECUTABLE',
job_action => '<path>/action.sh', <-- where is the location of action.sh ? not mentioned in doc. should this directory be the shared directory?
enabled => TRUE);
END IF;
GG_EXT_SHARED.sh
#!/bin/sh
#set Variables
OGG_HOME=/ogg/ora12c
FAL_NODE1=<primary node name> <---- should this be RAC node 1 hostname? or scan hostname?
FAL_NODE2=<standby node name> <--- hostname of standby DB?
ALT_DEST1=<primary node alternate archive destination> <--- what should this value be?
ALT_DEST2=<standby node alternate archive destination> <--- what should this value be?
PROFILE_NODE1=<primary node profile script name> <---where is this script lcoated?
PROFILE_NODE2=<standby node profile script name> <----where is this script lcoated?
NODE1_HOME=<primary node home directory where profile script resides> <---will this be ORACLE HOME?
NODE2_HOME=<standby node home directory where profile script resides> <---will this be ORACLE HOME?
extract=EXTDWH
pump=PXDWH
syspassword=<Oracle sys user password for the target db>
V_WAIT_FOR_ARCHIVE=<time in seconds to wait for archivelog after failover occurs> <---what should this value be?
#set DB profile
v_host=`hostname`
if [ "$v_host" = "$FAL_NODE1" ]
then
cd $NODE1_HOME
. ./$PROFILE_NODE1
else
cd $NODE2_HOME
. ./$PROFILE_NODE2
fi
#query the DB to get failover status
v_fal=`sqlplus -s "sys/$syspassword as sysdba"<<EOFF|grep -v "no rows selected"
set head off
select LAST_FAILOVER_REASON from V\\$fs_failover_stats;
exit
EOFF`
#if "$v_fal" empty then switchover else failover
if [ -z "$v_fal" ]
then
v_host=`hostname`
if [ "$v_host" = "$FAL_NODE1" ]

then
#switchover steps
#remote connection to stop mgr/pump in the failed node
ssh "$FAL_NODE2">/dev/null 2>&1 ". ./$PROFILE_NODE2;$OGG_HOME/ggsci <<EOFF
stop $pump
stop mgr!
exit
EOFF"
$OGG_HOME/ggsci <<EOFF
start mgr
sh sleep 2
start $extract
start $pump
exit
EOFF
exit 0
else
#remote connection to stop mgr/pump in the failed node
ssh "$FAL_NODE1">/dev/null 2>&1 ". ./$PROFILE_NODE1;$OGG_HOME/ggsci <<EOFF
stop $pump
stop mgr!
exit
EOFF"
$OGG_HOME/ggsci <<EOFF
start mgr
sh sleep 2
start $extract
start $pump
exit
EOFF
exit 0
fi
else
#failover steps
v_host=`hostname`
if [ "$v_host" = "$FAL_NODE1" ]
then
#remote connection to stop mgr/pump in the failed node
ssh "$FAL_NODE2">/dev/null 2>&1 ". ./$PROFILE_NODE2;$OGG_HOME/ggsci <<EOFF
stop $pump
stop mgr!
exit
EOFF"
$OGG_HOME/ggsci <<EOFF
start mgr
sh sleep 2
exit
EOFF
else

#remote connection to stop mgr/pump in the failed node
ssh "$FAL_NODE1">/dev/null 2>&1 ". ./$PROFILE_NODE1;$OGG_HOME/ggsci <<EOFF
stop $pump
stop mgr!
exit
EOFF"
$OGG_HOME/ggsci <<EOFF
start mgr
sh sleep 2
exit
EOFF
fi
#query the DB to get the resetlogs_id from last incarnation
v_reset_id=`sqlplus -s "sys/$syspassword as sysdba"<<EOFF
set head off
select max(resetlogs_id) from v\\$database_incarnation where resetlogs_id not in (select max(resetlogs_id) from v\\$database_incarnation);
exit
EOFF`
echo $v_reset_id>/tmp/t.tmp
v_reset_id=`cat /tmp/t.tmp`
rm /tmp/t.tmp
#sed operation to edit parameter file for archivelog only mode
v_thread=`sqlplus -s "sys/$syspassword as sysdba"<<EOFF
set head off
select max(thread#) from gv\\$log;
exit
EOFF`
echo $v_thread>/tmp/t.tmp
v_thread=`cat /tmp/t.tmp`
rm /tmp/t.tmp
if [ "$v_thread" = 1 ]
then
if [ "$v_host" = "$FAL_NODE1" ]
then
sed '
/userid/ a\
TRANLOGOPTIONS ARCHIVEDLOGONLY \nTRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY '"$ALT_DEST1"'\nTRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_'"$v_reset_id"'.dbf
'<$OGG_HOME/dirprm/${extract}.prm>$OGG_HOME/dirprm/${extract}_tmp.prm
else
sed '
/userid/ a\
TRANLOGOPTIONS ARCHIVEDLOGONLY \nTRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY '"$ALT_DEST2"'\nTRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_'"$v_reset_id"'.dbf
'<$OGG_HOME/dirprm/${extract}.prm>$OGG_HOME/dirprm/${extract}_tmp.prm
fi
cp $OGG_HOME/dirprm/${extract}_tmp.prm $OGG_HOME/dirprm/${extract}.prm

rm $OGG_HOME/dirprm/${extract}_tmp.prm
else
#rac steps
sed '
/userid/ a\
TRANLOGOPTIONS ARCHIVEDLOGONLY
'<$OGG_HOME/dirprm/${extract}.prm>$OGG_HOME/dirprm/${extract}_tmp1.prm
if [ "$v_host" = "$FAL_NODE1" ]
then
for i in `seq $v_thread`
do
sed '
/TRANLOGOPTIONS ARCHIVEDLOGONLY/ a\
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY THREAD '"$i"' '"$ALT_DEST1"'
'<$OGG_HOME/dirprm/${extract}_tmp1.prm>$OGG_HOME/dirprm/${extract}_tmp2.prm
mv $OGG_HOME/dirprm/${extract}_tmp2.prm $OGG_HOME/dirprm/${extract}_tmp1.prm
done
for i in `seq $v_thread`
do
sed '
/TRANLOGOPTIONS ARCHIVEDLOGONLY/ a\
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT THREAD '"$i"' %t_%s_'"$v_reset_id"'.dbf
'<$OGG_HOME/dirprm/${extract}_tmp1.prm>$OGG_HOME/dirprm/${extract}_tmp2.prm
mv $OGG_HOME/dirprm/${extract}_tmp2.prm $OGG_HOME/dirprm/${extract}_tmp1.prm
done
mv $OGG_HOME/dirprm/${extract}_tmp1.prm $OGG_HOME/dirprm/${extract}.prm
else
for i in `seq $v_thread`
do
sed '
/TRANLOGOPTIONS ARCHIVEDLOGONLY/ a\
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY THREAD '"$i"' '"$ALT_DEST2"'
'<$OGG_HOME/dirprm/${extract}_tmp1.prm>$OGG_HOME/dirprm/${extract}_tmp2.prm
mv $OGG_HOME/dirprm/${extract}_tmp2.prm $OGG_HOME/dirprm/${extract}_tmp1.prm
done
for i in `seq $v_thread`
do
sed '
/TRANLOGOPTIONS ARCHIVEDLOGONLY/ a\
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT THREAD '"$i"' %t_%s_'"$v_reset_id"'.dbf
'<$OGG_HOME/dirprm/${extract}_tmp1.prm>$OGG_HOME/dirprm/${extract}_tmp2.prm

mv $OGG_HOME/dirprm/${extract}_tmp2.prm $OGG_HOME/dirprm/${extract}_tmp1.prm
done
mv $OGG_HOME/dirprm/${extract}_tmp1.prm $OGG_HOME/dirprm/${extract}.prm
fi
fi
#get archivelog sequence number for the extract
v_seq=`$OGG_HOME/ggsci<<EOFF|egrep -v '^$|Oracle|Version|Linux|Copyright|GGSCI'|grep Seqno|awk '{print $4}'|cut -d ',' -f1
info etest1
exit
EOFF`
sleep $V_WAIT_FOR_ARCHIVE
$OGG_HOME/ggsci<<EOFF
start $extract
start $pump
exit
EOFF
sleep 30
#check to see if the extract crashed
report_file=`echo "$extract"|tr 'a-z' 'A-Z'`
grep -iq "ERROR" $OGG_HOME/dirrpt/${report_file}.rpt
if [ "$?" = 0 ]
then
#if crashed then exit the program
echo "Archive log 1_${v_seq}_${v_reset_id} not present. Failover not successfull">failover_actions.out
else
#else continue failover
v_eof=`$OGG_HOME/ggsci<<EOFF|egrep -v 'Sending|^$|Oracle|Version|Linux|Copyright|GGSCI'
send $extract logend
exit
EOFF`
#loop through until the extract finish capturing data from last incarnation
while [ "$v_eof" = "NO." ];
do
sleep 2
v_eof=`$OGG_HOME/ggsci<<EOFF|egrep -v 'Sending|^$|Oracle|Version|Linux|Copyright|GGSCI'
send $extract logend
exit
EOFF`
done
$OGG_HOME/ggsci<<EOFF
stop $extract
EOFF
#sed operation to delete archivelog only parameters

sed '/^TRANLOGOPTIONS/ d'<$OGG_HOME/dirprm/${extract}.prm>$OGG_HOME/dirprm/${extract}_tmp.prm
cp $OGG_HOME/dirprm/${extract}_tmp.prm $OGG_HOME/dirprm/${extract}.prm
rm $OGG_HOME/dirprm/${extract}_tmp.prm
sleep 2
#alter extract to start from new DB incarnation
if [ "$v_thread" = 1 ]
then
$OGG_HOME/ggsci<<EOFF
alter extract $extract extseqno 1 extrba 0
sh sleep 2
start $extract
exit
EOFF
else
for i in `seq $v_thread`
do
$OGG_HOME/ggsci<<EOFF
alter extract $extract thread $i extseqno 1 extrba 0
exit
EOFF
done
$OGG_HOME/ggsci<<EOFF
sh sleep 2
start $extract
exit
EOFF
fi
fi
fi
Can you please help with the scripts?

Regards,

Roshan

Comments
Post Details
Added on Nov 11 2020
0 comments
2 views