Script For Listing 11gR2 Cluster Resource Status in CRS_STAT Output Format

crs_stat -t is depreciated in 11gR2 and replaced with crsctl stat res -t. Following script could be used to generate resource status output in a format that is similar to crs_stat -t in 11gR1.

#!/bin/sh
# Sample 11gR2 CRS resource status query script
# gives output similar to crs_stat -t in 11gR1
# Asanga Pradeep

awk \
  'BEGIN {printf "%-35s %-25s %-18s %-18s\n", "Resource Name", "Type", "Target","State";
          printf "%-35s %-25s %-18s %-18s\n", "-----------", "------", "-------","--------";}'
crsctl stat res | egrep -w "NAME|TYPE|TARGET|STATE" | sed -e "s/   / /g" | awk \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TYPE/ && state == 1 {apptype = $2; state=2;}
  $1~/TARGET/ && state == 2 {apptarget = $2; state=3;}
  $1~/STATE/ && state == 3 {appstate = $2; state=4;}
  state == 4 {
split(apptarget,targetarray,", ");
x=length(targetarray);
split(appstate,statearray,", ");
for(ix=1;ix<=x;ix++)
printf "%-35s %-25s %-18s %-18s\n", appname, apptype,targetarray[ix], statearray[ix]; state=0;}'

Output

Resource Name                       Type                      Target             State
-----------                         ------                    -------            --------
ora.CLUSTERDG.dg                    ora.diskgroup.type        ONLINE             ONLINE on rac4
ora.CLUSTERDG.dg                    ora.diskgroup.type        ONLINE             ONLINE on rac5
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE on rac4
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE on rac5
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE on rac4
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE on rac5
ora.LISTENER.lsnr                   ora.listener.type         ONLINE             ONLINE on rac4
ora.LISTENER.lsnr                   ora.listener.type         ONLINE             ONLINE on rac5
ora.LISTENER_SCAN1.lsnr             ora.scan_listener.type    ONLINE             ONLINE on rac5
ora.asm                             ora.asm.type              ONLINE             ONLINE on rac4
ora.asm                             ora.asm.type              ONLINE             ONLINE on rac5
ora.cvu                             ora.cvu.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE on rac4
ora.net1.network                    ora.network.type          ONLINE             ONLINE on rac5
ora.oc4j                            ora.oc4j.type             OFFLINE            OFFLINE
ora.ons                             ora.ons.type              ONLINE             ONLINE on rac4
ora.ons                             ora.ons.type              ONLINE             ONLINE on rac5
ora.rac11g2.db                      ora.database.type         ONLINE             ONLINE on rac4
ora.rac11g2.db                      ora.database.type         ONLINE             ONLINE on rac5
ora.rac11g2.test.svc                ora.service.type          ONLINE             ONLINE on rac4
ora.rac11g2.test.svc                ora.service.type          ONLINE             ONLINE on rac5
ora.rac4.vip                        ora.cluster_vip_net1.type ONLINE             ONLINE on rac4
ora.rac5.vip                        ora.cluster_vip_net1.type ONLINE             ONLINE on rac5
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE on rac4
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE on rac5
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE on rac5

Related Post
Script to get full name of the resources from crs_stat

Update 13 May 2013
In the above script both state and host name are in the same column. Below script will have a separate column for state and hostname

#!/bin/sh
# Sample 11gR2 CRS resource status query script
# gives output similar to crs_stat -t in 11gR1
# Asanga Pradeep

awk \
  'BEGIN {printf "%-35s %-25s %-18s %-18s %-18s\n", "Resource Name", "Type", "Target","State","Host";
          printf "%-35s %-25s %-18s %-18s %-18s\n", "-------------", "------", "-------","--------","----------";}'
crsctl stat res | egrep -w "NAME|TYPE|TARGET|STATE" | sed -e "s/   / /g" | awk \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TYPE/ && state == 1 {apptype = $2; state=2;}
  $1~/TARGET/ && state == 2 {apptarget = $2; state=3;}
  $1~/STATE/ && state == 3 {appstate = $2; state=4;}
  state == 4 {
split(apptarget,targetarray,", ");
x=length(targetarray);
split(appstate,statearray,", ");
  for(ix=1;ix<=x;ix++){
  split(statearray[ix],statehostarray," ");
  printf "%-35s %-25s %-18s %-18s %-18s\n", appname, apptype,targetarray[ix], statehostarray[1], statehostarray[3]; state=0;}

}'

Output

Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m2
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m2
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m1
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m2
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m2
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m2
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.rhel6m2.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m2
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m2
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m2

Check which Binary Options are Installed

Oracle binary get linked with many of the database options.
RAC - Real Application Cluster
RAT - Real Application Testing
OLS - Oracle Label Security
DV - Database Vault
ASM - Automated Storage Management
OLAP - Oracle OLAP
PART - Oracle Partitioning
CTX - Context Management Text

Following script could be used to find out which options are already relinked with Oracle binary. (It is assumed ORACLE_HOME is set)

component=(RAC RAT OLS DV ASM OLAP PART CTX)
compfile=(kcsm.o  kecwr.o kzlilbac.o  kzvidv.o kfon.o xsyeolap.o kkpoban.o kciwcx.o)
i=0
for comp in ${component[@]}
  do
    x=`ar -t $ORACLE_HOME/rdbms/lib/libknlopt.a | grep -c ${compfile[i]}`
    if [[ "$x" == "0" ]]; then
      printf "%-15s %s \033[31m %8s \033[0m %2s\n" "$comp" "[" "NOT INSTALLED" "]"
    else
      printf "%-15s %s \033[32m %11s \033[0m %4s\n" "$comp" "["   "INSTALLED"   "]"
    fi
    let "i++"
  done

RAC             [  NOT INSTALLED   ]
RAT             [    INSTALLED     ]
OLS             [  NOT INSTALLED   ]
DV              [  NOT INSTALLED   ]
ASM             [  NOT INSTALLED   ]
OLAP            [    INSTALLED     ]
PART            [    INSTALLED     ]
CTX             [    INSTALLED     ]

More on metalink note
How to Check and Enable/Disable Oracle Binary Options [ID 948061.1]
How to Check Whether Oracle Binary/Instance is RAC Enabled and Relink Oracle Binary in RAC [ID 284785.1]

Inserting sar output to database table

This shell commands shows how to insert couple of sar commands' output into a database table.

Key

" - double quote
' - single quote

First command is

sar
Linux 2.6.18-194.el5          07/08/2010

12:00:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
12:10:01 AM       all      1.06      0.00      0.43      1.29      0.00     97.21
12:20:01 AM       all      0.34      0.00      0.43      1.04      0.00     98.19
12:30:01 AM       all      0.35      0.00      0.46      1.15      0.00     98.05
12:40:01 AM       all      0.34      0.00      0.46      1.11      0.00     98.09
12:50:01 AM       all      0.33      0.00      0.49      1.06      0.00     98.13
01:00:01 AM       all      0.34      0.00      0.46      1.09      0.00     98.11
01:10:01 AM       all      0.37      0.00      0.48      1.09      0.00     98.06
01:20:01 AM       all      0.33      0.00      0.49      1.12      0.00     98.06 

Create the table with

Create Table Sar_Cpu (Collection_Time Timestamp (6),CPU_PCT NUMBER(5,2),SYSTEM_PCT NUMBER(5,2),IOWAIT_PCT NUMBER(5,2),IDLE_PCT  Number(5,2));

Execute the command to insert data into the table

sar | grep -v "^[a-Z]\|CPU" | awk '{print "insert into sar_cpu values (to_timestamp(""'\''"$1"'\''"",
""'\''HH24:MI:SS""'\''""),"$4","$6","$7","$9");"}' | sqlplus -s asanga/***

Next is sar -q

Linux 2.6.18-194.el5       07/08/2010

12:00:01 AM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
12:10:01 AM         1       598      0.20      0.10      0.09
12:20:01 AM         0       596      0.21      0.27      0.16
12:30:01 AM         0       595      0.39      0.27      0.20
12:40:01 AM         0       596      0.66      0.40      0.29
12:50:01 AM         0       596      0.32      0.29      0.27
01:00:01 AM         0       595      0.01      0.08      0.16
01:10:01 AM         1       595      0.14      0.25      0.20
01:20:01 AM         0       596      0.03      0.10      0.14

Create the table to insert the data

create table sar_loadavg (sample_time timestamp,run_queue number(2),process_lis number(4),load_avg_1 number(5,2),load_avg_5 number(5,2),load_avg_15 number(5,2));

Run the shell command

sar -q | grep -v "^[a-Z]\|CPU" | awk '{print "insert into sar_loadavg values (to_timestamp(""'\''"$1"'\''"",
""'\''HH24:MI:SS""'\''""),"$3","$4","$5","$6","$7");"}' | sqlplus -s asanga/***

Choose something similar to ADMon to plot a graph with the inserted data

Script to get full name of the resources from crs_stat

More on metalink note 259301.1

#!/bin/sh
#
# Sample 10g CRS resource status query script
#
# Description:
# - Returns formatted version of crs_stat -t, in tabular
# format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
QSTAT=-u
AWK=/bin/awk # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

For 11.2 replace $CRS_HOME/bin/crs_stat QSTAT with $CRS_HOME/bin/crsctl stat res
Script For Listing 11gR2 Cluster Resource Status in CRS_STAT Output Format

/proc/statm , /proc/status and top

From top

PID ... VIRT RES SHR ... COMMAND
4743 ... 2388m 722m 3532 ... java

From cat /proc/4743/status

VmSize: 2445800 kB
VmLck: 0 kB
VmRSS: 740004 kB
VmData: 2373700 kB
VmStk: 24 kB
VmExe: 72 kB
VmLib: 53241 kB
StaBrk: 00515000 kB
Brk: 00557000 kB


getconf PAGE_SIZE 4096

From cat /proc/4743/statm
611450 185001 883 18 0 593431 0

1. size :- total program size (611450 X 4096/1024 = 2445800kB = 2388M)
2. resident :- resident set size (185001 X 4096/1024 = 740004kB = 722M)
3. share :- shared pages (883 X 4096 = 3532)
4. trs :- text (code) (18 X 4096/1024 = 72kB = VmExe )
5. drs :- data/stack
6. lrs :- library (593431 X 4096/1024 = 2373724kB = VmData +VmStk)
7. dt :- dirty pages


Script to list /proc/pid/statm content

#!/bin/sh


pageSize=$(getconf PAGE_SIZE)

printf "VMSize(KB)\tReserved(KB)\tShared(KB)\tCode\tLibrary\tData/Stack\tDirty\n"

while [ 2 > 1 ]
do

vals=`cat /proc/$1/statm`

totalVM=`echo $vals $pageSize | awk '{print $1*$8/1024}'`
reserved=`echo $vals $pageSize | awk '{print $2*$8/1024}'`
shared=`echo $vals $pageSize | awk '{print $3*$8/1024}'`
code=`echo $vals $pageSize | awk '{print $4*$8/1024}'`
datastack=`echo $vals $pageSize | awk '{print $5*$8/1024}'`
library=`echo $vals $pageSize | awk '{print $6*$8/1024}'`
dirty=`echo $vals $pageSize | awk '{print $7*$8/1024}'`


printf "$totalVM\t\t$reserved\t\t$shared\t\t$code\t$datastack\t$library\t\t$dirty\n"

sleep $2

done

LoadAvg and LoadFactor script

#!/bin/sh

printf "\tLoad Average\tLoad Factor(loadavg/#CPU)\n"
cpuno=$(cat /proc/cpuinfo | grep processor | wc -l)

while [ 2 > 1 ]
do

x=$(cat /proc/loadavg | awk '{print $1}')
#y=$(echo $x / $cpuno | bc)
y=`echo $x $cpuno | awk '{print $1/$2}'`
printf "\t$x\t\t$y\n"
sleep $1

done