Reporting and Automating Across Instances on a Host

When automating things on database hosts you may want to run something against all the databases in the oratab file, all the instances running on the host, or to capture that information for some purpose. Here are some one-liner scripts I use to generate a variety of output on Linux and Solaris systems.

Running instances (based on pmon), sorted:

ps -ef | grep pmon | grep -v grep | awk -F"_" '{print $NF}' | sort

Explanation:

• ps -ef gets all the processes.

• grep pmon captures only processes that match “pmon”.

• grep -v grep excludes any line containing “grep”.

• awk -F"_" '{print $NF}' sets the field separator to an underscore, then prints the last field ($NF).

Running instances (based on pmon) and excluding ASM:

ps -ef | grep pmon | egrep -v "grep|\+" | awk -F"_" '{print $NF}' | sort

Explanation:

• egrep works like grep but uses a regex to exclude lines, in this case those with grep or that include a +. Since + is a regex modifier it must be escaped with a backslash.

Print all running instances on one line:

(ps -ef | grep pmon | egrep -v "grep|\+" | awk -F"_" '{print $NF}' | sort | \
     awk 'BEGIN {ORS=" "}; {print $0}'); echo ""

Explanation:

• The second awk sets the record separator to a space rather than a line break.

• Each record from the previously sorted list is printed with a single space between them.

• The first part is enclosed in parentheses to force it to execute first, followed by the echo to add a line break to the end of the output (else the prompt will begin on the end of the line).

Prefix the list with the hostname:

echo $(hostname | awk -F"." '{print $1}')": " | tr -d '\n'; \
     (ps -ef | grep pmon | grep -v grep | awk -F"_" '{print $NF}' | sort | \
     awk 'BEGIN {ORS=" "}; {print $0}'); echo ""

Explanation:

• The host name is retrieved first. hostname -a doesn’t work in all environments; awk splits the host on the first period.

  • cut -d. -f1 works here, too. Potayto, potahto!

• Wrapping this inside $( … ) cases echo to evaluate and print the result, followed by a colon and space.

• tr -d '\n' trims the line feed at the end of the line.

• Ending one or more commands in a semicolon causes them to be evaluated before moving on to the next command.

List all databases defined in oratab

([ -f /etc/oratab ] && cat /etc/oratab || cat /var/opt/oracle/oratab) | \
     awk -F":" '/^[a-zA-Z]/ {print $1}' | sort

Explanation:

• Check for existence (-f) of the file /etc/oratab.

  • If the first part evaluates true the section followed by && runs, cat’ing the file.

  • If the first part is false, the section after || runs. Assuming an oratab file exists, if it’s not in /etc/oratab it must be /var/opt/oracle/oratab.

• The test is enclosed in parentheses to tell the shell where to stop. If not, it would see the awk as part of the code to be run under a false condition!

• The -F in the awk command sets the field separator to a colon.

• The expression inside the slashes is a regular expression:

  • ^ looks for a match in the first column.

  • The bracketed section matches anything in the range of A-Z, upper or lower case. This excludes:

    • blank lines and comments (they don’t start with a character)

    • ASM instances (they start with a +)

• Prints the first field (defined by the colon).

print $2 here returns the ORACLE_HOME. Adding | uniq to the end shows all the ORACLE_HOMEs on a host.

Why might you need this? I use it (sometimes adding the host) to create lists of databases on a host to be added to documentation, scripts, or menus:

echo $(hostname | awk -F"." '{print $1}')": " | tr -d '\n'; \
     (([ -f /etc/oratab ] && cat /etc/oratab || cat /var/opt/oracle/oratab) | \
     awk -F":" '/^[a-zA-Z]/ {print $1}' | sort | \
     awk 'BEGIN {ORS=" "}; {print $0}'); echo ""

ORS can be changed to make a comma delimited list, too!

(([ -f /etc/oratab ] && cat /etc/oratab || cat /var/opt/oracle/oratab) | \
     awk -F":" '/^[a-zA-Z]/ {print $1}' | sort | \
     awk 'BEGIN {ORS=", "}; {print $0}') | sed 's/, $//'; echo ""

The addition of sed ‘s/, $//’ removes the last “, “ added by awk from the end of the list!

Run SQL against all running instances

To run SQL against all the active instances on a host:

for i in $(egrep -v '^$|^#|^\+' /etc/oratab | cut -f1 -d:); do 
if [ $(ps -ef | egrep $$ | grep pmon | wc -l) -eq 1 ]
then 
echo $i
. oraenv <<< $i > /dev/null 2>&1
sqlplus -S "/ as sysdba" <<EOF
select name from v\$database;
EOF
fi
done

Explanation:

• $( … ) evaluates commands and returns the result to the command that calls it. In this case, the portion inside $() finds the ORACLE_SID in /etc/oratab, using egrep to exclude all lines matching a regular expression:

  • ^ is the start of a line; $ is the end of a line. With nothing between it’s a blank line

  • The pipe (|) is an OR condition

  • ^# matches comments

  • ^\+ matches ASM instances that begin with a +; the backslash evaluates the + literally

  • The -v excludes matches from the result, producing only non-blank, non-comment, non-ASM entries

• cut -f1 -d: cuts the first field out of the result, using a colon as the delimiter.

Note that any result that produces a list of SID will work here!

• for starts a loop based on the result, passing the result of each iteration into a local shell variable i.

• do things for each iteration:

  • ps -ef | egrep $$ | grep pmon checks to see if the instance $i is running by looking for a pmon process that includes the $i value. The value has to match to the end of the line. The curly braces clear up any confusion of which variable is being tested. The regex $ at the end matches the string to the end of the line. This prevents SIDs test1 and test11 from being confused!

  • wc -l counts how many pmon processes were found.

  • if … -eq 1 verifies that a single pmon process is running. If it evaluates as true, then:

    • Call oraenv to set the environment;

    • <<< passes the local variable $i into the command where oraenv would normally prompt;

    • /dev/null 2>&1 sends output (and errors) to limbo.

  • In a formal script it’s wise to verify a 0 return code before continuing. The return value of the last command is stored in a special bash variable, $? that can be tested with an if statement.

  • With the environment set, call sqlplus.

  • <<EOF tells the shell to continue evaluating lines, passing them to SQL*Plus, until it reaches matching characters. There’s nothing special about EOF; something like <<END or <<XYZ will work, too, it’s just a way of identifying the start and end of the “script”.

  • select name from v\$database; has a backslash. Why? Since a dollar sign signifies a shell variable, bash will translate v$database to v + whatever the variable $database is set as. Passed to SQL*Plus, the query will likely be parsed as select name from v; (assuming $database isn’t set in the environment) and won’t produce the intended result! But, this shows shell variables can be passed into SQL from a script. Cool!

  • EOF ends the script and fi closes the test for the single pmon process.

• done ends the loop.

Capturing the output of the SQL to a file is easy, too! Just modify the call to SQL*Plus:

sqlplus -S "/ as sysdba" <<EOF > /mydir/$i.out

or

sqlplus -S "/ as sysdba" <<EOF >> ./myfile.out

Explanation:

• > /mydir/$i.out redirects the output of SQL*Plus to a file named for the local variable, under the absolute path /mydir. The single > overwrites existing file contents. In this example a separate file is created for each instance. If the files already existed they’d be overwritten.

• >> ./myfile.out sends the output to a file in the local directory. The dual >> causes output to be appended. In this example, the query results for all instances are populated in the same file without overwriting each other, or what was there before.

And there you have it, plenty of creative ways to visualize and loop over Oracle database instances that are running or defined on a Linux server!