Oracle Connection Manager (CMAN) quick reporting scripts

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store