qa/1035

   1 #!/bin/sh
   2 # PCP QA Test No. 1035
   3 # Exercise the Oracle PMDA, verifying values from an installation.
   4 #
   5 # Copyright (c) 2016 Red Hat.
   6 #
   7
   8 seq=`basename $0`
   9 echo "QA output created by $seq"
  10
  11 # get standard environment, filters and checks
  12 . ./common.product
  13 . ./common.filter
  14 . ./common.check
  15
  16 [ -d $PCP_PMDAS_DIR/oracle ] || _notrun "Oracle PMDA not installed"
  17 [ -f $PCP_PMDAS_DIR/oracle/oracle.conf ] || _notrun "Oracle PMDA not configured"
  18
  19 # uses an existing oracle installation, so check its in place
  20 count=`pmprobe oracle.version | awk '{ print $2 }'`
  21 [ $count -ge 1 ] || _notrun "Oracle database PMDA is not setup and running"
  22
  23 $sudo rm -rf $tmp $tmp.* $seq.full
  24 perl $PCP_PMDAS_DIR/oracle/connect.pl >$seq.full 2>&1
  25 [ $? -eq 0 ] || _notrun "Oracle database connectivity unavailable"
  26
  27 # pick a sensible sounding default SID for oraenv
  28 [ -n "$ORACLE_SID" ] && export ORACLE_SID=master
  29 oraenv=`which oraenv 2>/dev/null`
  30 [ $? -eq 0 ] || _notrun "Oracle environment setup script (oraenv) not found"
  31 . $oraenv </dev/null >/dev/null
  32
  33 # extract configuration (os_user, username, password, sid) for sqlplus
  34 eval `perl $PCP_PMDAS_DIR/oracle/connect.pl -c`
  35
  36 status=1        # failure is the default!
  37 trap "cd $here; rm -rf $tmp.*; exit \$status" 0 1 2 3 15
  38
  39 queries="sysstat license latch filestat reqdist version waitstat"
  40
  41 #
  42 # setup input files for sqlplus value extraction - focus on one metric
  43 # from each cluster ($queries) to compare with pmprobe values, choosing
  44 # metrics likely to have non-zero values but unlikely to fluctuate while
  45 # the test is running
  46 #
  47
  48 $sudo rm -f $tmp.head
  49 echo 'set wrap off;' >> $tmp.head
  50 echo 'set colsep " | ";' >> $tmp.head
  51 echo 'set recsep off;' >> $tmp.head
  52 echo 'set heading off;' >> $tmp.head
  53 echo 'set feedback off;' >> $tmp.head
  54 echo 'set linesize 1024;' >> $tmp.head
  55 echo 'set arraysize 1500;' >> $tmp.head
  56 for query in $queries
  57 do
  58     cp $tmp.head $tmp.$query
  59 done
  60 echo 'select statistic#, name, value from v$sysstat;' >> $tmp.sysstat
  61 echo 'select sessions_highwater from v$license;' >> $tmp.license
  62 echo 'select latch#, name, gets from v$latch;' >> $tmp.latch
  63 cat >> $tmp.filestat << EOF
  64 select v\$filestat.file#, v\$datafile.name, v\$filestat.phyblkwrt
  65         from v\$filestat
  66         inner join v\$datafile
  67         on v\$filestat.file# = v\$datafile.file#;
  68 EOF
  69 echo 'select class, count from v$waitstat;' >> $tmp.waitstat
  70 echo 'select bucket, count from v$reqdist;' >> $tmp.reqdist
  71 cat >> $tmp.version << EOF
  72 select distinct banner from v\$version where banner like 'Oracle%';
  73 EOF
  74
  75 _filter()
  76 {
  77     sed \
  78         -e "s/$sids/ORACLE_SID/g" \
  79         -e 's/"Oracle Database.*"/"ORACLE_VERSION"/g' \
  80     #end
  81 }
  82
  83 _query()
  84 {
  85     table=$1
  86     file=$tmp.$1
  87
  88     echo === Fetching Oracle $table metrics
  89
  90     echo === Oracle Query: $table >> $seq.full
  91     #debug#       | tee -a $seq.full
  92     sqlplus -S $username/$password@$sids < $file \
  93         | tr '\t' ' ' | tr -s ' ' \
  94         | sed -e '/^$/d' -e 's/ | /|/g' -e 's/^ //' \
  95         > $tmp.ora 2> $tmp.err
  96     cat $tmp.ora >> $seq.full
  97     if [ -s $tmp.err ]
  98     then
  99         echo "=== Errors: $table" | tee -a $seq.full
 100         cat $tmp.err | tee -a $seq.full
 101     fi
 102     echo === Done: $table >> $seq.full
 103 }
 104
 105 _probe()
 106 {
 107     table=$1
 108     metric=$2
 109
 110     echo === Fetching PCP $table metrics
 111     pmprobe -v $metric | sed -e "s/$metric 1 //g" -e 's/"//g' > $tmp.pcp
 112
 113     echo === PCP Values: $table >> $seq.full
 114     cat $tmp.pcp >> $seq.full
 115     echo === Done: $table >> $seq.full
 116 }
 117
 118 _probe_insts()
 119 {
 120     table=$1
 121     metric=$2
 122
 123     echo "=== Fetching PCP $table metrics with instances"
 124     pminfo -f $metric | \
 125             sed \
 126                 -e '/^$/d' \
 127                 -e "/^$metric/d" \
 128                 -e "s# .*inst \[.* or \"$sids/##g" \
 129                 -e 's/\"\] value /|/g' \
 130             | LC_COLLATE=POSIX sort -u > $tmp.pcp
 131
 132     echo === PCP Values: $table >> $seq.full
 133     cat $tmp.pcp >> $seq.full
 134     echo === Done: $table >> $seq.full
 135 }
 136
 137 _exact_match()
 138 {
 139     echo "Comparing Oracle to PCP"
 140     if [ ! -f "$1" ]
 141     then
 142         echo "$1 is missing"
 143     elif [ ! -f "$2" ]
 144     then
 145         echo "$2 is missing"
 146     else
 147         diff $1 $2 && echo OK
 148     fi
 149     echo
 150 }
 151
 152 _fuzzy_match()
 153 {
 154     echo "Comparing Oracle to PCP"
 155     LC_COLLATE=POSIX join -t\| -a2 -1 1 $1 $2 \
 156     | $PCP_AWK_PROG '
 157         {
 158             a=$2; b=$3; d=a-b;
 159             if (d<0) d = -d;
 160             if (d > 5 && d>a/100) {
 161                 print "'$0' : Diff of more than 5 in total and 1 percent: ", $0
 162                 err++
 163             }
 164         }
 165         END {
 166             exit err
 167         }'
 168     status=$?
 169     if [ $status != 0 ]
 170     then
 171         echo $0 =========== ora output ==============
 172         cat $tmp.ora
 173         echo $0 =========== pcp output ==============
 174         cat $tmp.pcp
 175         echo $0 exit status $status
 176     else
 177         echo OK && echo
 178     fi
 179 }
 180
 181 # real QA test starts here
 182 _query version
 183 _probe version oracle.version
 184 _exact_match $tmp.ora $tmp.pcp
 185
 186 _query license
 187 _probe license oracle.license.highsess
 188 _exact_match $tmp.ora $tmp.pcp
 189
 190 _query waitstat
 191 _probe_insts waitstat oracle.waitstat.count
 192 sed -e 's/ /_/g' < $tmp.ora | \
 193         LC_COLLATE=POSIX sort > $tmp.ora2
 194 _exact_match $tmp.ora2 $tmp.pcp
 195
 196 _query latch
 197 _probe_insts latch oracle.latch.gets
 198 # remove the external instance name strings
 199 sed -e 's/|/ /' -e 's/[^0-9|]//g' < $tmp.ora | \
 200         LC_COLLATE=POSIX sort -u -n > $tmp.ora2
 201 sed -e 's/[^0-9|]//g' < $tmp.pcp | \
 202         LC_COLLATE=POSIX sort -u -n > $tmp.pcp2
 203 _fuzzy_match $tmp.ora2 $tmp.pcp2
 204
 205  _query filestat
 206  _probe_insts filestat oracle.file.phyblkwrt
 207 sed -e 's/|/ /' < $tmp.ora | \
 208         LC_COLLATE=POSIX sort -u -n > $tmp.ora3
 209  _fuzzy_match $tmp.ora3 $tmp.pcp
 210
 211 _query reqdist
 212 _probe_insts reqdist oracle.reqdist
 213 LC_COLLATE=POSIX sort -u -n < $tmp.ora > $tmp.ora4
 214 sed -e 's/bucket//g' -e 's/ - .*|/|/g' < $tmp.pcp | \
 215         LC_COLLATE=POSIX sort -u -n > $tmp.pcp4
 216 _exact_match $tmp.ora4 $tmp.pcp4
 217
 218 # check connection state modification (up/down)
 219 echo === Checking connection state
 220 pminfo -f oracle.control.connected | _filter
 221 pmstore oracle.control.connected 0 | _filter
 222 pminfo -f oracle.version | _filter
 223 # and bring it back up
 224 pminfo -f oracle.control.connected | _filter
 225 pmstore oracle.control.connected 1 | _filter
 226 pminfo -f oracle.version | _filter
 227
 228 # success, all done
 229 status=0
 230 exit