ALERT LOG MONITORING

One of the primary job for any DBA is checking the database errors frequently. In oracle, all the database activity/errors are stored in a place called diag. The exact location is configured by a parameter diagnostic_dest.
The file name is identified as alert_{ORACLE_SID}.log. 

SQL> show parameter diag

  NAME     TYPE VALUE
  ------------------------------------ ----------- ------------------------------
  diagnostic_dest     string /u01/app/oracle

  cat AlertLog_Monitor.sh
  #######################################################################
  #
  # Usage : ./AlertLog_Monitor.sh
  #
  # Script for checking any ORA- error in alert log.
  #
  # From TesDB
  #
  #########################################################################

  diff  /u01/app/oracle/diag/rdbms/tesdb/tesdb/trace/alert_tesdb.log /u01/app/oracle/diag/rdbms/tesdb/tesdb/trace
                                                            /alert_tesdb.bkp |egrep -i  "ORA-" > /tmp/AlertLog.err
  x=`diff  /u01/app/oracle/diag/rdbms/tesdb/tesdb/trace/alert_tesdb.log /u01/app/oracle/diag/rdbms/tesdb/tesdb/trace
                                                                              /alert_tesdb.bkp |egrep -i "ORA-"|wc -l`
  if [ $x -gt 0 ]
  then
  echo $x
  else
  echo "$x"
  fi
  cp /u01/app/oracle/diag/rdbms/tesdb/tesdb/trace/alert_tesdb.log /u01/app/oracle/diag/rdbms/tesdb/tesdb/trace/alert_tesdb.bkp


When errors present in alert log.

  [oracle@postdb trace]$ ./AlertLog_Monitor.sh
  2
  [oracle@postdb trace]$ cat /tmp/AlertLog.err
  < Ora-01555
  < ora-0914

In cron you can specify as shown Below.

  [oracle@tesdb ~]$ crontab -l
      */10 * * * * /home/oracle/scripts/AlertLog_Monitor.sh > /dev/null 2>&1


☛ Join to Learn from Experts: Oracle DBA Training In Chennai by TesDBAcademy
(File System Monitoring)