Forum Stats

  • 3,815,622 Users
  • 2,259,062 Discussions
  • 7,893,187 Comments

Discussions

GoldenGate DR

Robeen
Robeen Member Posts: 2,175 Silver Badge

Oracle DB 12.1.0.2


site A - RAC 2 nodes with below configurations.

trail files on shared filesystem (acfs)

SO: Solaris 11

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