Getting the Most Out of Enterprise Manager and Notifications

I ran out of time before I was able to provide an adequate white paper this year for my EM12c presentation, but there was some valuable info in what I had started, so thought I’d turn it into a mulit-part blog post…

The Oracle Enterprise Manager, (OEM) is the standard monitoring tool for Enterprise Edition Oracle databases.  The interface allows the DBA to manage the entire Oracle stack using a single console.  The installation and interface is easy for most DBA’s to implement and utilize.  In the newest EM12c version, it encompasses integrated systems management, application management, application-to-disk and cloud management , the following documentation will include some 10g but mostly, the EM12c version of the product.

The goal for any DBA is to be notified of an issue and only notified when there is an actual issue.  One of the most common downfalls of a monitored environment is the misconception that receiving emails upon success or checks stating that a process is running, is correctly configured monitoring.  This produces an environment that leads to a “white noise” effect, where DBA’s may misinterpret a notification as one of the success notifications when a real issue has actually arisen.

The optimal design is one where redundancy checks of the monitoring system is included to ensure that if there is an issue with the monitoring environment that deters it from monitoring and sending alerts, the system has a redundant check on a secondary server that is notifying the DBA on call of the issue.   Multiple Oracle Management Server Repositories , residing on separate servers can address this, but in my opinion, would be overkill when simple additional scripts run from a cron would suffice.

OEM Basics

The Oracle Enterprise Manager, 10g and EM12c comprises of the following, basic components:

  • The Oracle Management Server/Service, (OMS).
  • The OMS repository database.
  • The OMS Home, aka the EM state directory, which contains the bin files, log files, collection files and configuration files.
  • The Agent installation, application and configuration on each monitored host server.

EM12c has the additional weblogic components included automatically, along with the Cloud support features which can be installed.

Licensing

As long as the OMS is on its own server and is only used for the OMS repository and/or an RMAN backup catalog repository, individual oracle licensing IS NOT required for the Oracle database utilized for the repositories, (Please see pg. 15 of the following PDF from Oracle.)

http://download.oracle.com/docs/cd/B19306_01/license.102/b40010.pdf

Monitoring the OEM from a secondary server:

This can be performed easily from a shell script and allows the DBA(s) to rest easy, knowing that the interface to their database environment, if impacted, will notify them from a secondary server.  This allows for redundant checks without sending an “I’m OK” notification to grant comfort:

 

       #!/usr/bin/ksh
       #----------------------------------------------------------------------------
       # Author:   Kellyn Pot’Vin
       # Redundancy Check to OEM Server to ensure EM is up and Running!
       # Verify that all parameters are set in the remote host env. vars...
       #----------------------------------------------------------------------------
       if (( $# != 2 ))
       then
           echo "usage: $0 SID hostname"
           exit 1
      fi
      #
      #----------------------------------------------------------------------------
      # Set up Oracle environment...
      #----------------------------------------------------------------------------
      export ORACLE_SID=$1
      export who_to_ping=$2
      echo "Oracle SID: "${ORACLE_SID
      export AVL_LOG=${LOG_DIR}/oem_avl.log
      export AVL_ERR=${LOG_DIR}/oem_avl.err
      export AVL_PNG_ERR=${LOG_DIR}/ping_avl.err
      #Check Repository DB for Access
      $ORACLE_HOME/bin/sqlplus oem_chk/"${pass}"@${ORACLE_SID} <<EOF
      spool ${AVL_LOG};
      select sum(1+1) from dual@grid_chk;
      spool off;
      exit;
      EOF
      cat ${AVL_LOG} | grep "ORA-" > ${AVL_ERR}
      if [ -s ${AVL_ERR} ]
      then
       echo|mail -s "No Response from Grid Control from Oracle Management Server!" "<EML_Address>"  < ${AVL_LOG}
      fi
#Check to verify that EM12C is up!  This requires SSH authentication from remote server.
ssh oracle/n0c1u3ata11@
"$OMS_HOME/bin/emctl status oms" | grep Down > ${EM_LOG}

if [ -s ${EM_LOG} ]
then
 echo|mail -s "No Response from EM12C Grid Control!" "<EML_ADDRESS>"  < ${EM_LOG}
exit
fi
      #Check Grid Server, ensure that you can ping it as well
      date
      ping -c 3 ${who_to_ping}
      if [ $? -ne 0 ]
      then
          sleep 5
          ping -c 3 ${who_to_ping}
          if [ $? -ne 0 ]
          then
              echo "`hostname` CANNOT PING ${who_to_ping} the EM Server!" > /tmp/ping.$$
       echo|mail -s "`hostname` CANNOT PING ${who_to_ping} from Oracle Managent Server!" "<EML_Address>"
       rm -f /tmp/ping.$$
           fi
      fi
      rm -f ${AVL_LOG}
      rm -f ${AVL_ERR}
exit

 

Pretty simple to schedule in cron:

0,15,30,45 * * * * /home/oracle/scripts/admin/chk_grid.ksh <dbname> <servername> > /dev/null  2>&1

I’ve chosen a 15 minute interval on the checks, but this can be done with any interval as requirements are set.

 

Escalation

Due to Sarbanes-Oxley and/or outside support contracts, an enhanced escalation process may be required.  One that can offer more choices and escalation paths then what is currently offered in the 10g and EM12c console.  A simple package/support object implementation can be created to support this type of requirement that works with OEM.  The code presented here will allow one to set the on-call DBA, scheduler and escalation outside of the OEM interface, but will all OEM alerts and escalation from the OMS will utilize the data found in the supporting tables.

I will try to upload and post the supporting schema and code soon on dbakevlar.com

 

 

Blacking out DB from Agent Side with Shell Scripts:

Blackouts can be performed via a shell script to assist in automated processes that could trigger OEM alerts, sending false notifications when a blackout script is all that is required for Unix Admin or Application support personnel.

#!/usr/local/bin/ksh
# #######################################################
# start_blackout.ksh
# Usage ./start_blackout.ksh <oracle_sid>
# Rewrite Date: 4/22/2011
# Modified by:  reckl
#########################################################
usage="$0 <db_name>"
if (($# != 1))
then
    print $usage
    exit 1
fi
ORACLE_SID=$1
sudo su - oracle -c "$AGENT_HOME/bin/emctl start blackout ${ORACLE_SID}_blackout ${ORACLE_SID}"
exit

Patching

I am a supporter of patch deployments through OEM.  If you have not configured this or are working to get this feature approved in your database environments, I highly recommend it.  In the “Deployments” tab of the EM console, first ensure that the MOS credentials is configured:

Once this has been set up for your environment, you can then designate a patching strategy to deploy to development, test and then production with a full testing cycle that will make any DBA stop quaking in their boots when they receive the notification that new patches have arrived from Oracle Support.

The Deployment Procedure Manager allows the DBA group to schedule deployments of necessary patching with the most effective schedule and little DBA involvement required.

 

The DBA can then set up patching resource allocation and requirements from the “Offline Patching” UI and choose what to install for automatically patching:

To be continued in next post….

 

 

Print Friendly
January 16th, 2012 by

facebook comments:

  • I like your OEM checking script. We need to implement something like that here. I think you have a hard-coded password in the script, though. Also, in your database blackout script, you assume that the ORACLE_SID is the target name in OEM. We have several instances where that is not the case (all test/dev). What I did in my blackout script was:

    export DBNAME=`grep -i ${ORACLE_SID} ${AGENT_HOME}/sysman/emd/targets.xml | grep DISPLAY_NAME | awk -F\” ‘{ print $6 }’`

    And then blacked out DBNAME instead. I don’t know if that will work in 12c but it works in all our 10g OEM environments. Of course, my script makes the assumption that you don’t have a database called BOB and BOBBY on the same server…

  • mohammed qadar mohiuddin

    Hello kelly,

    Can we get DB level alerts through emails, actually need to configure on TEST and DEV boxes, but there is no licensing of this software? kindly advice which is the best and free tool available for DBA’s , and how we set alerts through mails?

  • I believe you are just looking for SMTP mail setup via the EM12c console, is that correct? Let me know, this comment just came up and I apologize with the delayed response.

  • Pingback: Agents Management Via EM12c Release 4 Console - Oracle - Oracle - Toad World()

  • Safaltek Rao

    Hi Kevlar,

    Can you please confirm in the script Monitoring the OEM from a secondary server:

    export who_to_ping=$2 (provide ip address for the server)

    Thanks,

  • DBAkevlar

    The $2 argument should use an alias that’s part of your profile or an email address for this entry.
    Thanks,
    Kellyn

  • Safaltek Rao

    Hi Kellyn,

    Thank you for your reply. I am using your script but it’s not working. I execute it but no log files are created and no email is sent. Can you please help.

    Thanks

  • DBAkevlar

    Let’s start with what error are you receiving? 🙂

  • Safaltek Rao

    I saved the file on a different server from where i want to monitor the OEM server but when i execute the file, no logs are created in Log_dir path and nothing happens.

  • DBAkevlar

    OK, first things first, let’s see what is going on when you are executing the script:
    #! -e

  • DBAkevlar

    Add the -e option to the beginning of your script and then run it again, let me know if anything pops up!
    Thanks!

  • Safaltek Rao

    I tried and nothing is happening.

    Can you please do me a favor and send me the script with the parameters that i need to provide and then steps to implement it.

  • DBAkevlar

    Honestly, my parameters are not going to do you any good. The call for this script would be:
    /path to script/mon_oem.ksh db1 host1

    Ensure the script is “chmod 774” or whatever your policy says scripts are set to.
    Thanks,
    Kellyn

  • Safaltek Rao

    Can i provide ip address instead of host name. Secondly can you please confirm, can we deploy OEM to monitor other OEM’s when we don’t any oracle license for the database.

    Thanks

  • Facebook
  • Google+
  • LinkedIn
  • Twitter