Category Archives: GoldenGate

Timeline GoldenGate Build Tasks

Request storage participant
Request network participant
Identify servers for testing traffic remote extract / apply
“Identify locations and IP addresses for “”ALL”” severs Network Eng. (21 servers 7 clusters)”
Server receipt
Request SCAN Ips for clusters (7 clusters) ROARS IPAM NRF
Request VIPs for node listeners (21 nodes) ROARS IPAM NRF
Request VIPs for server sets that will use XAG (7 clusters) ROARS IPAM NRF
Develop & submit request to storage team for all disk devices (LUNs) by location
Request disk devices for all clusters (OSRF)
Allocate disk for all clusters

Cluster Tasks
Cluster Build 1P LL (3 nodes) –
Cluster QA
Create DBFS database (3 nodes)
Create filesystems on DBFS database (3 nodes)
Create database on standby – LL Set02
Test failover configuration dataguard w/o GG and XAG
Install GG binaries on LL Set01 (3 nodes)
Install XAG binaries on LL Set01 (3 nodes)
Install XAG onto cluster using VIPs created for GG
Define failover sequence for local failover
Test local node failover of Node 01
Test local node failover of Node 02
Test local node failover of Node 03
Test DBFS dataguard failover w/GG and XAG

GG Process

<div class="container">
<div class="line number1 index0 alt2"><code class="plain plain">#!/bin/bash</code></div>
<div class="line number2 index1 alt1"></div>
<div class="line number3 index2 alt2"><code class="plain plain">###############################</code></div>
<div class="line number4 index3 alt1"><code class="plain plain"># determine the OS type</code></div>
<div class="line number5 index4 alt2"><code class="plain plain">###############################</code></div>
<div class="line number6 index5 alt1"><code class="plain plain">OSNAME=`uname`</code></div>
<div class="line number7 index6 alt2"></div>
<div class="line number8 index7 alt1"><code class="plain plain">case "$OSNAME" in</code></div>
<div class="line number9 index8 alt2"><code class="plain spaces">  </code><code class="plain plain">"SunOS")</code></div>
<div class="line number10 index9 alt1"><code class="plain spaces">    </code><code class="plain plain">echo "OSNAME = $OSNAME"</code></div>
<div class="line number11 index10 alt2"><code class="plain spaces">    </code><code class="plain plain">;;</code></div>
<div class="line number12 index11 alt1"><code class="plain spaces">  </code><code class="plain plain">"Linux")</code></div>
<div class="line number13 index12 alt2"><code class="plain spaces">    </code><code class="plain plain">echo "OSNAME = $OSNAME"</code></div>
<div class="line number14 index13 alt1"><code class="plain spaces">    </code><code class="plain plain">;;</code></div>
<div class="line number15 index14 alt2"><code class="plain spaces">  </code><code class="plain plain">"*")</code></div>
<div class="line number16 index15 alt1"><code class="plain spaces">    </code><code class="plain plain">echo "This script has not been verified on $OSNAME"</code></div>
<div class="line number17 index16 alt2"><code class="plain spaces">    </code><code class="plain plain">exit 1</code></div>
<div class="line number18 index17 alt1"><code class="plain spaces">    </code><code class="plain plain">;;</code></div>
<div class="line number19 index18 alt2"><code class="plain plain">esac</code></div>
<div class="line number20 index19 alt1"></div>
<div class="line number21 index20 alt2"><code class="plain plain">###############################</code></div>
<div class="line number22 index21 alt1"><code class="plain plain"># set the temp file</code></div>
<div class="line number23 index22 alt2"><code class="plain plain">###############################</code></div>
<div class="line number24 index23 alt1"><code class="plain plain">TMPFILE=/tmp/pmem.tmp</code></div>
<div class="line number25 index24 alt2"><code class="plain plain">if [ -f $TMPFILE ]</code></div>
<div class="line number26 index25 alt1"><code class="plain plain">then</code></div>
<div class="line number27 index26 alt2"><code class="plain spaces">  </code><code class="plain plain">rm -f $TMPFILE</code></div>
<div class="line number28 index27 alt1"><code class="plain plain">fi</code></div>
<div class="line number29 index28 alt2"></div>
<div class="line number30 index29 alt1"><code class="plain plain">################################</code></div>
<div class="line number31 index30 alt2"><code class="plain plain"># loop over the gg process types</code></div>
<div class="line number32 index31 alt1"><code class="plain plain">################################</code></div>
<div class="line number33 index32 alt2"><code class="plain plain">PROCESSES="extract replicat"</code></div>
<div class="line number34 index33 alt1"></div>
<div class="line number35 index34 alt2"><code class="plain plain">for PROCESS in $PROCESSES</code></div>
<div class="line number36 index35 alt1"><code class="plain plain">do</code></div>
<div class="line number37 index36 alt2"><code class="plain spaces">  </code><code class="plain plain">FLAG=""</code></div>
<div class="line number38 index37 alt1"><code class="plain spaces">  </code><code class="plain plain">FLAG=`ps -ef | grep $PROCESS`</code></div>
<div class="line number39 index38 alt2"><code class="plain spaces">  </code><code class="plain plain">if [ -z "FLAG" ]</code></div>
<div class="line number40 index39 alt1"><code class="plain spaces">  </code><code class="plain plain">then</code></div>
<div class="line number41 index40 alt2"><code class="plain spaces">    </code><code class="plain plain">echo "No $PROCESS processes found"</code></div>
<div class="line number42 index41 alt1"><code class="plain spaces">  </code><code class="plain plain">else</code></div>
<div class="line number43 index42 alt2"><code class="plain spaces">    </code><code class="plain plain">echo</code></div>
<div class="line number44 index43 alt1"><code class="plain spaces">    </code><code class="plain plain">echo "#####################################"</code></div>
<div class="line number45 index44 alt2"><code class="plain spaces">    </code><code class="plain plain">echo "# Individual $PROCESS Process Usage #"</code></div>
<div class="line number46 index45 alt1"><code class="plain spaces">    </code><code class="plain plain">echo "#####################################"</code></div>
<div class="line number47 index46 alt2"><code class="plain spaces">    </code><code class="plain plain">case "$OSNAME" in</code></div>
<div class="line number48 index47 alt1"><code class="plain spaces">      </code><code class="plain plain">"Linux")</code></div>
<div class="line number49 index48 alt2"><code class="plain spaces">        </code><code class="plain plain">ps -C $PROCESS -O rss > $TMPFILE</code></div>
<div class="line number50 index49 alt1"><code class="plain spaces">        </code><code class="plain plain">cat $TMPFILE | grep $PROCESS | awk '{print $2/1024, "MB", $12}' | sort -k 2</code></div>
<div class="line number51 index50 alt2"><code class="plain spaces">        </code><code class="plain plain">;;</code></div>
<div class="line number52 index51 alt1"><code class="plain spaces">      </code><code class="plain plain">"SunOS")</code></div>
<div class="line number53 index52 alt2"><code class="plain spaces">        </code><code class="plain plain">ps -efo vsz,uid,pid,ppid,pcpu,args | grep -v grep | grep $PROCESS > $TMPFILE</code></div>
<div class="line number54 index53 alt1"><code class="plain spaces">        </code><code class="plain plain">cat $TMPFILE | grep $PROCESS | awk '{print $1/1024, "MB", $8}' | sort -k 2</code></div>
<div class="line number55 index54 alt2"><code class="plain spaces">        </code><code class="plain plain">;;</code></div>
<div class="line number56 index55 alt1"><code class="plain spaces">      </code><code class="plain plain">"*")</code></div>
<div class="line number57 index56 alt2"><code class="plain spaces">        </code><code class="plain plain">echo "This script has not been verified on $OSNAME"</code></div>
<div class="line number58 index57 alt1"><code class="plain spaces">        </code><code class="plain plain">exit 1</code></div>
<div class="line number59 index58 alt2"><code class="plain spaces">        </code><code class="plain plain">;;</code></div>
<div class="line number60 index59 alt1"><code class="plain spaces">    </code><code class="plain plain">esac</code></div>
<div class="line number61 index60 alt2"><code class="plain spaces">    </code><code class="plain plain">rm -f $TMPFILE</code></div>
<div class="line number62 index61 alt1"></div>
<div class="line number63 index62 alt2"><code class="plain spaces">    </code><code class="plain plain">echo</code></div>
<div class="line number64 index63 alt1"><code class="plain spaces">    </code><code class="plain plain">echo "#####################################"</code></div>
<div class="line number65 index64 alt2"><code class="plain spaces">    </code><code class="plain plain">echo "#   Total $PROCESS Process Usage    #"</code></div>
<div class="line number66 index65 alt1"><code class="plain spaces">    </code><code class="plain plain">echo "#####################################"</code></div>
<div class="line number67 index66 alt2"><code class="plain spaces">    </code><code class="plain plain">case "$OSNAME" in</code></div>
<div class="line number68 index67 alt1"><code class="plain spaces">      </code><code class="plain plain">"Linux")</code></div>
<div class="line number69 index68 alt2"><code class="plain spaces">        </code><code class="plain plain">ps -C $PROCESS -O rss > $TMPFILE</code></div>
<div class="line number70 index69 alt1"><code class="plain spaces">        </code><code class="plain plain">cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$2; } END \</code></div>
<div class="line number71 index70 alt2"><code class="plain spaces">          </code><code class="plain plain">{ print "Number of processes      =",count; \</code></div>
<div class="line number72 index71 alt1"><code class="plain spaces">          </code><code class="plain plain">print "AVG Memory usage/process =",sum/1024/count, "MB"; \</code></div>
<div class="line number73 index72 alt2"><code class="plain spaces">          </code><code class="plain plain">print "Total memory usage       =", sum/1024,  " MB"}'</code></div>
<div class="line number74 index73 alt1"><code class="plain spaces">        </code><code class="plain plain">;;</code></div>
<div class="line number75 index74 alt2"><code class="plain spaces">      </code><code class="plain plain">"SunOS")</code></div>
<div class="line number76 index75 alt1"><code class="plain spaces">        </code><code class="plain plain">ps -efo vsz,uid,pid,ppid,pcpu,comm | grep -v grep | grep $PROCESS > $TMPFILE</code></div>
<div class="line number77 index76 alt2"><code class="plain spaces">        </code><code class="plain plain">cat $TMPFILE | awk '{count ++; sum=sum+$1; } END \</code></div>
<div class="line number78 index77 alt1"><code class="plain spaces">          </code><code class="plain plain">{ print "Number of processes      =",count; \</code></div>
<div class="line number79 index78 alt2"><code class="plain spaces">          </code><code class="plain plain">print "AVG Memory usage/process =",sum/1024/count, "MB"; \</code></div>
<div class="line number80 index79 alt1"><code class="plain spaces">          </code><code class="plain plain">print "Total memory usage       =", sum/1024,  " MB"}'</code></div>
<div class="line number81 index80 alt2"><code class="plain spaces">        </code><code class="plain plain">;;</code></div>
<div class="line number82 index81 alt1"><code class="plain spaces">      </code><code class="plain plain">"*")</code></div>
<div class="line number83 index82 alt2"><code class="plain spaces">        </code><code class="plain plain">echo "This script has not been verified on $OSNAME"</code></div>
<div class="line number84 index83 alt1"><code class="plain spaces">        </code><code class="plain plain">exit 1</code></div>
<div class="line number85 index84 alt2"><code class="plain spaces">        </code><code class="plain plain">;;</code></div>
<div class="line number86 index85 alt1"><code class="plain spaces">    </code><code class="plain plain">esac</code></div>
<div class="line number87 index86 alt2"><code class="plain spaces">    </code><code class="plain plain">rm -f $TMPFILE</code></div>
<div class="line number88 index87 alt1"><code class="plain spaces">  </code><code class="plain plain">fi</code></div>
<div class="line number89 index88 alt2"><code class="plain plain">done</code></div>
<div class="line number90 index89 alt1"></div>
<div class="line number91 index90 alt2"><code class="plain plain">exit 0</code></div>
<div class="line number91 index90 alt2"></div>
</div>

GoldenGate Checkpoints

When working with Oracle GoldenGate, you might need to refer to the checkpoints that are made by a process. Checkpoints save the state of the process for recovery purposes. Extracts and Replicats use checkpoints.

Extract checkpoint positions are composed of read checkpoints in the data source and write checkpoints in the trail. The following is a sampling of checkpoint information displayed with the INFO EXTRACT command with the SHOWCH option. In this case, the data source is an Oracle RAC database cluster, so there is thread information included in the output. You can view past checkpoints by specifying the number of them that you want to view after the SHOWCH argument.

gg_ext_stat


gg_ext_stat()
{

ext=$1

# read the environment file for gg here
# . /var/opt/oracle/.ggsora12_env
#
# info extract <extract name>
# send EP1XXXDM, report

echo
echo "Extract status................."
echo

/oracle/product/12.1/gg_1/ggsci <<EOF

sh date

send ${ext}, status

EOF

}

gg_zipfile_chk

 


gg_zipfile_chk()

# This assumes a standard directory of 
# /ora01/Oracle_SW/gg121 for the distribution media

{
#######################################################
# If the directory exists see if the files is there
#######################################################
clear

echo
echo "Checking for the V46695-01.zip file"
echo

echo
echo "Checking directory /ora01/Oracle_SW/gg121 for zip file ......."
echo

if [ -f /ora01/Oracle_SW/gg121/V46695-01.zip ]; then
echo "..../ora01/Oracle_SW/gg121/V46695-01.zip file found....."
else
echo "..../ora01/Oracle_SW/gg121/V46695-01.zip file not found."
fi

echo
echo "Checking directory /ora01/Oracle_SW/1212111/gg121 for zip file ......."
echo

if [ -f /ora01/Oracle_SW/gg121/1212111/V46695-01.zip ]; then
echo "..../ora01/Oracle_SW/gg121/1212111/V46695-01.zip file found....."
else
echo "..../ora01/Oracle_SW/gg121/1212111/V46695-01.zip file not found."
fi

echo
echo "Checking for the xagpack_7b.zip file"
echo

echo
echo "Checking /ora01/Oracle_SW/xag71 for zip file ......."
echo

if [ -f /ora01/Oracle_SW/xag71/xagpack_7b.zip ]; then
echo "..../ora01/Oracle_SW/xag71/xagpack_7b.zip file found....."
else
echo "..../ora01/Oracle_SW/xag71/xagpack_7b.zip file not found."
fi

}

GoldenGate Facts

When a transaction is committed on the source database, only new data is written to the Redo log. However for Oracle to apply these transactions on the destination database, the “before image” key values are required to identify the effected rows. This data is also placed in the trail file and used to identify the rows on the destination, using the key value the transactions are executed against them. This is a key consideration for data sizing for the trail files.

Important considerations for bi-directional replication

The customer should consider the following points in an active-active replication environment.

  • Primary Key: Helps to identify conflicts and Resolve them.
  • Sequences: Are not supported. The work around is use to use odd/even, range or concatenate sequences.
  • Triggers: These should be disabled or suppressed to avoid using uniqueness issue
  • Data Looping: This can be easily avoided using OGG itself
  • LAG: This should be minimized. If a customer says that there will not be any LAG due to network or huge load, then we don’t need to deploy CRDs. But this is not the case always as there would be some LAG and these can cause conflicts.
  • CDR (Conflict Detection & Resolution): OGG has built in CDRs for all kind of DMLs that can be used to detect and resolve them.
  • Packaged Application: These are not supported as it may contain data types which are not support by OGG or it might not allow the application modification to work with OGG.

OGG can be installed on ACFS

No, OGG binaries are not supported on DBFS. You can however store parameter files, data files (trail files), and checkpoint files on DBFS.

Goldengate Replication Setup Requirements

Operating System
a. Memory – Ensure that there is enough memory. This will determined by the number of GoldenGate processes which will be configured on the server.

b. Memory Per Process – Dependent on the size of the transactions and the amount of concurrent transactions in the database. By default one Goldengate extract or replicat process can take up to 8G of memory.

c. Swap space – Configure reasonable swap space in case Goldengate processes swap due to low memory condition.

d. Disk space – Enough disk space to hold the trail files for the required retention period. As a starting point it could be made approximately the same as the amount of archive logs generated in a specific time period. Also consider the amount of space that might be needed for an initial load.