Monday, July 19, 2010

Script for tailing alert log and useful aliases

Quickest ways to get access to alert*.log is to have aliases created for them. Following aliases will be useful in getting a tail output on asm alert log and crsd.log
asmsid=`grep ^\+ASM /etc/oratab | cut -d ":" -f1`
alias asmtail='tail -f $ORACLE_BASE/diag/asm/+asm/$asmsid/trace/alert_$asmsid.log'
alias crsdtail='tail -f $CRS_HOME/log/`hostname | cut -d "." -f1`/crsd/crsd.log'
Add these .bashrc or .bash_profile and alias will be available like any other normal command.
However to create a alias for getting tail of a database alert log is not so simple. There are several consideration specially in development environments if there are multiple databases (db1, db2),multiple versions (10g,11g) and multiple configurations (clustered and non-clustered) all will have different paths to the alert log.
Following scripts has been written incorporating all these complexities and has been tested on a environment where there was 11g RAC, 11g and 10g non-RAC database instances. dbtail script "function" will use the current ORACLE_SID value in the environment and tail the alert log corresponding to that ORACLE_SID.
Add to .bashrc or .bash_profile and script function will be available on the command prompt.
Only assumptions made are $CRS_HOME $ORACLE_BASE and $ORACLE_SID variables are set
dbtail() {

echo $ORACLE_SID
tailpath=""
# get current node's id
if [ -f $CRS_HOME/bin/olsnodes ]; then
nodeid=`$CRS_HOME/bin/olsnodes -l -n | cut -f2`
fi


#check if 11g
if [ -d $ORACLE_BASE/diag/rdbms ]; then

#check if 11g cluster db log
if [ -d $ORACLE_BASE/diag/rdbms/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/$ORACLE_SID ]; then

tailpath=$ORACLE_BASE/diag/rdbms/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/$ORACLE_SID/trace/alert_$ORACLE_SID.log
else
#check if 11g non cluster db log
if [ -d $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID ]; then
tailpath=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log

else

#check if 10g
if [ -d $ORACLE_BASE/admin ]; then

# non cluster 10g
if [ -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log ]; then

tailpath=$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
else

if [ -d $ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`} ]; then

tailpath=$ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/bdump/alert_$ORACLE_SID.log

fi

fi

fi

fi

fi
else
#no 11g in the system, check if 10g
if [ -d $ORACLE_BASE/admin ]; then

# non cluster 10g
if [ -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log ]; then

tailpath=$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
else

if [ -d $ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`} ]; then

tailpath=$ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/bdump/alert_$ORACLE_SID.log

fi

fi

fi
fi

echo "tailing output of --->>>"$tailpath
tail -100f $tailpath
}