Oracle Connection Manager (CMAN) quick reporting scripts

Here are a few scripts I use to parse Connection Manager “show service”

List services registered by instance

CMCTL can show the services in a long list, but I want something quick like this, with one line per service, and one column per endpoint that registers to CMAN:

script output, a bit obfuscated

The following script

• get all CMAN running on the current host (with pgrep tnslsnr)
• run CMCTL with the righ environment variables
• run “administer” and “show services”
• parse the output with AWK to put instances into columns
• resolves IP addresses by colling “host” and removes the domain name

ps --no-headers -o pid,args -p$(pgrep -f "tnslsnr .* -mode proxy") |
while IFS=" " read pid tnslsnr args
do
 # get environment variables
 awk '
  BEGIN{RS="\0"}
  /^ORACLE_HOME|^TNS_ADMIN|^LD_LIBRARY_PATH/{printf "%s ",$0}
  END{print cmctl,here}
  ' cmctl="$(dirname $tnslsnr)/cmctl" here="<<-'CMCTL'" /proc/$pid/environ
 # name is probably the first arg without '-' nor '='
 name=$(echo "$args"|awk 'BEGIN{RS=" "}/^[^-][^=]*$/{print;exit}')
 echo "administer $name"
 echo "show services"
 echo "CMCTL"
done | sh | awk '
function hostname(h){a=h;if (h~/^[0-9.]+$/){"host "h| getline a;close("host "h);sub(/^.* /,"",a);sub(/[.]$/,"",a)}return a}
/Service ".*" has .* instance/{
 gsub(qq," ")
 sub(/[.].*/,"") # remove domain
 service=$2
 all_service[service]=1
 stats="-"
}
/Instance ".*", status READY, has .* handler.* for this service/{
 gsub(qq," ")
 instance=$2
 all_instance[instance]=0
 stats="-"
}
/established:.* refused:.* state:.*/{
 sub(/^ */,"")
 sub(/.DEDICATED./,"D")
 sub(/established:/,"")
 sub(/refused:/,"/")
 sub(/state:/,"")
 sub(/ready/,"R")
 stats=$0
}
/ADDRESS.*HOST=.*PORT=/{
 port=$0;sub(/.*PORT=/,"",port);sub(/[)].*/,"",port)
 host=$0;sub(/.*HOST=/,"",host);sub(/[)].*/,"",host)
 if (host ~ /^[xxx0-9.]+$/) {
  "host "host| getline host_host
  sub(/^.* /,"",host_host)
  sub(/[.]$/,"",host_host)
  host=host_host
 }
 host=hostname(host)
 sub(/[.].*/,"",host) # remove domain
 all_instance_host[instance]=host
 all_instance_port[instance]=port
 all_instance_instance[instance]=instance
 all_instance_stats[instance]=stats
 all_service_instance[service,instance]=instance
 if (length(host) > all_instance[instance] ) {
  all_instance[instance]= length(host)
 }
 if (length(port) > all_instance[instance] ) {
  all_instance[instance]= length(port)
 }
 if (length(instance) > all_instance[instance] ) {
  all_instance[instance]= length(instance)
 }
}
END{
  # host
  printf "1%39s ","host:"
  for (instance in all_instance){
   printf "%-"all_instance[instance]"s ", all_instance_host[instance]
  }
  printf "\n"
  # port
  printf "2%39s ","port:"
  for (instance in all_instance){
   printf "%-"all_instance[instance]"s ", all_instance_port[instance]
  }
  printf "\n"
  # instance
  printf "3%39s ","instance:"
  for (instance in all_instance){
   printf "%-"all_instance[instance]"s ", all_instance_instance[instance]
  }
  printf "\n"
  # stats
  printf "4%39s ","established/refused:"
  for (instance in all_instance){
   printf "%-"all_instance[instance]"s ", all_instance_stats[instance]
  }
  printf "\n"
  # header
  printf "5%39s ","---------------------------------------"
  for (instance in all_instance){
   printf "%-"all_instance[instance]"s ", substr("----------------------------------------",1,all_instance[instance])
  }
  printf "\n"
 # services
 for (service in all_service){
  printf "%-40s ",service
  for (instance in all_instance){
   if (all_service_instance[service,instance]!="") {
    printf "%-"all_instance[instance]"s ", all_service_instance[service,instance]
   } else {
    printf "%-"all_instance[instance]"s ", ""
   }
  }
  printf "\n"
 }
}' qq='"'| sort

Of course, it may be improved, and probably there are better solutions already existing. This was just faster for me rather than looking for an existing solution. Feedbacks on twitter, please:

List connections

This one formats the output of “show connections detail” like this:

In this example the last line shows that xxxc543 host has a connection to CMAN gateway 1 which is a proxy for database instance xxxxx5254 service xxxbi. The connection has been established 19 hours 57 minutes is idle for 2:37 minutes. It has received 67KB from the client, 100% of it having been sent to the instance, which returned 294KB which has been 100% transferred to the client.

Here is the script:

ps --no-headers -o pid,args -p$(pgrep -f "tnslsnr .* -mode proxy") |
while IFS=" " read pid tnslsnr args
do
 # get environment variables
 awk '
  BEGIN{RS="\0"}
  /^ORACLE_HOME|^TNS_ADMIN|^LD_LIBRARY_PATH/{printf "%s ",$0}
  END{print cmctl,here}
  ' cmctl="$(dirname $tnslsnr)/cmctl" here="<<-'CMCTL'" /proc/$pid/environ
 # name is probably the first arg without '-' nor '='
 name=$(echo "$args"|awk 'BEGIN{RS=" "}/^[^-][^=]*$/{print;exit}')
 echo "administer $name"
 echo "show connections detail"
 echo "show connections count"
 echo "CMCTL"
done | sh | awk '
function time(s){if (s==4294967295){return "runnning  "};d=int(s/86400);s=s-(d*86400);h=int(s/3600);s=s-(h*3600);m=int(s/60);s=s-(m*60);return sprintf("%dd+%02d:%02d:%02d",d,h,m,s)}
function size(b){u="";if(b>1024){b=b/1024;u="K"};if(b>1024){b=b/1024;u="M"};if(b>1024){b=b/1024;u="G"};return int(b)""u}
function hostname(h){a=h;if (h~/^[0-9.]+$/){"host "h| getline a;close("host "h);sub(/^.* /,"",a);sub(/[.]$/,"",a)}return a}
function pct(a,b){return int(100*a/b)"%"}
/Number of connections:/{print} #{print>"/dev/stderr"}
/Connection ID/{id=$NF}
/Gateway ID/{gw=$NF}
/Source/{src=$NF}/Destination/{dst=$NF}
/Service/{srv=$NF}/State/{sta=$NF}
/Idle time/{idl=$NF}/Connected time/{con=$NF}
/Bytes Received *.IN./{rci=$NF}
/Bytes Received *.OUT./{rco=$NF}
/Bytes Sent *.IN./{sni=$NF}/Bytes Sent *.OUT./{sno=$NF}
/^$/ || /The command completed successfully/{
if (con>0) printf "%-12s %60s %-40s %15s (idle: %9s) %9s>(%3d%%) %9s<(%3d%%) id:%6d gw:%3d\n", sta,hostname(src)"->"hostname(dst),srv,time(con),time(idl),size(rci),pct(sno,rci),size(rco),pct(sni,rco),id,gw
con=0}' | sort -k3,2

Both scripts call the Linux command “host” to resolve IP addresses to hostnames, with the following function:

function hostname(h){a=h;if (h~/^[0-9.]+$/){"host "h| getline a;close("host "h);sub(/^.* /,"",a);sub(/[.]$/,"",a)}return a}

go to the log directory

Here is a similar script to get, and go to the log directory:

cd $(
ps --no-headers -o pid,args -p$(pgrep -f "tnslsnr .* -mode proxy") |
while IFS=" " read pid tnslsnr args
do
 # get environment variables
 awk '
  BEGIN{RS="\0"}
  /^ORACLE_HOME|^TNS_ADMIN|^LD_LIBRARY_PATH/{printf "%s ",$0}
  END{print cmctl,here}
  ' cmctl="$(dirname $tnslsnr)/cmctl" here="<<-'CMCTL'" /proc/$pid/environ
 # name is probably the first arg without '-' nor '='
 name=$(echo "$args"|awk 'BEGIN{RS=" "}/^[^-][^=]*$/{print;exit}')
 echo "administer $name"
 echo "show status"
 echo "CMCTL"
done | sh | awk '/Instance Log directory/{print $NF}'
)

There, I run something like that to aggregate some information:

{
# 11g log:
for i in $(hostname -s)_{1..9}* ; do [ -f $i ] && fuser $i && cat $i ; done
# 12c log:
[ -f log.xml ] && awk '/^ <txt>/{sub(/^ <txt>/,"");print}' log.xml
} | awk -F '*' '
toupper($1)<toupper(start){next}
$2==" service_update "{
 update_count[$3]=update_count[$3]+1
 next
}
$4==" establish "{
 service_count[$5]=service_count[$5]+1
 client_count[$2]=client_count[$2]+1
 next
}
#{print >"/dev/stderr"}
END{
for (i in update_count){
 printf "%6d update from\t%-s\n",update_count[i],i
}
for (i in service_count){
 printf "%6d establish to\t%-s\n",service_count[i],i
}
for (i in client_count){
 printf "%6d establish from\t%-s\n",client_count[i],i
}
}
' start=$(TZ=GMT+24 date +%d-%b-%Y)| sort -n