Expired oracle accounts - GRACE period

Have you ever experienced expired oracle users? Or perhaps just about and seen the user enter a GRACE period. This is a common mechanism to ensure that users renew their passwords and follow specified security profiles. But if a service account expires, applications might begin to fail. If you have a monitoring tool that allows you to add your own checks, it can be a good idea to look for accounts that are about to expire, to catch the situation before it becomes a problem. The reason that a service account expires may be that it has a wrong profile. So verify the profile as well. However, it does not take long to make a check that captures all databases. Before an account expires, it will enter a GRACE period. This gives you some time to adjust. In its simplest form, a check may look something like this. Install in cron and run every morning.

#!/bin/bash

 script_dir="/your_scripts"
 logfile="grace.log"
 ORACLE_BASE="/your_oracle"
 receivers="your-email-address"
 running_db=`ps -ef|grep smon|grep -v grep|awk '{print $8}'|awk -F "ora_smon_" '{print $2}'`

 export ORACLE_BASE

 date > $script_dir/$logfile

 for i in `echo $running_db`;do
    ORACLE_SID=$i
    ORAENV_ASK=NO
    . /usr/local/bin/oraenv 1> /dev/null 2> /dev/null

    $ORACLE_HOME/bin/sqlplus /nolog <$script_dir/grace.sql |grep tag: > $script_dir/$i.grace

    grep "GRACE" $script_dir/$i.grace 1> /dev/null 2> /dev/null
    found="$?"

    if [ "$found" -eq 0 ]
    then
       echo "$i contain one or more accounts in grace mode" >>  $script_dir/$logfile
       cat $script_dir/$i.grace >> $script_dir/$logfile
    else
       rm -f $script_dir/$i.grace
    fi
 done

 nbo_lines=`cat $script_dir/$logfile|wc -l`

 if [ "$nbo_lines" -gt 1 ] then
    cat $script_dir/$logfile | mailx -s "Oracle warning - accounts in GRACE mode" $receivers
 fi

 exit 0

grace.sql:
 connect
 select 'tag:'||username,account_status from dba_users where account_status like '%GRACE%';