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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/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.
