归档—监控ORACLE数据库告警日志

ORACLE的告警日志里面包含许多有用的信息，尤其是一些ORACLE的ORA错误信息，所以有必要及时归档、监控数据库告警日志的ORA错误，及时提醒数据库管理员DBA处理这些错误信息，那么我们首先来看看告警日志的内容片断：Thread 1 advanced to log sequence 37749 (LGWR switch)Current log# 6 seq# 37749 mem# 0: /u01/oradata/SCM2/redo06.logThu Jun 27 15:02:30 2013Thread 1 advanced to log sequence 37750 (LGWR switch)Current log# 2 seq# 37750 mem# 0: /u01/oradata/SCM2/redo02.logThu Jun 27 15:13:43 2013Thread 1 advanced to log sequence 37751 (LGWR switch)Current log# 3 seq# 37751 mem# 0: /u01/oradata/SCM2/redo03.logThu Jun 27 15:25:30 2013Thread 1 advanced to log sequence 37752 (LGWR switch)Current log# 4 seq# 37752 mem# 0: /u01/oradata/SCM2/redo04.logThu Jun 27 15:32:20 2013ORA-00060: Deadlock detected. More info in file/u01/app/oracle/admin/SCM2/bdump/scm2_s001_14052.trc.Thu Jun 27 15:35:05 2013Thread 1 advanced to log sequence 37753 (LGWR switch)Current log# 5 seq# 37753 mem# 0: /u01/oradata/SCM2/redo05.logThu Jun 27 15:43:11 2013Thread 1 advanced to log sequence 37754 (LGWR switch)Current log# 1 seq# 37754 mem# 0: /u01/oradata/SCM2/redo01.logThu Jun 27 15:49:58 2013Thread 1 advanced to log sequence 37755 (LGWR switch)Current log# 6 seq# 37755 mem# 0: /u01/oradata/SCM2/redo06.logThu Jun 27 16:01:25 2013Thread 1 advanced to log sequence 37756 (LGWR switch)Current log# 2 seq# 37756 mem# 0: /u01/oradata/SCM2/redo02.logThu Jun 27 16:12:14 2013Thread 1 advanced to log sequence 37757 (LGWR switch)Current log# 3 seq# 37757 mem# 0: /u01/oradata/SCM2/redo03.logThu Jun 27 16:24:10 2013Thread 1 advanced to log sequence 37758 (LGWR switch)归档告警日志文件告警日志文件如果不加管理的话，那么文件会持续增长，有时候文件会变得非常大，不利于读写。

一般建议将告警日志按天归档，归档文件保留三个月（视情况而定），下面来看看将告警日志文件归档的两个Shell脚本：alert_log_archive.sh version 11.#*************************************************************************2.#FileName:alert_log_archive.sh3.#*************************************************************************4.#Author:Kerry5.#CreateDate:2013-07-026.# blogs :/kerrycode7.#Description:this script is made the alert log archived every day8.#*************************************************************************9.10.#!/bin/bash11.12.date=`date+%Y%m%d`13.14.alert_log_path="$ORACLE_BASE/admin/$ORACLE_SID/bdump"15.16.alert_log_file="alert_$ORACLE_SID.log"17.18.alert_arc_file="alert_$ORACLE_SID.log""."${date}19.20.cd ${alert_log_path};21.22.23.if[ ! -e "${alert_log_file}" ];then24.echo"the alert log didn't exits, please check file pathis correct!";25.exit;26.fi27.28.29.if[ -e ${alert_arc_file} ];then30.31.echo"the alert log file have been archived!"32.33.else34.35.cat ${alert_log_file}>> ${alert_arc_file}36.37.cat/dev/null> ${alert_log_file}38.39.fi其实脚本1和脚本差别不大，仅仅是mv与cat >>的区别1.2.#*************************************************************************3.#FileName:alert_log_archive.sh4.#*************************************************************************5.#Author:Kerry6.#CreateDate:2013-07-027.# blogs :/kerrycode8.#Description:this script is made the alert log archived every day9.#*************************************************************************10.11.#!/bin/bash12.13.date=`date+%Y%m%d`14.15.alert_log_path="$ORACLE_BASE/admin/$ORACLE_SID/bdump"16.17.alert_log_file="alert_$ORACLE_SID.log"18.19.alert_arc_file="alert_$ORACLE_SID.log""."${date}20.21.cd ${alert_log_path};22.23.24.if[ ! -e "${alert_log_file}" ];then25.echo"the alert log didn't exits, please check file pathis correct!";26.exit;27.fi28.29.30.if[ -e ${alert_arc_file} ];then31.32.echo"the alert log file have been archived!"33.34.else35.36.mv ${alert_log_file} ${alert_arc_file}37.38.cat/dev/null> ${alert_log_file}39.40.fi然后在crontab定时任务里面加上下面语句，每天23点59对告警日志进行归档。

[oracle@DB-Server scripts]$ crontab -l# the alert log archived every day Add by kerry 2013-07-0259 23 * * * /home/oracle/scripts/alert_log_archive.sh >/dev/null 2>$1细心的朋友可能已经发现上面的脚本、配置错误了，我在部署测试的过程中，是指定二十分钟执行一次，但是等了四十分钟，发现定时任务一次都没有执行，手工执行上面脚本是完全没有问题的，最后仔细的检查一遍，居然发现悲剧的发现时自己一时粗心将&符号写成了$,真是很二的一个错误59 23 * * * /home/oracle/scripts/alert_log_archive.sh >/dev/null 2>$159 23 * * * /home/oracle/scripts/alert_log_archive.sh >/dev/null 2>&1接下来测试发现脚本执行有问题，在crontab 里执行该shell脚本时，获取不到ORACLE的环境变量，这是因为crontab 环境变量问题，Crontab的环境默认情况下并不包含系统中当前用户的环境。

所以，你需要在shell脚本中添加必要的环境变量的设置，修改的脚本如下：1.#*************************************************************************2.#FileName:alert_log_archive.sh3.#*************************************************************************4.#Author:Kerry5.#CreateDate:2013-07-026.# blogs :/kerrycode7.#Description:this script is made the alert log archived every day8.#*************************************************************************9.10.#!/bin/bash11.12.#these solved the oracle variable problem.13.export ORACLE_SID=gps14.export ORACLE_BASE=/u01/app/oracle15.16.date=`date+%Y%m%d`17.18.alert_log_path="$ORACLE_BASE/admin/$ORACLE_SID/bdump"19.20.alert_log_file="alert_$ORACLE_SID.log"21.22.alert_arc_file="alert_$ORACLE_SID.log""."${date}23.24.cd ${alert_log_path};25.26.27.if[ ! -e "${alert_log_file}" ];then28.echo"the alert log didn't exits, please check file pathis correct!";29.exit;30.fi31.32.33.if[ -e ${alert_arc_file} ];then34.35.echo"the alert log file have been archived!"36.37.else38.39.cat ${alert_log_file}>> ${alert_arc_file}40.41.cat/dev/null> ${alert_log_file}42.43.fi1.#*************************************************************************2.#FileName:alert_log_archive.sh3.#*************************************************************************4.#Author:Kerry5.#CreateDate:2013-07-06.# blogs :/kerrycode7.#Description:this script is made the alert log archived every day8.#*************************************************************************9.10.#!/bin/bash11.12.#these solved the oracle variable problem.13.export ORACLE_SID=gps14.export ORACLE_BASE=/u01/app/oracle15.16.date=`date+%Y%m%d`17.18.alert_log_path="$ORACLE_BASE/admin/$ORACLE_SID/bdump"19.20.alert_log_file="alert_$ORACLE_SID.log"21.22.alert_arc_file="alert_$ORACLE_SID.log""."${date}23.24.cd ${alert_log_path};25.26.27.if[ ! -e "${alert_log_file}" ];then28.echo"the alert log didn't exits, please check file pathis correct!";29.exit;30.fi31.32.33.if[ -e ${alert_arc_file} ];then34.35.echo"the alert log file have been archived!"36.37.else38.39.mv ${alert_log_file} ${alert_arc_file}40.41.cat/dev/null> ${alert_log_file}42.43.fi监控告警日志文件接下来看看如何监控告警日志文件的ORA错误，这里是采用Perl结合Shell的方式，因为Shell获取错误的时间、行数等不如Perl操作字符串方便。