If you're working in a large Enterprise and are using Cloud Control, you will have access to a neat utility called emcli.
emcli is short for "Enterprise Manager Command Line Interface" and is documented
here.
In short, emcli can be used to perform scripted jobs on hosts, or on databases running on hosts, that are registered in your Cloud Control 12c.
The jobs you create and execute will show up in Cloud Control under "Enterprise"-->"Jobs"-->"Job Activity"
In this particular case, I wanted to use the
adrci
utility to set new retention policies.
Instead of logging into each and every oracle 11g databaser server, I used emcli to loop through the different groups that our Cloud Control Administrator had created.
To change target from a specific host to a group, exchange the
-target to a valid group name, followed by the keyword "group". For example
-targets="prod_db_servers:group"
First, make sure you're logged into emcli:
emcli login -username=sysman -password=mypassword
Login successful
You can query the targets usable by emcli by using "get_targets" like this:
oracle@myomsserver:[OMS]# emcli get_targets |grep -i myserver1
If you have a large enterprise with many targets, you may need to add "-limit_rows" to the "get_targets" command in order to accommodate a larger list, since "get_targets" will automatically limit the rows extracted to 2000:
oracle@myomsserver: [OMS]# emcli get_targets -limit_rows=5000 |grep -i myserver2
The following command will execute a host command of type "file", and the actual file is pointed out by the -input_file flag:
emcli execute_hostcmd -cmd="/bin/sh -s" -osscript="FILE" -input_file="FILE:/u01/app/scripts/change_adrci_policy.sh" -credential_set_name="HostCredsNormal" -targets="myhost.mydomain.no:host"
The file "change_adrci_policy.sh":
# Oric Consulting AS
# Vegard Kasa
# Version 2.0
#!/usr/bin/ksh
# Special coding required for AIX hosts, as there are many different flavours of setting the environment...
#
echo "Hostname: " `hostname`
echo "Running as user: " `whoami`
export NUMDB=`cat /etc/oratab | egrep -v "\#|\*" | sed '/^$/d' | wc -l | tr -d ' '`
export OS=`uname -s`
echo "There are $NUMDB database(s) on this server."
# Loop through the databases extracted from /etc/oratab
for x in $(cat /etc/oratab | grep -v "#" | grep -v "*" | awk -F":" '{print $1}' | sed '/^$/d'); do
case $OS in
'Linux') echo "This is Linux, sourcing .bash_profile...";
. ~/.bash_profile;
# set the ORACLE_SID, and make sure LIBPATH is set
export ORACLE_SID=$x;
export LIBPATH=$ORACLE_HOME/lib;
# PATH must be set explictly, even after sourcing profile, to avoid mixing database home binaries
# and Oracle Agent home binaries.
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/local/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/drift:/home/oracle/bin;
echo "adrci exec=\"spool /home/oracle/get_adr_home.lst;show homes;quit\"" > find_adr_home.sh;
chmod 755 /home/oracle/find_adr_home.sh;
/home/oracle/find_adr_home.sh;
export ADR_HOME=`cat /home/oracle/get_adr_home.lst | grep rdbms | grep $ORACLE_SID`;;
'AIX') echo "This is AIX...";
echo "Now checking for oracle11g installations..."
USEDSHELL=`cat /etc/passwd | grep ora11g | cut -f7 -d":"`
# The user ora11g is not found
if [ -z "${USEDSHELL}" ]; then
echo "Oracle user \"ora11g\" does not exist on server `hostname`"
break;
# The user ora11g is indeed found in /etc/passwd
else
if [ "${USEDSHELL}" = "/usr/bin/bash" ]; then
# We found that ora11g is using bash shell. Source .bash_profile
echo "Sourcing .bash_profile in this environment..."
. ~/.bash_profile
else
# We found that ora11g is using a shell <> from bash. Source .profile
echo "Sourcing .profile in this environment..."
. ~/.profile;
IAM=`whoami`
# We have sourced .profile and the user turns out to be ora10g
if [ "${IAM}" = "ora10g" ]; then
echo "Oracle installation under user \"ora10g\" found."
echo "Only Oracle11g is applicable for adrci purging. Exiting."
break;
fi
fi
fi
# set the ORACLE_SID, and make sure LIBPATH is set
export ORACLE_SID=$x;
export LIBPATH=$ORACLE_HOME/lib
# PATH must be set explictly, even after sourcing profile, to avoid mixing database home binaries
# and Oracle Agent home binaries.
export PATH=$ORACLE_HOME/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/ora11g/bin:/usr/bin/X11:/sbin:/usr/java131/bin:/usr/vac/bin:/usr/local/bin;
# create an executable file on the target server
echo "adrci exec=\"spool /home/ora11g/get_adr_home.lst;show homes;quit\"" > find_adr_home.sh;
chmod 755 /home/ora11g/find_adr_home.sh;
# execute the file
/home/ora11g/find_adr_home.sh;
# grep for the ADR home in the log file produced by the execution above. Look for rdbms home
# Then set the ADR_HOME to the path reflecting the rdbms instance
export ADR_HOME=`cat /home/ora11g/get_adr_home.lst | grep rdbms | grep $ORACLE_SID`;;
esac
# Show the current settings (for logging purposes)
echo "ORACLE_SID is " $ORACLE_SID
echo "ORACLE_HOME is: " $ORACLE_HOME
echo "PATH: " $PATH
echo "LIBPATH: " $LIBPATH
echo "adrci located in " `which adrci`
echo "ADR HOME selected: " $ADR_HOME
# finally, execute the set control statements in adrci against the rdbms home
adrci exec="set home $ADR_HOME;show control;set control \(shortp_policy = 336\);set control\(longp_policy=2160\);show control;quit"
done
exit 0
