#!/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'