Tips to Improve your Oracle DB Shellscripting!

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 ora_pmon | grep -v grep| awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u

# To get all DBSids in server:

ps -ef | grep ora_pmon | grep -v grep | awk -F_ '{ print $3}'

# To get all DBNames in server:

ps -ef | grep ora_pmon | grep -v grep | 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 ora_pmon | grep -v grep| awk -F_ '{ print $1}'|cut -f1 -d" "|sort -u`
do
for DBNAME in `ps -ef | grep ora_pmon | grep -v grep |grep ${DBUSER} | awk -F_ '{ print substr($3, 1, length($3)-1)}'`
do
DBSID=`ps -ef | grep ora_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!

Anything I forgot to put here? Tell me with a comment!
Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s