Category Archives: Scripts

Scripts

View Segments Script


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

 

Scripts

sp_who.sql

/* ####################################################################### */
/*                                                                         */
/* 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;
Scripts, shell, Shell Code

longops.sql


-- -----------------------------------------------------------------------------------
-- 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#;
GoldenGate, Scripts

GoldenGate Lag Script

Leave a comment 

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

&nbsp;

# uncomment below command if you want to get lag information about running process #

&nbsp;

#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

&nbsp;

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################

&nbsp;

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################

&nbsp;

&nbsp;

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

&nbsp;

# uncomment below command if you want to get lag and checkpoint both information #

&nbsp;

#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

&nbsp;

##########################################################
## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###
##########################################################

&nbsp;

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

&nbsp;

################# SCRIPT END ######################

&nbsp;

&nbsp;

&nbsp;

&nbsp;

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

&nbsp;

&nbsp;

&nbsp;
Scripts

chk_sgausage.sql

Leave a comment 


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
/
Exadata, Scripts

cell_iops.ksh Cell Node IOPS script

Leave a comment 
#!/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
GoldenGate, GoldenGate 12.2, Scripts

GoldenGate sample environment file ggsora12_env

Leave a comment

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'
ASM, Scripts

ASM statistics script

Leave a comment 

#!/bin/ksh 
############################################################################ 
# Script Name..: 
# Description..: 
# Author.......: Michael Culp 
# Date.........: / /2012 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
#    alter session set current 
# Login User...: 
# Run Order....: 
# Dependent on.: 
# Script type..: # 
############################################################################ 

echo "ASM File types stored in this instance" 
echo ----------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    
set lines 120    
set pagesize 66    
column name format a25    
column type format a20    
select distinct type      
  from v\$asm_file; 
EOF 

echo "ASM files stored in ASM instance (better)" 
echo ----------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF 
SET LINESIZE  150 
SET PAGESIZE  9999 
SET VERIFY    off 
COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name' 
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?' 
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes' 
COLUMN blocks                 FORMAT 9,999,999,999,999    HEAD 'Blocks' 
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space' 
COLUMN type                   FORMAT a18                  HEAD 'File Type' 
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy' 
COLUMN striped                FORMAT a8                   HEAD 'Striped' 
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint BREAK ON report ON disk_group_name 
SKIP 1 compute sum label ""            of bytes space on disk_group_name 
compute sum label "Grand Total: " of bytes space on report 

SELECT CONCAT('+' || disk_group_name
     , SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path   
     , bytes   
     , space   
     , NVL(LPAD(type, 18)
     , '&lt;DIRECTORY&gt;')  type   
     , creation_date   
     , disk_group_name   
     , LPAD(system_created, 4) system_created 
FROM ( SELECT g.name disk_group_name
            , a.parent_index    pindex
            , a.name            alias_name 
            , a.reference_index rindex         
            , a.system_created  system_created         
            , f.bytes           bytes         
            , f.blocks          blocks         
            , f.space           space         
            , f.type            type         
            , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date       
FROM v\$asm_file f RIGHT OUTER JOIN 
     v\$asm_alias a USING (group_number, file_number)   JOIN 
     v\$asm_diskgroup g USING (group_number)) 
WHERE type IS NOT NULL START WITH (MOD(pindex, POWER(2, 24))) = 0     
CONNECT BY PRIOR rindex = pindex; 


echo "Amount of space left on individual ASM disk devices" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    
set pagesize 66    
set lines 100    
column path format a20    

select path
       , state
       , total_mb
       , free_mb
  from v\$asm_disk; 

EOF 



echo "IO Stats ASM disks" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    
set pagesize 66    
set lines 120    
column path format a20    
column read_time heading "Read Time" format 999,999.99    
column write_time                    format 999,999.99    
column writes                        format 999,999,999,999    
column reads                         format 999,999,999,999   
column bytes_written                 format 999,999,999,999    
column bytes_read                    format 999,999,999,999    

select path
      ,reads
      ,read_time
      ,bytes_read,           writes
,           write_time
,           bytes_written      
from v\$asm_disk; 



echo "Amount of space left on individual ASM disk devices" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" &lt;&lt;EOF    
set pagesize 66    
set lines 100    
column path format a20    
select path
      ,state
      ,total_mb
      ,free_mb
 from v\$asm_disk; 

EOF 

echo "IO Stats ASM disks" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    

set pagesize 66    
set lines 120    
column path format a20    
column read_time heading "Read Time" format 999,999.99    
column write_time                    format 999,999.99    
column writes                        format 999,999,999,999    
column reads                         format 999,999,999,999    
column bytes_written                 format 999,999,999,999    
column bytes_read                    format 999,999,999,999    
select path
,           reads
,           read_time
,           bytes_read
,           writes
,           write_time
,           bytes_written      
from v\$asm_disk; 
EOF 

#!/bin/ksh 
############################################################################ 
# Script Name..: 
# Description..: 
# Author.......: Michael Culp 
# Date.........: / /2012 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
#    alter session set current 
# Login User...: 
# Run Order....: 
# Dependent on.: 
# Script type..: 
# ############################################################################ 
echo "ASM File types stored in this instance" 
echo ----------------------------------------------------- 
sqlplus -s "/ as sysdba" >>EOF    
set lines 120    
set pagesize 66    
column name format a25    
column type format a20    
select distinct type      
from v\$asm_file; 
EOF echo "ASM files stored in ASM instance (better)" 
echo ----------------------------------------------------- 

sqlplus -s "/ as sysdba" &lt;&lt;EOF 
SET LINESIZE  150 
SET PAGESIZE  9999 
SET VERIFY    off 
COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name' 
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?' 
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes' 
COLUMN blocks                 FORMAT 9,999,999,999,999    HEAD 'Blocks' 
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space' 
COLUMN type                   FORMAT a18                  HEAD 'File Type' COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy' COLUMN striped                FORMAT a8                   HEAD 'Striped' COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date' COLUMN disk_group_name        noprint BREAK ON report ON disk_group_name SKIP 1 compute sum label ""              of bytes space on disk_group_name compute sum label "Grand Total: " of bytes space on report SELECT     CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path   , bytes   , space   , NVL(LPAD(type, 18), '&lt;DIRECTORY&gt;')  type   , creation_date   , disk_group_name   , LPAD(system_created, 4) system_created FROM     ( SELECT g.name           disk_group_name,              a.parent_index   pindex,              a.name               alias_name ,              a.reference_index    rindex         , a.system_created     system_created         , f.bytes              bytes         , f.blocks             blocks         , f.space              space         , f.type               type         , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date       FROM           v\$asm_file f RIGHT OUTER JOIN v\$asm_alias     a USING (group_number, file_number)                                     JOIN v\$asm_diskgroup g USING (group_number)     ) WHERE type IS NOT NULL START WITH (MOD(pindex, POWER(2, 24))) = 0     CONNECT BY PRIOR rindex = pindex; echo "Amount of space left on individual ASM disk devices" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 100    column path format a20    select path,           state,           total_mb,           free_mb      from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 120    column path format a20    column read_time heading "Read Time" format 999,999.99    column write_time                    format 999,999.99    column writes                        format 999,999,999,999    column reads                         format 999,999,999,999    column bytes_written                 format 999,999,999,999    column bytes_read                    format 999,999,999,999    select path,           reads,           read_time,           bytes_read,           writes,           write_time,           bytes_written      from v\$asm_disk; EOF                                                                                                123,1         Bot echo "Amount of space left on individual ASM disk devices" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 100    column path format a20    select path,           state,           total_mb,           free_mb      from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 120    column path format a20    column read_time heading "Read Time" format 999,999.99    column write_time                    format 999,999.99    column writes                        format 999,999,999,999    column reads                         format 999,999,999,999    column bytes_written                 format 999,999,999,999    column bytes_read                    format 999,999,999,999    select path,           reads,           read_time,           bytes_read,           writes,           write_time,           bytes_written      from v\$asm_disk; EOF