Oracle Connection Manager (CMAN) quick reporting scripts

Franck Pachot
Aug 15 · 4 min read

List services registered by instance

script output, a bit obfuscated
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

List connections

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

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}'
)
{
# 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

Franck Pachot

Written by

https://twitter.com/FranckPachot DBA at CERN, Oracle OCM 12c, Oracle ACE Director, Oak Table member. My 499 posts at dbi-services: http://blog.dbi.pachot.net

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade