oracle IF_面试官：如何实时监控oracle数据库告警日志文件？

概述

今天分享下怎么去监控Oracle数据库的告警日志文件，注意这里是采用Perl结合Shell的方式，因为Shell获取错误的时间、行数等不如Perl操作字符串方便。

以下基于Oracle 11.2.0.4 RAC ，操作系统为redhat 6.6进行测试。

---

1、脚本：monitoring_alert_log.pl

#**********************************************************************************# FileName :monitoring_alert_log.pl#**********************************************************************************# Author :hwb# CreateDate :2019-09-02# Description :check the alert log and find out the ora error#***********************************************************************************  #! /usr/bin/perl  use strict; my($argv) = @ARGV; if ( @ARGV != 1){  print ' Parameter error: you must assined the alert log file as a input parameter or the number of prarameter is not right.';  exit}  if( ! -e $argv ){  print '  Usage: monitoring_alert_log.pl   $ cat alert_[sid].log | monitoring_alert_log.pl $ tail -f alert_[sid].log | monitoring_alert_log.pl  $ monitoring_alert_log.pl alert_[sid].log   '; exit; } my $err_regex = '^(w+ w+ d{2} d{2}:d{2}:d{2} d{4})|(ORA-d+:.+)$'; my $date = ""; my $line_counter = 0; while ( <> ) {  $line_counter++;  if( m/$err_regex/oi )  {  if ($1)  {  $date = $1;  next;  }  print "$line_counter | $date | $2 " if ($2);  } }

执行如下：

perl monitoring_alert_log.pl /backup/alert_log/alert_RFDB1.log.20190902

---

2、脚本：monitoring_alert_log.sh

#**********************************************************************************# FileName : monitoring_alert_log.sh#**********************************************************************************# Author : hwb # CreateDate : 2019-09-05# Description: check the alert log and find out the ora error#*********************************************************************************** #!/bin/bashif [ -f ~/.bash_profile ];then . ~/.bash_profilefinew_log_file="/home/oracle/scripts/new_err_log.txt"old_log_file="/home/oracle/scripts/old_err_log.txt"pl_monitoring_alert="/home/oracle/scripts/monitoring_alert_log.pl"email_content="/home/oracle/scripts/sendmail.txt"alert_logfile="/u01/app/oracle/diag/rdbms/rfdb/RFDB1/trace/alert_RFDB1.log"#delete the old alter error log file rm -f ${old_log_file}mv ${new_log_file} ${old_log_file}#rm -f${pl_sendmail}#run the perl and check if exists the ora error perl ${pl_monitoring_alert} ${alert_logfile}> ${new_log_file} #if have no error in alert log then exit the program if [[ -e "${new_log_file}" && ! -s "${new_log_file}" ]]; then exit;fi  new_err_num=`cat ${new_log_file} | wc -l` old_err_num=`cat ${old_log_file} | wc -l` if [ ${new_err_num} -le ${old_err_num} ]; then exitfi date_today=`date +%Y_%m_%d`toemail="huangwb@fslgz.com,wucw@fslgz.com"subject="Monitoring the Oracle Alert logs and find ora errors"content="The Instance ${ORACLE_SID}' alert log occured the ora errors ,please see the detail in attachment and take action for it. many thanks! The Error is blow :${new_log_file}"#echo 'Content-Type: text/html' > ${email_content}#echo 'To: huangwb@fslgz.com' >> ${email_content}#echo ${subject} >> ${email_content}#echo '

' >> ${email_content}#echo ${content} >> ${email_content}#cat ${new_log_file} >>${email_content} 2>&1#echo 'Oracle Alert Services' >> ${email_content} #/usr/sbin/sendmail -t -f ${subject} < ${email_content}echo ${content} > t.txtmail -s "${subject}" $toemail < t.txtrm -f ${email_content}#( ${content} ; uuencode ${new_log_file} ${reportname} ) | /bin/mail -s ${subject} "huangwb@fslgz.com"# uuencode 附件文件 附件文件别名(在邮箱中显示的文件名)# # Linux中程序生成的文件默认为utf-8编码，在windows中使用excel打开.csv文件时，默认编码是gb2312，所以直接写文件会导致汉字显示乱码。# 解决方法：Linux程序写入附件文件前，将汉字转化成gb2312编码(使用)，再写入即可。# sendmail发送带附件的邮件，需要用uuencode命令，这个命令在sharutils组件中，可用yum install sharutils或者rpm -ivh sharutils-4.7-6.1.el6.x86_64.rpm安装。#(${content};uuencode ${new_log_file} ${reportname})|/bin/mail -s ${subject} huangwb@fslgz.com

调试情况：

查看邮件：

不是很好看...初衷是想把报错当成附件传上来的，还有去做一些格式美化的，但是一直调试不成功，所以就先这样了..后面再研究

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~