forked from freddenis/oracle-scripts
-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathrac-status.sh
executable file
·888 lines (813 loc) · 58.7 KB
/
rac-status.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
#!/bin/bash
# Fred Denis -- Jan 2016 -- http://unknowndba.blogspot.com -- [email protected]
#
# Quickly shows a status of all running instances accross a 12c 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)
#
# 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 20190626
#
# History :
#
# 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
#
# 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, " ") ;
#printf(COLOR_BEGIN WHITE " %-"12"s\n " COLOR_END, ": Has been restarted less than "DIFF_HOURS" hours ago ("IN_DAYS" days ago)") ;
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.") ;
}
}
#
# 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, 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-1"s" COLOR_END"|", "See -->", WHITE); # "See -->"
print_port_later = 1 ;
} else {
printf(COLOR_BEGIN WHITE " %-"COL_VER-1"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, 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-1"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, 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-7"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
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
#*********************************************************************************************************