Category Archives: Exadata

cell_iops.ksh Cell Node IOPS script

#!/bin/ksh
#
# cell_iops.sh - a "sort of" end to end Exadata IO monitoring script
#     * inspired by http://glennfawcett.wordpress.com/2013/06/18/analyzing-io-at-the-exadata-cell-level-a-simple-tool-for-iops/
#       and modified to show end to end breakdown of IOPS, inter-database, consumer groups, and latency across Exadata storage cells
#     * you must use this script together with "iostat -xmd" on storage cells on both flash and spinning disk and database IO latency on 
#       system level (AWR) and session level (Tanel Poder's snapper) for a "real" end to end IO troubleshooting and monitoring
#     * the inter-database and consumer groups data is very useful for overall resource management and IORM configuration and troubleshooting 
#     * check out the sample viz that can be done by mining the data here goo.gl/0Q1Oeo
#
# Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE, OakTable
# http://karlarao.wordpress.com
#
# on any Exadata storage cell node you can run this one time
#     ./cell_iops.sh
#
# OR on loop spooling to a file and consume later with Tableau for visualization
#     while :; do ./cell_iops.sh >> cell_iops.csv ; egrep "CS,ALL|DB,_OTHER_DATABASE_" cell_iops.csv ; sleep 60; echo "--"; done
#
# Here are the 19 column headers:
#
#     TM             - the time on each snap
#     CATEGORY       - CS (cell server - includes IOPS, MBs, R+W breakdown, latency), DB (database - IOPS, MBs), CG (consumer group - IOPS, MBs)
#     GROUP          - grouping per CATEGORY, it could be databases or consumer groups.. a pretty useful dimension in Tableau to drill down on IO
#     DISK_IOPS      - (applies to CS, DB, CG) high level spinning disk IOPS
#     FLASH_IOPS     - (applies to CS, DB, CG) high level flash disk IOPS
#     DISK_MBS       - (applies to CS, DB, CG) high level spinning disk MB/s (bandwidth)
#     FLASH_MBS      - (applies to CS, DB, CG) high level flash disk MB/s (bandwidth)
#     DISK_IOPS_R    - (applies to CS only) IOPS breakdown, spinning disk IOPS read
#     FLASH_IOPS_R   - (applies to CS only) IOPS breakdown, flash disk IOPS read
#     DISK_IOPS_W    - (applies to CS only) IOPS breakdown, spinning disk IOPS write
#     FLASH_IOPS_W   - (applies to CS only) IOPS breakdown, flash disk IOPS write
#     DLAT_RLG       - (applies to CS only) average latency breakdown, spinning disk large reads
#     FLAT_RLG       - (applies to CS only) average latency breakdown, flash disk large reads
#     DLAT_RSM       - (applies to CS only) average latency breakdown, spinning disk small reads
#     FLAT_RSM       - (applies to CS only) average latency breakdown, flash disk small reads
#     DLAT_WLG       - (applies to CS only) average latency breakdown, spinning disk large writes
#     FLAT_WLG       - (applies to CS only) average latency breakdown, flash disk large writes
#     DLAT_WSM       - (applies to CS only) average latency breakdown, spinning disk small writes
#     FLAT_WSM       - (applies to CS only) average latency breakdown, flash disk small writes
#
 
 
datafile=`echo /tmp/metriccurrentall.txt`
/usr/local/bin/dcli -l root -g /root/cell_group "cellcli -e list metriccurrent" > $datafile
export TM=$(date +%m/%d/%y" "%H:%M:%S)
 
 
# Header
print "TM,CATEGORY,GROUP,DISK_IOPS,FLASH_IOPS,DISK_MBS,FLASH_MBS,DISK_IOPS_R,FLASH_IOPS_R,DISK_IOPS_W,FLASH_IOPS_W,DLAT_RLG,FLAT_RLG,DLAT_RSM,FLAT_RSM,DLAT_WLG,FLAT_WLG,DLAT_WSM,FLAT_WSM"
 
#######################################
# extract IOPS for cells
#######################################
export DRW=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FRW=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DRWM=`cat $datafile | egrep  'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FRWM=`cat $datafile | egrep  'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DR=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FR=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DW=`cat $datafile | egrep  'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FW=`cat $datafile | egrep  'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DLATRLG=`cat $datafile | egrep  'CD_IO_TM_R_LG_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATRLG=`cat $datafile | egrep  'CD_IO_TM_R_LG_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
export DLATRSM=`cat $datafile | egrep  'CD_IO_TM_R_SM_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATRSM=`cat $datafile | egrep  'CD_IO_TM_R_SM_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
export DLATWLG=`cat $datafile | egrep  'CD_IO_TM_W_LG_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATWLG=`cat $datafile | egrep  'CD_IO_TM_W_LG_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
export DLATWSM=`cat $datafile | egrep  'CD_IO_TM_W_SM_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATWSM=`cat $datafile | egrep  'CD_IO_TM_W_SM_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
print "$TM,CS,ALL,$DRW,$FRW,$DRWM,$FRWM,$DR,$FR,$DW,$FW,$DLATRLG,$FLATRLG,$DLATRSM,$FLATRSM,$DLATWLG,$FLATWLG,$DLATWSM,$FLATWSM"
 
 
#######################################
# extract IOPS for database
#######################################
export db_str=`cat $datafile | egrep 'DB_FD_IO_RQ_LG_SEC' | grep -v DBUA | awk '{ print $3}' | sort | uniq`
 
for db_name in `echo $db_str`
do
  # Calculate Total IOPS of harddisk
  # DB_IO_RQ_LG_SEC
  # DB_IO_RQ_SM_SEC
  db_drw=`cat $datafile | egrep 'DB_IO_RQ_LG_SEC|DB_IO_RQ_SM_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total IOPS of flashdisk
  # DB_FD_IO_RQ_LG_SEC
  # DB_FD_IO_RQ_SM_SEC
  db_frw=`cat $datafile | egrep 'DB_FD_IO_RQ_LG_SEC|DB_FD_IO_RQ_SM_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total MB/s of harddisk
  # DB_IO_BY_SEC
  db_drwm=`cat $datafile | egrep 'DB_IO_BY_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total MB/s of flashdisk
  # DB_FC_IO_BY_SEC
  # DB_FD_IO_BY_SEC
  # DB_FL_IO_BY_SEC
  db_frwm=`cat $datafile | egrep 'DB_FC_IO_BY_SEC|DB_FD_IO_BY_SEC|DB_FL_IO_BY_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  print "$TM,DB,$db_name,$db_drw,$db_frw,$db_drwm,$db_frwm,0,0,0,0,0,0,0,0,0,0,0,0"
 
done
 
 
#######################################
# extract IOPS for DBRM consumer groups
#######################################
export cg_str=`cat $datafile | egrep 'CG_FD_IO_RQ_LG_SEC' | grep -v DBUA | awk '{ print $3}' | sort | uniq`
 
for cg_name in `echo $cg_str`
do
 
  # Calculate Total IOPS of harddisk
  # CG_IO_RQ_LG_SEC
  # CG_IO_RQ_SM_SEC
  cg_drw=`cat $datafile | egrep 'CG_IO_RQ_LG_SEC|CG_IO_RQ_SM_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total IOPS of flashdisk
  # CG_FD_IO_RQ_LG_SEC
  # CG_FD_IO_RQ_SM_SEC
  cg_frw=`cat $datafile | egrep 'CG_FD_IO_RQ_LG_SEC|CG_FD_IO_RQ_SM_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
 
  # Calculate Total MB/s of harddisk
  # CG_IO_BY_SEC
  cg_drwm=`cat $datafile | egrep 'CG_IO_BY_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total MB/s of flashdisk
  # CG_FC_IO_BY_SEC
  # CG_FD_IO_BY_SEC
  cg_frwm=`cat $datafile | egrep 'CG_FC_IO_BY_SEC|CG_FD_IO_BY_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  print "$TM,CG,$cg_name,$cg_drw,$cg_frw,$cg_drwm,$cg_frwm,0,0,0,0,0,0,0,0,0,0,0,0"
 
done

cell_iops.ksh function

<div class="container">
<div class="line number1 index0 alt2"><code class="bash preprocessor bold">#!/bin/ksh</code></div>
<div class="line number2 index1 alt1"><code class="bash comments">#</code></div>
<div class="line number3 index2 alt2"><code class="bash comments"># cell_iops.sh - a "sort of" end to end Exadata IO monitoring script</code></div>
<div class="line number4 index3 alt1"><code class="bash comments">#     * inspired by http://glennfawcett.wordpress.com/2013/06/18/analyzing-io-at-the-exadata-cell-level-a-simple-tool-for-iops/</code></div>
<div class="line number5 index4 alt2"><code class="bash comments">#       and modified to show end to end breakdown of IOPS, inter-database, consumer groups, and latency across Exadata storage cells</code></div>
<div class="line number6 index5 alt1"><code class="bash comments">#     * you must use this script together with "iostat -xmd" on storage cells on both flash and spinning disk and database IO latency on </code></div>
<div class="line number7 index6 alt2"><code class="bash comments">#       system level (AWR) and session level (Tanel Poder's snapper) for a "real" end to end IO troubleshooting and monitoring</code></div>
<div class="line number8 index7 alt1"><code class="bash comments">#     * the inter-database and consumer groups data is very useful for overall resource management and IORM configuration and troubleshooting </code></div>
<div class="line number9 index8 alt2"><code class="bash comments">#     * check out the sample viz that can be done by mining the data here goo.gl/0Q1Oeo</code></div>
<div class="line number10 index9 alt1"><code class="bash comments">#</code></div>
<div class="line number11 index10 alt2"><code class="bash comments"># Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE, OakTable</code></div>
<div class="line number12 index11 alt1"><code class="bash comments"># http://karlarao.wordpress.com</code></div>
<div class="line number13 index12 alt2"><code class="bash comments">#</code></div>
<div class="line number14 index13 alt1"><code class="bash comments"># on any Exadata storage cell node you can run this one time</code></div>
<div class="line number15 index14 alt2"><code class="bash comments">#     ./cell_iops.sh</code></div>
<div class="line number16 index15 alt1"><code class="bash comments">#</code></div>
<div class="line number17 index16 alt2"><code class="bash comments"># OR on loop spooling to a file and consume later with Tableau for visualization</code></div>
<div class="line number18 index17 alt1"><code class="bash comments">#     while :; do ./cell_iops.sh >> cell_iops.csv ; egrep "CS,ALL|DB,_OTHER_DATABASE_" cell_iops.csv ; sleep 60; echo "--"; done</code></div>
<div class="line number19 index18 alt2"><code class="bash comments">#</code></div>
<div class="line number20 index19 alt1"><code class="bash comments"># Here are the 19 column headers:</code></div>
<div class="line number21 index20 alt2"><code class="bash comments">#</code></div>
<div class="line number22 index21 alt1"><code class="bash comments">#     TM             - the time on each snap</code></div>
<div class="line number23 index22 alt2"><code class="bash comments">#     CATEGORY       - CS (cell server - includes IOPS, MBs, R+W breakdown, latency), DB (database - IOPS, MBs), CG (consumer group - IOPS, MBs)</code></div>
<div class="line number24 index23 alt1"><code class="bash comments">#     GROUP          - grouping per CATEGORY, it could be databases or consumer groups.. a pretty useful dimension in Tableau to drill down on IO</code></div>
<div class="line number25 index24 alt2"><code class="bash comments">#     DISK_IOPS      - (applies to CS, DB, CG) high level spinning disk IOPS</code></div>
<div class="line number26 index25 alt1"><code class="bash comments">#     FLASH_IOPS     - (applies to CS, DB, CG) high level flash disk IOPS</code></div>
<div class="line number27 index26 alt2"><code class="bash comments">#     DISK_MBS       - (applies to CS, DB, CG) high level spinning disk MB/s (bandwidth)</code></div>
<div class="line number28 index27 alt1"><code class="bash comments">#     FLASH_MBS      - (applies to CS, DB, CG) high level flash disk MB/s (bandwidth)</code></div>
<div class="line number29 index28 alt2"><code class="bash comments">#     DISK_IOPS_R    - (applies to CS only) IOPS breakdown, spinning disk IOPS read</code></div>
<div class="line number30 index29 alt1"><code class="bash comments">#     FLASH_IOPS_R   - (applies to CS only) IOPS breakdown, flash disk IOPS read</code></div>
<div class="line number31 index30 alt2"><code class="bash comments">#     DISK_IOPS_W    - (applies to CS only) IOPS breakdown, spinning disk IOPS write</code></div>
<div class="line number32 index31 alt1"><code class="bash comments">#     FLASH_IOPS_W   - (applies to CS only) IOPS breakdown, flash disk IOPS write</code></div>
<div class="line number33 index32 alt2"><code class="bash comments">#     DLAT_RLG       - (applies to CS only) average latency breakdown, spinning disk large reads</code></div>
<div class="line number34 index33 alt1"><code class="bash comments">#     FLAT_RLG       - (applies to CS only) average latency breakdown, flash disk large reads</code></div>
<div class="line number35 index34 alt2"><code class="bash comments">#     DLAT_RSM       - (applies to CS only) average latency breakdown, spinning disk small reads</code></div>
<div class="line number36 index35 alt1"><code class="bash comments">#     FLAT_RSM       - (applies to CS only) average latency breakdown, flash disk small reads</code></div>
<div class="line number37 index36 alt2"><code class="bash comments">#     DLAT_WLG       - (applies to CS only) average latency breakdown, spinning disk large writes</code></div>
<div class="line number38 index37 alt1"><code class="bash comments">#     FLAT_WLG       - (applies to CS only) average latency breakdown, flash disk large writes</code></div>
<div class="line number39 index38 alt2"><code class="bash comments">#     DLAT_WSM       - (applies to CS only) average latency breakdown, spinning disk small writes</code></div>
<div class="line number40 index39 alt1"><code class="bash comments">#     FLAT_WSM       - (applies to CS only) average latency breakdown, flash disk small writes</code></div>
<div class="line number41 index40 alt2"><code class="bash comments">#</code></div>
<div class="line number42 index41 alt1"></div>
<div class="line number43 index42 alt2"></div>
<div class="line number44 index43 alt1"><code class="bash plain">datafile=`</code><code class="bash functions">echo</code> <code class="bash plain">/tmp/metriccurrentall</code><code class="bash plain">.txt`</code></div>
<div class="line number45 index44 alt2"><code class="bash plain">/usr/local/bin/dcli</code> <code class="bash plain">-l root -g </code><code class="bash plain">/root/cell_group</code> <code class="bash string">"cellcli -e list metriccurrent"</code> <code class="bash plain">> $datafile</code></div>
<div class="line number46 index45 alt1"><code class="bash functions">export</code> <code class="bash plain">TM=$(</code><code class="bash functions">date</code> <code class="bash plain">+%m/%d/%y</code><code class="bash string">" "</code><code class="bash plain">%H:%M:%S)</code></div>
<div class="line number47 index46 alt2"></div>
<div class="line number48 index47 alt1"></div>
<div class="line number49 index48 alt2"><code class="bash comments"># Header</code></div>
<div class="line number50 index49 alt1"><code class="bash plain">print </code><code class="bash string">"TM,CATEGORY,GROUP,DISK_IOPS,FLASH_IOPS,DISK_MBS,FLASH_MBS,DISK_IOPS_R,FLASH_IOPS_R,DISK_IOPS_W,FLASH_IOPS_W,DLAT_RLG,FLAT_RLG,DLAT_RSM,FLAT_RSM,DLAT_WLG,FLAT_WLG,DLAT_WSM,FLAT_WSM"</code></div>
<div class="line number51 index50 alt2"></div>
<div class="line number52 index51 alt1"><code class="bash comments">#######################################</code></div>
<div class="line number53 index52 alt2"><code class="bash comments"># extract IOPS for cells</code></div>
<div class="line number54 index53 alt1"><code class="bash comments">#######################################</code></div>
<div class="line number55 index54 alt2"><code class="bash functions">export</code> <code class="bash plain">DRW=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number56 index55 alt1"><code class="bash functions">export</code> <code class="bash plain">FRW=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number57 index56 alt2"></div>
<div class="line number58 index57 alt1"><code class="bash functions">export</code> <code class="bash plain">DRWM=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number59 index58 alt2"><code class="bash functions">export</code> <code class="bash plain">FRWM=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number60 index59 alt1"></div>
<div class="line number61 index60 alt2"><code class="bash functions">export</code> <code class="bash plain">DR=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number62 index61 alt1"><code class="bash functions">export</code> <code class="bash plain">FR=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number63 index62 alt2"></div>
<div class="line number64 index63 alt1"><code class="bash functions">export</code> <code class="bash plain">DW=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number65 index64 alt2"><code class="bash functions">export</code> <code class="bash plain">FW=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number66 index65 alt1"></div>
<div class="line number67 index66 alt2"><code class="bash functions">export</code> <code class="bash plain">DLATRLG=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_R_LG_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number68 index67 alt1"><code class="bash functions">export</code> <code class="bash plain">FLATRLG=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_R_LG_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number69 index68 alt2"></div>
<div class="line number70 index69 alt1"><code class="bash functions">export</code> <code class="bash plain">DLATRSM=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_R_SM_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number71 index70 alt2"><code class="bash functions">export</code> <code class="bash plain">FLATRSM=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_R_SM_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number72 index71 alt1"></div>
<div class="line number73 index72 alt2"><code class="bash functions">export</code> <code class="bash plain">DLATWLG=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_W_LG_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number74 index73 alt1"><code class="bash functions">export</code> <code class="bash plain">FLATWLG=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_W_LG_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number75 index74 alt2"></div>
<div class="line number76 index75 alt1"><code class="bash functions">export</code> <code class="bash plain">DLATWSM=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_W_SM_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number77 index76 alt2"><code class="bash functions">export</code> <code class="bash plain">FLATWSM=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code>  <code class="bash string">'CD_IO_TM_W_SM_RQ'</code> <code class="bash plain">|</code><code class="bash functions">grep</code>  <code class="bash plain">FD_ |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'</code><code class="bash plain">`</code></div>
<div class="line number78 index77 alt1"></div>
<div class="line number79 index78 alt2"><code class="bash plain">print </code><code class="bash string">"$TM,CS,ALL,$DRW,$FRW,$DRWM,$FRWM,$DR,$FR,$DW,$FW,$DLATRLG,$FLATRLG,$DLATRSM,$FLATRSM,$DLATWLG,$FLATWLG,$DLATWSM,$FLATWSM"</code></div>
<div class="line number80 index79 alt1"></div>
<div class="line number81 index80 alt2"></div>
<div class="line number82 index81 alt1"><code class="bash comments">#######################################</code></div>
<div class="line number83 index82 alt2"><code class="bash comments"># extract IOPS for database</code></div>
<div class="line number84 index83 alt1"><code class="bash comments">#######################################</code></div>
<div class="line number85 index84 alt2"><code class="bash functions">export</code> <code class="bash plain">db_str=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'DB_FD_IO_RQ_LG_SEC'</code> <code class="bash plain">| </code><code class="bash functions">grep</code> <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">DBUA | </code><code class="bash functions">awk</code> <code class="bash string">'{ print $3}'</code> <code class="bash plain">| </code><code class="bash functions">sort</code> <code class="bash plain">| </code><code class="bash functions">uniq</code><code class="bash plain">`</code></div>
<div class="line number86 index85 alt1"></div>
<div class="line number87 index86 alt2"><code class="bash keyword">for</code> <code class="bash plain">db_name </code><code class="bash keyword">in</code> <code class="bash plain">`</code><code class="bash functions">echo</code> <code class="bash plain">$db_str`</code></div>
<div class="line number88 index87 alt1"><code class="bash keyword">do</code></div>
<div class="line number89 index88 alt2"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total IOPS of harddisk</code></div>
<div class="line number90 index89 alt1"><code class="bash spaces">  </code><code class="bash comments"># DB_IO_RQ_LG_SEC</code></div>
<div class="line number91 index90 alt2"><code class="bash spaces">  </code><code class="bash comments"># DB_IO_RQ_SM_SEC</code></div>
<div class="line number92 index91 alt1"><code class="bash spaces">  </code><code class="bash plain">db_drw=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'DB_IO_RQ_LG_SEC|DB_IO_RQ_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$db_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number93 index92 alt2"></div>
<div class="line number94 index93 alt1"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total IOPS of flashdisk</code></div>
<div class="line number95 index94 alt2"><code class="bash spaces">  </code><code class="bash comments"># DB_FD_IO_RQ_LG_SEC</code></div>
<div class="line number96 index95 alt1"><code class="bash spaces">  </code><code class="bash comments"># DB_FD_IO_RQ_SM_SEC</code></div>
<div class="line number97 index96 alt2"><code class="bash spaces">  </code><code class="bash plain">db_frw=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'DB_FD_IO_RQ_LG_SEC|DB_FD_IO_RQ_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$db_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number98 index97 alt1"></div>
<div class="line number99 index98 alt2"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total MB/s of harddisk</code></div>
<div class="line number100 index99 alt1"><code class="bash spaces">  </code><code class="bash comments"># DB_IO_BY_SEC</code></div>
<div class="line number101 index100 alt2"><code class="bash spaces">  </code><code class="bash plain">db_drwm=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'DB_IO_BY_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$db_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number102 index101 alt1"></div>
<div class="line number103 index102 alt2"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total MB/s of flashdisk</code></div>
<div class="line number104 index103 alt1"><code class="bash spaces">  </code><code class="bash comments"># DB_FC_IO_BY_SEC</code></div>
<div class="line number105 index104 alt2"><code class="bash spaces">  </code><code class="bash comments"># DB_FD_IO_BY_SEC</code></div>
<div class="line number106 index105 alt1"><code class="bash spaces">  </code><code class="bash comments"># DB_FL_IO_BY_SEC</code></div>
<div class="line number107 index106 alt2"><code class="bash spaces">  </code><code class="bash plain">db_frwm=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'DB_FC_IO_BY_SEC|DB_FD_IO_BY_SEC|DB_FL_IO_BY_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$db_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number108 index107 alt1"></div>
<div class="line number109 index108 alt2"><code class="bash spaces">  </code><code class="bash plain">print </code><code class="bash string">"$TM,DB,$db_name,$db_drw,$db_frw,$db_drwm,$db_frwm,0,0,0,0,0,0,0,0,0,0,0,0"</code></div>
<div class="line number110 index109 alt1"></div>
<div class="line number111 index110 alt2"><code class="bash keyword">done</code></div>
<div class="line number112 index111 alt1"></div>
<div class="line number113 index112 alt2"></div>
<div class="line number114 index113 alt1"><code class="bash comments">#######################################</code></div>
<div class="line number115 index114 alt2"><code class="bash comments"># extract IOPS for DBRM consumer groups</code></div>
<div class="line number116 index115 alt1"><code class="bash comments">#######################################</code></div>
<div class="line number117 index116 alt2"><code class="bash functions">export</code> <code class="bash plain">cg_str=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'CG_FD_IO_RQ_LG_SEC'</code> <code class="bash plain">| </code><code class="bash functions">grep</code> <code class="bash plain">-</code><code class="bash functions">v</code> <code class="bash plain">DBUA | </code><code class="bash functions">awk</code> <code class="bash string">'{ print $3}'</code> <code class="bash plain">| </code><code class="bash functions">sort</code> <code class="bash plain">| </code><code class="bash functions">uniq</code><code class="bash plain">`</code></div>
<div class="line number118 index117 alt1"></div>
<div class="line number119 index118 alt2"><code class="bash keyword">for</code> <code class="bash plain">cg_name </code><code class="bash keyword">in</code> <code class="bash plain">`</code><code class="bash functions">echo</code> <code class="bash plain">$cg_str`</code></div>
<div class="line number120 index119 alt1"><code class="bash keyword">do</code></div>
<div class="line number121 index120 alt2"></div>
<div class="line number122 index121 alt1"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total IOPS of harddisk</code></div>
<div class="line number123 index122 alt2"><code class="bash spaces">  </code><code class="bash comments"># CG_IO_RQ_LG_SEC</code></div>
<div class="line number124 index123 alt1"><code class="bash spaces">  </code><code class="bash comments"># CG_IO_RQ_SM_SEC</code></div>
<div class="line number125 index124 alt2"><code class="bash spaces">  </code><code class="bash plain">cg_drw=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'CG_IO_RQ_LG_SEC|CG_IO_RQ_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$cg_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number126 index125 alt1"></div>
<div class="line number127 index126 alt2"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total IOPS of flashdisk</code></div>
<div class="line number128 index127 alt1"><code class="bash spaces">  </code><code class="bash comments"># CG_FD_IO_RQ_LG_SEC</code></div>
<div class="line number129 index128 alt2"><code class="bash spaces">  </code><code class="bash comments"># CG_FD_IO_RQ_SM_SEC</code></div>
<div class="line number130 index129 alt1"><code class="bash spaces">  </code><code class="bash plain">cg_frw=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'CG_FD_IO_RQ_LG_SEC|CG_FD_IO_RQ_SM_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$cg_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number131 index130 alt2"></div>
<div class="line number132 index131 alt1"></div>
<div class="line number133 index132 alt2"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total MB/s of harddisk</code></div>
<div class="line number134 index133 alt1"><code class="bash spaces">  </code><code class="bash comments"># CG_IO_BY_SEC</code></div>
<div class="line number135 index134 alt2"><code class="bash spaces">  </code><code class="bash plain">cg_drwm=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'CG_IO_BY_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$cg_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number136 index135 alt1"></div>
<div class="line number137 index136 alt2"><code class="bash spaces">  </code><code class="bash comments"># Calculate Total MB/s of flashdisk</code></div>
<div class="line number138 index137 alt1"><code class="bash spaces">  </code><code class="bash comments"># CG_FC_IO_BY_SEC</code></div>
<div class="line number139 index138 alt2"><code class="bash spaces">  </code><code class="bash comments"># CG_FD_IO_BY_SEC</code></div>
<div class="line number140 index139 alt1"><code class="bash spaces">  </code><code class="bash plain">cg_frwm=`</code><code class="bash functions">cat</code> <code class="bash plain">$datafile | </code><code class="bash functions">egrep</code> <code class="bash string">'CG_FC_IO_BY_SEC|CG_FD_IO_BY_SEC'</code> <code class="bash plain">|</code><code class="bash functions">grep</code> <code class="bash plain">$cg_name |</code><code class="bash functions">sed</code> <code class="bash string">'s/,//g'</code><code class="bash plain">|</code><code class="bash functions">awk</code> <code class="bash string">'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'</code><code class="bash plain">`</code></div>
<div class="line number141 index140 alt2"></div>
<div class="line number142 index141 alt1"><code class="bash spaces">  </code><code class="bash plain">print </code><code class="bash string">"$TM,CG,$cg_name,$cg_drw,$cg_frw,$cg_drwm,$cg_frwm,0,0,0,0,0,0,0,0,0,0,0,0"</code></div>
<div class="line number143 index142 alt2"></div>
<div class="line number144 index143 alt1"><code class="bash keyword">done</code></div>
<div>

GoldenGate Install & Config On Exadata

Oracle Goldengate on DBFS for RAC and Exadata

Let us take a look at the process of configuring Golden Gate 12c to work in an Oracle 12c Grid Infrastructure RAC or Exadata environment using DBFS on Linux x86-64.

This is now supported on ACFS as well, which may outperform the DBFS solution.

Simply put the Oracle Database File System (DBFS) is a standard file system interface on top of files and directories that are stored in database tables as LOBs.

Until recently Exadata did not support using ACFS but ACFS is now supported on version 12.1.0.2 of the RAC Grid Infrastructure.

In summary the steps involved are:

1) Install and configure FUSE (Filesystem in Userspace)
2) Create the DBFS user and DBFS tablespaces
3) Mount the DBFS filesystem
5) Create symbolic links for the Goldengate software directories dirchk,dirpcs, dirdat, BR to point to directories on DBFS
6) Create the Application VIP
7) Download the mount-dbfs.sh script from MOS and edit according to our environment
8) Create the DBFS Cluster Resource
9) Download and install the Oracle Grid Infrastructure Bundled Agent
10) Register Goldengate with the bundled agents using agctl utility