Browse All Articles > Downloading Oracle Log Files in AWS RDS
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.
Before we get started there are 2 things you'll need installed: Perl and the AWS Command Line Interface (https://aws.amazon.com/cli/ ). They have an installer for the AWS CLI for Windows, but if you're using Mac or Linux you'll need python 2.6.5 or higher to install it.
Let's get right to it. The core of the file is all built around this aws cli command:
If you want to get actual alerting on the alert log, this is the script I setup to run every 5 minutes. It determines where it left off in the log using readPos() and writePos(). If it finds a trace file, it also downloads that and emails it to $mailRecipient. Downloading a trace file is the same as downloading the alert log.
Another thing to note are the .ignore and .trigger files, this is how I determine what lines are worth alerting on. I'd recommend alerting on everything at first, and then adding stuff to the ignore file as it comes up. Right now since this is for an RDS instance in AWS most of what we are ignoring we have little/no control over.
I don't recommend copying this directly, but rather using it to give you an idea on how you want to do something like this. Downloading the alert log is simple, the alerting itself (if you want to do it) is where it gets more complicated.
Feel free to comment if there's something you don't understand and I'll try to reply and update the article to better explain.
Let's get right to it. The core of the file is all built around this aws cli command:
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
If you want to get actual alerting on the alert log, this is the script I setup to run every 5 minutes. It determines where it left off in the log using readPos() and writePos(). If it finds a trace file, it also downloads that and emails it to $mailRecipient. Downloading a trace file is the same as downloading the alert log.
Another thing to note are the .ignore and .trigger files, this is how I determine what lines are worth alerting on. I'd recommend alerting on everything at first, and then adding stuff to the ignore file as it comes up. Right now since this is for an RDS instance in AWS most of what we are ignoring we have little/no control over.
I don't recommend copying this directly, but rather using it to give you an idea on how you want to do something like this. Downloading the alert log is simple, the alerting itself (if you want to do it) is where it gets more complicated.
analyze_log.pl
#!/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
## 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
## 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/Feel free to comment if there's something you don't understand and I'll try to reply and update the article to better explain.
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)