Oracle Application users are different from normal database users that we typically set-up as Oracle DBAs. These users use the different modules of the E-Business suite through packages and thus enforcing security is different. The users are not available at the database level; the database only stores their information and authenticates them at the Application level. Once authenticated, the users connect to different modules through packages and the packages connect to the database objects through the module-specific users. These users are "super-users" for that module and exist at the database level. They own the actual table and indexes related to their module and all the other objects--such as packages, functions and procedures--that manipulate their data are owned by Application super-user apps.

These users would also be the actual end-users of the application. Sometimes it becomes very difficult to keep track of which users in which department actually use the Application. When you want to notify them of an outage or simple maintenance work like bringing the Concurrent Managers down, it becomes very difficult to identify and inform them of the impending maintenance.

Here is a script that spools the Mail ID's of the Application users and mails them whatever you want to notified them. This only requires that when a user is created, the Mail ID information is fed in and exists in the AOL tables of the database. The script was tested on 10.7 NCA and Rel 11. Be sure to test it at your setup before using it!

 
#!/bin/ksh  
#===========================================================================  
# Filename:    mailer  
# Author  :     Shankar Govindan.  
# Created :     12/28/2001  
# Description : This script is used for sending out a mail to Oracle APPS 
# Users, It picks up the email_address of FND_USER, spools them and sends  
# out a mail. The mail message text can be placed in a message.lst file  
# and input the same. I have used a password file with system/manager.  
#  
#    1st Argument: mail  
#    2nd Argument: MESSAGE (optional like SHUTDOWN)  
#  
#========================================================================  
 
if ( [ "$1" != "mail" ] )  
then  
        echo "Usage: $0 mail message"  
        echo " Eg.: $0 mail SHUTDOWN (one word)"  
        exit  
fi  
 
CMD=$1  
MESSAGE=$2  
ORACLE_SID=`echo $ORACLE_SID`  
MAILDIR=/export/home/shankar  
GROUP=`groups | cut -d" " -f1`  
 
function _mailercmd  
{  
  echo "======================================================================="  
  echo "Sending mail for Database ${ORACLE_SID} and the mail message is ${MESSAGE}                     "  
  date  
  echo "======================================================================="  
  echo  
 PWD_FILE=/export/home/shankar/.${GROUP}_${ORACLE_SID}.pwd  
 SYSTEM_PWD=`grep -i "^system/" $PWD_FILE`  
 case $CMD in  
    mail)  
            if [ "$SYSTEM_PWD" = "" ] ; then  
                echo "nERROR: userid=system does not exist in ${PWD_FILE}"  
                return 1  
            fi  
            echo "** Sending mail for Oracle database=${ORACLE_SID}"  
            (echo ${SYSTEM_PWD}; echo set pages 0 pause off verify off feedback 
                  off termout off; echo "select email_address from  
                  apps.fnd_user where upper(email_address) not in 
                  ('N/A','NONE');") | sqlplus -s > $MAILDIR/mailers 
 
            grep -i "cnf.com" $MAILDIR/mailers >/dev/null  
            if [ $? = 0 ]  
            then  
              mailx -s "$MESSAGE" $(cat $MAILDIR/mailers) < message_file.lst  
              echo "For Database=$ORACLE_SID , mailing $MESSAGE"  
             return 1  
              echo  
              echo  
            else  
              echo "Database=$ORACLE_SID is DOWN"  
              echo  
              echo  
            fi  
            return 0  
            ;;  
*) echo "nERROR: Invalid option=$CMD (in function _mailercmd)"  
            return 2  
            ;;  
  esac  
}  
#  
#MAIN  
#  
if [ "$CMD" = "mail" ];  
then  
            _mailercmd $CMD $MESSAGE  
   else  
     echo " Database is down "  
         wait  
return 1         
fi  

Reader Feedback

Manjushree D. writes: The script is somewhat confusing...maybe because it is not documented properly. My questions are:

1. The first argument HAS TO BE 'mail'. Then why it is not included in the sctipt itself. User need not type it. Morever, it is checked more than once in the script, which appears to be unnecessary.
2. I couldn't understand the line: GROUP=`groups | cut -d" " -f1`. This returns the user's primary group. It may or may not be dba. How is this PRIMARY group associated with Oracle functioning or privileges?
3. The line: SYSTEM_PWD=`grep -i "^system/" $PWD_FILE` tells you that the Oracle system password is stored in a text file. This is a very insecure way of keeping passwords.
4. I couldn't understand the purpose of: grep -i "cnf.com" $MAILDIR/mailers >/dev/null.

The author responds:

1. True, that is not necessary. If the script is part of a larger script and mail is one of them -- like say status check of processes and then call this function to mail to Apps users -- then it can be used.
2. Well, the database passwords are owned by the Oracle group. Any user trying to login to the database and access Apps objects with System or SYS privilege should belong to the Oracle group. In 11i you can have different groups for different instances on the same server.
3. Show me a better way and I will incorporate it; that's the way large shops keep passwords so shell scripts can access them.
4. That's an example to check the output file for the MAILLIST mailer domain. The MAILLIST needs to be modified in the script with your MAIL ID, otherwise the mail would come to my account!

About the author
Shankar Govindan is an Oracle Certified 7, 8 and 8i DBA working in Portland, Oregon.