Hello everyone,
This is my first post on the oracle blogs section!
I really hope the posts I will publish here help some of you to have a more dynamic and comfortable work.
This time I'm publishing a simple KSH script that can allow you to apply a single command or a SQL script to all running databases in a single host.
#!/bin/ksh | |
########################### | |
## | |
## File: apply_to_all.ksh | |
## | |
## Author: Jesus Sanchez (jsanchez.consultant@gmail.com) | |
## | |
## Changelog: | |
## 2015-05-11 Jesus Sanchez Created this script | |
## | |
###################################################################### | |
############### | |
## FUNCTIONS ## | |
############### | |
##################### | |
# Utility Functions # | |
##################### | |
export LAUNCH_DIR=`pwd` | |
export SOURCE_DIR=$( cd -P -- "$(dirname -- "$(command -v -- "$0")")" && pwd -P ) | |
source $SOURCE_DIR/utility_functions.ksh | |
export TEMPDIR=$LAUNCH_DIR | |
function printUsage | |
{ | |
print "USAGE: this script has exlusive modes for running." | |
print "FILE MODE: PATH/TO/SCRIPT/apply_to_all_db.ksh -f <FILENAME>" | |
print "COMMAND MODE: PATH/TO/SCRIPT/apply_to_all_db.ksh -c <DOUBLE-QUOTED COMMAND ENDED BY SEMICOLON>" | |
} | |
######################### | |
# Function: crawl | |
# | |
# Description: this function will look for pmon running processes, | |
# gather instance names and "crawl" through them, running the | |
# command/SQL file on each instance on the same host. | |
# | |
# Usage: | |
# | |
######################################################## | |
function crawl { | |
## Crawl through databases and execute command/file | |
for DBNAME in $(ps -ef | grep pmon | cut -d"_" -s -f3 | grep -v ASM) | |
do | |
. oraenv <<_EOORA_ | |
${DBNAME} | |
_EOORA_ | |
sqlplus / as sysdba<<_EOSQL_ | |
select global_name from global_name; | |
!echo "[INFO] About to execute: $1"; | |
$1 | |
exit | |
_EOSQL_ | |
done | |
msgPrint -info "All done!!!" | |
} | |
## MAIN ALGORITHM ## | |
## Check parameters ## | |
if [[ -z $2 ]]; | |
then | |
printUsage | |
else | |
if [[ $1 = "-f" ]]; | |
then | |
msgPrint -info "Using file mode" | |
crawl "@${2}" | |
elif [[ $1 = "-c" ]]; | |
then | |
msgPrint -info "Using command mode" | |
crawl "${2}" | |
else | |
printUsage | |
fi | |
fi | |
exit 0 |
Now let's explain each part:
- Functions
- Utility Functions: refer to this post to check the utility functions I normally use.
- You'll see debugPrint and msgPrint in most of my shell scripts. Those are used to give a nice format to the message I'm sending to the console/log.
- Crawl
- This is the main function that receives the command/sql file to be run in every instance.
- Utility Functions: refer to this post to check the utility functions I normally use.
- Main Algorythm
- for DBNAME in $(ps -ef | grep pmon | cut -d"_" -s -f3 | grep -v ASM)
- Here, we cycle through all the running instances in the host. Looking for pmon processes and getting only the INSTANCE name from it.
- crawl "@${2}"
- In the SQL script mode see how the argument passed to the crawl function is appended to the @ sign, so that we don't have to check for it when running inside the function.
- In the SQL script mode see how the argument passed to the crawl function is appended to the @ sign, so that we don't have to check for it when running inside the function.
- crawl "${2}"
- In the command mode we pass the argument inside double quotes so that it is considered a single argument by the function while running.
- for DBNAME in $(ps -ef | grep pmon | cut -d"_" -s -f3 | grep -v ASM)
With some light modifications, we can get this same schema to work with any of the command line utilities from Oracle. But that's a future project of mine. I will try to add support for RMAN in the next few months as my work allows me to.
Hope that some of you find this post useful.