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

&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;


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'

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram