There are some small tricks to speed up your shellscripts for Oracle DBs. I’m pretty sure you can do almost magic only with those:
# To get all OS Users with an Oracle Instance in server:
ps -ef | grep [o]ra_pmon | awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u
# To get all DBSids in server:
ps -ef | grep [o]ra_pmon | awk -F_ '{ print $3}'
# To get all DBNames in server:
ps -ef | grep [o]ra_pmon | awk -F_ '{ print substr($3, 1, length($3)-1)}'
# To get Oracle Home from /etc/oratab for a database:
grep "dbname:" /etc/oratab|cut -d: -f2 -s
# Looping for any of the lists from commands above:
for DBUSER|DBNAME|DBINST in `$Comand_From_ListAbove` do [...] done
# Example:
– Take DBInstance and Oracle Home for Each DBName on Each OSUser:
for DBUSER in `ps -ef | grep [o]ra_pmon | awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u`
do
for DBNAME in `ps -ef | grep [o]ra_pmon | grep ${DBUSER} | awk -F_ '{ print substr($3, 1, length($3)-1)}'`
do
DBSID=`ps -ef | grep [o]ra_pmon | grep -i ${DBNAME^^}| awk -F_ '{ print $3}'`
DBOH=`grep "^${DBNAME,,}:" /etc/oratab|cut -d: -f2 -s`
done
done
# Variable Upper Case or Lower Case:
For uppercase: ${DBNAME^^}
For lowercase: ${DBNAME,,}
# Putting Colors on your messages:
RED='\033[0;31m'
GREEN='\033[0;32m'
PURPLE='\033[0;35m'
NC='\033[0m' # No Color
echo -e "Status: ${GREEN}All Good!${NC}";
echo -e "Status: ${RED}ERROR!${NC}"
echo -e "Status: ${PURPLE}Do you like messages in purple?${NC}"
# Conditional: Is there any line with a specific content?
if [`cat myfile.log | grep "text_I_want"| wc -l |awk '{print $1}'` -gt 0 ]; then
echo -e "Count Greater Then Zero: ${GREEN}Line Exists!${NC}";
else echo -e "Count equals to Zero: ${RED}No line with this text...${NC}"
fi;
# Some other related posts:
Shellscript & Oracle: Run Script for a List of Databases
Shellscript: Using eval and SQLPlus
Recursive string change
Grepping Entries from Alert.log
Grepping Alert by Day
Shellscript: Master Blaster KB!
PS: Thanks for Jared Still for suggesting improvements and saving some command greps (grep ora_pmon |grep -v grep -> grep [o]ra_pmon). That’s a nice tip. Thanks! 😀
Anything I forgot to put here? Tell me with a comment!
Cheers!
