目的
管理好告警日志，有利于主系统状态的跟踪以及故障的排查
告警日志介绍
告警日志文件是一类特殊的跟踪文件（trace file）。告警日志文件命名一般为alert_<SID>.log，其中SID为ORACLE数据库实例名称。数据库告警日志是按时间顺序记录message和错误信息。
告警日志文件位置
在ORACLE 10g中，BACKGROUND_DUMP_DEST参数确定了告警日志的位置，但是告警日志的文件名无法修改，告警日志的名称为：alert_<SID>.log ,其中<SID>是实例的名称。BACKGROUND_DUMP_DEST参数是动态的。
SQL> show parameter background_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/dai
kindb/DAIKINDB/trace
告警日志以及所有后台跟踪文件都会被写至BACKGROUND_DUMP_DEST参数所指定的目录。
在ORACLE 11g 以及ORACLE 12c中，告警日志文件的位置有了变化。主要是因为引入了ADR(Automatic Diagnostic Repository:一个存放数据库诊断日志、跟踪文件的目录)，关于ADR对应的目录位置可以通过查看v$diag_info系统视图。如下所示（ORACLE 11g ）
SQL>set linesize 200 pages 100 trimspool on numwidth 14
col name format a30
col value format a70
select * from v$diag_info;
INST_ID NAME VALUE
-------------- ------------------------------ ----------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/daikindb/DAIKINDB
1 Diag Trace /u01/app/oracle/diag/rdbms/daikindb/DAIKINDB/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/daikindb/DAIKINDB/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/daikindb/DAIKINDB/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/daikindb/DAIKINDB/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/daikindb/DAIKINDB/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/daikindb/DAIKINDB/trace/DAIKINDB_ora_117813
.trc
1 Active Problem Count 1
1 Active Incident Count 5
11 rows selected.
如上所示，Diag Trace对应的目录为文本格式的告警日志文件所在的目录,而Diag Alert对应的目录为XML格式的警告日志(对应为log_x.xml)
告警日志内容
那么告警日志非常关键与重要，那么告警日志里面包含了那些内容信息呢？告警日志包含了下面一些内容的信息。像一些ORA错误，对于监控数据库有极其重要的作用。
1：所有的内部错误（ORA-600）信息，块损坏错误（ORA-1578）信息，以及死锁错误（ORA-60）信息等。
2：管理操作，例如CREATE、ALTER、DROP语句等，以及数据库启动、关闭以及日志归档的一些信息。
2.1 涉及物理结构的所有操作：例如创建、删除、重命名数据文件与联机重做日志文件的ALTER DATABASE命令，此外还涉及重新分配数据文件大小以及将数据文件联机与脱机的操作。
2.2 表空间操作，例如DROP与CREATE命令，此外还包括为了进行用户管理的备份而将表空间置入和取出热备份模式的操作
3：与共享服务器或调度进程相关功能的消息和错误信息。
4：物化视图的自动刷新过程中出现的错误。
5：动态参数的修改信息。
告警日志监控
通过外部表来查看告警日志文件的内容。相当的方便。然后也是使用定制SQL语句来查询错误信息。
SQL> create or replace directory bdump as '/u01/app/oracle/admin/GSP/bdump';
Directory created.
SQL> create table alert_logs
2 (
3 text varchar2(2000)
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory bdump
9 access parameters
10 (
11 records delimited by newline
12 fields
13 reject rows with all null fields
14 )
15 location
16 (
17 'alert_GSP.log'
18 )
19 )
20 reject limit unlimited;
Table created.
SQL> select * from alert_logs;
TEXT
--------------------------------------------------------------------------------
Thu Aug 7 14:50:28 2014
Thread 1 advanced to log sequence 14
Current log# 1 seq# 14 mem# 0: /u01/app/oracle/oradata/GSP/redo01.log
SQL>
告警日志归档
告警日志文件如果不加管理的话，那么文件会持续增长，有时候文件会变得非常大，不利于读写。一般建议将告警日志按天归档，归档文件保留三个月（视情况而定），下面来看看将告警日志文件归档的两个Shell脚本：
#*************************************************************************
# FileName :alert_log_archive.sh
#*************************************************************************
# Author :Kerry
# CreateDate :2013-07-02
# blogs :www.cnblogs.com/kerrycode
# Description :this script is made the alert log archived every day
#*************************************************************************
#! /bin/bash
# these solved the oracle variable problem.
export ORACLE_SID=gps
export ORACLE_BASE=/u01/app/oracle
date=`date +%Y%m%d`
alert_log_path="$ORACLE_BASE/admin/$ORACLE_SID/bdump"
alert_log_file="alert_$ORACLE_SID.log"
alert_arc_file="alert_$ORACLE_SID.log""."${date}
cd ${alert_log_path};
if [ ! -e "${alert_log_file}" ]; then
echo "the alert log didn't exits, please check file path is correct!";
exit;
fi
if [ -e ${alert_arc_file} ];then
echo "the alert log file have been archived!"
else
cat ${alert_log_file} >> ${alert_arc_file}
cat /dev/null > ${alert_log_file}
fi
其实脚本1和脚本差别不大，仅仅是mv与cat >>的区别
#*************************************************************************
# FileName :alert_log_archive.sh
#*************************************************************************
# Author :Kerry
# CreateDate :2013-07-0
# blogs :www.cnblogs.com/kerrycode
# Description :this script is made the alert log archived every day
#*************************************************************************
#! /bin/bash
# these solved the oracle variable problem.
export ORACLE_SID=gps
export ORACLE_BASE=/u01/app/oracle
date=`date +%Y%m%d`
alert_log_path="$ORACLE_BASE/admin/$ORACLE_SID/bdump"
alert_log_file="alert_$ORACLE_SID.log"
alert_arc_file="alert_$ORACLE_SID.log""."${date}
cd ${alert_log_path};
if [ ! -e "${alert_log_file}" ]; then
echo "the alert log didn't exits, please check file path is correct!";
exit;
fi
if [ -e ${alert_arc_file} ];then
echo "the alert log file have been archived!"
else
mv ${alert_log_file} ${alert_arc_file}
cat /dev/null > ${alert_log_file}
fi
监控告警日志
接下来看看如何监控告警日志文件的ORA错误，这里是采用Perl结合Shell的方式，因为Shell获取错误的时间、行数等不如Perl操作字符串方便。
#**********************************************************************************
# FileName :monitoring_alert_log.pl
#**********************************************************************************
# Author :Kerry
# CreateDate :2013-07-01
# blogs :www.cnblogs.com/kerrycode
# Description :check the alert log and find out the ora error
#**********************************************************************************
# Modified Date Modified User Version Modified Reason
# 2013-07-02 Kerry V01.0.1 add comment for this script
#***********************************************************************************
#! /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 \n" if ($2);
}
}
#**********************************************************************************
# FileName : monitoring_alert_log.sh
#**********************************************************************************
# Author : Kerry
# CreateDate : 2013-07-01
# blogs : www.cnblogs.com/kerrycode
# Description: check the alert log and find out the ora error
#**********************************************************************************
# Modified Date Modified User Version Modified Reason
# 2013-07-02 Kerry V01.0.1 add comment and modified script
#***********************************************************************************
#!/bin/bash
# these solved the oracle variable problem.
export ORACLE_SID=gsp
export ORACLE_BASE=/u01/app/oracle
logfile="/home/oracle/scripts/alter_err_log.txt"
pl_monitoring_alert="/home/oracle/scripts/monitoring_alert_log.pl"
pl_sendmail="/home/oracle/scripts/sendmail.pl"
alert_logfile="$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log"
#delete the old alter error log file
rm -f${logfile}
rm -f${pl_sendmail}
#run the perl and check if exists the ora error
perl ${pl_monitoring_alert} ${alert_logfile}> ${logfile}
#if have no error in alert log then exit the program
if [[ -e "${logfile}" && ! -s "${logfile}" ]]; then
exit;
fi
date_today=`date +%Y_%m_%d`
subject="Monitoring the Oracle Alert logs and find ora errors"
content="Dear All,
The Instance ${ORACLE_SID}\' alert log occured the ora errors ,please see the detail in attachment and take action for it. many thanks!
Oracle Alert Services
"
echo "#!/usr/bin/perl" >> ${pl_sendmail}
echo "use Mail::Sender;" >> ${pl_sendmail}
echo "\$sender = new Mail::Sender {smtp => '10.xxx.xxx.xxx', from => 'xxxx@xxxx.com'}; ">> ${pl_sendmail}
echo "\$sender->MailFile({to => 'kerry@xxxxx.com',">> ${pl_sendmail}
echo "cc=>'konglb@esquel.com'," >> ${pl_sendmail}
echo "subject => '$subject',">> ${pl_sendmail}
echo "msg => '$content',">> ${pl_sendmail}
echo "file => '$logfile'});">> ${pl_sendmail}
perl ${pl_sendmail}
摘录：https://www.cnblogs.com/kerrycode/p/3168662.html