This blog has moved here.

Monday, January 24, 2011

crs_stat pretty print on RAC 11.2

I really don't like the way crs_stat -t displays RAC status information on my 11.2.0.2 database. For example, one of my instances are down. Can you figure out this looking at the output below?

[grid@owl ~]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    hen         
ora....ER.lsnr ora....er.type ONLINE    ONLINE    hen         
ora....N1.lsnr ora....er.type ONLINE    ONLINE    hen         
ora....N2.lsnr ora....er.type ONLINE    ONLINE    hen         
ora....N3.lsnr ora....er.type ONLINE    ONLINE    owl         
ora.asm        ora.asm.type   ONLINE    ONLINE    hen         
ora.cvu        ora.cvu.type   ONLINE    ONLINE    hen         
ora.gns        ora.gns.type   ONLINE    ONLINE    hen         
ora.gns.vip    ora....ip.type ONLINE    ONLINE    hen         
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....SM2.asm application    ONLINE    ONLINE    hen         
ora....EN.lsnr application    ONLINE    ONLINE    hen         
ora.hen.gsd    application    OFFLINE   OFFLINE               
ora.hen.ons    application    ONLINE    ONLINE    hen         
ora.hen.vip    ora....t1.type ONLINE    ONLINE    hen         
ora....network ora....rk.type ONLINE    ONLINE    hen         
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    hen         
ora.ons        ora.ons.type   ONLINE    ONLINE    hen         
ora....SM1.asm application    ONLINE    ONLINE    owl         
ora....WL.lsnr application    ONLINE    ONLINE    owl         
ora.owl.gsd    application    OFFLINE   OFFLINE               
ora.owl.ons    application    ONLINE    ONLINE    owl         
ora.owl.vip    ora....t1.type ONLINE    ONLINE    owl         
ora.poc.db     ora....se.type ONLINE    ONLINE    hen         
ora....uci.svc ora....ce.type ONLINE    ONLINE    hen         
ora....ry.acfs ora....fs.type ONLINE    ONLINE    hen         
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    hen         
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    hen         
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    owl         
The resource "ora.poc.db" is ONLINE therefore no red flags you might say. Well, bad luck: the database is up & running but with only one instance. The other one is dead. The database is policy managed but I want to be aware if all instances from the pool are running. How can we figure this out? Not a very big deal, just issue: "crsctl status resource". You'll get something like this:

NAME=ora.poc.db
TYPE=ora.database.type
TARGET=OFFLINE, ONLINE
STATE=OFFLINE, ONLINE on hen

Looking at TARGET and STATE is clear that something is OFFLINE and the red flag pops up right away. Of course, "crsctl status resource" command has the same problem as crs_stat when it comes to pretty print the result. It also has a tabular format (see the -t switch) but it'a a little bit verbose as it also displays the local resources status. But hey, do you remember Note 259301.1? It was about an awk script used for parsing the crs_stat output and displaying it in a nicer way. Okey, let's take that script and change it to take the output of the "crsctl status resource" command. I'm not an awk expert, but the following script is working pretty well:

#!/usr/bin/ksh
#
# 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:
$ORACLE_HOME/bin/crsctl status resource | $AWK \
 '
function ltrim(s) { sub(/^[ \t]+/, "", s); return s }
function rtrim(s) { sub(/[ \t]+$/, "", s); return s }
function trim(s)  { return rtrim(ltrim(s)); }

  BEGIN { FS="="; state = 0; }
  $1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TARGET/ && state == 1 {apptarget = $2; split(apptarget, atarget, ","); state=2;}
  $1~/STATE/ && state == 2 {appstate = $2; split(appstate, astate, ","); state=3;}
  state == 3 { split(appname, a, ","); 
               for (i = 1; i <= length(atarget); i++) { 
                 printf "%-45s %-10s %-18s\n", appname, trim(atarget[i]), trim(astate[i]) 
               }; 
               state=0;}'
And the output is:
[grid@owl ~]$ cs
HA Resource                                   Target     State             
-----------                                   ------     -----             
ora.DATA.dg                                   ONLINE     ONLINE on hen     
ora.DATA.dg                                   ONLINE     ONLINE on owl     
ora.LISTENER.lsnr                             ONLINE     ONLINE on hen     
ora.LISTENER.lsnr                             ONLINE     ONLINE on owl     
ora.LISTENER_SCAN1.lsnr                       ONLINE     ONLINE on hen     
ora.LISTENER_SCAN2.lsnr                       ONLINE     ONLINE on hen     
ora.LISTENER_SCAN3.lsnr                       ONLINE     ONLINE on owl     
ora.asm                                       ONLINE     ONLINE on hen     
ora.asm                                       ONLINE     ONLINE on owl     
ora.cvu                                       ONLINE     ONLINE on hen     
ora.gns                                       ONLINE     ONLINE on hen     
ora.gns.vip                                   ONLINE     ONLINE on hen     
ora.gsd                                       OFFLINE    OFFLINE           
ora.gsd                                       OFFLINE    OFFLINE           
ora.hen.vip                                   ONLINE     ONLINE on hen     
ora.net1.network                              ONLINE     ONLINE on hen     
ora.net1.network                              ONLINE     ONLINE on owl     
ora.oc4j                                      ONLINE     ONLINE on hen     
ora.ons                                       ONLINE     ONLINE on hen     
ora.ons                                       ONLINE     ONLINE on owl     
ora.owl.vip                                   ONLINE     ONLINE on owl     
ora.poc.db                                    OFFLINE    OFFLINE           
ora.poc.db                                    ONLINE     ONLINE on hen     
ora.poc.muci.svc                              ONLINE     ONLINE on hen     
ora.poc.muci.svc                              ONLINE     OFFLINE           
ora.registry.acfs                             ONLINE     ONLINE on hen     
ora.registry.acfs                             ONLINE     ONLINE on owl     
ora.scan1.vip                                 ONLINE     ONLINE on hen     
ora.scan2.vip                                 ONLINE     ONLINE on hen     
ora.scan3.vip                                 ONLINE     ONLINE on owl     

Looking at the above output I can clearly see the partial OFFLINE status of my database. From my point of view, is much better.

4 comments:

Anonymous said...

I changed your script for Solaris 10 nawk.

#!/usr/bin/bash
#
# Description:
# - Returns formatted version of crsctl stat res, 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:
#

RSC_KEY=$1
if [ -z "$RSC_KEY" ];
then
RSC_KEY='^'
fi
QSTAT=-u
AWK=/bin/nawk

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

# Table body:
$GRID_HOME/bin/crsctl status resource | $AWK \
'
function ltrim(s) { sub(/^[ \t]+/, "", s); return s }
function rtrim(s) { sub(/[ \t]+$/, "", s); return s }
function trim(s) { return rtrim(ltrim(s)); }

BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; n = split(apptarget, atarget, ","); state=2;}
$1~/STATE/ && state == 2 {appstate = $2; split(appstate, astate, ","); state=3;}
state == 3 { split(appname, a, ",");
for (i = 1; i <= n; i++) {
printf "%-45s %-10s %-18s\n", appname, trim(atarget[i]), trim(astate[i])
};
state=0;}'

Alexandru Tica said...

Great!

Adrian Mielcioiu said...

have to replace

for (i = 1; i <= length(atarget); i++)

with

for (i = 1; i <= length(atarget[i]); i++)

Your script is very useful.

Alexandru Tică said...

Hi Adrian,

Thanks for the update!