← RAC
Oracle · RAC
How to get a compact RAC status overview with rac-status.sh
Colored terminal overview of every database, listener, and service across an Oracle RAC cluster with per-node state, from Fred Denis' rac-status.sh utility.
Tested on11g12c18c19c21c23ai26ai
rac-status.sh is a self-contained bash utility that queries crsctl once and prints a color-coded table showing every database instance, listener, and service across every node of an Oracle RAC. Works on 11g, 12c, 18c, 19c and beyond, and colors recently-restarted resources so incidents jump out immediately.
Author: Fred Denis. Source and download: https://github.com/freddenis/oracle-scripts. Full body reproduced below; see the upstream repo for the latest version.
Read-only: runs crsctl queries only. Needs a working oraenv (or the -e option to skip it) and a user allowed to query GI.
#!/bin/bash
# Fred Denis -- Jan 2016 -- http://unknowndba.blogspot.com -- fred.denis3@gmail.com
#
# Quickly shows a status of all running instances accross a 11g, 12c, 18c+ cluster
# The script just needs to have a working oraenv, if rac-status.sh hangs, you may suffer from http://bit.ly/2IODPJo (alternatively ,see the -e option)
# Ultimately, feel free to contact me
#
# Please have a look at http://bit.ly/2MFkzDw for some details and screenshots
# The latest version of the script can be downloaded here : http://bit.ly/2XEXa6j
#
# The current script version is 20190701
#
# History :
#
# 20190701 - Fred Denis - Minor fixes, alignements issues with the sorting
# 20190626 - Fred Denis - Better sorting, better recently restarted legend
# 20190621 - Fred Denis - Fixed a bug on the sorting when version was different as other (12.1 instead of 12.1.0.0)
# - Option -w now also supports d for day, w for week, m for month and y for year to specify the delay
# 20190620 - Fred Denis - Fixed an issue with the sorting when there was recently restarted instances
# 20190617 - Fred Denis - New -c option to sort the databases output
# 20190606 - Fred Denis - Show a yellow background when a resource has been restarted less than DIFF_HOURS hours
# A new -w option can be use to specify a number of hours through the command line
# Owners and groups which contained numbers were ignored, this is fixed
# 20190524 - Fred Denis - Fixed a bug when hostnames had more than 1 "db" pattern in their names
# 20190508 - Fred Denis - Show the whole service name and not only part of it when it contains "."
# 20190426 - Fred Denis - which gawk for AIX
# 20190104 - Fred Denis - A new -r option to Reverse the colors (useful for clear terminal backgrounds)
# A new -u option to show an Uncolored output
# 20190325 - Fred Denis - Solaris sed does not support sed -i, use gsed instead
# New -e option to NOT use oraenv to set the ASM environment but to use the current manually set environment
# (USE_ORAENV="NO" on top of the script to have this permanently)
# 20190318 - Fred Denis - Dont show the owner:group legend about '' menaing same as above if only 1 Home
# 20190307 - Fred Denis - Added owner:group behind the ORACLE_HOME (useful when owner are different) -- thanks Andrey for the feature idea !
# Also removed the P for Primary and S for Stanby legend; it looks self explanatory enough already
# 20190204 - Fred Denis - Oracle Restart support
# 20190130 - Fred Denis - 11g support (BREAK_HERE); 11g and 12c crsctl outputs are quite different
# - A new -o option to specify a file to save the crsctl commands output
# - A new -f option to specify an input file (a file generated by the -o option for example)
# 20190122 - Fred Denis - Multi OS support for AWK (especially for Solaris)
# 20190115 - Fred Denis - Fixed minor alignement issues
# Add grep (-g) and ungrep (-v) feature
# 20181110 - Fred Denis - Show short names in the tables instead of the whole hostnames if possible for better visibility
# - Col 1 and col 2 now align dynamically depending on the largest element to keep all the tables well aligned
# - Dynamic calculation of an offser for the status column size depending on the number of nodes
# - This can also be fixed by setting a non 0 value to COL_NODE_OFFSET on top of the script
# - Better alignements, centered databases and service were not nice, they are now left aligned which is more clear
# 20181010 - Fred Denis - Added the services
# Added default value and options to show and hide some resources (./rac-status.sh -h for more information)
# 20181009 - Fred Denis - Show the usual blue "-" when a target is offline on purpose instead of a red "Offline" which was confusing
# 20180921 - Fred Denis - Added the listeners
# 20180227 - Fred Denis - Make the the size of the DB column dynamic to handle very long database names (Thanks Michael)
# - Added a (P) for Primary databases and a (S) for Stanby for color blind people who
# may not see the difference between white and red (Thanks Michael)
# 20180225 - Fred Denis - Make the multi status like "Mounted (Closed),Readonly,Open Initiated" clear in the table by showing only the first one
# 20180205 - Fred Denis - There was a version alignement issue with more than 10 different ORACLE_HOMEs
# - Better colors for the label "White for PRIMARY, Red for STANBY"
# 20171218 - Fred Denis - Modify the regexp to better accomodate how the version can be in the path (cannot get it from crsctl)
# 20170620 - Fred Denis - Parameters for the size of the columns and some formatting
# 20170619 - Fred Denis - Add a column type (RAC / RacOneNode / Single Instance) and color it depending on the role of the database
# (WHITE for a PRIMARY database and RED for a STANDBY database)
# 20170616 - Fred Denis - Shows an ORACLE_HOME reference in the Version column and an ORACLE_HOME list below the table
# 20170606 - Fred Denis - A new 12cR2 GI feature now shows the ORACLE_HOME in the STATE_DETAILS column from "crsctl -v"
# - Example : STATE_DETAILS=Open,HOME=/u01/app/oracle/product/11.2.0.3/dbdev_1 instead of STATE_DETAILS=Open in 12cR1
# 20170518 - Fred Denis - Add a readable check on the ${DBMACHINE} file - it happens that it exists but is only root readable
# 20170501 - Fred Denis - First release
#
#
# Variables
#
TMP=/tmp/status$$.tmp # A tempfile
TMP2=/tmp/status2$$.tmp # Another tempfile
DBMACHINE=/opt/oracle.SupportTools/onecommand/databasemachine.xml # File where we should find the Exadata model as oracle user
GREP="." # What we grep -- default is everything
UNGREP="nothing_to_ungrep_unless_v_option_is_used$$" # What we don't grep (grep -v) -- default is nothing
USE_ORAENV="YES" # Use oraenv to set the ASM env (-e changes this to NO)
REVERSE="NO" # Revert the colors to make them visible, useful for clear terminal backgrounds
WITH_COLORS="YES" # Output with colors, (-b changes this to NO); set to NO for permanent no colored output
WHITE="37m" # White color code
DIFF_HOURS="24" # Nb of hours the instance has been restarted
SORT_BY="" # Column to sort by (see the help for possible values)
# Choose the information what you want to see -- the last uncommented value wins
# ./rac-status.sh -h for more information
SHOW_DB="YES" # Databases
#SHOW_DB="NO"
SHOW_LSNR="YES" # Listeners
#SHOW_LSNR="NO"
SHOW_SVC="YES" # Services
SHOW_SVC="NO"
# Number of spaces between the status and the "|" of the column - this applies before and after the status
# A value of 2 would print 2 spaces before and after the status and like | Open |
# A value of 8 would print | Open |
# A value of 99 means that this parameter is dynamically calculated depending on the number of nodes
# A non 99 value is applied regardless of the number of nodes
COL_NODE_OFFSET=99
#
# Different OS support
#
OS=`uname`
case ${OS} in
SunOS)
AWK=`which gawk` ;
SED=`which gsed` ;;
Linux)
AWK=`which awk` ;
SED=`which sed` ;;
HP-UX)
AWK=`which awk` ;
SED=`which sed` ;;
AIX)
AWK=`which gawk` ;
SED=`which sed` ;;
*) printf "\n\t\033[1;31m%s\033[m\n\n" "Unsupported OS, cannot continue." ;
exit 666 ;;
esac
# Check if we have an AWK and a SED to continue
if [[ ! -f ${AWK} ]]
then
printf "\n\t\033[1;31m%s" "No awk found on your system, cannot continue, if you run Solaris, please ensure that gawk is in your path"
printf "\t%s\033[m\n\n" ${AWK}
exit 678
fi
if [[ ! -f ${SED} ]]
then
printf "\n\t\033[1;31m%s" "No sed found on your system, cannot continue, if you run Solaris, please ensure that gsed is in your path"
printf "\t%s\033[m\n\n" ${SED}
exit 679
fi
#
# An usage function
#
usage()
{
printf "\n\033[1;37m%-8s\033[m\n" "NAME" ;
cat << END
`basename $0` - A nice overview of databases, listeners and services running across a GI 12c
END
printf "\n\033[1;37m%-8s\033[m\n" "SYNOPSIS" ;
cat << END
$0 [-a] [-n] [-d] [-l] [-s] [-o] [-f] [-r] [-u] [-h]
END
printf "\n\033[1;37m%-8s\033[m\n" "DESCRIPTION" ;
cat << END
`basename $0` needs to be executed with a user allowed to query GI using crsctl; oraenv also has to be working
`basename $0` will show what is running or not running accross all the nodes of a GI 12c :
- The databases instances (and the ORACLE_HOME they are running against)
- The type of database : Primary, Standby, RAC One node, Single
- The listeners (SCAN Listener and regular listeners)
- The services
With no option, `basename $0` will show what is defined by the variables :
- SHOW_DB # To show the databases instances
- SHOW_LSNR # To show the listeners
- SHOW_SVC # To show the services
These variables can be modified in the script itself or you can use command line option to revert their value (see below)
END
printf "\n\033[1;37m%-8s\033[m\n" "OPTIONS" ;
cat << END
-a Show everything regardless of the default behavior defined with SHOW_DB, SHOW_LSNR and SHOW_SVC
-n Show nothing regardless of the default behavior defined with SHOW_DB, SHOW_LSNR and SHOW_SVC
-a and -n are handy to erase the defaults values:
$ ./rac-status.sh -n -d # Show the databases output only
$ ./rac-status.sh -a -s # Show everything but the services (then the listeners and the databases)
-d Revert the behavior defined by SHOW_DB ; if SHOW_DB is set to YES to show the databases by default, then the -d option will hide the databases
-l Revert the behavior defined by SHOW_LSNR; if SHOW_LSNR is set to YES to show the listeners by default, then the -l option will hide the listeners
-s Revert the behavior defined by SHOW_SVC ; if SHOW_SVC is set to YES to show the services by default, then the -s option will hide the services
-g Act as a grep command to grep a pattern from the output (key sensitive)
-v Act as "grep -v" to ungrep from the output
-g and -v examples :
$ ./rac-status.sh -g Open # Show only the lines with "Open" on it
$ ./rac-status.sh -g Open # Show only the lines with "Open" on it
$ ./rac-status.sh -g "Open|Online" # Show only the lines with "Open" or "Online" on it
$ ./rac-status.sh -g "Open|Online" -v 12 # Show only the lines with "Open" or "Online" on it but no those containing 12
-c Column to sort by, please have a look at "Sort the database output" in http://bit.ly/2MFkzDw for more details on this -c option
-o Specify a file to save the crsctl commands output
$ ./rac-status.sh -o /tmp/rac-status_output.log
-f A file to use as input file (one generated by the -o option for example)
$ ./rac-status.sh -f /tmp/rac-status_output.log
-e Do not use oraenv to set the ASM environment but relies on the current environment
Set USE_ORAENV="NO" on top of the script to have a permanent -e option
-r Reverse the colors (useful for clear terminal backgrounds)
-u Shows the Uncolored output (no colors); set WITH_COLORS="NO" on top of the script to have it permanently
-w Show a yellow background when a resource has been restarted less than the number of hours in parameter (default is $DIFF_HOURS)
h for hours (default) d for day, w for week, m for month and y for year can be used to specify the delay:
$ ./rac-status.sh -w 24 # 24 hours
$ ./rac-status.sh -w 24h # 24 hours
$ ./rac-status.sh -w 2d # 2 days
$ ./rac-status.sh -w 3m # 3 months
-h Shows this help
Note : the options are cumulative and can be combined with a "the last one wins" behavior :
$ $0 -a -l # Show everything but the listeners (-a will force show everything then -l will hide the listeners)
$ $0 -n -d # Show only the databases (-n will force hide everything then -d with show the databases)
Experiment and enjoy !
END
exit 123
}
# Options
while getopts "andslhg:v:o:f:eruw:c:" OPT; do
case ${OPT} in
a) SHOW_DB="YES" ; SHOW_LSNR="YES" ; SHOW_SVC="YES" ;;
n) SHOW_DB="NO" ; SHOW_LSNR="NO" ; SHOW_SVC="NO" ;;
d) if [ "$SHOW_DB" = "YES" ]; then SHOW_DB="NO"; else SHOW_DB="YES"; fi ;;
s) if [ "$SHOW_SVC" = "YES" ]; then SHOW_SVC="NO"; else SHOW_SVC="YES"; fi ;;
l) if [ "$SHOW_LSNR" = "YES" ]; then SHOW_LSNR="NO"; else SHOW_LSNR="YES"; fi ;;
g) GREP=${OPTARG} ;;
c) SORT_BY=${OPTARG} ;;
v) UNGREP=${OPTARG} ;;
f) FILE=${OPTARG} ;;
o) OUT=${OPTARG} ;;
e) USE_ORAENV="NO" ;;
r) REVERSE="YES" ;;
w) DIFF_HOURS=${OPTARG} ;;
u) WITH_COLORS="NO" ;;
h) usage ;;
\?) echo "Invalid option: -$OPTARG" >&2; usage ;;
esac
done
#
# Manage the diff hours depending on the unit in the -w option
#
DIFF_HOURS_UNIT=${DIFF_HOURS: -1}
if [[ ! "${DIFF_HOURS_UNIT}" =~ [0-9] ]]
then
HOURS=`echo ${DIFF_HOURS} | sed s'/.$//'`
case ${DIFF_HOURS_UNIT} in
h) NB_HOURS=1 ;;
d) NB_HOURS=24 ;;
w) NB_HOURS=$((24*7)) ;;
m) NB_HOURS=$((24*7*31)) ;;
y) NB_HOURS=$((24*7*31*365)) ;;
esac
DIFF_HOURS=$(($HOURS * $NB_HOURS))
else
DIFF_HOURS_UNIT="h"
HOURS=${DIFF_HOURS}
fi
#
# If we dont show the DB we dont need to sort
#
if [ "$SHOW_DB" = "NO" ]
then
SORT_BY=""
fi
#
# Check that the input file is here if specified
#
if [[ "${REVERSE}" == "YES" ]]
then
WHITE="30m" ; # Black
fi
if [ -n "$FILE" ] # Input file specified, we wont run any crsctl command and rely on the file as input
then
if [ ! -f ${FILE} ]
then
printf "\n\t\033[1;31m%s\033[m\n\n" "Cannot find the ${FILE} input file; cannot continue"
exit 222
else # we use $FILE as input
printf "\n\t\033[1;34m%s\033[m\n\n" "Proceeding with the ${FILE} file as input file"
fi
fi
if [ -z "$FILE" ] # This is not needed when using an input file
then
if [[ "${USE_ORAENV}" == "YES" ]]
then
#
# Set the ASM env to be able to use crsctl commands
#
ORACLE_SID=`ps -ef | grep pmon | grep asm | ${AWK} '{print $NF}' | sed s'/asm_pmon_//' | egrep "^[+]"`
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
fi
if ! type crsctl > /dev/null 2>&1
then
printf "\n\t\033[1;31m%s\033[m\n\n" "Cannot find crsctl, cannot continue, please check if oraenv works or set your environment manually and use the -e option." ;
exit 777
fi
#
# List of the nodes of the cluster
#
# Try to find if there is "db" in the hostname, if yes we can delete the common "<clustername>" pattern from the hosts for visibility
SHORT_NAMES="NO"
if [[ `olsnodes | head -1 | sed s'/,.*$//g' | tr '[:upper:]' '[:lower:]'` == *"db"* ]]
then
NODES=`olsnodes | sed s'/^.*db/db/g' | ${AWK} '{if (NR<2){txt=$0} else{txt=txt","$0}} END {print txt}'`
# CLUSTER_NAME=`olsnodes | head -1 | sed s'/db.*$//g'`
CLUSTER_NAME=`olsnodes | head -1 | sed s'/\(^.*\)db.*/\1/'`
SHORT_NAMES="YES"
else
NODES=`olsnodes | ${AWK} '{if (NR<2){txt=$0} else{txt=txt","$0}} END {print txt}'`
CLUSTER_NAME=`olsnodes -c`
fi
# if oracle restart, olsnodes is here but returns nothing, we then set the NODES with the current hostname
if [ -z "${NODES}" ]
then
NODES=`hostname -s`
fi
if [[ "$WITH_COLORS" == "YES" ]]
then
COLOR_FOR_CLUSTER="\e[1;"${WHITE}
else
COLOR_FOR_CLUSTER=""
fi
printf "\n\t\t%s"${COLOR_FOR_CLUSTER}"%s\e[m" "Cluster " "$CLUSTER_NAME"
#
# Show the Exadata model if possible (if this cluster is an Exadata)
#
if [ -f ${DBMACHINE} ] && [ -r ${DBMACHINE} ]
then
MODEL=`grep -i MACHINETYPES ${DBMACHINE} | sed -e s':</*MACHINETYPES>::g' -e s'/^ *//' -e s'/ *$//'`
printf "%s"${COLOR_FOR_CLUSTER}"%s\e[m\n" " is a " "$MODEL"
else
printf "\n"
fi
printf "\n"
# Get the info we want
cat /dev/null > $TMP
if [ "$SHOW_DB" = "YES" ]
then
crsctl stat res -p -w "TYPE = ora.database.type" >> $TMP
crsctl stat res -v -w "TYPE = ora.database.type" >> $TMP
fi
if [ "$SHOW_LSNR" = "YES" ]
then
crsctl stat res -v -w "TYPE = ora.listener.type" >> $TMP
crsctl stat res -p -w "TYPE = ora.listener.type" >> $TMP
crsctl stat res -v -w "TYPE = ora.scan_listener.type" >> $TMP
crsctl stat res -p -w "TYPE = ora.scan_listener.type" >> $TMP
fi
if [ "$SHOW_SVC" = "YES" ]
then
crsctl stat res -v -w "TYPE = ora.service.type" >> $TMP
#crsctl stat res -p -w "TYPE = ora.service.type" >> $TMP # not used, in case we need it one day
fi
# Easiest way to manage the different versions of crsctl outputs
awk '{if ($1 ~ /^NAME=/) {print "BREAK_HERE"; print $0} else {print $0}}' $TMP > $TMP2
cp ${TMP2} ${TMP}
if [ "$SHORT_NAMES" = "YES" ]
then
${SED} -i "s/$CLUSTER_NAME//g" $TMP
fi
NB_NODES=`olsnodes | wc -l`
else # If we use an input file
cp ${FILE} ${TMP}
NODES=`grep LAST_SERVER $TMP | awk -F"=" '{print $2}' | sort | uniq | grep -v "^$" | awk '{if (NR<2){txt=$0} else{txt=txt","$0}} END {print txt}'`
NB_NODES=`grep LAST_SERVER $TMP | awk -F"=" '{print $2}' | sort | uniq | wc -l`
fi # End if [ -z "$FILE" ]
#
# Define the offset to apply to the status column depending on the number of nodes to make the tables visible for big implementations
#
if [ "$COL_NODE_OFFSET" = "99" ]
then
COL_NODE_OFFSET=3 ;
if [ "$NB_NODES" -eq "2" ]; then COL_NODE_OFFSET=6 ; fi ;
if [ "$NB_NODES" -eq "4" ]; then COL_NODE_OFFSET=5 ; fi ;
if [ "$NB_NODES" -gt "4" ]; then COL_NODE_OFFSET=3 ; fi ;
fi
${AWK} -v NODES="$NODES" -v col_node_offset="$COL_NODE_OFFSET" \
-v REVERSE="$REVERSE" \
-v DIFF_HOURS="$DIFF_HOURS" \
-v HOURS="$HOURS" \
-v DIFF_HOURS_UNIT="$DIFF_HOURS_UNIT" \
'BEGIN\
{ FS = "=" ;
n = split(NODES, nodes, ",") ; # Make a table with the nodes of the cluster
# some colors
COLOR_BEGIN = "\033[1;" ;
#COLOR_BEGIN = "\033[" ;
COLOR_END = "\033[m" ;
RED = "31m" ;
GREEN = "32m" ;
YELLOW = "33m" ;
BLUE = "34m" ;
TEAL = "36m" ;
WHITE = "37m" ;
WITH_BACK = "43m" ;
if (REVERSE == "YES")
{
WHITE = "30m" ; # Black
TEAL = "34m" ; # Blue
COLOR_BEGIN = "\033[2;" ; # Bold
}
UNKNOWN = "-" ; # Something to print when the status is unknown
# Default columns size
COL_NODE = 0 ;
COL_NODE_OFFSET = col_node_offset * 2 ; # Defined on top the script, have a look for explanations on this
COL_DB = 12 ;
COL_VER = 15 ;
COL_TYPE = 14 ;
COL_OH = 24 ; # to print the ORACLE_HOMEs
COL_OWNER = 6 ; # to print owner:group
COL_GROUP = 3 ; # to print owner:group
COL_DEFAULT = BLUE ; # for the "-"
RECENT_RESTARTED = 0 ; # To show a legend if we found a recent restarted
}
#
# A function to center the outputs with colors
#
function center( str, n, color)
{ right = int((n - length(str)) / 2) ;
left = n - length(str) - right ;
return sprintf(COLOR_BEGIN color "%" left "s%s%" right "s" COLOR_END "|", "", str, "" ) ;
}
#
# Get a date in format MM/DD/YYYY HH24:MI:SS and return the rounded number hours difference
# between this date and the current date
function diff_hours(a_date)
{ split(a_date, temp, /[\/ :]/) ;
#return sprintf("%d",(systime()-mktime (temp[3]" "temp[1]" "temp[2]" "temp[4]" "temp[5]" "temp[6]))/(60*60)) ;
return (systime()-mktime (temp[3]" "temp[1]" "temp[2]" "temp[4]" "temp[5]" "temp[6]))/(60*60) ;
}
#
# Print a legend for the recent restarted instances, listeners and services
#
function print_legend_recent_restarted()
{
if (RECENT_RESTARTED == 1)
{ #IN_DAYS=sprintf("%.2f", DIFF_HOURS/24) ; # Round nb of days
printf ("\n") ;
printf(COLOR_BEGIN WITH_BACK " %-"3"s" COLOR_END, " ") ;
if (DIFF_HOURS_UNIT == "h") { UNIT="hour" }
if (DIFF_HOURS_UNIT == "d") { UNIT="day" }
if (DIFF_HOURS_UNIT == "w") { UNIT="week" }
if (DIFF_HOURS_UNIT == "m") { UNIT="month" }
if (DIFF_HOURS_UNIT == "y") { UNIT="year" }
if (HOURS > 1) { UNIT=UNIT"s" }
printf(COLOR_BEGIN WHITE " %-"12"s\n " COLOR_END, ": Has been restarted less than "HOURS" "UNIT" ago.") ;
RECENT_RESTARTED = 0 ;
}
}
#
# A function that just print a "---" white line
#
function print_a_line(size)
{
if ( ! size)
{ size = COL_DB+COL_VER+(COL_NODE*n)+COL_TYPE+n+3 ;
}
printf("%s", COLOR_BEGIN WHITE) ;
for (k=1; k<=size; k++) {printf("%s", "-");} ; # n = number of nodes
printf("%s", COLOR_END"\n") ;
}
{
# Fill 2 tables with the OH and the version from "crsctl stat res -p -w "TYPE = ora.database.type""
if ($1 == "NAME")
{
sub("^ora.", "", $2) ;
sub(".db$", "", $2) ;
if ($2 ~ ".lsnr"){sub(".lsnr$", "", $2); tab_lsnr[$2] = $2;} ; # Listeners
if ($2 ~ ".svc") # Services
{ sub(".svc$", "", $2) ;
tab_svc[$2]=$2 ;
service=$2 ;
sub(/^[^.]*\./, "", service) ; # Remove the DB name
if (length(service) > COL_VER-1) # To adapt the column size
{ COL_VER = length(service) +1 ;
}
}
DB=$2 ;
split($2, temp, ".") ;
if (length(temp[1]) > COL_DB-1) # To adapt the 1st column size
{ COL_DB = length(temp[1]) +1 ;
}
getline; getline ;
if ($1 == "ACL") # crsctl stat res -p output
{
if ((DB in version == 0) && (DB in tab_lsnr == 0) && (DB in tab_svc == 0))
{
# Get the owner and the group
match($2, /owner:([[:alnum:]]*):.*/, OWNER) ;
match($2, /^.*pgrp:([[:alnum:]]*):.*/, GROUP) ;
while (getline)
{
if ($1 == "ORACLE_HOME")
{ OH = $2 ;
match($2, /1[0-9]\.[0-9]\.?[0-9]?\.?[0-9]?/) ; # Grab the version from the OH path)
VERSION = substr($2,RSTART,RLENGTH) ;
}
if ($1 == "DATABASE_TYPE") # RAC / RACOneNode / Single Instance are expected here
{
dbtype[DB] = $2 ;
}
if ($1 == "ROLE") # Primary / Standby expected here
{ role[DB] = $2 ;
}
if ($0 ~ /^$/)
{ version[DB] = VERSION ;
oh[DB] = OH ;
if (!(OH in oh_list))
{
oh_ref++ ;
oh_list[OH] = oh_ref ;
o_list[OH] = OWNER[1] ;
g_list[OH] = GROUP[1] ;
if (length(OH) > COL_OH) { COL_OH = length(OH) ; }
if (length(OWNER[1]) > COL_OWNER) { COL_OWNER = length(OWNER[1]) ; }
if (length(GROUP[1]) > COL_GROUP) { COL_GROUP = length(GROUP[1]) ; }
}
break ;
}
}
}
if (DB in tab_lsnr == 1)
{
while(getline)
{
if ($1 == "ENDPOINTS")
{
port[DB] = $2 ;
break ;
}
}
}
}
if ($1 == "LAST_SERVER") # crsctl stat res -v output
{ NB = 0 ; # Number of instance as CARDINALITY_ID is sometimes irrelevant
SERVER = $2 ;
if (length(SERVER) > COL_NODE)
{ COL_NODE = length(SERVER) + COL_NODE_OFFSET ;
}
while (getline)
{
if ($1 == "LAST_SERVER") { SERVER = $2 ; }
if ($1 == "STATE") { gsub(" on .*$", "", $2) ;
if ($2 ~ /ONLINE/ ) {STATE="Online" ;
if (length(STATE) > COL_NODE) { COL_NODE = length(STATE) + COL_NODE_OFFSET;}
}
if ($2 ~ /OFFLINE/) {STATE="" ;}
}
if ($1 == "TARGET") { TARGET = $2 ;}
if ($1 == "LAST_RESTART") { started[DB,SERVER]=diff_hours($2" "$3) ;}
if ($1 == "STATE_DETAILS") { NB++ ; # Number of instances we came through
sub("STATE_DETAILS=", "", $0) ;
sub(",HOME=.*$", "", $0) ; # Manage the 12cR2 new feature, check 20170606 for more details
sub("),.*$", ")", $0) ; # To make clear multi status like "Mounted (Closed),Readonly,Open Initiated"
if ($0 == "")
{ status[DB,SERVER] = STATE ;}
else {
if ($0 == "Instance Shutdown") { status[DB,SERVER] = "Shutdown" ; } else
if ($0 ~ "Readonly") { status[DB,SERVER] = "Readonly" ; } else
if ($0 ~ /Mount/) { status[DB,SERVER] = "Mounted" ; } else
{ status[DB,SERVER] = $0 ; }
if (length(status[DB,SERVER]) > COL_NODE)
{ COL_NODE = length(status[DB,SERVER]) + COL_NODE_OFFSET ;
}
}
}
if ($1 == "BREAK_HERE") { break;}
}
}
} # End of if ($1 ~ /^NAME/)
}
END { if (length(tab_lsnr) > 0) # We print only if we have something to show
{
# A header for the listeners
printf("%s", center("Listener" , COL_DB, WHITE)) ;
printf("%s", center("Port" , COL_VER+1, WHITE)) ;
n=asort(nodes) ; # sort array nodes
for (i = 1; i <= n; i++) {
printf("%s", center(nodes[i], COL_NODE, WHITE)) ;
}
printf("%s", center("Type" , COL_TYPE, WHITE)) ;
printf("\n") ;
# a "---" line under the header
print_a_line() ;
# print the listeners
x=asorti(tab_lsnr, lsnr_sorted) ;
for (j = 1; j <= x; j++)
{
printf(COLOR_BEGIN WHITE " %-"COL_DB-1"s" COLOR_END"|", lsnr_sorted[j], WHITE); # Listener name
# It may happen that listeners listen on many ports then it wont fit this column
# We then print it outside of the table after the last column
if (length(port[lsnr_sorted[j]]) > COL_VER)
{
printf(COLOR_BEGIN WHITE " %-"COL_VER"s" COLOR_END"|", "See -->", WHITE); # "See -->"
print_port_later = 1 ;
} else {
printf(COLOR_BEGIN WHITE " %-"COL_VER"s" COLOR_END"|", port[lsnr_sorted[j]], WHITE); # Port
}
for (i = 1; i <= n; i++)
{
dbstatus = status[lsnr_sorted[j],nodes[i]] ;
if ((started[lsnr_sorted[j],nodes[i]] < DIFF_HOURS) && (started[lsnr_sorted[j],nodes[i]]))
{ COL_ONLINE=WITH_BACK ;
COL_OTHER=WITH_BACK ;
RECENT_RESTARTED=1 ;
} else {
COL_ONLINE=GREEN ;
COL_OTHER=RED ;
}
if (dbstatus == "") {printf("%s", center(UNKNOWN , COL_NODE, COL_DEFAULT )) ;} else
if (dbstatus == "Online") {printf("%s", center(dbstatus, COL_NODE, COL_ONLINE )) ;}
else {printf("%s", center(dbstatus, COL_NODE, COL_OTHER )) ;}
}
if (toupper(lsnr_sorted[j]) ~ /SCAN/)
{ LSNR_TYPE = "SCAN" ;
} else {
LSNR_TYPE = "Listener" ;
}
printf("%s", center(LSNR_TYPE, COL_TYPE, WHITE)) ;
if (print_port_later)
{ print_port_later = 0 ;
printf(COLOR_BEGIN WHITE " %-"COL_VER-1"s" COLOR_END, port[lsnr_sorted[j]], WHITE); # Port
}
printf("\n") ;
}
# a "---" line under the header
print_a_line() ;
printf("\n") ;
}
if (length(tab_svc) > 0) # We print only if we have something to show
{
# A header for the services
printf("%s", center("DB" , COL_DB, WHITE)) ;
printf("%s", center("Service" , COL_VER+1, WHITE)) ;
n=asort(nodes) ; # sort array nodes
for (i = 1; i <= n; i++) {
printf("%s", center(nodes[i], COL_NODE, WHITE)) ;
}
printf("\n")
# a "---" line under the header
print_a_line(COL_DB+COL_NODE*n+COL_VER+n+2) ;
# Print the Services
x=asorti(tab_svc, svc_sorted) ;
for (j = 1; j <= x; j++)
{ split(svc_sorted[j], to_print, ".") ; # The service we have is <db_name>.<service_name>
service = svc_sorted[j] ;
sub(/^[^.]*\./, "", service) ; # Remove the DB name only
if (previous_db != to_print[1]) # Do not duplicate the DB names on the output
{
printf(COLOR_BEGIN WHITE " %-"COL_DB-1"s" COLOR_END"|", to_print[1], WHITE); # Database
previous_db = to_print[1] ;
}else {
printf("%s", center("", COL_DB, WHITE)) ;
}
#printf(COLOR_BEGIN WHITE " %-"COL_VER-1"s" COLOR_END"|", to_print[2], WHITE); # Service
printf(COLOR_BEGIN WHITE " %-"COL_VER"s" COLOR_END"|", service, WHITE); # Service
for (i = 1; i <= n; i++)
{
dbstatus = status[svc_sorted[j],nodes[i]] ;
if ((started[svc_sorted[j],nodes[i]] < DIFF_HOURS) && (started[svc_sorted[j],nodes[i]] < DIFF_HOURS))
{ COL_ONLINE=WITH_BACK ;
COL_OTHER=WITH_BACK ;
RECENT_RESTARTED=1 ;
} else {
COL_ONLINE=GREEN ;
COL_OTHER=RED ;
}
if (dbstatus == "") {printf("%s", center(UNKNOWN , COL_NODE, COL_DEFAULT )) ;} else
if (dbstatus == "Online") {printf("%s", center(dbstatus, COL_NODE, COL_ONLINE )) ;}
else {printf("%s", center(dbstatus, COL_NODE, COL_OTHER )) ;}
}
printf("\n") ;
}
# a "---" line under the header
print_a_line(COL_DB+COL_NODE*n+COL_VER+n+2) ;
printf("\n") ;
}
if (length(version) > 0) # We print only if we have something to show
{
# A header for the databases
printf("%s", center("DB" , COL_DB, WHITE)) ;
printf("%s", center("Version" , COL_VER+1, WHITE)) ;
n=asort(nodes) ; # sort array nodes
for (i = 1; i <= n; i++) {
printf("%s", center(nodes[i], COL_NODE, WHITE)) ;
}
printf("%s", center("DB Type" , COL_TYPE, WHITE)) ;
printf("\n") ;
# a "---" line under the header
print_a_line() ;
# Print the databases
m=asorti(version, version_sorted) ;
for (j = 1; j <= m; j++)
{
printf(COLOR_BEGIN WHITE " %-"COL_DB-1"s" COLOR_END"|", version_sorted[j], WHITE); # Database
printf(COLOR_BEGIN WHITE " %-"COL_VER-6"s" COLOR_END, version[version_sorted[j]], COL_VER, WHITE) ; # Version
printf(COLOR_BEGIN WHITE "%6s" COLOR_END"|"," ("oh_list[oh[version_sorted[j]]] ") ") ; # OH id
for (i = 1; i <= n; i++) {
dbstatus = status[version_sorted[j],nodes[i]] ;
#
# Print the status here, all that are not listed in that if ladder will appear in RED
#
if ((started[version_sorted[j],nodes[i]] < DIFF_HOURS) && (started[version_sorted[j],nodes[i]]))
{ COL_OPEN=WITH_BACK ;
COL_READONLY=WITH_BACK ;
COL_SHUT=WITH_BACK ;
COL_OTHER=WITH_BACK ;
RECENT_RESTARTED=1 ;
} else {
COL_OPEN=GREEN ;
COL_READONLY=WHITE ;
COL_SHUT=YELLOW ;
COL_OTHER=RED ;
}
if (dbstatus == "") {printf("%s", center(UNKNOWN , COL_NODE, COL_DEFAULT )) ;} else
if (dbstatus == "Open") {printf("%s", center(dbstatus, COL_NODE, COL_OPEN )) ;} else
if (dbstatus ~ /Readonly/) {printf("%s", center(dbstatus, COL_NODE, COL_READONLY)) ;} else
if (dbstatus ~ /Shut/) {printf("%s", center(dbstatus, COL_NODE, COL_SHUT )) ;} else
{printf("%s", center(dbstatus, COL_NODE, COL_OTHER )) ;}
}
#
# Color the DB Type column depending on the ROLE of the database (20170619)
#
if (role[version_sorted[j]] == "PRIMARY") { ROLE_COLOR=WHITE ; ROLE_SHORT=" (P)"; } else { ROLE_COLOR=RED ; ROLE_SHORT=" (S)" }
printf("%s", center(dbtype[version_sorted[j]] ROLE_SHORT, COL_TYPE, ROLE_COLOR)) ;
printf("\n") ;
}
# a "---" line as a footer
print_a_line() ;
# Print the OH list and a legend for the DB Type colors underneath the table
printf ("%s", "ORACLE_HOME references listed in the Version column ") ;
if (oh_ref > 1)
{
printf ("(%s)", "\"" sprintf(COLOR_BEGIN TEAL "%s" COLOR_END, "\47\47") "\" means \"same as above\"") ;
}
printf ("\n\n") ;
previous_group = "" ;
previous_owner = "" ;
if (COL_OWNER%2) { COL_OWNER++ }
if (COL_GROUP%2) { COL_GROUP++ }
g_same_as_above=sprintf(COLOR_BEGIN TEAL "%"(COL_GROUP/2)-1"s%s" COLOR_END, "", "\47\47");
o_same_as_above=sprintf(COLOR_BEGIN TEAL "%"(COL_OWNER/2)-1"s%s%"(COL_OWNER/2)-1"s" COLOR_END, "", "\47\47", "");
# to ease the ORACLE_HOME sorting
for (x in oh_list)
{
to_print[oh_list[x]] = x ;
}
for (i=1; i<=oh_ref; i++)
{
# to ease the naming
the_oh=to_print[i] ;
owner=o_list[to_print[i]] ;
group=g_list[to_print[i]] ;
if (group == previous_group) { group_to_print = g_same_as_above ; } else { group_to_print = group ; }
if (owner == previous_owner) { owner_to_print = o_same_as_above ; } else { owner_to_print = owner ; }
printf("\t%2d : %-"COL_OH"s\t%-"COL_OWNER"s %s\n", i, the_oh, owner_to_print, group_to_print) ;
previous_group = group ;
previous_owner = owner ;
}
}
}
END { print_legend_recent_restarted();
} ' $TMP | ${AWK} -v GREP="$GREP" -v UNGREP="$UNGREP" ' BEGIN {FS="|"} # AWK used to grep and ungrep
{ if ((NF >= 3) && ($(NF-1) !~ /Type/) && ($2 !~ /Service/))
{ if (($0 ~ GREP) && ($0 !~ UNGREP))
{
print $0 ;
}
} else {
print $0 ;
}
}' | sed s'/^/ /' > ${TMP2} # We can reuse TMP2 here
#
# Special sort order (option -c)
#
if [[ -n ${SORT_BY} ]] # Special sort order
then
SORT_COL="${SORT_BY:0:1}" # First character
SORT_NODE="${SORT_BY:1:1}" # Second character
SORT_ORDER="${SORT_BY: -1}" # Last character
if [[ "${SORT_COL}" =~ [1-9] ]]
then SORT_NODE=${SORT_COL}
SORT_COL="c"
fi
# Sort order can only be "r" for reverse or "" for normal
if [[ "${SORT_ORDER}" != "r" ]]
then SORT_ORDER=""
else SORT_ORDER="r"
fi
# Column or node number
if [[ ! "${SORT_NODE}" =~ [1-9] ]]
then SORT_NODE=1
fi
# Assign the column number depending of what we want to sort by
SORT_NUM=1
SORT_NUM2=2 # Second column to sort by
SORT_NUM3=2 # Third column to sort by
case ${SORT_COL} in
c ) if [[ "${SORT_NODE}" -gt "2" ]]
then
SORT_NUM=$(((${SORT_NODE}*2)+2))
SORT_NUM2=$((${SORT_NUM}-1))
else SORT_NUM=$(( ${SORT_NODE}*2 ))
fi ;; # Sort by column number
d ) SORT_NUM=2 ;; # Sort by DB name
v ) SORT_NUM=4 ;; # Sort by version
s ) SORT_NUM=$(((${SORT_NODE}*2)+6)) ; # Sort by status (Shutdown, Open)
SORT_NUM2=$((${SORT_NUM}-1)) ;;
t ) TYPE_COL=`cat ${TMP2} | awk 'BEGIN {FS="|"}{if ($2 ~ "Version"){print (NF-1); exit}}'` ;
SORT_NUM=$(((${TYPE_COL}*2)+1)) ;; # Sort by Type
esac
SORT_K_1=" -k"${SORT_NUM}${SORT_ORDER}" "
SORT_K_2=" -k"${SORT_NUM2}" "
SORT_K_3=" -k"${SORT_NUM3}" "
cat ${TMP2} | awk 'BEGIN {FS="|"} {print $0; if ($2 ~ "Version"){getline; print $0; exit;}}' > ${TMP}
cat ${TMP2} | awk 'BEGIN {FS="|"}{if ($2 ~ "Version"){getline; while(getline){if ($0 ~ /---------------/){break}; print $0; }}}' | sort -i ${SORT_K_1} ${SORT_K_2} ${SORT_K_3} >> ${TMP}
tac ${TMP2} | awk '{print $0; if ($0 ~ /---------------/){exit;}}' | tac >> ${TMP}
cp ${TMP} ${TMP2}
fi
if [[ "$WITH_COLORS" == "YES" ]]
then
cat ${TMP2}
else
cat ${TMP2} | sed -r "s/\x1B\[([0-9]{1,2}(;[0-9]{1,2})?)?[m|K]//g" # Remove the colors
fi
printf "\n"
if [ -f ${TMP} ]
then
if [ -n "$OUT" ]
then
cp $TMP $OUT
printf "\n\t\033[1;34m%s\033[m\n\n" "Output file $OUT has been generated"
fi
rm -f ${TMP}
fi
if [ -f ${TMP2} ]
then
rm -f ${TMP2}
fi
#*********************************************************************************************************
# E N D O F S O U R C E
#*********************************************************************************************************
ractoolcrsctlthird-party