#!/bin/ksh # # Script to install SQLT # # -- usage: /oracle/admin/scripts/install_sqlt.ksh <DBNAME> # #################################################################################### DBNAME=$1 APP_ID=$2 log=/oracle/admin/scripts/install_sqlt_$DBNAME.log mkdir -p /ora01/SQLT/$DBNAME cp /oracle/admin/scripts/sqlt.zip /ora01/SQLT/$DBNAME cd /ora01/SQLT/$DBNAME unzip sqlt.zip cd /ora01/SQLT/$DBNAME/sqlt/install sqlplus -s "/ as sysdba" <<EOF @sqdrop.sql exit EOF sqlplus -s "/ as sysdba" <<EOF @sqcinternal.sql exit EOF exit
#!/bin/ksh
############################################################################
# Script Name..:
# Description..:
# Author.......: Michael Culp
# Date.........: 04/ /2013
# Version......:
# Modified By..:
# Date Modified:
# Comments.....: You must have ASM parameters set for this to run properly
# : A Diskgroup was setup called ACFS_DATA_DG01 to house the
# : the new filesystems
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
-- spool <some file name>
-- col fs_name format a25
-- col vol_device format a25
select segment_name
, blocks
, extents
from dba_segments
where segment_name in ( 'SOMETABLE_01', 'SOMETABLE_02' )
and owner ='MRCODS_OWNER';
-- spool off
EOF
/* ####################################################################### */
/* */
/* FILENAME: sp_who.sql */
/* */
/* Purpose: Shows who is in the database */
/* */
/* Parameters: None */
/* */
/* *** DO NOT HAND EDIT THIS FILE *** */
/* ####################################################################### */
/* */
/* Revision History: */
/* */
/* REV DATE BY DESCRIPTION */
/* --- ------ -------------- ------------------------------------------ */
/* 1.0 INFRASTRUCTURE Initial Relase */
/* */
/* *** DO NOT HAND EDIT THIS FILE *** */
/* */
/* ####################################################################### */
set pages 30 head off feed off
select 'User Status for Oracle Database is ', name from v\$database;
set head on
prompt
prompt All currently running processes
prompt ===============================
set pages 60 lines 132
col sid format 999 heading "SID"
col Typ format a3
col process format a10 heading "Client PID"
col spid format a10 heading "Server PID"
col username format a10 heading "ORA User"
col osuser format a8 heading "OS User"
col term format a7 heading "TTY"
col stat format a2 heading "ST"
col lw format a2 heading "LK"
col com format a8 heading "Command"
col mach format a10 heading "Host"
col latchw format a2 heading "LA"
select s.sid,
s.serial#,
nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
nvl(p.spid,'?') spid,
s.username username, nvl(substr(s.osuser,1,8),'?') osuser,
replace(replace(nvl(substr(s.terminal,1,7),'?'),'PC',''),'tty','') term,
decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
decode(s.lockwait,NULL,'N','Y') lw,
nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
45,'ROLLBACK',46,'SVEPOINT','IDLE'),'?') com,
substr(nvl(s.machine,'?'),1,9) mach,
decode(p.latchwait,NULL,'N','Y') latchw,
decode(least(instr(p.program,'TCP'),1),1,'TCP',
decode(least(instr(p.program,'TNS'),1),1,'TNS',
decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
from v\$session s, v\$process p
where addr = paddr
and p.background is NULL
union
select s.sid,
s.serial#,
nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
nvl(p.spid,'?') spid,
p.username username, nvl(substr(s.osuser,1,8),'?') osuser,
replace(replace(substr(p.terminal,1,7),'PC',''),'tty','') term,
decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
decode(s.lockwait,NULL,'N','Y') lw,
nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
45,'ROLLBACK',46,'SVEPOINT'),'?') com,
substr(nvl(s.machine,'?'),1,9) mach,
decode(p.latchwait,NULL,'N','Y') latchw,
decode(least(instr(p.program,'TCP'),1),1,'TCP',
decode(least(instr(p.program,'TNS'),1),1,'TNS',
decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
from v\$session s, v\$process p
where addr = paddr
and p.background is not NULL
order by 1;
prompt
prompt Rollback Segment Usage
prompt ======================
set lines 150
col name format a10
col usn format 99
col sid format 999
col xacts format 9999
col extents format 999999
col extends format 99999
col waits format 9999
col wraps format 9999999
col shrnk format 999999
col name format a12
col osusr format a6
col status format a9
col writes format 9999999999999
select r.usn, r.name, s.osuser osusr,
s.sid, x.extents, x.xacts,
x.extends, x.waits, x.shrinks shrnk,
x.wraps, x.writes, x.status
from v\$rollstat x,
v\$rollname r,
v\$session s,
v\$transaction t
where t.addr = s.taddr (+)
and x.usn (+) = r.usn
and t.xidusn (+) = r.usn
order by r.usn;
exit;
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/longops.sql
-- Author : Tim Hall
-- Description : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax : @longops
-- Last Modified: 03/07/2003
-- -----------------------------------------------------------------------------------
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid
, s.serial#
, s.machine
, ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed
, ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining
, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v\$session s,
v\$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
#!/bin/ksh
############################################
# Name: ggs_lag.ksh #
# PURPOSE: TO MONITOR LAG OF GOLDEN GATE #
# NOTE: THIS SCRIPT CALLS ggs.ksh #
# THIS SCRIPT NOTIFY IF LAG IS MORE THEN 30 MIN #
# ONLY FOR FOR EXT AND PMP PROCESS GROUP #
###########################################
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;./ggsci'
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
LOGDIR=/export/home/oracle/dba_scripts/ggs/logs
EMAILFile=$LOGDIR/ggs_email.log
BOX=$(uname -a | awk '{print $2}')
##########################################################################
# RUNNING SCRIPT TO GET GOLDEN GATE INFORMATION #
##########################################################################
/export/home/oracle/dba_scripts/ggs/ggs.ksh > $LOGDIR/ggs_1.log
#to check when script was running
echo "script ggsksh completed from ggs_lag at `date`" >> /tmp/ggs_check.log
##################################################################################
## FORMATTING INFORMATION: change cut -d":" -f 1,4 TO cut -d":" -f 1,2 ##
## to get information about lag instead of checkpoint ##
## this command grep only EXT_ and PMP_ if you need more pattern ##
## if you need more pattern to be grep'd please add to '(EXT_|PMP_|pattern)' ##
##################################################################################
cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log
# uncomment below command if you want to get lag and checkpoint both information #
#cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_|DART)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log
# uncomment below command if you want to get lag information about running process #
#cat $LOGDIR/ggs_1.log|grep RUNNING|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 >
$LOGDIR/ggs_2.log
##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################
##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################
awk '{if ( $4 > 00 || $5 >=30 ) {print $1 " " $3 " HAS LAG of " $4" hour " $5 " min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " " d >> "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log
# uncomment below command if you want to get lag and checkpoint both information #
#awk '{if ($4 >=30 || $5>=30 ) {print $1 " " $3 " has lag of "$4" min with checkpoint of "$5" min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " "d > "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log
##########################################################
## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###
##########################################################
if [ -s $EMAILFile ]; then
#echo "ERRORS FOUND"
mailx -s "GG LAG FOUND ON: $BOX" your.email@gmail.com team.email@gmail.com < $EMAILFile else cat /dev/null > $EMAILFile
#echo "ERRORS NOT FOUND"
fi
################# SCRIPT END ######################
#!/bin/ksh
#########################################
#Name: ggs.ksh #
#THIS SCRIPT WILL CALLED BY ggs_lag.ksh #
#########################################
#alias gate='clear;cd $GGATE;./ggsci'
echo "ggsksh started `date`" >> /tmp/ggs_check.log
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;ggsci'
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
#cd $GGATE
/opt/oracle/u01/app/oracle/ggs/ggsci <<>
info all
exit
EOF
echo "ggsksh completed at `date` " >> /tmp/ggs_check.log
set Define on
set numwidth 16
col username format a15
col machine format a30
col sysdate format a21
select count(*)
,username
,machine
from gv\$session
where username = upper('&1')
group by username
,machine;
/
set define off
select count(*),
username,
machine
from gv$session
where username = upper('&1')
group by username,
machine;
Col pool_name format a15
Col in_mb format 999,999
Col inuse_mb format 999,999
Break on snap_id skip 1
Select r.snap_id,cast(k.begin_interval_time as Date) snap_time,r.inst_id,r.pool_name,
round(sum(r.in_mb)) In_Mb,
round(sum(r.inuse_mb)) Inuse_Mb
from (select snap_id,
instance_number inst_id,
case when name = 'buffer_cache'
then 'db_cache_size'
when name = 'log_buffer'
then 'log_buffer'
else pool
end pool_name,
bytes/1024/1024 in_mb,
case when name <> 'free memory'
then bytes/1024/1024
end inuse_mb
from dba_hist_sgastat
where instance_number = &inst_id
and snap_id between &beg_snap and &end_snap
) r,
dba_hist_snapshot k
where r.snap_id = k.snap_id
and r.inst_id = k.instance_number
and r.pool_name is not null
group by r.snap_id,cast(k.begin_interval_time as Date),r.inst_id,r.pool_name
order by 1,2 asc, 5 desc
/
This script will help determine IOPS being delivered from ExaData
#!/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
Here is a sample GoldenGate environment file, feel free to comment about changes or additions
# This should be a .ggora12_env file
# This variable could be used to identify a site
export GG_SITE=01
# This could be used to identify a location
export GG_LOC=CO
# if there is only one DB related to this GG home,
# set NEW_ORACLE_SID to avoid constant switch between a DB env and its GG env
export NEW_ORACLE_SID=MRC01D011; . ~/.std_profile
# otherwise, set NEW_ORACLE_SID to dummy if there are multiple replicated databases related to same GG home
#export NEW_ORACLE_SID=dummy; . ~/.std_profile
# uncomment next three lines if ORACLE_SID is dummy
# export ORACLE_HOME=/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/$JAVA_HOME/lib/amd64/server:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export GGS_HOME=/oracle/product/gg12.1
export PATH=$GGS_HOME:$PATH
# For OEM12c GG monitoring
export JAVA_HOME=/oracle/product/12.1.0/oem_1/agent/core/12.1.0.2.0/jdk/jre
export PATH=$JAVA_HOME/bin:$PATH
# export LD_LIBRARY_PATH=$JAVA_HOME/lib/amd64/server:$LD_LIBRARY_PATH
#############################################
## For GI Agent (XAG)
##############################################
export XAG_HOME=/oracle/product/xag71
export PATH=$XAG_HOME/bin:$PATH
PS1="\\
\${PWD} \\
\${SNAME} [\${ORACLE_SID}] [GG12_site$GG_SITE"_"$GG_LOC]-> "
export PS1
alias ggsora12='. $HOME/.ggsora12_env;cd $GGS_HOME'
alias xag='. $HOME/.ggsora12_env;cd $XAG_HOME'
alias ggstatus='$XAG_HOME/bin/agctl status goldengate testdb01_oggapp'