Shell Script–Applying a command or a SQL script to all databases in the same host

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

view raw apply_to_all_db.sh hosted with ❤ by GitHub

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.
• 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.
      
  • 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.

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.