-
Notifications
You must be signed in to change notification settings - Fork 1
/
rac-on_all_db.sh
128 lines (114 loc) · 6.83 KB
/
rac-on_all_db.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
#!/bin/bash
#
# Fred Denis -- Nov 2017 -- http://unknowndba.blogspot.com -- [email protected]
#
# Execute a SQL command on all databases wherever they have an instance opened
# -- The node where this script is executed should have ssh key deployed to all the other database nodes
# -- oraenv should be working on every server
# -- Look for "Query customization" to customize your query
#
# More information here : https://unknowndba.blogspot.com.au/2018/04/rac-onalldbsh-easily-execute-query-on.html
#
# Version of the script is 20180318
# 20210407 -- Fred Denis -- Remove the use of bc as some dont have bc installed and it is easy to bypass
# 20180318 -- Fred Denis -- Initial release
#
#
# Set the default output to 90% of the screen size
#
COLS=$(printf %.f $(($(tput cols)*90/100)))
#
# If for any reason we couldn't get the number of cols, we set it to 120
#
if ! [[ $COLS =~ ^[0-9]+$ ]]
then
COLS=120 # Size of the output
fi
COLS=120 # Not sure the above dynamic output is useful here, set COLS to 120 for now
# Comment this line if you want the output to be dynamic
#
# 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
for X in `crsctl stat res -v -w "TYPE = ora.database.type" |\
awk ' BEGIN {FS="="}
{
if (($1 ~ /^NAME/) && ($0 ~ //))
{
sub("^ora.", "", $2) ;
sub(".db$" , "", $2) ;
DB=$2 ;
while(getline)
{
if (($1 ~ /STATE/) && ($2 ~/ONLINE/))
{
gsub (".*on ", "", $2) ;
print DB"|"$2 ;
next ;
}
}
}
}'`
do
DB=`echo $X | awk -F "|" '{print $1}'`
SERVER=`echo $X | awk -F "|" '{print $2}'`
INSTANCE=${DB}`echo "${SERVER: -1}"`
printf "\n\n\t\t\033[1;37m%30s\033[m\n" "Query Result on $INSTANCE@$SERVER"
(ssh -q -o batchmode=yes oracle@${SERVER} << END_SSH 2> /dev/null
. oraenv <<< ${DB} > /dev/null 2>&1
export ORACLE_SID=${INSTANCE}
sqlplus -S / as sysdba << END_SQL
set lines $COLS ;
--------------------------------
-- Query customization --
--------------------------------
set echo off ;
set term off ;
set wrap on ;
col name for a50 ;
col value for a60 ;
col host_name for a26 ;
col instance_name for a18 ;
set pages 5000 ;
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS' ;
select instance_name, host_name, version, sysdate from v\\\$instance ;
-- select name, value from v\\\$parameter where name like '%exafusion%' ;
select name, value from v\\\$parameter order by name ;
--------------------------------
-- End of query customization --
--------------------------------
END_SQL
END_SSH
) | grep -v logout | grep -v altered | grep -v profile | tail -n +3 \
| awk -v COLS="$COLS" ' BEGIN\
{ # some colors
COLOR_BEGIN = "\033[1;" ;
COLOR_END = "\033[m" ;
RED = "31m" ;
GREEN = "32m" ;
YELLOW = "33m" ;
BLUE = "34m" ;
TEAL = "36m" ;
WHITE = "37m" ;
print_a_line() ;
}
function print_a_line()
{
printf("%s", COLOR_BEGIN BLUE) ;
for (k=1; k<3 ; k++) {printf("%s", " ")} ;
for (k=3; k<=COLS; k++) {printf("%s", "-")} ;
printf("%s\n", COLOR_END) ;
}
{
printf(COLOR_BEGIN BLUE " %1s" COLOR_END "%-118s\n", "|\t", $0) ;
}
END\
{ print_a_line() ;
}
'
done
#*******************************************************************************************************#
# E N D O F S O U R C E #
#*******************************************************************************************************#