That downloads the current alert log for the specified RDS instance. That's all you really need to get started on a script of your own. Fill in the $variables with whatever values apply to your instance.
/usr/bin/aws rds download-db-log-file-portion --region $region --db-instance-identifier $instanceName --log-file-name trace/alert_$sid.log > $logfile
#!/usr/bin/perl
#
# Title: analyze_log.pl
# Date: Sep 17 2010
# Purpose: Check for new "Tracefile" entries in the alert.log file
# Hisory: 1.0 09/17/2010 * Initial version
# 2.0 08/08/2015 * Updated to pull from AWS
use Carp qw( confess );
$SIG{__DIE__} = \&confess;
$SIG{__WARN__} = \&confess;
use strict;
use File::Basename;
use Sys::Syslog qw(:standard :macros);
# Configuration Vars
my $myName = basename($0);
my $myPath = dirname($0);
my $POS_DIR = $myPath . "/../var";
my $TRIGGER_FILE = $myPath . "/../conf/analyze_log.trigger";
my $IGNORE_FILE = $myPath . "/../conf/analyze_log.ignore";
my $mailRecipient = "to\@whoever.whatever";;
my $FROM_EMAIL = "from\@whoever.whatever";
my $alertRecipient = "to\@whoever.whatever"; #we send this to datadog for alerting
my $sendmail="/usr/sbin/sendmail -t -f$FROM_EMAIL";
my $extraLines = 5; # Number of lines included around error.
my $traceMaxLen = 1000; # Max number of lines included in email
my $true = 1;
my $false = 0;
# Global Vars
my @triggerRules;
my @ignoreRules;
my @traceFiles;
my $lastErrCursor;
my $sid;
#AWS vars
my $instanceName;
my $region; #us-west-2
my $downloadLocation = "/var/log/aws-rds/"; #location to download trace and alert files
########################################################################
## Main
{
my $base;
openlog($myName, "ndelay,pid", LOG_DAEMON);
$sid = $ARGV[0];
$instanceName = $ARGV[1];
$region = $ARGV[2];
die "Oracle SID not specified" unless ($sid);
die "RDS instance name not specified" unless ($instanceName);
die "RDS region not specified" unless ($region);
$ENV{ORACLE_SID} = $sid;
$ENV{ORACLE_BASE} = '/u01/app/oracle';
$ENV{ORACLE_HOME} = '/u01/app/oracle/product/11.2.0/dbhome_1';
$base = $ENV{'ORACLE_BASE'};
die "Oracle Base not specified" unless ($base);
@triggerRules = loadRules($TRIGGER_FILE);
# Add Trigger for trace files
push (@triggerRules, "/(Errors|More info) in file/");
@ignoreRules = loadRules($IGNORE_FILE);
analyzeLog($base);
closelog();
exit 0;
}
########################################################################
## analyzeLog - Read log file and look for problems.
sub analyzeLog($) {
my ($base) = @_;
my $lower_sid = lc($sid);
my $logfile = "$downloadLocation/alert_$sid.log";
`/usr/bin/aws rds download-db-log-file-portion --region $region --db-instance-identifier $instanceName --log-file-name trace/alert_$sid.log > $logfile`;
my $maxPos;
my $oldPos = readPos();
my $cursor;
my ($line, @lines);
@lines = readFile($logfile);
$maxPos = @lines;
if ($maxPos < $oldPos) { # The file has shrunk, start over.
$oldPos = 0;
}
$lastErrCursor = $oldPos;
$cursor = $oldPos;
while ($cursor < $maxPos) {
$line = $lines[$cursor];
if (checkLine($line)) {
reportLine(\@lines, $cursor, $maxPos);
}
$cursor++;
}
mailTraceFiles();
writePos($maxPos);
}
########################################################################
## mailTraceFiles - Mail out trace files.
sub mailTraceFiles () {
my %seen = ();
my $file;
# sometimes the log files aren't there right away, wait a few minutes
sleep(300);
# Mail a trace file only once.
foreach $file (@traceFiles) {
mailFile($file) unless $seen{$file}++;
}
}
########################################################################
## mailFile - Mail file.
sub mailFile ($) {
my ($file) = @_;
my ($line, @lines);
my $i = 0;
my $trace_file;
#download file here
`/usr/bin/aws rds download-db-log-file-portion --region $region --db-instance-identifier $instanceName --log-file-name $file > $downloadLocation/$file 2>> /tmp/aws-trace-error.log`;
$trace_file = "$downloadLocation/$file";
open (SENDMAIL, "| $sendmail") || die "can't open $sendmail: $!\n";
print SENDMAIL "To: $mailRecipient\n";
print SENDMAIL "From: $FROM_EMAIL\n";
print SENDMAIL "User-Agent: $myName\n";
print SENDMAIL "Subject: Alert Trace: " . $sid . " - " . basename($file) . " - " . `date` . "\n";
if ( -f $trace_file ) {
open (TRACEFILE, "$trace_file") or die "Could not open '$trace_file' for reading: $!";
foreach $line (<TRACEFILE>) {
chomp($line);
if ($i > $traceMaxLen) {
print SENDMAIL "-- File Truncated --\n";
last;
}
print SENDMAIL $line . "\n";
$i++
}
close (TRACEFILE);
} else {
print SENDMAIL "File '$trace_file' not found.\n";
}
close (SENDMAIL);
}
########################################################################
## checkLine - Check the line for problems.
sub checkLine ($) {
my ($line) = @_;
my ($rule, $result);
my $ret = $false;
foreach $rule (@ignoreRules) {
$result = eval 'if ($line =~ ' . $rule . ') { return 1; } else { return 0; }';
if ($@ ne "") {
die "error executing rule ($rule) on line ($line): $@";
}
if ($result) {
goto CONT;
}
}
foreach $rule (@triggerRules) {
$result = eval 'if ($line =~ ' . $rule . ') { return 1; } else { return 0; }';
if ($@ ne "") {
die "error executing rule ($rule) on line ($line): $@";
}
if ($result) {
$ret = $true;
}
}
# Check for trace files
if ($line =~ /^(Errors|More info|ORA-00060: Deadlock detected\. More info) in file (.*)$/) {
my @parts = split(/\s/, $2);
my $file = $parts[0];
$file =~ s/^.*\/trace/trace/;
$file =~ s/\.$//;
$file =~ s/:$//;
push (@traceFiles, $file);
}
CONT:
return $ret;
}
########################################################################
## reportLine - Report problems.
sub reportLine ($$$) {
my ($lines_ref, $cursor, $maxPos) = @_;
my ($beg, $end);
my $i;
my ($subject, $message);
$beg = $cursor - $extraLines;
$beg = 0 if ($beg < 0);
#Try not to include the last error.
$beg = $lastErrCursor if ($beg < $lastErrCursor);
$lastErrCursor = $cursor + 1;
$end = $cursor + $extraLines;
$end = $maxPos - 1 if ($end > $maxPos - 1);
#Try not to include the next error.
foreach $i (1 .. $extraLines) {
if (checkLine($lines_ref -> [$cursor + $i])) {
$end = $cursor + $i - 1;
last;
}
}
foreach $i ($beg .. $end) {
if ($i == $cursor) {
$subject = $sid . " - " . $lines_ref -> [$i];
$message .= "==> " . $lines_ref -> [$i] . "\n";
#log_warning("==> " . $lines_ref -> [$i]);
} else {
$message .= " " . $lines_ref -> [$i] . "\n";
#log_warning(" " . $lines_ref -> [$i]);
}
}
log_warning($subject, $message);
}
########################################################################
## setEnv - Read in variables and set environment.
sub setEnv ($) {
my ($file) = @_;
my ($line, @lines);
my ($parm, $value);
@lines = readFile($file);
foreach $line (@lines) {
$line =~ s/\t/ /g; # Convert tabs to spaces
$line = trim($line);
if ($line eq "") {
next;
}
if ($line =~ /^#/) {
next;
}
next unless ($line =~ /=/);
$line =~ s/^export//;
$line = trim($line);
($parm, $value) = split (/=/, $line);
if ( $value =~ /^"(.*)"$/ ) {
$value = $1;
}
if ( $value =~ /^'(.*)'$/ ) {
$value = $1;
}
$ENV{$parm} = $value;
}
}
########################################################################
## writePos - Write Position File
sub writePos ($) {
my ($pos) = @_;
my $file = $POS_DIR . "/" . $myName . "-" . $sid . ".loc";
open (FILE, ">$file") or die "Could not open '$file' for writing: $!";
print FILE $pos . "\n";
close (FILE);
}
########################################################################
## readPos - Read Position File
sub readPos () {
my @lines;
my $posFile = $POS_DIR . "/" . $myName . "-" . $sid . ".loc";
my $loc = 0;
if ( -f $posFile ) {
@lines = readFile($posFile);
$loc = $lines[0] * 1; # Make sure it's a number
$loc = 0 if ($loc < 0);
}
return $loc;
}
########################################################################
## readFile - Read file into an array
sub readFile ($) {
my ($file) = @_;
my @array;
my @parts;
@parts = split(/\s/, $file);
$file = $parts[0];
open (FILE, "$file") or die "Could not open '$file' for reading: $!";
@array = <FILE>;
close (FILE);
chomp(@array);
return @array;
}
########################################################################
## trim - Trim leading and trailing whitespace
sub trim($)
{
my $string = shift;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}
########################################################################
## loadRules - Load the ignore and trigger rules.
sub loadRules($)
{
my ($file) = @_;
my ($line, @lines);
my ($test, $result);
my @rules;
@lines = readFile($file);
foreach $line (@lines) {
next if ($line =~ /^#/);
$line =~ s/#.*//g;
$line = trim($line);
next unless ($line);
# test that the rule is a valid regular expression
$result = eval 'if ($test =~ ' . $line . ') { return 1; } else { return 0; }';
if ($@ ne "") {
die "error testing rule ($line): $@";
}
push (@rules, $line);
}
return @rules;
}
########################################################################
## log_warning - Send warning logger message
sub log_warning($$)
{
my ($subject, $message) = @_;
#print "EMERGENCY: " . $message . "\n";
open (SENDMAIL2, "| $sendmail") || die "can't open $sendmail: $!\n";
print SENDMAIL2 "To: $alertRecipient\n";
print SENDMAIL2 "From: $FROM_EMAIL\n";
print SENDMAIL2 "User-Agent: $myName\n";
print SENDMAIL2 "Subject: Alert Trace: " . $subject . "\n";
print SENDMAIL2 $message . "\n";
if($message =~ /\.trc/)
{
print SENDMAIL2 "Trace file emailed to '$mailRecipient'";
}
close (SENDMAIL2);
}
## analyze_log.ignore - Ignore items
###
# Entries in this file should be perl regular expression that are evaluated and
# compared against incoming Oracle alert log entries. When a match is found, the
# corresponding entry is ignored. Regular expressions should be as strict as
# possible to avoid skipping of entries that deserve to be checked.
#
# Entries in this file take presedence over analyze_log.trigger.
#
# Comments are encouraged.
#
# example:
# /message repeated \d+ times/
#
# below is a list of characters that should be escaped and there corresponding
# escaped values and an example
#
# / \/
# ( \(
# ) \)
# [ \[
# ] \]
# * \*
# + \+
# . \.
###
# Ignore "continue" errors.
/Archival Error\. Archiver continuing\./
# Ignore 16166 log writer errors
/ORA-16166/
/Errors in file .+_lgwr_\d+\.trc/
/LGWR: I\/O error 16166 archiving log/
# Ignore ORA-16401 log archiver errors
/ORA-16401/
/Errors in file .+_arc0_\d+\.trc/
# Ignore ORA-19583/19914/28365 backup errors
/ORA-19583/
/ORA-19914/
/ORA-28365/
/Errors in file .+_m000_\d+\.trc/
# Ignore ORA-03113 log archiver errors
/ORA-03113/
/LGWR: Error 3113 creating archivelog file/
/LGWR: Error 3113 disconnecting from destination/
# Ignore RFS errors during Standby resync
/ORA-16040/
/FAL archive, error 16040 creating remote archivelog/
/ORA-16055/
# Ignore ORA-16400: standby destination archive log file is locked
/ORA-16400/
# Ignore ORA-48913 non-critical error, tracefile reached max size
/ORA-48913/
# Ignore TNS errors
/Fatal NI connect error \d+\./
/Tns error struct:/
# Ignore TNS errors
/Fatal NI connect error \d+,/
/opiodr aborting process unknown ospid/
# Ignore when Oracle has limited trace files
/Suppressing further error logging/
# Ignore DataGuard log failures
/ORA-16145/
/Errors in file .+_pr00_\d+\.trc/
# Ignore missing files (AWS RDS should handle this)
/ORA-00308/
/ORA-27037: unable to obtain file status/
/Linux-x86_64 Error: 2: No such file or directory/
## analyze_log.trigger - Error Condition regular expressions.
###
# Entries in this file should be perl regular expression that are evaluated and
# compared against incoming Oracle alert log entries. When a match is found, the
# corresponding entry is escalated and sent to syslog. Regular expressions
# should be as strict as possible to avoid false positives.
#
# Entries in this file are overridden by analyze_log.ignore.
#
# Comments are encouraged.
#
# example:
# /message repeated \d+ times/
#
# below is a list of characters that should be escaped and there corresponding
# escaped values and an example
#
# / \/
# ( \(
# ) \)
# [ \[
# ] \]
# * \*
# + \+
# . \.
###
/ORA-/
/[Ee]rror/
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)