OGG High Availability using Oracle clusterware
=======================================
1) Oracle Golden Gate cluster high availability Pre-requsite.
a. Oracle Golden gate runs on one server at any time.
b. In the event of the failure on one node, the oracle GG can be started on the another node.
c. In order to resume processing on the another node, we need to maintain/store the
recover relagted files (checkpoint file & trailfiles) on shared location.
d. Oracle ACFS is the recommended cluster file system for Oracle Golden Gate binaries and
trail files in Real Application Cluster configurations for ease of management and high availability.
Note: ACFS can be used for Oracle Golden Gate trail files with no restrictions. Oracle GoldenGate installation can be done on ACFS and you can also store the recovery-related files in a cluster configuration in ACFS to make them accessible to all nodes. However if your Oracle Grid Infrastructure version is older than 11.2.0.3 then ACFS mounted on multiple servers concurrently does not currently support file locking, thus you would need to mount ACFS on only one server.
If ACFS is mounted on one server at a time then file locking is supported in pre 11.2.0.3 Grid Infrastructure releases. This file locking issue has been resolved in Oracle Grid Infrastructure release 12c and the fix has been back ported up to version 11.2.0.3.
2) Oracle clusterware
a) Oracle clusterware provides the capability to manage the third-party applications.
b) There are commands to register an application and instruct Oracle Clusterware how to manage the application in a clustered environment.
c) This capability will be used to register the Oracle GoldenGate manager process as an application managed through Oracle Clusterware.
d) Oracle Clusterware can be installed standalone without an Oracle RAC database and still manage a cluster of servers and various applications running on these servers. As such Oracle Clusterware can also be installed on more than just the database servers to form a single cluster.
3) Oracle Golden Gate Installations.
a) You may choose to perform a local installation on every server, or a single installation on a shared file system. You will need shared storage for the recovery-related files. On a Unix/Linux platform you can use a symbolic link to a central location for the shared directories.
4) Virtual IP.
a) Oracle Clusterware uses the concept of a Virtual IP address (VIP) to manage high availability for applications that require incoming network traffic (including the Oracle RAC database).
b) A VIP is an IP address on the public subnet that can be used to access a server. If the server hosting the VIP were to go down, then Oracle Clusterware will migrate the VIP to a surviving server to minimize interruptions for the application accessing the server (through the VIP).
c) This concept enables faster failovers compared to time-out based failovers on a server's actual IP address in case of a server failure.
d) For Oracle GoldenGate, you should use a VIP to access the manager process to isolate access to the manager process from the physical server that is running Oracle GoldenGate. Remote pumps must use the VIP to contact the Oracle GoldenGate manager. The VIP must be an available IP address on the public subnet and cannot be determined through DHCP.
Ask a system administrator for an available fixed IP address for Oracle GoldenGate managed through Oracle Clusterware.
5. We need to instruct Oracle clusterware how to start, stop, check process.
i) Start
a) Oracle GoldenGate manager is the process that starts all other Oracle GoldenGate processes. The only process that Oracle Clusterware should start is the manager process. Use the AUTOSTART parameter in the manager parameter file to start extract and replicat processes. You can use wild cards (AUTOSTART ER *) to start all extract and replicat processes.
b) Also note that once manager is started through Oracle Clusterware, it is Oracle Clusterware that manages its availability. If you would stop manager through the command interface ggsci, then Oracle Clusterware will attempt to restart it. Use the Oracle Clusterware commands to stop Oracle GoldenGate and prevent Oracle Clusterware from attempting to restart it.
ii) check
a) The validation whether Oracle GoldenGate is running is equivalent to making sure the Oracle GoldenGate manager runs.
iii) Stop
a) Stop must stop all Oracle GoldenGate processes, including manager. Stop may be called during a planned downtime (e.g. a server is taken out of a cluster for maintenance reasons) and/or if you manually instruct Oracle Clusterware to relocate Oracle GoldenGate to a different server (e.g. to change the load on a server). If a server crashes then all processes will go down with it, in which case they can be started on another server.
Setup
=====
1 . As of now below setup is running.
a. Source is two node rac where the GG is configured using ACFS.
b. Configured one extract and one pump process on the source.
c. Target is standalone db.
d. One Replicat process is configured in the target.
2. Now we need to register the Golden gate in Oracle cluster ware. We need to use oracle clusterware commands to create, register and set privileges on the VIP and the Oracle Golden gate application.Once registered, use the Oracle Clusterware commands to start, relocate and stop Oracle GoldenGate.
3. Add an application VIP.
a) The first step is to create an application VIP. The VIP will be used to access Oracle GoldenGate.
Oracle Clusterware will assign the VIP to a physical server, and migrate the VIP if that server were to go down or if you instruct Clusterware to do so.
b. Update the below vip in the /etc/hosts file on both the nodes , ( the vip should be on the same subnet of the public ip).
########## VIP FOR GOLDENGATE ################################
192.168.0.22 goldengate-vip.manzoor.com goldengate-vip
c. Create a application VIP using below command as root user.
[root@rhel11gr2rac1 bin]# cd /grid/11.2/bin
[root@rhel11gr2rac1 bin]# ./appvipcfg -help
Production Copyright 2007, 2008, Oracle.All rights reserved
Unknown option: help
Usage: appvipcfg create -network= -ip= -vipname=
-user=[-group=] [-failback=0 | 1]
delete -vipname=
To identifiy the network number execute the below command.
[root@rhel11gr2rac1 bin]# ./crsctl stat res -p | grep -ie.network -ie subnet | grep -ie name -ie subnet
NAME=ora.net1.network
USR_ORA_SUBNET=192.168.0.0
here ora.net1 in NAME denotes the network number which is 1, and the USR_ORA_SUBNET denotes the subnet under which
the vip will be created.
Execute the below command to create the application vip.
./appvipcfg create -network=1 -ip=192.168.0.22 -vipname=goldengate-vip -user=root
Production Copyright 2007, 2008, Oracle.All rights reserved
2013-10-01 23:18:11: Creating Resource Type
2013-10-01 23:18:11: Executing /grid/11.2/bin/crsctl add type app.appvip_net1.type -basetype ora.cluster_vip_net1.type -file /grid/11.2/crs/template/appvip.type
2013-10-01 23:18:11: Executing cmd: /grid/11.2/bin/crsctl add type app.appvip_net1.type -basetype ora.cluster_vip_net1.type -file /grid/11.2/crs/template/appvip.type
2013-10-01 23:18:13: Create the Resource
2013-10-01 23:18:13: Executing /grid/11.2/bin/crsctl add resource goldengate-vip -type app.appvip_net1.type -attr "USR_ORA_VIP=192.168.0.22,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x',HOSTING_MEMBERS=rhel11gr2rac1.manzoor.com,APPSVIP_FAILBACK="
2013-10-01 23:18:13: Executing cmd: /grid/11.2/bin/crsctl add resource goldengate-vip -type app.appvip_net1.type -attr "USR_ORA_VIP=192.168.0.22,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x',HOSTING_MEMBERS=rhel11gr2rac1.manzoor.com,APPSVIP_FAILBACK="
d) Now allow the oracle clusterware owner (eg. oracle or grid) to run the script to start the VIP.
execute the below as root.
./crsctl setperm resource goldengate-vip -u user:oracle:r-x
e) As oracle user start the vip.
[oracle@rhel11gr2rac1 bin]$ ./crsctl start resource goldengate-vip
CRS-2672: Attempting to start 'goldengate-vip' on 'rhel11gr2rac1'
CRS-2676: Start of 'goldengate-vip' on 'rhel11gr2rac1' succeeded
f) Check the status of the vip.
[oracle@rhel11gr2rac1 bin]$ ./crsctl stat res goldengate-vip
NAME=goldengate-vip
TYPE=app.appvip_net1.type
TARGET=ONLINE
STATE=ONLINE on rhel11gr2rac1
e) Now we can able to ping the vip from the other nodes. Test it in node 2.
[root@rhel11gr2rac2 ~]# ping 192.168.0.22
PING 192.168.0.22 (192.168.0.22) 56(84) bytes of data.
64 bytes from 192.168.0.22: icmp_seq=1 ttl=64 time=2.29 ms
64 bytes from 192.168.0.22: icmp_seq=2 ttl=64 time=0.389 ms
64 bytes from 192.168.0.22: icmp_seq=3 ttl=64 time=0.372 ms
--- 192.168.0.22 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.372/1.018/2.295/0.903 ms
4) Now develop and agent script.
a) Oracle Clusterware runs resource-specific commands through an entity called an agent.
The agent script must be able to accept 5 parameter values: start, stop, check, clean and abort (optional).
b) Now we will create an script to and will also place the script in the shared location, here we have placed the script under the gg home which will be accessed on both the nodes. (This is the sample script provided by oracle we can also have a customized script as per our requirement).
Script name = gg_monitor_start.sh
#!/bin/sh
#goldengate_action.scr
. ~oracle/.bash_profile
[ -z "$1" ]&& echo "ERROR!! Usage $0"&& exit 99
GGS_HOME=/golden_gate
#specify delay after start before checking for successful start
start_delay_secs=5
#Include the Oracle GoldenGate home in the library path to start GGSCI
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${GGS_HOME}
#set the oracle home to the database to ensure Oracle GoldenGate will get
#the right environment settings to be able to connect to the database
export ORACLE_HOME=/u01/app/oracle/product/11.2/db
export CRS_HOME=/grid/11.2
#Set NLS_LANG otherwise it will default to US7ASCII
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
logfile=/tmp/crs_gg_start.log
###########################
function log
###########################
{
DATETIME=`date +%d/%m/%y-%H:%M:%S`
echo $DATETIME "goldengate_action.scr>>" $1
echo $DATETIME "goldengate_action.scr>>" $1 >> $logfile
}
#check_process validates that a manager process is running at the PID
#that Oracle GoldenGate specifies.
check_process () {
dt=`date +%d/%m/%y-%H:%M:%S`
if ( [ -f "${GGS_HOME}/dirpcs/MGR.pcm" ] )
then
pid=`cut -f8 "${GGS_HOME}/dirpcs/MGR.pcm"`
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |awk '{ print $1 }'` ]
then
#manager process is running on the PID . exit success
echo $dt "manager process is running on the PID . exit success">> /tmp/check.out
exit 0
else
#manager process is not running on the PID
echo $dt "manager process is not running on the PID" >> /tmp/check.out
exit 1
fi
else
#manager is not running because there is no PID file
echo $ dt"manager is not running because there is no PID file" >> /tmp/check.out
exit 1
fi
}
#call_ggsci is a generic routine that executes a ggsci command
call_ggsci () {
log "entering call_ggsci"
ggsci_command=$1
#log "about to execute $ggsci_command"
log "id= $USER"
cd ${GGS_HOME}
ggsci_output=`${GGS_HOME}/ggsci << EOF
${ggsci_command}
exit
EOF`
log "got output of : $ggsci_output"
}
case $1 in
'start')
#Updated by Sourav B (02/10/2011)
# During failover if the “mgr.pcm” file is not deleted at the node crash
# then Oracle clusterware won’t start the manager on the new node assuming the
# manager process is still running on the failed node. To get around this issue
# we will delete the “mgr.prm” file before starting up the manager on the new
# node. We will also delete the other process files with pc* extension and to
# avoid any file locking issue we will first backup the checkpoint files and then
# delete them from the dirchk directory.After that we will restore the checkpoint
# files from backup to the original location (dirchk directory).
log "removing *.pc* files from dirpcs directory..."
cd $GGS_HOME/dirpcs
rm -f *.pc*
log "creating tmp directory to backup checkpoint file...."
cd $GGS_HOME/dirchk
mkdir tmp
log "backing up checkpoint files..."
cp *.cp* $GGS_HOME/dirchk/tmp
log "Deleting checkpoint files under dirchk......"
rm -f *.cp*
log "Restore checkpoint files from backup to dirchk directory...."
cp $GGS_HOME/dirchk/tmp/*.cp* $GGS_HOME/dirchk
log "Deleting tmp directory...."
rm -rf tmp
log "starting manager"
call_ggsci 'start manager'
#there is a small delay between issuing the start manager command
#and the process being spawned on the OS . wait before checking
log "sleeping for start_delay_secs"
sleep ${start_delay_secs}
#check whether manager is running and exit accordingly
check_process
;;
'stop')
#attempt a clean stop for all non-manager processes
call_ggsci 'stop er *'
#ensure everything is stopped
call_ggsci 'stop er *!'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'check')
check_process
exit 0
;;
'clean')
#attempt a clean stop for all non-manager processes
call_ggsci 'stop er *'
#ensure everything is stopped
call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'abort')
#ensure everything is stopped
call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
esac
c) Now we need to add a clusterware resource for the ggate application. As oracle user execute the
below command.
[oracle@rhel11gr2rac1 bin]$ ./crsctl add resource ggateapp -type cluster_resource -attr "ACTION_SCRIPT=/golden_gate/gg_monitor_start.sh,CHECK_INTERVAL=30,START_DEPENDENCIES='hard(goldengate-vip) pullup(goldengate-vip)', STOP_DEPENDENCIES='hard(goldengate-vip)'"
where ggateapp - is the application name we have given for golden gate resource.
START_DEPENDENCIES: there is a hard start dependency on goldengate-vip. This indicates that the VIP and the ggateapp application should always start together.
STOP_DEPENDENCIES: there is a hard stop dependency on goldengate-vip. This indicates that the VIP and the ggateapp application should always stop together.
d) Now set the ownership of the oracle golden gate application if it is different from the oracle clusterware owner eg(ggowner) If oracle goldengate owner is same then ignore the below.
As root execute the below command.
./crsctl setperm resource ggateapp -o ggowner
e) Now start the resource using oracle user.
[oracle@rhel11gr2rac1 bin]$ ./crsctl start res ggateapp
CRS-2672: Attempting to start 'ggateapp' on 'rhel11gr2rac1'
CRS-2676: Start of 'ggateapp' on 'rhel11gr2rac1' succeeded
[oracle@rhel11gr2rac1 bin]$ ./crsctl status res ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on rhel11gr2rac1
[oracle@rhel11gr2rac1 bin]$ ./crsctl stop res ggateapp
CRS-2673: Attempting to stop 'ggateapp' on 'rhel11gr2rac1'
CRS-2677: Stop of 'ggateapp' on 'rhel11gr2rac1' succeeded
-- Now lets check the status in ggsci.
[oracle@rhel11gr2rac1 golden_gate]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (rhel11gr2rac1.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED PTBLS 00:00:00 00:00:29
EXTRACT STOPPED XTBLS 00:00:02 00:00:27
--- Its showing stopped..
[oracle@rhel11gr2rac1 bin]$ ./crsctl start res ggateapp
CRS-2672: Attempting to start 'ggateapp' on 'rhel11gr2rac1'
CRS-2676: Start of 'ggateapp' on 'rhel11gr2rac1' succeeded
[oracle@rhel11gr2rac1 golden_gate]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (rhel11gr2rac1.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:10
EXTRACT RUNNING XTBLS 00:00:01 00:00:09
--- Now lets relocate the ggapp to the another node (-- scheduled downtime)
[oracle@rhel11gr2rac1 bin]$ ./crsctl relocate resource ggateapp -f
CRS-2673: Attempting to stop 'ggateapp' on 'rhel11gr2rac1'
CRS-2677: Stop of 'ggateapp' on 'rhel11gr2rac1' succeeded
CRS-2673: Attempting to stop 'goldengate-vip' on 'rhel11gr2rac1'
CRS-2677: Stop of 'goldengate-vip' on 'rhel11gr2rac1' succeeded
CRS-2672: Attempting to start 'goldengate-vip' on 'rhel11gr2rac2'
CRS-2676: Start of 'goldengate-vip' on 'rhel11gr2rac2' succeeded
CRS-2672: Attempting to start 'ggateapp' on 'rhel11gr2rac2'
CRS-2676: Start of 'ggateapp' on 'rhel11gr2rac2' succeeded
-- Lets check the gg process on node 2.
[oracle@rhel11gr2rac2 golden_gate]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (rhel11gr2rac2.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:04
EXTRACT RUNNING XTBLS 00:00:05 00:00:06
[oracle@rhel11gr2rac1 bin]$ ./crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on rhel11gr2rac2
-- Now lets check how the failover is working.
Lets crash the node 2...(power of from vmware home)
Below are the staus after the node 2 is down....
Cluster Resources
--------------------------------------------------------------------------------
ggateapp
1 ONLINE OFFLINE
goldengate-vip
1 ONLINE OFFLINE STARTING
Cluster Resources
--------------------------------------------------------------------------------
ggateapp
1 ONLINE ONLINE rhel11gr2rac1
goldengate-vip
1 ONLINE ONLINE rhel11gr2rac1
-- Could see the ggateapp resource and goldengate-vip has been failed over from
node 2 to node 1...
Below is the output from ggsci.
GGSCI (rhel11gr2rac1.manzoor.com) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED PTBLS 00:00:00 00:04:10
EXTRACT RUNNING XTBLS 00:00:00 00:00:09
-- The manager and the extract process are started but the pump extract has been abended with the below
error.
2013-10-02 22:20:56 ERROR OGG-01031 There is a problem in network communication, a remote file problem, encryption keys for target and source do not matc
h (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file "./dirdat/XT000016" (error 11, Resource temporarily unavailable)).
2013-10-02 22:20:56 ERROR OGG-01668 PROCESS ABENDING.
Source --
GGSCI (rhel11gr2rac1.manzoor.com) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED PTBLS 00:00:00 00:08:18
EXTRACT RUNNING XTBLS 00:00:00 00:00:00
GGSCI (rhel11gr2rac1.manzoor.com) 18> info PTBLS
EXTRACT PTBLS Last Started 2013-10-02 22:24 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:08:24 ago)
Log Read Checkpoint File ./dirdat/XT000019
2013-10-02 22:16:24.415084 RBA 1111
Target --
GGSCI (standalone2.manzoor.com) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTBLS 00:00:00 00:00:05
GGSCI (standalone2.manzoor.com) 3> info rtbls
REPLICAT RTBLS Last Started 2013-10-02 20:38 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File ./dirdat/XT000016
2013-10-02 22:16:24.422522 RBA 1991
GGSCI (standalone2.manzoor.com) 4> send rtbls status
Sending STATUS request to REPLICAT RTBLS ...
Current status: At EOF
Sequence #: 16
RBA: 1991
0 records in current transaction
--- The replicat process shows it is completed all the data and currently it is at end of file.
-- Now we will do an et (extact trail) rollover on the source and
Source--
GGSCI (rhel11gr2rac1.manzoor.com) 21> alter extract ptbls etrollover
2013-10-02 22:34:04 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after
starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.
GGSCI (rhel11gr2rac1.manzoor.com) 23> start extract ptbls
Sending START request to MANAGER ...
EXTRACT PTBLS starting
GGSCI (rhel11gr2rac1.manzoor.com) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:58
EXTRACT RUNNING XTBLS 00:00:00 00:00:08
GGSCI (rhel11gr2rac1.manzoor.com) 31> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:05
EXTRACT RUNNING XTBLS 00:00:00 00:00:00
GGSCI (rhel11gr2rac1.manzoor.com) 32> info ptbls
EXTRACT PTBLS Last Started 2013-10-02 22:35 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File ./dirdat/XT000020
2013-10-02 22:20:46.216461 RBA 1111
--- Target
in target we need to move the replicat to start again from the next sequnce since we have
did an ET rollover on the source.
GGSCI (standalone2.manzoor.com) 3> stop replicat rtbls
Sending STOP request to REPLICAT RTBLS ...
Request processed.
GGSCI (standalone2.manzoor.com) 4> alter replicat rtbls extseqno 17 extrba 0
REPLICAT altered.
GGSCI (standalone2.manzoor.com) 5> start replicat rtbls
-- Now lets update some data on source.
SQL> select count(*) from emp;
COUNT(*)
----------
4000
SQL> begin
2 for i in 4001..5000 loop
3 insert into emp values (i, dbms_random.string('U',30),30);
4 END LOOP;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
5000
-- Lets check whether it replicated to target.
SQL> select count(*) from emp;
COUNT(*)
----------
5000
---- Reference -- Oracle White Paper—Oracle GoldenGate high availability with Oracle Clusterware
Note - 1527310.1,
=======================================
1) Oracle Golden Gate cluster high availability Pre-requsite.
a. Oracle Golden gate runs on one server at any time.
b. In the event of the failure on one node, the oracle GG can be started on the another node.
c. In order to resume processing on the another node, we need to maintain/store the
recover relagted files (checkpoint file & trailfiles) on shared location.
d. Oracle ACFS is the recommended cluster file system for Oracle Golden Gate binaries and
trail files in Real Application Cluster configurations for ease of management and high availability.
Note: ACFS can be used for Oracle Golden Gate trail files with no restrictions. Oracle GoldenGate installation can be done on ACFS and you can also store the recovery-related files in a cluster configuration in ACFS to make them accessible to all nodes. However if your Oracle Grid Infrastructure version is older than 11.2.0.3 then ACFS mounted on multiple servers concurrently does not currently support file locking, thus you would need to mount ACFS on only one server.
If ACFS is mounted on one server at a time then file locking is supported in pre 11.2.0.3 Grid Infrastructure releases. This file locking issue has been resolved in Oracle Grid Infrastructure release 12c and the fix has been back ported up to version 11.2.0.3.
2) Oracle clusterware
a) Oracle clusterware provides the capability to manage the third-party applications.
b) There are commands to register an application and instruct Oracle Clusterware how to manage the application in a clustered environment.
c) This capability will be used to register the Oracle GoldenGate manager process as an application managed through Oracle Clusterware.
d) Oracle Clusterware can be installed standalone without an Oracle RAC database and still manage a cluster of servers and various applications running on these servers. As such Oracle Clusterware can also be installed on more than just the database servers to form a single cluster.
3) Oracle Golden Gate Installations.
a) You may choose to perform a local installation on every server, or a single installation on a shared file system. You will need shared storage for the recovery-related files. On a Unix/Linux platform you can use a symbolic link to a central location for the shared directories.
4) Virtual IP.
a) Oracle Clusterware uses the concept of a Virtual IP address (VIP) to manage high availability for applications that require incoming network traffic (including the Oracle RAC database).
b) A VIP is an IP address on the public subnet that can be used to access a server. If the server hosting the VIP were to go down, then Oracle Clusterware will migrate the VIP to a surviving server to minimize interruptions for the application accessing the server (through the VIP).
c) This concept enables faster failovers compared to time-out based failovers on a server's actual IP address in case of a server failure.
d) For Oracle GoldenGate, you should use a VIP to access the manager process to isolate access to the manager process from the physical server that is running Oracle GoldenGate. Remote pumps must use the VIP to contact the Oracle GoldenGate manager. The VIP must be an available IP address on the public subnet and cannot be determined through DHCP.
Ask a system administrator for an available fixed IP address for Oracle GoldenGate managed through Oracle Clusterware.
5. We need to instruct Oracle clusterware how to start, stop, check process.
i) Start
a) Oracle GoldenGate manager is the process that starts all other Oracle GoldenGate processes. The only process that Oracle Clusterware should start is the manager process. Use the AUTOSTART parameter in the manager parameter file to start extract and replicat processes. You can use wild cards (AUTOSTART ER *) to start all extract and replicat processes.
b) Also note that once manager is started through Oracle Clusterware, it is Oracle Clusterware that manages its availability. If you would stop manager through the command interface ggsci, then Oracle Clusterware will attempt to restart it. Use the Oracle Clusterware commands to stop Oracle GoldenGate and prevent Oracle Clusterware from attempting to restart it.
ii) check
a) The validation whether Oracle GoldenGate is running is equivalent to making sure the Oracle GoldenGate manager runs.
iii) Stop
a) Stop must stop all Oracle GoldenGate processes, including manager. Stop may be called during a planned downtime (e.g. a server is taken out of a cluster for maintenance reasons) and/or if you manually instruct Oracle Clusterware to relocate Oracle GoldenGate to a different server (e.g. to change the load on a server). If a server crashes then all processes will go down with it, in which case they can be started on another server.
Setup
=====
1 . As of now below setup is running.
a. Source is two node rac where the GG is configured using ACFS.
b. Configured one extract and one pump process on the source.
c. Target is standalone db.
d. One Replicat process is configured in the target.
2. Now we need to register the Golden gate in Oracle cluster ware. We need to use oracle clusterware commands to create, register and set privileges on the VIP and the Oracle Golden gate application.Once registered, use the Oracle Clusterware commands to start, relocate and stop Oracle GoldenGate.
3. Add an application VIP.
a) The first step is to create an application VIP. The VIP will be used to access Oracle GoldenGate.
Oracle Clusterware will assign the VIP to a physical server, and migrate the VIP if that server were to go down or if you instruct Clusterware to do so.
b. Update the below vip in the /etc/hosts file on both the nodes , ( the vip should be on the same subnet of the public ip).
########## VIP FOR GOLDENGATE ################################
192.168.0.22 goldengate-vip.manzoor.com goldengate-vip
c. Create a application VIP using below command as root user.
[root@rhel11gr2rac1 bin]# cd /grid/11.2/bin
[root@rhel11gr2rac1 bin]# ./appvipcfg -help
Production Copyright 2007, 2008, Oracle.All rights reserved
Unknown option: help
Usage: appvipcfg create -network=
-user=
delete -vipname=
To identifiy the network number execute the below command.
[root@rhel11gr2rac1 bin]# ./crsctl stat res -p | grep -ie.network -ie subnet | grep -ie name -ie subnet
NAME=ora.net1.network
USR_ORA_SUBNET=192.168.0.0
here ora.net1 in NAME denotes the network number which is 1, and the USR_ORA_SUBNET denotes the subnet under which
the vip will be created.
Execute the below command to create the application vip.
./appvipcfg create -network=1 -ip=192.168.0.22 -vipname=goldengate-vip -user=root
Production Copyright 2007, 2008, Oracle.All rights reserved
2013-10-01 23:18:11: Creating Resource Type
2013-10-01 23:18:11: Executing /grid/11.2/bin/crsctl add type app.appvip_net1.type -basetype ora.cluster_vip_net1.type -file /grid/11.2/crs/template/appvip.type
2013-10-01 23:18:11: Executing cmd: /grid/11.2/bin/crsctl add type app.appvip_net1.type -basetype ora.cluster_vip_net1.type -file /grid/11.2/crs/template/appvip.type
2013-10-01 23:18:13: Create the Resource
2013-10-01 23:18:13: Executing /grid/11.2/bin/crsctl add resource goldengate-vip -type app.appvip_net1.type -attr "USR_ORA_VIP=192.168.0.22,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x',HOSTING_MEMBERS=rhel11gr2rac1.manzoor.com,APPSVIP_FAILBACK="
2013-10-01 23:18:13: Executing cmd: /grid/11.2/bin/crsctl add resource goldengate-vip -type app.appvip_net1.type -attr "USR_ORA_VIP=192.168.0.22,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x',HOSTING_MEMBERS=rhel11gr2rac1.manzoor.com,APPSVIP_FAILBACK="
d) Now allow the oracle clusterware owner (eg. oracle or grid) to run the script to start the VIP.
execute the below as root.
./crsctl setperm resource goldengate-vip -u user:oracle:r-x
e) As oracle user start the vip.
[oracle@rhel11gr2rac1 bin]$ ./crsctl start resource goldengate-vip
CRS-2672: Attempting to start 'goldengate-vip' on 'rhel11gr2rac1'
CRS-2676: Start of 'goldengate-vip' on 'rhel11gr2rac1' succeeded
f) Check the status of the vip.
[oracle@rhel11gr2rac1 bin]$ ./crsctl stat res goldengate-vip
NAME=goldengate-vip
TYPE=app.appvip_net1.type
TARGET=ONLINE
STATE=ONLINE on rhel11gr2rac1
e) Now we can able to ping the vip from the other nodes. Test it in node 2.
[root@rhel11gr2rac2 ~]# ping 192.168.0.22
PING 192.168.0.22 (192.168.0.22) 56(84) bytes of data.
64 bytes from 192.168.0.22: icmp_seq=1 ttl=64 time=2.29 ms
64 bytes from 192.168.0.22: icmp_seq=2 ttl=64 time=0.389 ms
64 bytes from 192.168.0.22: icmp_seq=3 ttl=64 time=0.372 ms
--- 192.168.0.22 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.372/1.018/2.295/0.903 ms
4) Now develop and agent script.
a) Oracle Clusterware runs resource-specific commands through an entity called an agent.
The agent script must be able to accept 5 parameter values: start, stop, check, clean and abort (optional).
b) Now we will create an script to and will also place the script in the shared location, here we have placed the script under the gg home which will be accessed on both the nodes. (This is the sample script provided by oracle we can also have a customized script as per our requirement).
Script name = gg_monitor_start.sh
#!/bin/sh
#goldengate_action.scr
. ~oracle/.bash_profile
[ -z "$1" ]&& echo "ERROR!! Usage $0
GGS_HOME=/golden_gate
#specify delay after start before checking for successful start
start_delay_secs=5
#Include the Oracle GoldenGate home in the library path to start GGSCI
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${GGS_HOME}
#set the oracle home to the database to ensure Oracle GoldenGate will get
#the right environment settings to be able to connect to the database
export ORACLE_HOME=/u01/app/oracle/product/11.2/db
export CRS_HOME=/grid/11.2
#Set NLS_LANG otherwise it will default to US7ASCII
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
logfile=/tmp/crs_gg_start.log
###########################
function log
###########################
{
DATETIME=`date +%d/%m/%y-%H:%M:%S`
echo $DATETIME "goldengate_action.scr>>" $1
echo $DATETIME "goldengate_action.scr>>" $1 >> $logfile
}
#check_process validates that a manager process is running at the PID
#that Oracle GoldenGate specifies.
check_process () {
dt=`date +%d/%m/%y-%H:%M:%S`
if ( [ -f "${GGS_HOME}/dirpcs/MGR.pcm" ] )
then
pid=`cut -f8 "${GGS_HOME}/dirpcs/MGR.pcm"`
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |awk '{ print $1 }'` ]
then
#manager process is running on the PID . exit success
echo $dt "manager process is running on the PID . exit success">> /tmp/check.out
exit 0
else
#manager process is not running on the PID
echo $dt "manager process is not running on the PID" >> /tmp/check.out
exit 1
fi
else
#manager is not running because there is no PID file
echo $ dt"manager is not running because there is no PID file" >> /tmp/check.out
exit 1
fi
}
#call_ggsci is a generic routine that executes a ggsci command
call_ggsci () {
log "entering call_ggsci"
ggsci_command=$1
#log "about to execute $ggsci_command"
log "id= $USER"
cd ${GGS_HOME}
ggsci_output=`${GGS_HOME}/ggsci << EOF
${ggsci_command}
exit
EOF`
log "got output of : $ggsci_output"
}
case $1 in
'start')
#Updated by Sourav B (02/10/2011)
# During failover if the “mgr.pcm” file is not deleted at the node crash
# then Oracle clusterware won’t start the manager on the new node assuming the
# manager process is still running on the failed node. To get around this issue
# we will delete the “mgr.prm” file before starting up the manager on the new
# node. We will also delete the other process files with pc* extension and to
# avoid any file locking issue we will first backup the checkpoint files and then
# delete them from the dirchk directory.After that we will restore the checkpoint
# files from backup to the original location (dirchk directory).
log "removing *.pc* files from dirpcs directory..."
cd $GGS_HOME/dirpcs
rm -f *.pc*
log "creating tmp directory to backup checkpoint file...."
cd $GGS_HOME/dirchk
mkdir tmp
log "backing up checkpoint files..."
cp *.cp* $GGS_HOME/dirchk/tmp
log "Deleting checkpoint files under dirchk......"
rm -f *.cp*
log "Restore checkpoint files from backup to dirchk directory...."
cp $GGS_HOME/dirchk/tmp/*.cp* $GGS_HOME/dirchk
log "Deleting tmp directory...."
rm -rf tmp
log "starting manager"
call_ggsci 'start manager'
#there is a small delay between issuing the start manager command
#and the process being spawned on the OS . wait before checking
log "sleeping for start_delay_secs"
sleep ${start_delay_secs}
#check whether manager is running and exit accordingly
check_process
;;
'stop')
#attempt a clean stop for all non-manager processes
call_ggsci 'stop er *'
#ensure everything is stopped
call_ggsci 'stop er *!'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'check')
check_process
exit 0
;;
'clean')
#attempt a clean stop for all non-manager processes
call_ggsci 'stop er *'
#ensure everything is stopped
call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'abort')
#ensure everything is stopped
call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
esac
c) Now we need to add a clusterware resource for the ggate application. As oracle user execute the
below command.
[oracle@rhel11gr2rac1 bin]$ ./crsctl add resource ggateapp -type cluster_resource -attr "ACTION_SCRIPT=/golden_gate/gg_monitor_start.sh,CHECK_INTERVAL=30,START_DEPENDENCIES='hard(goldengate-vip) pullup(goldengate-vip)', STOP_DEPENDENCIES='hard(goldengate-vip)'"
where ggateapp - is the application name we have given for golden gate resource.
START_DEPENDENCIES: there is a hard start dependency on goldengate-vip. This indicates that the VIP and the ggateapp application should always start together.
STOP_DEPENDENCIES: there is a hard stop dependency on goldengate-vip. This indicates that the VIP and the ggateapp application should always stop together.
d) Now set the ownership of the oracle golden gate application if it is different from the oracle clusterware owner eg(ggowner) If oracle goldengate owner is same then ignore the below.
As root execute the below command.
./crsctl setperm resource ggateapp -o ggowner
e) Now start the resource using oracle user.
[oracle@rhel11gr2rac1 bin]$ ./crsctl start res ggateapp
CRS-2672: Attempting to start 'ggateapp' on 'rhel11gr2rac1'
CRS-2676: Start of 'ggateapp' on 'rhel11gr2rac1' succeeded
[oracle@rhel11gr2rac1 bin]$ ./crsctl status res ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on rhel11gr2rac1
[oracle@rhel11gr2rac1 bin]$ ./crsctl stop res ggateapp
CRS-2673: Attempting to stop 'ggateapp' on 'rhel11gr2rac1'
CRS-2677: Stop of 'ggateapp' on 'rhel11gr2rac1' succeeded
-- Now lets check the status in ggsci.
[oracle@rhel11gr2rac1 golden_gate]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (rhel11gr2rac1.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED PTBLS 00:00:00 00:00:29
EXTRACT STOPPED XTBLS 00:00:02 00:00:27
--- Its showing stopped..
[oracle@rhel11gr2rac1 bin]$ ./crsctl start res ggateapp
CRS-2672: Attempting to start 'ggateapp' on 'rhel11gr2rac1'
CRS-2676: Start of 'ggateapp' on 'rhel11gr2rac1' succeeded
[oracle@rhel11gr2rac1 golden_gate]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (rhel11gr2rac1.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:10
EXTRACT RUNNING XTBLS 00:00:01 00:00:09
--- Now lets relocate the ggapp to the another node (-- scheduled downtime)
[oracle@rhel11gr2rac1 bin]$ ./crsctl relocate resource ggateapp -f
CRS-2673: Attempting to stop 'ggateapp' on 'rhel11gr2rac1'
CRS-2677: Stop of 'ggateapp' on 'rhel11gr2rac1' succeeded
CRS-2673: Attempting to stop 'goldengate-vip' on 'rhel11gr2rac1'
CRS-2677: Stop of 'goldengate-vip' on 'rhel11gr2rac1' succeeded
CRS-2672: Attempting to start 'goldengate-vip' on 'rhel11gr2rac2'
CRS-2676: Start of 'goldengate-vip' on 'rhel11gr2rac2' succeeded
CRS-2672: Attempting to start 'ggateapp' on 'rhel11gr2rac2'
CRS-2676: Start of 'ggateapp' on 'rhel11gr2rac2' succeeded
-- Lets check the gg process on node 2.
[oracle@rhel11gr2rac2 golden_gate]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (rhel11gr2rac2.manzoor.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:04
EXTRACT RUNNING XTBLS 00:00:05 00:00:06
[oracle@rhel11gr2rac1 bin]$ ./crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on rhel11gr2rac2
-- Now lets check how the failover is working.
Lets crash the node 2...(power of from vmware home)
Below are the staus after the node 2 is down....
Cluster Resources
--------------------------------------------------------------------------------
ggateapp
1 ONLINE OFFLINE
goldengate-vip
1 ONLINE OFFLINE STARTING
Cluster Resources
--------------------------------------------------------------------------------
ggateapp
1 ONLINE ONLINE rhel11gr2rac1
goldengate-vip
1 ONLINE ONLINE rhel11gr2rac1
-- Could see the ggateapp resource and goldengate-vip has been failed over from
node 2 to node 1...
Below is the output from ggsci.
GGSCI (rhel11gr2rac1.manzoor.com) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED PTBLS 00:00:00 00:04:10
EXTRACT RUNNING XTBLS 00:00:00 00:00:09
-- The manager and the extract process are started but the pump extract has been abended with the below
error.
2013-10-02 22:20:56 ERROR OGG-01031 There is a problem in network communication, a remote file problem, encryption keys for target and source do not matc
h (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file "./dirdat/XT000016" (error 11, Resource temporarily unavailable)).
2013-10-02 22:20:56 ERROR OGG-01668 PROCESS ABENDING.
Source --
GGSCI (rhel11gr2rac1.manzoor.com) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED PTBLS 00:00:00 00:08:18
EXTRACT RUNNING XTBLS 00:00:00 00:00:00
GGSCI (rhel11gr2rac1.manzoor.com) 18> info PTBLS
EXTRACT PTBLS Last Started 2013-10-02 22:24 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:08:24 ago)
Log Read Checkpoint File ./dirdat/XT000019
2013-10-02 22:16:24.415084 RBA 1111
Target --
GGSCI (standalone2.manzoor.com) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTBLS 00:00:00 00:00:05
GGSCI (standalone2.manzoor.com) 3> info rtbls
REPLICAT RTBLS Last Started 2013-10-02 20:38 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File ./dirdat/XT000016
2013-10-02 22:16:24.422522 RBA 1991
GGSCI (standalone2.manzoor.com) 4> send rtbls status
Sending STATUS request to REPLICAT RTBLS ...
Current status: At EOF
Sequence #: 16
RBA: 1991
0 records in current transaction
--- The replicat process shows it is completed all the data and currently it is at end of file.
-- Now we will do an et (extact trail) rollover on the source and
Source--
GGSCI (rhel11gr2rac1.manzoor.com) 21> alter extract ptbls etrollover
2013-10-02 22:34:04 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after
starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.
GGSCI (rhel11gr2rac1.manzoor.com) 23> start extract ptbls
Sending START request to MANAGER ...
EXTRACT PTBLS starting
GGSCI (rhel11gr2rac1.manzoor.com) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:58
EXTRACT RUNNING XTBLS 00:00:00 00:00:08
GGSCI (rhel11gr2rac1.manzoor.com) 31> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PTBLS 00:00:00 00:00:05
EXTRACT RUNNING XTBLS 00:00:00 00:00:00
GGSCI (rhel11gr2rac1.manzoor.com) 32> info ptbls
EXTRACT PTBLS Last Started 2013-10-02 22:35 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File ./dirdat/XT000020
2013-10-02 22:20:46.216461 RBA 1111
--- Target
in target we need to move the replicat to start again from the next sequnce since we have
did an ET rollover on the source.
GGSCI (standalone2.manzoor.com) 3> stop replicat rtbls
Sending STOP request to REPLICAT RTBLS ...
Request processed.
GGSCI (standalone2.manzoor.com) 4> alter replicat rtbls extseqno 17 extrba 0
REPLICAT altered.
GGSCI (standalone2.manzoor.com) 5> start replicat rtbls
-- Now lets update some data on source.
SQL> select count(*) from emp;
COUNT(*)
----------
4000
SQL> begin
2 for i in 4001..5000 loop
3 insert into emp values (i, dbms_random.string('U',30),30);
4 END LOOP;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
5000
-- Lets check whether it replicated to target.
SQL> select count(*) from emp;
COUNT(*)
----------
5000
---- Reference -- Oracle White Paper—Oracle GoldenGate high availability with Oracle Clusterware
Note - 1527310.1,
