Scripted Collection of OS Watcher Files on Exadata

I’ve been working a lot with graphing DB and OS metrics in R. I find it especially useful in Exadata POVs (proof of value) to gather and graph the oswatcher vmstat files for the compute nodes and iostat for the cells. For an example, take a look at this graph (PDF, 168 KB) of what happens when you have a connection pooling issue and keep creating connections throughout the test (14,930 to be precise). It’s a nice picture of what happened to the OS as it ran out of memory, started swapping, then fell over. To quote the Real World Performance Team: “When Databases swap, Databases stop”.

Anyway, back to the point of this post. OS Watcher files are automatically collected on each db node and storage cell. It’s not too bad to get them manually on a 1/4 rack as that’s only 5 components, but what about a full X3-2 with 22 components? Or the previous POV I did with 3 X2-8’s which had 42 storage cells and 6 DB nodes? Note that support will often ask for these files when you have an issue and log an SR.

Here’s a script I wrote to collect the files with various find options in the comments at the top. It also optionally takes in 1 parameter of a name to include in the tar.gz files so you can identify them easier. It will create 1 tar.gz file for each component, so in the case of a 1/4 rack it will create 5 files.

#/bin/bash
file_name=""
if [ $# -gt 0 ]
then
	file_name="_$1"
fi

# A few find examples
# \( -name "*.*" \) \
# \( -name "*vmstat*.*" -o -name "*iostat*.*" \) \
# \( -name "*vmstat*.*" -o -name "*iostat*.*" \) -mmin -60 \
# \( -name "*vmstat*.*" -o -name "*iostat*.*" -o -name "*netstat*.*" \) -mtime -8 \

while read line; do
	(ssh -n -q root@$line 'find /opt/oracle.oswatcher/osw/archive  \
	\( -name "*vmstat*.*" -o -name "*iostat*.*" \) \
	-prune -print0 2>/dev/null | \
	xargs -0 tar --no-recursion -czf - 2>/dev/null ' | cat >  osw${file_name}_${line}.tar.gz
)
done < /home/oracle/tyler/osw/allnodes

Note the file “allnodes” on the last line is just a file with the names of the db nodes and cells, each on a new line. It’s the same format you use for dcli. This is of course a lot easier if you’ve setup root equivalencies for each component…